PL/SQL Code Examples: Procedures, Functions, and Cursors

Posted on Jan 4, 2025 in Technology

PL/SQL Code Examples

Always do:

set serveroutput on;


Program that adds two numbers


declare
 a number;
 b number;
 suma number;
begin
a:=5;
b:=4;
suma:=a+b;
dbms_output.put_line(suma);
end;


 set serveroutput on



Input of variables from the keyboard:


 declare
  a number;
  b number;
  nom varchar2(40);
  suma number;
 begin
 nom:='&nombre';
 a:=&numero1;
 b:=&numero2;
 suma:=a+b;
 dbms_output.put_line(nom);
 dbms_output.put_line('The sum is: '||suma);
 end;



Compare two numbers


declare
 a number;
 b number;
begin
a:=5;
b:=14;
if (a>b) then
 dbms_output.put_line(a ||' is greater than ' ||b);
else
 dbms_output.put_line(b ||' is greater than ' ||a);
end if;
end;





Output from 1 to 10 vertically:
declare
 i number;
begin
 i:=1;
 while (i<=10) loop
   dbms_output.put_line(i);
   i:=i+1;
 end loop;
end;


Output from 1 to 10 horizontally:


declare
 i number;
 cadena varchar2(30);
begin
 i:=1;
 cadena:='';
 while (i<=10) loop
   cadena:=cadena||' '||i;
   i:=i+1;
 end loop;
dbms_output.put_line(cadena);
end;


With a for loop:


declare
 cadena varchar2(30);
begin
 cadena:='';
 for i in 1..10 loop
   cadena:=cadena||' '||i;
 end loop;
dbms_output.put_line(cadena);
end;


With a loop:


declare
 i number;
 cadena varchar2(30);
begin
 i:=1;
 cadena:='';
 loop
   exit when (i>10);
   cadena:=cadena||' '||i;
   i:=i+1;
 end loop;
dbms_output.put_line(cadena);
end;



Function call (long):


declare
 euros number;
begin
  euros:=Euros_X(1000);
  dbms_output.put_line(euros);
end;


Function call (short)



begin
  dbms_output.put_line(Euros_X(1000));
end;


It can be put as a column of a select


Call a procedure:


1.
 execute Tres_fechas('7/1/14');


2.
begin
 Tres_fechas('7/1/14');
end;


Show from 1 to 10 and their squares:
begin
for i in 1..10 loop
  dbms_output.put_line(i||'-'||i*i); 
end loop;
end;


From 1 to 50 the odd numbers:
declare
 i number;
begin
 i:=1;
 while (i<=50) loop
   dbms_output.put_line(i);
   i:=i+2;
 end loop;
end;



Multiplication table of 7


 CREATE OR REPLACE PROCEDURE Mult_tabla_x
 IS
 BEGIN
  FOR i IN 1..10 LOOP
   dbms_output.put_line('7*'||i||'='||7*i);
  END LOOP;
 END;
 /


execute Mult_tabla_x;


CREATE OR REPLACE PROCEDURE Mult_tabla_x (num number)
IS
BEGIN
 FOR i IN 1..10 LOOP
  dbms_output.put_line(num||'*'||i||'='||num*i);
 END LOOP;
END;


FINAL EXERCISES:
1.
CREATE OR REPLACE PROCEDURE suma (num1 number,num2 number)
IS
s number;
BEGIN
s:=num1+num2; 
dbms_output.put_line(s);
END;
2.
CREATE OR REPLACE PROCEDURE Gira_cadena (cad varchar2)
IS
letra VARCHAR2(1);
paraula VARCHAR2(30):='';
BEGIN
 FOR i IN REVERSE 1..length(cad) LOOP
  letra:=substr(cad,i,1);
  paraula:=paraula||letra;
 END LOOP;
dbms_output.put_line(paraula);
END;


3.
CREATE OR REPLACE FUNCTION fsuma (num1 number,num2 number)
return number
IS
s number;
BEGIN
s:=num1+num2; 
return s;
END;


Function call:
begin
  dbms_output.put_line(fsuma(10,5));
end;



