Proceduri şi funcţii PL/SQL memorate

Sintaxa pentru memorarea unei proceduri sau funcţii, în baza de date, este:
[CREATE [OR REPLACE]] PRECEDURE nume [(def_param [, def_param]...)] {IS | AS}
[declaratii]
BEGIN
  instructiuni
  [EXCEPTION
    tratare exceptii
  ]
END;

[CREATE [OR REPLACE]] FUNCTION nume [(def_param [, def_param]...)] RETURN tip_data {IS | AS}
[declaratii]
BEGIN
  instructiuni, printre care apare si instructiunea: RETURN valoare;
  [EXCEPTION
    tratare exceptii
  ]
END;

Un parametru se defineşte prin: 

denumire [{IN | OUT | IN OUT}] tip_data [ DEFAULT expresie]
deci un argument are: In Oracle fiecare identificator trebuie declarat înainte de folosire. Deoarece este posibilă recursivitatea (directă sau indirectă), este necesară precizarea părţii de definire înainte de descrierea conţinutului:
PROCEDURE nume [(...)];
FUNCTION nume [(...)] RETURN tip_data;
Aceasta este o declaraţie anticipată, urmând ca subprogramul să fie precizat mai târziu (dar cu aceeaşi parte de definire).
Această variantă (precizarea părţii de definire mai repede) se foloseşte mai ales în cazul în care există multe subprograme şi se doreşte definirea lor în ordine alfabetică.
Mai multe subprograme pot să primească acelaşi nume, iar diferenţierea să se facă prin parametri (ca număr sau ca tip). La apel, alegerea subprogramului se face tot prin parametri, deci parametrii nu trebuie să fie din aceeaşi familie (de ex. întreg şi real).
Un subprogram este compilat înainte de execuţie. In baza de date se poate păstra atât formatul sursă, cât şi varianta compilată. Dacă nu se cere efectiv compilarea, atunci la execuţie se face o compilare implicită. Compilarea explicită a unui subprogram memorat se poate efectua prin:
ALTER {PROCEDURE | FUNCTION} [schema.]nume COMPILE;
Eliminarea unui subprogram din baza de date se realizează prin:
DROP {PROCEDURE | FUNCTION} [schema.]nume;

Execuţia subprogramelor

  1. In PL/SQL:
    Observaţie. Transferul valorii unui parametru se poate face prin:
    Observaţie. Dacă la definirea unui subprogram nu s-au precizat parametri formali, atunci la apel nu se folosesc parantezele.
    Observaţie. Parametrii care lipsesc la apelul unui subprogram primesc valoarea implicită specificată la definire. Este indicat ca astfel de parametri să fie pozitionaţi la sfârşitul listei în momentul definirii subprogramelor.
  2. In SQL*PLUS:
  3. In diverse limbaje de programare
Exemple:
-- factorial
set serveroutput on
 
DECLARE
  n number;
  m number;
  i number;
  FUNCTION f(n in number) RETURN NUMBER;
  FUNCTION f(n in number) RETURN NUMBER IS
    BEGIN
     IF n=0 THEN RETURN 1;
     ELSE RETURN n*f(n-1);
     END IF;
    END;
BEGIN
  dbms_output.enable;
  n:=10;
  FOR i IN 1..n LOOP
    m:=f(i);
    dbms_output.put_line('(' || to_char(i) || ')! = ' || to_char(m));
  END LOOP i;
END;

Repartizarea burselor

-- se cere o procedura care repartizeaza bursele la o facultate (studentii sunt grupati pe sectii):
-- numarul total de burse care se repartizeaza: nrtb
-- media minima pentru bursa: med
-- bursele se repartizeaza pe sectii proportional cu numarul de studenti din sectie
-- se foloseste o baza de date unde s-a definit view-ul 
--    infstud[SECTIA, DENUMIRE, ANSTUDIU, GRUPA, NUME, PRENUME,  MEDIA]

create or replace procedure burse (nrtb number, med number) is  
  -- se defineste un cursor cu nr.de studenti din fiecare sectie
  cursor nrstud is 
    select sectia,count(*) as nr 
    from infstud
    group by sectia;
  -- se defineste un cursor cu posibilii bursieri dintr-o sectie
  cursor burse (s number) is
    select nume,prenume,media 
    from infstud
    where sectia=s and media >= med 
    order by media desc,nume,prenume;
  -- se precizeaza datele necesare despre o sectie pentru a se putea repartiza bursele
  type sa is record (sectia number, nrstud number(3), procent number(4,2),
    bursec number(4,1), bursea number(3), supliment number(3), mediamin number(5,2));
  -- se defineste o colectie cu datele necesare pentru fiecare sectie
  type tabel is table of sa;
  t tabel:=tabel();
  -- variabile locale
  nrs integer;  -- nr.de studenti luati in considerare la burse
  i integer:=0;
  j integer;
  r nrstud%rowtype;
  s burse%rowtype;
  st infstud%rowtype;
  s1 integer:=0;
  s2 integer:=0;
  s3 integer:=0;
  s4 integer:=0;
  nrb number;
  nrbs number;
  m number;
