If you want to test your select knowledge.
http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial
Not necessarily database related. So just for fun we did these.
- Simple maths question
i.e. sqrt(a+15) + sqrt(a) = 15 (knew it would be a low number so tried up to 100).
SQL>
1 DECLARE
2 c integer;
3 a integer := 0;
4 x number;
5 BEGIN
6 WHILE a < 100 LOOP
7 SELECT ((sqrt (a+15)) + (sqrt(a))) into x from dual;
8 dbms_output.put_line('loop ' || a ||' value '|| x);
9 IF (x = 15) then dbms_output.put_line('Success........');
10 END IF;
11 a := a+ 1;
12 END LOOP;
13* END;
SQL> /
set server output on
…..
loop 47 value 14.72966247441285514462090589789685682414
loop 48 value 14.865457163469280945614632626941270744
loop 49 value 15
Success........
PL/SQL procedure successfully completed.
So the square root of (49+15) added to the square root of 49 equals 15
- Lotto generator
100 numbers and we would pick six numbers from the output in the range we wanted.
There are thousands of examples out there to do this but at least now we can use our "Lucky Dips" haha
Using
TRUNC or ROUND to alter the precision as required. For example, to produce random integer values between 1 and 59 truncate the output and add 1 to the upper boundary.TRUNC(DBMS_RANDOM.value(1,60))
1 DECLARE
2 c integer;
3 a integer := 0;
4 TYPE ref_cursor IS REF CURSOR;
5 cur REF_CURSOR;
6 BEGIN
7 WHILE a < 100 LOOP
8 OPEN CUR for 'SELECT MOD(trunc(DBMS_RANDOM.Value(1, 60)) from dual';
9 FETCH CUR into c;
10 dbms_output.put_line('value of lotto: ' || c);
11 IF (c = 0) then dbms_output.put_line('value of lotto is Zero');
12 END IF;
13 a := a+ 1;
14 END LOOP;
15 CLOSE CUR;
16* END;
SQL> /
- Sine wave generator
SQL> l
1 declare
2 d integer := 0;
3 e number(7,4);
4 f integer;
5 begin
6 dbms_output.put_line('Sine value');
7 loop
8 d := d + 1;
9 e := ((sin (d *(3.1412857/720))));
10 dbms_output.put_line(e);
11 exit when d >= 180;
12 end loop;
13* end;
Unfortunately, I had to put the output in Excel to see the Sine wave. I tried to use "tput" and asterisks in a shell script but it did not quite work as expected.
If anybody has any strange things that can be done in PLSQL please let me know.
SQL> l
1 declare
2 a integer :=0;
3 x character;
4 begin
5 while a < 128 loop
6 select chr(a) into x from dual;
7 dbms_output.put_line('character ' || a ||' value '|| x);
8 a := a+1;
9 end loop;
10* end;
Variation to count rows from a table every hour for a week
1 declare
2 x integer :=1;
3 y integer;
4 z date;
5 begin
6 while x < 168 loop
7 select count(*) into y from EC_MAIN.API_REQUEST_LOG where INSERTED_DTM < round(SYSDATE-(x/24),'hh');
8 select round(SYSDATE-(x/24),'hh') into z from dual;
9 dbms_output.put_line( z || ' ' || y);
10 x := x+1;
11 end loop;
12* end;
13 /
06-MAY-2019 10:00:00 4243025
06-MAY-2019 09:00:00 4229012
06-MAY-2019 08:00:00 4214438
...etc
e.g. so for 09-10am 4243025 - 4229012 = 14013 rows
Run a count for the whole days (should match the row sum for the 24 hours) :
1* select trunc(inserted_dtm), count(*) from ec_main.api_request_log group by trunc(inserted_dtm) order by 1
2 /
...
06-MAY-2019 00:00:00 258371
...
Ran a double check to show hours :
1 declare
2 x integer :=1;
3 y date;
4 begin
5 while x < 168 loop
6 select round (SYSDATE-(x/24),'hh') into y from dual;
7 dbms_output.put_line( x || '-- ' || y);
8 x := x+1;
9 end loop;
10* end;
SQL> /
1-- 02-MAY-2019 09:00:00
2-- 02-MAY-2019 08:00:00
3-- 02-MAY-2019 07:00:00
4-- 02-MAY-2019 06:00:00
5-- 02-MAY-2019 05:00:00
6-- 02-MAY-2019 04:00:00
7-- 02-MAY-2019 03:00:00
8-- 02-MAY-2019 02:00:00
9-- 02-MAY-2019 01:00:00
.....
....
164-- 25-APR-2019 14:00:00
165-- 25-APR-2019 13:00:00
166-- 25-APR-2019 12:00:00
167-- 25-APR-2019 11:00:00
PL/SQL procedure successfully completed.