CREATE OR REPLACE FUNCTION fGira_cadena (cad varchar2)
return VARCHAR2
IS
letra VARCHAR2(1);
paraula VARCHAR2(30):='';
BEGIN
 FOR i IN REVERSE 1..length(cad) LOOP
  letra:=substr(cad,i,1);
  paraula:=paraula||letra;
 END LOOP;
return paraula;
END;



4


CREATE OR REPLACE FUNCTION anyo(v_fecha DATE)
return NUMBER
IS
 v_anyo NUMBER;
BEGIN
 v_anyo:=to_number(to_char(v_fecha,'YYYY'));
return v_anyo;
END;




5. call:


begin
  dbms_output.put_line(anyos_completos('21/01/14','01/01/12'));
end;



6.


CREATE OR REPLACE FUNCTION anyos_completos
  (v_fecha1 DATE,v_fecha2 DATE)
return NUMBER
IS
 v_anyo NUMBER;
BEGIN
 v_anyo:=trunc(months_between(v_fecha1,v_fecha2)/12);
return v_anyo;
END;



7.
CREATE OR REPLACE FUNCTION trienios
  (v_fecha1 DATE,v_fecha2 DATE)
return NUMBER
IS
 v_anyo NUMBER;
 v_trienios NUMBER;
BEGIN
  v_trienios:=trunc(anyos_completos(v_fecha1,v_fecha2)/3);
  return v_trienios;
END;


8.
CREATE OR REPLACE PROCEDURE suma5
  (num1 number,num2 number,num3 number default 0,
  num4 number default 0,num5 number default 0)
IS
s number;
BEGIN
s:=num1+num2+num3+num4+num5; 
dbms_output.put_line(s);
END;


9.
CREATE OR REPLACE FUNCTION convierte (cad varchar2)
return VARCHAR2
IS
letra VARCHAR2(1);
desti VARCHAR2(30):='';
BEGIN
 FOR i IN 1..length(cad) LOOP
  letra:=substr(cad,i,1);
  if (letra>='a' and letra<='z') or 
      (letra>='A' and letra<='Z') then
  desti:=desti||letra;
   else
  desti:=desti||' ';
  end if;
 END LOOP;
return desti;
END;


Another if:
if (letra between 'a' and 'z' or letra between 'A' and 'Z')


begin
  dbms_output.put_line(convierte('Ho+++la mund99o qu444e t||@al '));
end;


10.
CREATE OR REPLACE PROCEDURE delemple (num number)
IS
n number;
BEGIN
select count(*) into n from emple where emp_no=num;
if (n=1) then
delete emple where emp_no=num;
dbms_output.put_line('Employee '||num||' deleted');
else
 dbms_output.put_line('Employee '||num||' does not exist');
end if;
END;
/



11.
CREATE OR REPLACE PROCEDURE modloc (num number,vloc varchar2)
IS


n number;
BEGIN


select count(*) into n 
from depart where dept_no=num;


if (n=1) then


 UPDATE DEPART
 SET LOC=vloc
 where dept_no=num;


else
 dbms_output.put_line('the department '||num||' does not exist'); 
end if;
END;



5. next page:



CREATE OR REPLACE PROCEDURE inserta(nom varchar2,lloc varchar2)
IS
 num number;
 cont number;
BEGIN


select count(*)
into cont
from depart
where dnombre=nom and loc=lloc;


if cont=1 then
  dbms_output.put_line('The department '||nom||' already exists in '||lloc);


else
select max(DEPT_NO)+10
into num
from depart;


if num is null then
 num:=10;
end if;


insert into depart values (num,nom,lloc);



end if;
END;




CURSOR WITH FOR — WHEN THE ENTIRE CURSOR IS TRAVERSED:


CREATE OR REPLACE PROCEDURE recorredepart
IS
cursor c is
select dnombre,loc
from depart;
begin
 for v in c loop
   dbms_output.put_line(v.dnombre||' '||v.loc);
 end loop;
end;


CURSOR WITH LOOP


