p1 -- PL/SQL is a programming language that is similar -- to ADA and PASCAL. It let's you access ORACLE -- tables. However, now we study the basics. -- Main program with variable and if-then-else declare books integer; begin books := 20; if books = 8820 then dbms_output.put_line('they are equal'); else dbms_output.put_line('***$$$### they are not equal'); end if; end; / p2 -- Better output declare books integer; begin books := 20; if books = 20 then dbms_output.put_line('there are ' || books || ' books'); else dbms_output.put_line('they are not equal'); end if; end; / p3 -- This is a kind of Fibonacci generator. -- It introduces a WHILE loop. -- Also it shows a new output function. declare current integer; next integer; previous integer; begin current := 1; next := 1; while current < 100000 loop dbms_output.put(current); dbms_output.put(' '); previous := current; current := next; next := previous + current; end loop; dbms_output.new_line; end; / p4 -- This is another Fibonacci generator. -- Also shows another kind of comments. /* Note that in this version we generate the initial 1 1 in a different way. */ -- Lastly, this shows a use of elsif. declare previous integer; current integer; next integer; result varchar2(80); counter integer; begin counter := 20; previous := 1; current := 1; result := ''; if counter = 1 then result := ' 1'; elsif counter = 2 then result := ' 1 1 '; else result := ' 1 1 '; while counter > 2 loop next := previous + current; previous := current; current := next; result := result || ' ' || current; counter := counter - 1; end loop; end if; dbms_output.put_line(result); -- All output here. end; / p5 -- A basic procedure with no parameters. -- A procedure is like a function that does not return -- anything. create or replace procedure hello3 as begin dbms_output.put_line('hello_world_33'); end; / -- This is how to call that procedure. -- This is the main program. begin hello3; end; / -- Show where to see procedures. E.g. on SQLdeveloper. p5b -- A procedure call with one parameter. -- This is called an "in" parameter -- because data can be sent ONLY from the main program -- into the procedure. (But not out.) create or replace procedure hello3(num in number) as begin dbms_output.put_line('hello_world_3'); dbms_output.put_line(num); end; / -- This is how to call that procedure. begin hello3(12); end; / p6 -- A basic function declaration for a function with one parameter. create or replace function mysquare(num in number) -- "in parameter" values go INto function return number as begin -- Following line for debugging only! dbms_output.put_line('Inside of function ' || num * num); return num * num; end; / -- This is one way to call that function. declare z number; begin z := mysquare(12); dbms_output.put_line('Outside of function ' || z); end; / p6b -- The same function again. create or replace function mysquare( num in number) return number as begin dbms_output.put_line(num * num); return num * num; end; / -- Another way to call that function. begin dbms_output.put_line(mysquare(15)); end; / p6c -- No, it is not case sensitive -- Same function again. create or replace function mysquare( num in number) return number as begin dbms_output.put_line(num * num); return num * num; end; / -- This is how to call that function. BEGIN dbms_OUTPUT.put_line(MYSQUARE(19)); END; / p6d -- One in parameter and one out parameter. create or replace procedure mytest( num in number, num2 out number) as begin -- num := 21; /* This would cause an error message. */ dbms_output.put_line('Inside of procedure ' || num2); num2 := 55; end; / -- This is how to call that procedure. declare y number; z number; begin y := 888; z := 999; dbms_output.put_line('In main program before call' || y || ' ' || z); mytest(y, z); -- z is changed!! dbms_output.put_line('In main program after call' || y || ' ' || z); end; / p6e -- This adds a parameter that is both in and out at the same time. create or replace procedure mytest( num in number, num2 out number, num3 in out number) as begin -- num := 21; /* This would cause an error message. */ num2 := 55; num3 := 44; end; / -- This is how to call that procedure. declare y number; z number; x number; begin y := 888; z := 999; x := 777; dbms_output.put_line('Before call ' || y || ' ' || z || ' ' || x); mytest(y, z, x); -- z and x are changed!! dbms_output.put_line('After call ' || y || ' ' || z || ' ' || x); end; / p6f -- Out parameters need to be called with variables. create or replace procedure mytest( num in number, num2 out number, num3 in out number) as begin -- num := 21; num2 := 55; num3 := 44; end; / -- This is how to call that procedure. declare z number; x number; begin z := 999; x := 777; mytest(888, z, x); -- IN parameter called with a number; -- mytest(888, 22, x); -- Would cause an error! -- mytest(888, z, 22); -- Out parameters have to be variables. -- In Out parameters have to be variables. dbms_output.put_line( z || ' ' || x); end; / p6g -- Two more data types -- Booleans cannot be output with put_line()! declare b boolean; c number(10,3); begin b := false; -- b := 0; This would give an error message. if b then dbms_output.put_line(2); end if; c := 1234567.891; dbms_output.put_line(c); end; / -- 10 - 3 = 7 is the maximum number of positions -- allowed BEFORE the decimal point. -- 3 is the number of digits after the decimal point.