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.