CREATE OR REPLACE PROCEDURE recorredepart
IS
cursor c is
select dnombre,loc
from depart;
v c%ROWTYPE; 
begin
open c;
loop
   fetch c into v;
   exit when c%NOTFOUND;
   dbms_output.put_line(v.dnombre||' '||v.loc);
end loop; 
close c;
end;


CURSOR WITH WHILE;


CREATE OR REPLACE PROCEDURE recorredepart
IS
cursor c is
select dnombre,loc
from depart;
v c%ROWTYPE; 
begin
open c;
fetch c into v;
while c%FOUND loop
   dbms_output.put_line(c%rowcount||v.dnombre||' '||v.loc);
   fetch c into v;
end loop; 
close c;
end;



1.


CREATE OR REPLACE PROCEDURE empleordenats
IS
cursor c is
select apellido,fecha_alt
from emple
order by apellido;
begin
 for v in c loop
   dbms_output.put_line(v.fecha_alt||' '||v.apellido);
 end loop;
end;



2.
// This could be done in a FOR loop
select dnombre,count(*)
from emple,depart
where emple.dept_no=depart.dept_no
group by dnombre




2.
CREATE OR REPLACE PROCEDURE empleperdepart
IS
cursor c is
select * 
from depart;
num number; 
begin
 for v in c loop
   select count(*)
   into num
   from emple
   where dept_no=v.dept_no;
   dbms_output.put_line(v.dnombre ||v.loc|| ' '|| num);
 end loop;
end;



3.
CREATE OR REPLACE PROCEDURE cincosalarios
IS
cursor c is
 select apellido,salario
 from emple
 order by salario desc;
v c%ROWTYPE; 
fila number;
begin


fila:=1;
open c;
fetch c into v;
while fila<6 and c%found loop
   dbms_output.put_line(v.apellido||' '||v.salario);
   fetch c into v;
   fila:=fila+1;
end loop; 
close c;
end;



4.
CREATE OR REPLACE PROCEDURE dosmenoscobran
IS
cursor c is
select emp_no, salario, oficio
from emple
order by oficio,salario;
ofiant varchar2(20):=' ';
contant number:=0;
begin
 for v in c loopcount


   if v.oficio!=ofiant then
     contant:=0;
     dbms_output.put_line('--------------- ');
     dbms_output.put_line(v.oficio);
     dbms_output.put_line('---------------');
     dbms_output.put_line(v.apellido ||' '||v.salario);
   elsif contant<2 then
     dbms_output.put_line(v.apellido ||' '||v.salario);
   end if;
   ofiant:=v.oficio;
   contant:=contant+1;
 end loop;
end;



8.
create or replace procedure subir_salario
(num number, importe number,porcen number) is
cursor c is select emp_no,salario
        from emple
   where dept_no=num;
sumsalari number;
contsalari number;
begin


for v in c loop
  sumsalari:=v.salario+importe;
  contsalari:=v.salario+v.salario*porcen/100;
  if (sumsalari>contsalari) then
    update emple
    set salario=sumsalari
    where emp_no=v.emp_no;
  else
    update emple
    set salario=contsalari
    where emp_no=v.emp_no;
  end if;
end loop;
end;



execute subir_salario(20,100,20);



9.


declare
cursor c is
select apellido,salario,oficio,dnombre,fecha_alt,
      comision,emp_no
from depart,emple
where depart.dept_no=emple.dept_no
order by apellido;
trienios number;
subordinados number;
total number;


begin


for v in c loop
dbms_output.put_line('**************************************');
dbms_output.put_line('Employee liquidation:'||v.apellido);
dbms_output.put_line('Dept:                  '||v.dnombre);
dbms_output.put_line('Job:                  '||v.oficio);
dbms_output.put_line('Salary:               '||v.salario);


trienios:=trunc(((sysdate-v.fecha_alt)/365)/3);


dbms_output.put_line('Seniority bonus:              '||trienios*50);


select count(*)
into subordinados
from emple
where dir=v.emp_no;


dbms_output.put_line('Responsibility bonus:    '||subordinados*100);


if v.comision is null then
  v.comision:=0;
end if;


dbms_output.put_line('Commission:              '||v.comision);