[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:
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).
ALTER {PROCEDURE | FUNCTION} [schema.]nume COMPILE;Eliminarea unui subprogram din baza de date se realizează prin:
DROP {PROCEDURE | FUNCTION} [schema.]nume;
-- 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;
-- 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, CNP, 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: 12Singura 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;