begin
  dbms_output.enable;   --datele se vor extrage pe ecran, in SQL*Plus
  select count(*) into nrs from infstud;  --determina numarul de studenti
  for r in nrstud loop  -- se iau toate inregistrarile din nrstud[sectia,nr]
    t.extend;           -- se extinde colectia t cu o pozitie
    i:=i+1;             -- se vor memora date in colectie pe pozitia i
    t(i).sectia:=r.sectia;
    t(i).nrstud:=r.nr;
    t(i).procent:=r.nr/nrs*100;  -- procentul din totalul studentilor
    t(i).bursec:=r.nr/nrs*nrtb;  -- nr.de burse cuvenite
    --dbms_output.put_line(t(i).sectia || ',' || t(i).nrstud || ',' || t(i).procent);
  end loop;
  dbms_output.put_line('Specializarea  Nr.   Burse Burse Supl. Ultima');
  dbms_output.put_line('               stud. nec.  date        medie');
  dbms_output.put_line('--------------------------------------------');
  --repartizarea burselor
  for i in 1..t.count loop
    nrb:=0;  -- nr.de burse repartizate pt.sectie
    m:=0;    -- ultima medie cu bursa
    nrbs:=0;  -- nr.burselor suplimentare pentru ultima medie
    --se parcurg studentii de la o sectie
    for s in burse(t(i).sectia) loop
      if s.media>=med and nrb < round(t(i).bursec) then
        -- media e mai mare decat ultima permisa
        -- mai exista burse nerepartizate
        nrb:=nrb+1;  -- se mai acorda o bursa
        m:=s.media;  -- schimba ultima medie
      else  -- nu mai exista burse
        if s.media=m then nrb:=nrb+1; nrbs:=nrbs+1; end if;
          --mai exista un student cu ultima medie
      end if;
    end loop;
    t(i).mediamin:=m;
    t(i).bursea:=nrb;
    t(i).supliment:=nrbs;
    s3:=s3+nrb;   --suma numarului de burse acordate dupa procent
    s4:=s4+nrbs;   --suma numarului de burse necesare
    dbms_output.put_line(t(i).sectia || to_char(t(i).nrstud,'9999') || 
      to_char(t(i).bursec,'999') || to_char(t(i).bursea,'999') || to_char(t(i).supliment,'999') ||
      to_char(t(i).mediamin,'9999.99'));
  end loop;
  dbms_output.put_line('Nume si prenume                  Specializarea Media');
  dbms_output.put_line('----------------------------------------------------');
  for st in (select * from infstud where media>=med order by nume,prenume) loop
    i:=0;
    for j in 1..t.count loop
      if t(j).sectia=st.sectia then i:=j; end if;
    end loop;
    if st.media>=t(i).mediamin then
      dbms_output.put_line(trim(st.nume) || ' ' || st.prenume || ' - ' || t(i).sectia || to_char(st.media,'999.99'));
    end if;
  end loop;
  exception
    when others then
      dbms_output.put_line(to_char(sqlcode) || '-' || sqlerrm);
end;

Utilizarea acestei proceduri: 

exec burse(300,7);
Se cere o funcţie care evaluează o expresie (ca eval în javascript) (soluţie în On EVAL'ing an expression). 
create or replace function eval (expr varchar2)
  return varchar2
as 
  v varchar2(4000);
begin 
  execute immediate 'begin :1 := ' || expr || '; end;' using out v; 
  return v;
end;

Exemplu de folosire: 

set serveroutput on
declare
  v varchar2(1000);
begin
  v:=to_char(eval('sysdate+1000'));
  dbms_output.put_line(v);
end;

Relativ la declararea tipului argumentelor (nu se pot stabili restricţii), apare următoarea situaţie: On an integer not always being an integer.
Tipul integer este un alias pentru number(38). Dacă argumentul efectiv este de tipul argumentului, atunci nu se fac conversii, dar dacă acest argument este de un tip diferit, atunci se face conversia la tipul precizat în definitie.

create or replace procedure exemplu(i in integer) is
  x number(38);
  y number(10);
begin
  x := i;
  y := i;
  dbms_output.put_line('i: ' || i || ', x: ' || x || ', y: ' || y);
end;
/
exec exemplu(12.34567895);

care va extrage valoarea: 

i: 12.34567895, x: 12.34567895, y: 12
Singura conversie care se face este la "y:=i", care va efectua şi o rotunjire implicită.

Se cere un bloc care determină numărul de înregistrări din fiecare tabel (din schema utilizatorului curent). 

SET serveroutput ON size 1000000
 
DECLARE
  c      VARCHAR2(200);
  nr     NUMBER(10);
  CURSOR tabele IS SELECT table_name FROM user_tables ORDER BY table_name;
  tabel  tabele%ROWTYPE;
BEGIN
  dbms_output.enable;
  FOR tabel IN tabele LOOP
    c:='select count(*) nr from ' || tabel.table_name;
    EXECUTE IMMEDIATE c INTO nr;
    DBMS_OUTPUT.PUT_LINE(tabel.table_name || ': ' || to_char(nr));
  END LOOP;
END;