| CATEGORII DOCUMENTE |
Subprograme in PL/SQL
Notiunea de subprogram (procedura sau functie) a fost conceputa cu scopul de a grupa o multime de comenzi SQL cu instructiuni procedurale pentru a construi o unitate logica de tratament.
Unitatile de program ce pot fi create in PL/SQL sunt:
subprograme locale (definite in partea declarativa a unui bloc PL/SQL sau a unui alt subprogram);
subprograme independente (stocate in baza de date si considerate drept obiecte ale acesteia);
subprograme impachetate (definite intr-un pachet care incapsuleaza proceduri si functii).
Procedurile si functiile stocate sunt unitati de program PL/SQL apelabile, care exista ca obiecte in schema bazei de date Oracle Recuperarea unui subprogram (in cazul unei corectii) nu cere recuperarea intregii aplicatii. Subprogramul incarcat in memorie pentru a fi executat, poate fi partajat intre obiectele (aplicatii) care il solicita.
Este important de facut distinctie intre procedurile stocate si procedurile locale (declarate si folosite in blocuri anonime).
Procedurile care sunt declarate si apelate in blocuri anonime sunt temporare. O procedura stocata (creata cu CREATE PROCEDURE sau continuta intr-un pachet) este permanenta in sensul ca ea poate fi invocata printr-un script iSQL*Plus, un subprogram PL/SQL sau un declansator.
Procedurile si functiile stocate, care sunt compilate si stocate in baza de date, nu mai trebuie sa fie compilate a doua oara pentru a fi executate, in timp ce procedurile locale sunt compilate de fiecare data cand este executat blocul care contine procedurile si functiile respective.
Procedurile si functiile stocate pot fi apelate din orice bloc de catre utilizatorul care are privilegiul EXECUTE asupra subprogramului, in timp ce procedurile si functiile locale pot fi apelate numai din blocul care le contine.
Cand este creat un subprogram stocat, utilizand comanda CREATE OR REPLACE, subprogramul este depus in dictionarul datelor. Este depus atat textul sursa, cat si forma compilata (p-code). Cand subprogramul este apelat, p-code este citit de pe disc, este depus in shared pool, unde poate fi accesat de mai multi utilizatori si este executat daca este necesar. El va parasi shared pool conform algoritmului LRU (least recently used).
Subprogramele se pot declara in blocuri PL/SQL, in alte subprograme sau in pachete, dar la sfarsitul sectiunii declarative. La fel ca blocurile PL/SQL anonime, subprogramele contin o parte declarativa, o parte executabila si optional, o parte de tratare a erorilor.
Dezvoltare de subprograme utilizand iSQL*Plus
Oracle9i dispune de o interfata Internet pentru SQL*Plus, care este iSQL*Plus. Se poate utiliza un Web browser pentru conectare la o baza de date Oracle si pentru a executa prin iSQL*Plus orice actiune fezabila cu SQL*Plus
Algoritmul de lucru este urmatorul:
se utilizeaza un editor de texte pentru a crea un script file SQL (extensia este .sql);
se utilizeaza optiunea Browse din meniu pentru a localiza acest fisier;
se utilizeaza optiunea Load Script din meniul afisat pentru a incarca acest script in buffer-ul iSQL*Plus;
se utilizeaza optiunea Execute din meniu pentru executarea codului (implicit, rezultatul codului este afisat pe ecran).
Crearea subprogramelor stocate
se editeaza subprogramul (CREATE PROCEDURE sau CREATE FUNCTION) si se salveaza intr-un script file SQL;
se incarca si se executa acest script file, este compilat codul sursa, se obtine p-code (subprogramul este creat);
se utilizeaza comanda SHOW ERRORS (in iSQL*Plus sau in SQL*Plus) pentru vizualizarea eventualelor erori la compilare ale procedurii care a fost cel mai recent compilata sau SHOW ERRORS PROCEDURE nume pentru orice procedura compilata anterior (nu poate fi invocata o procedura care contine erori de compilare);
se executa subprogramul pentru a realiza actiunea dorita (de exemplu, procedura poate fi executata de cate ori este necesar, utilizand comanda EXECUTE din iSQL*Plus) sau se invoca functia dintr-un bloc PL/SQL.
Cand este apelat subprogramul, motorul PL/SQL executa p-code.
Daca exista erori la compilare si se fac corectiile corespunzatoare, atunci este necesara fie comanda DROP PROCEDURE (respectiv DROP FUNCTION), fie sintaxa OR REPLACE in cadrul comenzii CREATE.
Cand este apelata o procedura PL/SQL, server-ul Oracle parcurge etapele:
Verifica daca utilizatorul are privilegiul sa execute procedura (fie pentru ca el a creat procedura, fie pentru ca i s-a dat acest privilegiu).
Verifica daca procedura este prezenta in shared pool. Daca este prezenta va fi executata, altfel va fi incarcata de pe disc in database buffer cache.
Verifica daca starea procedurii este valida sau invalida. Starea unei proceduri PL/SQL este invalida, fie pentru ca au fost detectate erori la compilarea procedurii, fie pentru ca structura unui obiect s-a schimbat de cand procedura a fost executata ultima oara. Daca starea procedurii este invalida atunci este recompilata automat. Daca nici o eroare nu a fost detectata, atunci va fi executata noua versiune a procedurii.
Daca procedura apartine unui pachet atunci toate procedurile si functiile pachetului sunt de asemenea incarcate in database cache (daca ele nu erau deja acolo). Daca pachetul este activat pentru prima oara intr-o sesiune, atunci server-ul va executa blocul de initializare al pachetului.
Dezvoltare de subprograme utilizand Oracle Procedure Builder
Oracle Procedure Builder permite crearea si depanarea diferitelor unitati de program (aplicatie sau unitati stocate). Subprogramele pot fi editate, create, compilate, salvate, depanate, apelate utilizand facilitatile oferite de acest instrument Oracle, prin intermediul unui editor grafic. Procedure Builder permite dezvoltarea de subprograme PL/SQL care pot fi folosite de aplicatii client si aplicatii server.
Procedure Builder contine cinci componente.
Object Navigator furnizeaza o interfata outline pentru a vizualiza obiecte, relatii intre obiecte, pentru a edita proprietati ale obiectelor. Cu ajutorul acestei componente poate fi listata o ierarhie a tuturor obiectelor ce pot fi accesate in timpul sesiunii utilizatorului.
PL/SQL Interpreter depaneaza si evalueaza codul PL/SQL in timp real.
Program Unit Editor creeaza si editeaza codul PL/SQL sursa (client-side).
Stored Program Unit Editor creeaza si editeaza constructii PL/SQL server-side, listeaza mesajele erorilor generate in timpul compilarii
Database Trigger Editor creeaza si editeaza declansatori baza de date.
Pentru a afisa codul unui subprogram, parametrii acestuia, precum si alte informatii legate de subprogram poate fi utilizata comanda DESCRIBE.
Proceduri PL/SQL
Procedura PL/SQL este un program independent care se gaseste compilat in schema bazei de date Oracle. Cand procedura este compilata, identificatorul acesteia (stabilit prin comanda CREATE PROCEDURE) devine un nume obiect in dictionarul datelor. Tipul obiectului este PROCEDURE.
Sintaxa generala pentru crearea unei proceduri este urmatoarea:
[CREATE [OR REPLACE]] PROCEDURE nume_procedura
[(parametru[, parametru]...)]
[AUTHID ]
[PRAGMA AUTONOMOUS_TRANSACTION;]
[declaratii locale
BEGIN
partea executabila
[EXCEPTION
partea de manuire a exceptiilor
END [nume_procedura];
unde parametrii au urmatoarea forma sintactica:
nume_parametru [IN | OUT [NOCOPY] | IN OUT [NOCOPY]
tip_de_date expresie]
Clauza CREATE permite ca procedura sa fie stocata in baza de date. Cand procedurile sunt create folosind clauza CREATE OR REPLACE, ele vor fi stocate in BD in forma compilata. Daca procedura exista, atunci clauza OR REPLACE va avea ca efect stergerea procedurii si inlocuirea acesteia cu noua versiune. Daca procedura exista, iar OR REPLACE nu este prezent, atunci comanda CREATE va returna eroarea "ORA-955: Name is already used by an existing object".
Clauza AUTHID specifica faptul ca procedura stocata se executa cu drepturile proprietarului (implicit) sau ale utilizatorului curent. De asemenea, aceasta clauza precizeaza daca referintele la obiecte sunt rezolvate in schema proprietarului procedurii sau a utilizatorului curent.
Clauza PRAGMA_AUTONOMOUS_TRANSACTION anunta compilatorul PL/SQL ca aceasta procedura este autonoma (independenta). Tranzactiile autonome permit suspendarea tranzactiei principale, executarea unor instructiuni SQL, commit-ul sau rollback-ul acestor operatii si continuarea tranzactiei principale.
Parametrii formali (variabile declarate in lista parametrilor specificatiei subprogramului) pot sa fie de tipul: %TYPE, %ROWTYPE sau un tip explicit fara specificarea dimensiunii.
Exemplu:
Sa se creeze o procedura stocata care micsoreaza cu o cantitate data (cant) valoarea politelor de asigurare emise de firma ASIROM.
CREATE OR REPLACE PROCEDURE mic (cant IN NUMBER) AS
BEGIN
UPDATE politaasig
SET valoare = valoare - cant
WHERE firma = 'ASIROM
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20010,'nu exista ASIROM')
END;
Daca sunt operatii de reactualizare in subprograme si exista declansatori relativ la aceste operatii care nu trebuie considerati, atunci inainte de apelarea subprogramului declansatorii trebuie dezactivati, urmand ca ei sa fie reactivati dupa ce s-a terminat executia subprogramului. De exemplu, in problema prezentata anterior ar trebui dezactivati declansatorii referitori la tabelul politaasig, apelata procedura mic si in final reactivati acesti declansatori.
ALTER TABLE politaasig DISABLE ALL TRIGGERS;
EXECUTE mic(10000)
ALTER TABLE politaasig ENABLE ALL TRIGGERS;
Exemplu
Sa se creeze o procedura locala prin care se insereaza informatii in tabelul editata_de.
DECLARE
PROCEDURE editare
(v_cod_sursa editata_de.cod_sursa%TYPE,
v_cod_autor editata_de.cod_autor%TYPE)
IS
BEGIN
INSERT INTO editata_de
VALUES (v_cod_sursa,v_cod_autor);
END;
BEGIN
.
editare(75643, 13579); .
END;
Procedurile stocate pot fi apelate:
din corpul altei proceduri sau a unui declansator;
interactiv de utilizator utilizand un instrument Oracle (de exemplu, iSQL Plus
explicit dintr-o aplicatie (de exemplu, SQL*Forms sau utilizarea de precompilatoare).
Utilizarea (apelarea) unei proceduri se poate face:
in iSQL*Plus prin comanda:
EXECUTE nume_procedura [(lista_parametri_actuali
in PL/SQL prin aparitia numelui procedurii urmat de lista parametrilor actuali.
Functii PL/SQL
Functia PL/SQL este similara unei proceduri cu exceptia ca ea trebuie sa intoarca un rezultat. O functie fara comanda RETURN va genera eroare la compilare.
Cand functia este compilata, identificatorul acesteia devine obiect in dictionarul datelor avand tipul FUNCTION. Algoritmul din interiorul corpului subprogramului functie trebuie sa asigure ca toate traiectoriile sale conduc la comanda RETURN. Daca o traiectorie a algoritmului trimite in partea de tratare a erorilor, atunci handler-ul acesteia trebuie sa includa o comanda RETURN. O functie trebuie sa aiba un RETURN in antet si cel putin un RETURN in partea executabila.
Sintaxa simplificata pentru scrierea unei functii este urmatoarea:
[CREATE [OR REPLACE]] FUNCTION nume_functie
[(parametru[, parametru])]
RETURN tip_de_date
[AUTHID ]
[DETERMINISTIC]
[PRAGMA AUTONOMOUS_TRANSACTION;]
[declaratii locale]
BEGIN
partea executabila
[EXCEPTION
partea de manuire a exceptiilor
END [nume_functie];
Optiunea tip_de_date specifica tipul valorii returnate de functie, tip care nu poate contine specificatii de marime. Daca totusi sunt necesare aceste specificatii se pot defini subtipuri, iar parametrii vor fi declarati de subtipul respectiv.
In interiorul functiei trebuie sa apara RETURN expresie, unde expresie este valoarea rezultatului furnizat de functie. Pot sa fie mai multe comenzi RETURN intr-o functie, dar numai una din ele va fi executata, deoarece dupa ce valoarea este returnata, procesarea blocului inceteaza. Comanda RETURN (fara o expresie asociata) poate sa apara si intr-o procedura. In acest caz, ea va avea ca efect revenirea la comanda ce urmeaza instructiunii apelante.
Optiunea DETERMINISTIC ajuta optimizorul Oracle in cazul unor apeluri repetate ale aceleasi functii, avand aceleasi argumente. Ea asigura folosirea unui rezultat obtinut anterior.
In blocul PL/SQL al unei proceduri sau functii stocate (defineste actiunea efectuata de functie) nu pot fi referite variabile host sau variabile bind.
O functie poate accepta unul sau mai multi parametri, dar trebuie sa returneze o singura valoare. Ca si in cazul procedurilor, lista parametrilor este optionala. Daca subprogramul nu are parametri, parantezele nu sunt necesare la declarare si la apelare.
Exemplu:
Sa se creeze o functie stocata care determina numarul operelor de arta realizate pe panza, ce au fost achizitionate la o anumita data.
CREATE OR REPLACE FUNCTION numar_opere
(v_a IN opera.data_achizitie%TYPE)
RETURN NUMBER AS
alfa NUMBER;
BEGIN
SELECT COUNT(ROWID)
INTO alfa
FROM opera
WHERE material='panza'
AND data_achizitie = v_a;
RETURN alfa;
END numar_opere;
Daca apare o eroare de compilare, utilizatorul o va corecta in fisierul editat si apoi va trimite fisierul modificat nucleului, cu optiunea OR REPLACE.
Sintaxa pentru apelul unei functii este:
[[schema.]nume_pachet] nume_functie [@dblink] [(lista_parametri_actuali
O functie stocata poate fi apelata in mai multe moduri.
Apelarea functiei si atribuirea valorii acesteia intr-o variabila de legatura iSQL*Plus:
VARIABLE val NUMBER
EXECUTE :val := numar_opere(SYSDATE)
PRINT val
Cand este utilizata declaratia VARIABLE, pentru variabilele host de tip NUMBER nu trebuie specificata dimensiunea, iar pentru cele de tip CHAR sau VARCHAR2 valoarea implicita este 1 sau poate fi specificata o alta valoare intre paranteze. PRINT si VARIABLE sunt comenzi iSQL*Plus.
Apelarea functiei intr-o instructiune SQL
SELECT numar_opere(SYSDATE)
FROM dual;
Aparitia numelui functiei intr-o comanda din interiorul unui bloc PL/SQL (de exemplu, intr-o instructiune de atribuire):
ACCEPT data PROMPT 'dati data achizitionare'
DECLARE
num NUMBER;
v_data opera.data_achizitie%TYPE := '&data';
BEGIN
num := numar_opere(v_data);
DBMS_OUTPUT.PUT_LINE('numarul operelor de arta
achizitionate la data' || TO_CHAR(v_data) || este'
|| TO_CHAR(num));
END;
Exemplu:
Sa se creeze o procedura stocata care pentru un anumit tip de opera de arta (dat ca parametru) calculeaza numarul operelor din muzeu de tipul respectiv, numarul de specialisti care au expertizat sau au restaurat aceste opere, numarul de expozitii in care au fost expuse, precum si valoarea nominala totala a acestora.
CREATE OR REPLACE PROCEDURE date_tip_opera
(v_tip opera.tip%TYPE) AS
FUNCTION nr_opere (v_tip opera.tip%TYPE)
RETURN NUMBER IS
v_numar NUMBER(3);
BEGIN
SELECT COUNT(*)
INTO v_numar
FROM opera
WHERE tip = v_tip;
RETURN v_numar;
END;
FUNCTION valoare_totala (v_tip opera.tip%TYPE)
RETURN NUMBER IS
v_numar opera.valoare%TYPE;
BEGIN
SELECT SUM(valoare)
INTO v_numar
FROM opera
WHERE tip = v_tip;
RETURN v_numar;
END;
FUNCTION nr_specialisti (v_tip opera.tip%TYPE)
RETURN NUMBER IS
v_numar NUMBER(3);
BEGIN
SELECT COUNT(DISTINCT studiaza.cod_specialist)
INTO v_numar
FROM studiaza, opera
WHERE studiaza.cod_opera = opera.cod_opera
AND opera.tip = v_tip;
RETURN v_numar;
END;
FUNCTION nr_expozitii (v_tip opera.tip%TYPE)
RETURN NUMBER IS
v_numar NUMBER(3);
BEGIN
SELECT COUNT(DISTINCT figureaza_in.cod_expozitie)
INTO v_numar
FROM figureaza_in, opera
WHERE figureaza_in.cod_opera = opera.cod_opera
AND opera.tip = v_tip;
RETURN v_numar;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Numarul operelor de arta este '||
nr_opere(v_tip));
DBMS_OUTPUT.PUT_LINE('Valoarea oerelor de arta este '||
valoare_totala(v_tip));
DBMS_OUTPUT.PUT_LINE('Numarul de specialisti este '||
nr_specialisti(v_tip));
DBMS_OUTPUT.PUT_LINE('Numarul de expozitii este '||
nr_expozitii(v_tip);
END;
Instructiunea CALL
O instructiune specifica pentru Oracle9i este comanda CALL care permite apelarea subprogramelor PL SQL stocate (independente sau incluse in pachete) si a subprogramelor Java.
CALL este o comanda SQL care nu este valida intr-un bloc PL/SQL. Poate fi utilizata in PL/SQL doar dinamic, prin intermediul comenzii EXECUTE IMMEDIATE. Pentru executarea acestei comenzi, utilizatorul trebuie sa aiba privilegiul EXECUTE asupra subprogramului. Comanda poate fi executata interactiv din iSQL*Plus. Ea are sintaxa urmatoare:
CALL [schema.]nume_subprogram ([lista_parametri actuali])
[@dblink_nume] [INTO :variabila_host]
Nume_subprogram este numele unui subprogram sau numele unei metode. Clauza INTO este folosita numai pentru variabilele de iesire ale unei functii. Daca clauza @dblink_nume lipseste, sistemul se refera la baza de date locala, iar intr-un sistem distribuit clauza specifica numele bazei care contine subprogramul.
Exemplu:
Sunt prezentate doua exemple prin care o functie PL/SQL este apelata din SQL*Plus, respectiv o procedura externa C este apelata, folosind SQL dinamic, dintr-un bloc PL/SQL.
CREATE OR REPLACE FUNCTION apelfunctie(a IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Apel functie cu ' || a);
RETURN a;
END apelfunctie;
SQL> --apel valid
SQL> VARIABLE v_iesire VARCHAR2(20)
SQL> CALL apelfunctie('Salut!') INTO :v_iesire
Apel functie cu Salut!
Call completed
SQL> PRINT v_iesire
v_iesire
Salut!
DECLARE
a NUMBER(7);
x VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'CALL alfa_extern_procedura (:aa, :xx)'
USING a, x;
END;
Modificarea si suprimarea subprogramelor PL/SQL
Pentru a lua in considerare modificarea unei proceduri sau functii, recompilarea acestora se face prin comanda:
ALTER [schema.]nume COMPILE;
Comanda recompileaza doar procedurile catalogate standard. Procedurile unui pachet se recompileaza intr-o alta maniera.
Ca si in cazul tabelelor, functiile si procedurile pot fi suprimate cu ajutorul comenzii DROP. Aceasta presupune eliminarea subprogramelor din dictionarul datelor. DROP este o comanda ce apartine limbajului de definire a datelor, astfel ca se executa un COMMIT implicit atat inainte, cat si dupa comanda.
Cand este sters un subprogram prin comanda DROP, automat sunt revocate toate privilegiile acordate referitor la acest subprogram. Daca este utilizata sintaxa CREATE OR REPLACE, privilegiile acordate asupra acestui obiect (subprogram) raman aceleasi.
DROP [schema.]nume;
Transferarea valorilor prin parametri
Lista parametrilor unui subprogram este compusa din parametri de intrare (IN), de iesire (OUT), de intrare/iesire (IN OUT), separati prin virgula.
Daca nu este specificat nimic, atunci implicit parametrul este considerat IN. Un parametru formal cu optiunea IN poate primi valori implicite chiar in cadrul comenzii de declarare. Acest parametru este read-only si deci nu poate fi schimbat in corpul subprogramului. El actioneaza ca o constanta. Parametrul actual corespunzator poate fi literal, expresie, constanta sau variabila initializata.
Un parametru formal cu optiunea OUT este neinitializat si prin urmare, are automat valoarea NULL. In interiorul subprogramului, parametrilor cu optiunea OUT sau IN OUT trebuie sa li se asigneze o valoare explicita. Daca nu se atribuie nici o valoare, atunci parametrul actual corespunzator va fi NULL. Parametrul actual trebuie sa fie o variabila, nu poate fi o constanta sau o expresie.
Daca in procedura apare o exceptie, atunci valorile parametrilor formali cu optiunile IN OUT sau OUT nu sunt copiate in valorile parametrilor actuali.
Implicit, transmiterea parametrilor este prin referinta in cazul parametrilor IN si este prin valoare in cazul parametrilor OUT sau IN OUT. Daca pentru realizarea unor performante se doreste transmiterea prin referinta si in cazul parametrilor IN OUT sau OUT atunci se poate utiliza optiunea NOCOPY. Daca optiunea NOCOPY este asociata unui parametru IN, atunci va genera o eroare la compilare deoarece acesti parametri se transmit de fiecare data prin referinta.
Cand este apelata o procedura PL/SQL, sistemul Oracle furnizeaza doua metode pentru definirea parametrilor actuali:
specificarea explicita prin nume;
specificarea prin pozitie.
Exemplu:
CREATE PROCEDURE p1(a IN NUMBER, b IN VARCHAR2,
c IN DATE, d OUT NUMBER) AS.;
Sunt prezentate diferite moduri pentru apelarea acestei proceduri.
DECLARE
var_a NUMBER;
var_b VARCHAR2;
var_c DATE;
var_d NUMBER;
BEGIN
--specificare prin pozitie
p1(var_a,var_b,var_c,var_d);
--specificare prin nume
p1(b=>var_b,c=>var_c,d=>var_d,a=>var_a);
--specificare prin nume si pozitie
p1(var_a,var_b,d=>var_d,c=>var_c);
END;
Exemplu:
Fie proces_data o procedura care proceseaza in mod normal data zilei curente, dar care optional poate procesa si alte date. Daca nu se specifica parametrul actual corespunzator parametrului formal plan_data, atunci acesta va lua automat valoarea data implicit.
PROCEDURE proces_data(data_in IN NUMBER,
plan_data IN DATE:=SYSDATE) IS.
Urmatoarele comenzi reprezinta apeluri corecte ale procedurii proces_data:
proces_data(10);
proces_data(10,SYSDATE+1);
proces_data(plan_data=>SYSDATE+1,data_in=>10);
O declaratie de subprogram (procedura sau functie) fara parametri este specificata fara paranteze. De exemplu, daca procedura react_calc_dur si functia obt_date nu au parametri, atunci:
react_calc_dur; apel corect
react_calc_dur(); apel incorect
data_mea := obt_date; apel corect
Module overload
In anumite conditii, doua sau mai multe module pot sa aiba aceleasi nume, dar sa difere prin lista parametrilor. Aceste module sunt numite module overload (supraincarcate). Functia TO_CHAR este un exemplu de modul overload.
In cazul unui apel, compilatorul compara parametri actuali cu listele parametrilor formali pentru modulele overload si executa modulul corespunzator. Toate programele overload trebuie sa fie definite in acelasi bloc PL/SQL (bloc anonim, modul sau pachet). Nu poate fi definita o versiune intr-un bloc, iar alta versiune intr-un bloc diferit.
Modulele overload pot sa apara in programele PL/SQL fie in sectiunea declarativa a unui bloc, fie in interiorul unui pachet. Supraincarcarea functiilor sau procedurilor nu se poate face pentru functii sau proceduri stocate, dar se poate face pentru subprograme locale, subprograme care apar in pachete sau pentru metode.
Observatii:
Doua programe overload trebuie sa difere, cel putin, prin tipul unuia dintre parametri. Doua programe nu pot fi overload daca parametri lor formali difera numai prin subtipurile lor si daca aceste subtipuri se bazeaza pe acelasi tip de date.
Nu este suficient ca lista parametrilor programelor overload sa difere numai prin numele parametrilor formali.
Nu este suficient ca lista parametrilor programelor overload sa difere numai prin tipul acestora (IN, OUT, IN OUT). PL/SQL nu poate face diferente (la apelare) intre tipurile IN sau OUT.
Nu este suficient ca functiile overload sa difere doar prin tipul datei returnate (tipul datei specificate in clauza RETURN a functiei).
Exemplu:
Urmatoarele subprograme nu pot fi overload.
a) FUNCTION alfa(par IN POSITIVE).;
FUNCTION alfa(par IN BINARY_INTEGER).;
b) FUNCTION alfa(par IN NUMBER).;
FUNCTION alfa(parar IN NUMBER).;
c) PROCEDURE beta(par IN VARCHAR2) IS.;
PROCEDURE beta(par OUT VARCHAR2) IS.;
Exemplu:
Sa se creeze doua functii (locale) cu acelasi nume care sa calculeze media valorilor operelor de arta de un anumit tip. Prima functie va avea un argument reprezentand tipul operelor de arta, iar cea de a doua va avea doua argumente, unul reprezentand tipul operelor de arta, iar celalalt reprezentand stilul operelor pentru care se calculeaza valoarea medie (adica functia va calcula media valorilor operelor de arta de un anumit tip si care apartin unui stil specificat).
DECLARE
medie1 NUMBER(10,2);
medie2 NUMBER(10,2);
FUNCTION valoare_medie (v_tip opera.tip%TYPE)
RETURN NUMBER IS
medie NUMBER(10,2);
BEGIN
SELECT AVG(valoare)
INTO medie
FROM opera
WHERE tip = v_tip;
RETURN medie;
END;
FUNCTION valoare.medie (v_tip opera.tip%TYPE,
v_stil opera.stil%TYPE)
RETURN NUMBER IS
medie NUMBER(10,2);
BEGIN
SELECT AVG(valoare)
INTO medie
FROM opera
WHERE tip = v_tip AND stil = v_stil;
RETURN medie;
END;
BEGIN
medie1 := valoare_medie('pictura');
DBMS_OUTPUT.PUT_LINE('Media valorilor picturilor din
muzeu este ' || medie1);
medie2 := valoare_medie('pictura', 'impresionism');
DBMS_OUTPUT.PUT_LINE('Media valorilor picturilor
impresioniste din muzeu este ' || medie2);
END;
Procedura versus functie
Pot fi marcate cateva deosebiri esentiale intre functii si proceduri.
Procedura se executa ca o comanda PL/SQL, iar functia se invoca ca parte a unei expresii.
Procedura poate returna (sau nu) una sau mai multe valori, iar functia trebuie sa returneze (cel putin) o singura valoare.
Procedura nu trebuie sa contina RETURN tip_date, iar functia trebuie sa contina aceasta optiune.
De asemenea, pot fi marcate cateva elemente esentiale, comune atat functiilor cat si procedurilor. Ambele pot:
accepta valori implicite;
avea sectiuni declarative, executabile si de tratare a erorilor;
utiliza specificarea prin nume sau pozitie a parametrilor;
pot accepta parametri NOCOPY.
Recursivitate
Un subprogram recursiv presupune ca acesta se apeleaza pe el insusi.
In Oracle o problema delicata este legata de locul unde se plaseaza un apel recursiv. De exemplu, daca apelul este in interiorul unui cursor FOR sau intre comenzile OPEN si CLOSE, atunci la fiecare apel este deschis alt cursor. In felul acesta, programul poate depasi limita pentru OPEN_CURSORS setata in parametrul de initializare Oracle.
Exemplu:
Sa se calculeze recursiv al m-lea termen din sirul lui Fibonacci.
FUNCTION fibona(m POSITIVE) RETURN INTEGER IS
BEGIN
IF (m = 1) OR (m = 2) THEN
RETURN 1;
ELSE
RETURN fibona(m-1) + fibona(m-2);
END IF;
END fibona;
Declaratii forward
Subprogramele sunt reciproc recursive daca ele se apeleaza unul pe altul direct sau indirect. Declaratiile forward permit definirea subprogramelor reciproc recursive.
In PL/SQL, un identificator trebuie declarat inainte de a-l folosi. De asemenea, un subprogram trebuie declarat inainte de a-l apela.
PROCEDURE alfa ( ) IS
BEGIN
beta( ); -- apel incorect
END;
PROCEDURE beta ( ) IS
BEGIN
END;
Procedura beta nu poate fi apelata deoarece nu este inca declarata. Problema se poate rezolva simplu, inversand ordinea celor doua proceduri. Aceasta solutie nu este eficienta intotdeauna.
PL/SQL permite un tip special de declarare a unui subprogram numit forward. El consta dintr-o specificare de subprogram terminata prin ";".
PROCEDURE beta ( ); -- declaratie forward
PROCEDURE alfa ( ) IS
BEGIN
beta( );
END;
PROCEDURE beta ( ) IS
BEGIN
END;
Se pot folosi declaratii forward pentru a defini subprograme intr-o anumita ordine logica, pentru a defini subprograme reciproc recursive, pentru a grupa subprograme intr-un pachet.
Lista parametrilor formali din declaratia forward trebuie sa fie identica cu cea corespunzatoare corpului subprogramului. Corpul subprogramului poate aparea oriunde dupa declaratia sa forward, dar trebuie sa ramana in aceeasi unitate de program.
Utilizarea in expresii SQL a functiilor definite de utilizator
Incepand cu Release 7.1, o functie stocata poate fi referita intr-o comanda SQL la fel ca orice functie standard furnizata de sistem (built-in function), dar cu anumite restrictii. Functiile PL/SQL definite de utilizator pot fi apelate din orice expresie SQL in care pot fi folosite functii SQL standard.
Functiile PL/SQL pot sa apara in:
lista de selectie a comenzii SELECT;
conditia clauzelor WHERE si HAVING;
clauzele CONNECT BY, START WITH, ORDER BY si GROUP BY;
clauza VALUES a comenzii INSERT;
clauza SET a comenzii UPDATE.
Exemplu:
Sa se afiseze operele de arta (titlu, valoare, stare) a caror valoare este mai mare decat valoarea medie a tuturor operelor de arta din muzeu.
CREATE OR REPLACE FUNCTION valoare_medie
RETURN NUMBER IS
v_val_mediu opera.valoare%TYPE;
BEGIN
SELECT AVG(valoare)
INTO v_val_mediu
FROM opera;
RETURN v_val_mediu;
END;
Referirea acestei functii intr-o comanda SQL se poate face prin:
SELECT titlu, valoare, stare
FROM opera
WHERE valoare >= valoare_medie;
Exista restrictii referitoare la folosirea functiilor definite de utilizator intr-o comanda SQL.
functia definita de utilizator trebuie sa fie o functie stocata (procedurile stocate nu pot fi apelate in expresii SQL), nu poate fi locala unui alt bloc;
functia apelata dintr-o comanda SELECT, sau din comenzi paralelizate INSERT, UPDATE si DELETE nu poate contine comenzi LMD care modifica tabelele bazei de date;
functia apelata dintr-o comanda UPDATE sau DELETE nu poate interoga sau modifica tabele ale bazei reactualizate chiar de aceste comenzi (table mutating);
functia apelata din comenzile SELECT, INSERT, UPDATE sau DELETE nu poate executa comenzi LCD (COMMIT), ALTER SYSTEM, SET ROLE sau comenzi LDD (CREATE);
functia nu poate sa apara in clauza CHECK a unei comenzi CREATE/ALTER TABLE;
functia nu poate fi folosita pentru a specifica o valoare implicita pentru o coloana in cadrul unei comenzi CREATE/ALTER TABLE;
functia poate fi utilizata intr-o comanda SQL numai de catre proprietarul functiei sau de utilizatorul care are privilegiul EXECUTE asupra acesteia;
functia definita de utilizator, apelabila dintr-o comanda SQL, trebuie sa aiba doar parametri de tip IN, cei de tip OUT si IN OUT nefiind acceptati;
parametrii unei functii PL/SQL apelate dintr-o comanda SQL trebuie sa fie specificati prin pozitie (specificarea prin nume nefiind permisa);
parametrii formali ai unui subprogram functie trebuie sa fie de tip specific bazei de date (NUMBER, CHAR, VARCHAR2, ROWID, LONG, LONGROW, DATE), nu tipuri PL/SQL (BOOLEAN sau RECORD);
tipul returnat de un subprogram functie trebuie sa fie un tip intern pentru server, nu un tip PL/SQL (nu poate fi TABLE, RECORD sau BOOLEAN);
functia nu poate apela un subprogram care nu respecta restrictiile anterioare.
Exemplu:
CREATE OR REPLACE FUNCTION calcul (p_val NUMBER)
RETURN NUMBER IS
BEGIN
INSERT INTO opera(cod_opera, tip, data_achizitie, valoare);
VALUES (1358, 'gravura', SYSDATE, 700000);
RETURN (p_val*7);
END;
UPDATE opera
SET valoare = calcul (550000)
WHERE cod_opera = 7531;
Comanda UPDATE va returna o eroare deoarece tabelul opera este mutating. Reactualizarea este insa permisa asupra oricarui alt tabel diferit de opera.
Informatii referitoare la subprograme
Informatiile referitoare la subprogramele PL/SQL si modul de acces la aceste informatii sunt urmatoarele:
codul sursa, utilizand vizualizarea USER_SOURCE din dictionarul datelor (DD);
informatii generale, utilizand vizualizarea USER_OBJECTS din dictionarul datelor;
tipul parametrilor (IN, OUT, IN OUT), utilizand comanda DESCRIBE din iSQL*Plus;
p-code (nu este accesibil utilizatorilor);
erorile la compilare, utilizand vizualizarea USER_ERRORS din dictionarul datelor sau comanda SHOW ERRORS din iSQL*Plus;
informatii de depanare, utilizand pachetul DBMS_OUTPUT.
Vizualizarea USER_OBJECTS contine informatii generale despre toate obiectele manipulate in BD, in particular si despre subprogramele stocate.
Vizualizarea USER_OBJECTS are urmatoarele campuri:
OBJECT_NAME - numele obiectului;
OBJECT_TYPE, - tipul obiectului (PROCEDURE, FUNCTION etc.);
OBJECT_ID - identificator intern al obiectului;
CREATED - data cand obiectul a fost creat;
LAST_DDL_TIME - data ultimei modificari a obiectului;
TIMESTAMP - data si momentul ultimei recompilari;
STATUS - starea obiectului (VALID sau INVALID).
Pentru a verifica daca recompilarea explicita (ALTER) sau implicita a avut succes se poate verifica starea subprogramelor utilizand USER_OBJECTS.
Orice obiect are o stare (status) sesizata in DD, care poate fi:
VALID (obiectul a fost compilat si poate fi folosit cand este referit);
INVALID (obiectul trebuie compilat inainte de a fi folosit).
Exemplu:
Sa se listeze procedurile si functiile detinute de utilizatorul curent, precum si starea acestora.
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION');
Dupa ce subprogramul a fost creat, codul sursa al acestuia poate fi obtinut consultand vizualizarea USER_SOURCE din DD, care are urmatoarele campuri:
NAME - numele obiectului;
TYPE - tipul obiectului;
LINE - numarul liniei din codul sursa;
TEXT - textul liniilor codului sursa.
Exemplu:
Sa se afiseze codul complet pentru functia numar_opere.
SELECT TEXT
FROM USER_SOURCE
WHERE NAME = 'numar_opere'
ORDER BY LINE;
Exemplu:
Sa se scrie o procedura care recompileaza toate obiectele invalide din schema personala.
CREATE OR REPLACE PROCEDURE sterge IS
CURSOR obj_curs IS
SELECT OBJECT_TYPE, OBJECT_NAME
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN
('PROCEDURE', 'FUNCTION', PACKAGE',
'PACKAGE BODY', 'VIEW');
BEGIN
FOR obj_rec IN obj_curs LOOP
DBMS_DDL.ALTER_COMPILE(obj_rec.OBJECT_TYPE,
USER, obj_rec.OBJECT_NAME);
END
END sterge;
Daca se recompileaza un obiect PL/SQL, atunci server-ul va recompila orice obiect invalid de care depinde.
Daca recompilarea automata implicita a procedurilor locale dependente are probleme, atunci starea obiectului va ramane INVALID si server-ul Oracle semnaleaza eroare. Prin urmare:
este preferabil ca recompilarea sa fie manuala (recompilare explicita utilizand comanda ALTER (PROCEDURE, FUNCTION, TRIGGER, PACKAGE) cu optiunea COMPILE;
este necesar ca recompilarea sa se faca cat mai repede, dupa definirea unei schimbari referitoare la obiectele bazei.
Pentru a obtine valori
(de exemplu, valoarea contorului pentru un
Dependenta subprogramelor
Cand este compilat un subprogram, toate obiectele Oracle care sunt referite vor fi inregistrate in dictionarul datelor. Subprogramul este dependent de aceste obiecte. Un subprogram care are erori la compilare este marcat ca "invalid" in dictionarul datelor. Un subprogram stocat poate deveni, de asemenea, invalid daca o operatie LDD este executata asupra unui obiect de care depinde.
Obiecte dependente Obiecte referite
View, Table Table, Secventa
Procedure View
Function Procedure
Package Specification Function
Package Body Synonym
Database Trigger Package Specification
Obiect definit de utilizator Obiect definit de utilizator
Tip colectie Tip colectie
Daca se modifica definitia unui obiect referit, obiectul dependent poate (sau nu) sa continue sa functioneze normal.
Exista doua tipuri de dependente:
dependenta directa, in care obiectul dependent (de exemplu, procedure sau function) face referinta direct la un table, view, sequence, procedure, function.
dependenta indirecta, in care obiectul dependent (procedure sau function) face referinta indirect la un table, view, sequence, procedure, function prin intermediul unui view, procedure sau function.
In cazul dependentelor locale, cand un obiect referit este modificat, obiectele dependente sunt invalidate. La urmatorul apel al obiectului invalidat, acesta va fi recompilat automat de catre server-ul Oracle.
In cazul dependentelor la distanta, procedurile stocate local si toate obiectele dependente vor fi invalidate. Ele nu vor fi recompilate automat la urmatorul apel.
Exemplu:
Se presupune ca procedura filtru va referi direct tabelul opera si ca procedura adaug va reactualiza tabelul opera prin intermediul unei vizualizari nou_opera.
Pentru aflarea dependentelor directe se poate utiliza vizualizarea USER_DEPENDENCIES din dictionarul datelor.
SELECT NAME, TYPE, REFENCED_NAME, REFENCED_TYPE
FROM USER_DEPENDENCIES
WHERE REFENCED_NAME IN ('opera', 'nou_opera'
NAME TYPE REFENCED_NAME REFENCED_TYPE
filtru Procedure opera Table
adaug Procedure nou_opera View
nou_opera View opera Table
Dependentele indirecte pot fi afisate utilizand vizualizarile DEPTREE si IDEPTREE. Vizualizarea DEPTREE afiseaza o reprezentare a tuturor obiectelor dependente (direct sau indirect). Vizualizarea IDEPTREE afiseaza o reprezentare a aceleasi informatii, sub forma unui arbore.
Pentru a utiliza aceste vizualizari furnizate de sistemul Oracle trebuie:
executat scriptul UTLDTREE;
executata procedura DEPTREE_FILL (are trei argumente: tipul obiectului referit, schema obiectului referit, numele obiectului referit).
Exemplu:
@UTLDTREE
EXECUTE DEPTREE_FILL ('TABLE', 'SCOTT', 'opera')
SELECT NESTED_LEVEL, TYPE, NAME
FROM DEPTREE
ORDER BY SEQ#;
NESTED_LEVEL TYPE NAME
0 Table opera
1 View nou_opera
2 Procedure adaug
1 Procedure filtru
SELECT *
FROM IDEPTREE;
DEPENDENCIES
TABLE nume_schema.opera
VIEW nume_schema.nou_opera
PROCEDURE nume_schema.adaug
PROCEDURE nume_schema.filtru
Dependentele la distanta sunt manipulate prin una din modalitatile alese de utilizator: modelul timestamp (implicit) sau modelul signature.
Fiecare unitate PL/SQL are un timestamp care este setat cand unitatea este modificata (creata sau recompilata) si care este depus in campul LAST_DDL_TIME din dictionarul datelor. Modelul timestamp realizeaza compararea momentelor ultimei modificari a celor doua obiecte analizate. Daca obiectul (referit) bazei are momentul ultimei modificari mai recent ca cel al obiectului dependent, atunci obiectul dependent va fi recompilat.
Modelul signature determina momentul la care obiectele bazei distante trebuie recompilate. Cand este creata o procedura, o signature este depusa in dictionarul datelor, alaturi de p-code. Aceasta contine: numele constructiei PLSQL (PROCEDURE, FUNCTION, PACKAGE), tipurile parametrilor, ordinea parametrilor, numarul acestora si modul de transmitere (IN, OUT, IN OUT). Daca parametrii se schimba, atunci evident signature se schimba. Daca signatura nu se schimba, atunci executia continua.
Recompilarea procedurilor si functiilor dependente este fara succes daca:
obiectul referit este distrus (DROP) sau redenumit (RENAME);
tipul coloanei referite este schimbat;
coloana referita este stearsa;
o vizualizare referita este inlocuita printr-o vizualizare avand alte coloane;
lista parametrilor unei proceduri referite este modificata.
Recompilarea procedurilor si functiilor dependente este cu succes daca:
tabelul referit are noi coloane;
nici o coloana nou definita nu are restrictia NOT NULL;
tipul coloanelor referite nu s-a schimbat;
un tabel 'private' este sters, dar exista un tabel 'public' avand acelasi nume si structura;
toate comenzile INSERT contin efectiv lista coloanelor;
corpul PL/SQL a unei proceduri referite a fost modificat si recompilat cu succes.
Cum pot fi minimizate erorile datorate dependentelor?
utilizand comenzi SELECT cu optiunea
incluzand lista coloanelor in cadrul comenzii INSERT;
declarand variabile cu atributul %TYPE;
declarand inregistrari cu atributul %ROWTYPE.
In concluzie:
Daca procedura depinde de un obiect local, atunci se face recompilare automata la prima reexecutie.
Daca procedura depinde de o procedura distanta, atunci se face recompilare automata, dar la a doua reexecutie. Este preferabila o recompilare manuala pentru prima reexecutie sau implementarea unei strategii de reinvocare a ei (a doua oara).
Daca procedura depinde de un obiect distant, dar care nu este procedura, atunci nu se face recompilare automata.
Rutine externe
PL/SQL a fost special conceput pentru Oracle si este specializat pentru procesarea tranzactiilor SQL.
Totusi, intr-o aplicatie complexa pot sa apara cerinte si functionalitati care sunt mai eficient de implementat in C, Java sau alt limbaj de programare. Daca aplicatia trebuie sa efectueze anumite actiuni care nu pot fi implementate optim utilizand PL/SQL, atunci este preferabil sa fie utilizate alte limbaje care realizeaza performant actiunile respective. In acest caz este necesara comunicarea intre diferite module ale aplicatiei care sunt scrise in limbaje diferite.
Pana la versiunea Oracle8, singura modalitate de comunicare intre PL/SQL si alte limbaje (de exemplu, limbajul C) a fost utilizarea pachetelor DBMS_PIPE si/sau DBMS_ALERT.
Incepand cu Oracle8, comunicarea este simplificata prin utilizarea rutinelor externe. O rutina externa este o procedura sau o functie scrisa intr-un limbaj diferit de PL/SQL, dar apelabila dintr-un program PL/SQL PL/SQL extinde functionalitatea server-ului Oracle, furnizand o interfata pentru apelarea rutinelor externe. Orice bloc PL/SQL executat pe server sau pe client poate apela o rutina externa. Singurul limbaj acceptat pentru rutine externe in Oracle8 era limbajul C.
Pentru a marca apelarea unei rutine externe in programul PL/SQL este definit un punct de intrare (wrapper) care directioneaza spre codul extern (program PL/SQL → wrapper → cod extern). O clauza speciala (AS EXTERNAL) este utilizata (in cadrul comenzii CREATE OR REPLACE PROCEDURE) pentru crearea unui wrapper. De fapt, clauza contine informatii referitoare la numele bibliotecii in care se gaseste subprogramul extern (clauza LIBRARY), numele rutinei externe (clauza NAME) si corespondenta (C <-> PL/SQL) intre tipurile de date (clauza PARAMETERS). Ultimele versiuni renunta la clauza AS EXTERNAL.
Rutinele externe (scrise in C) sunt compilate, apoi depuse intr-o biblioteca dinamica (DLL - dynamic link library) si sunt incarcate doar cand este necesar acest lucru. Daca se invoca o rutina externa scrisa in C, trebuie setata conexiunea spre aceasta rutina. Un proces numit extproc este declansat automat de catre server. La randul sau, procesul extproc va incarca biblioteca identificata prin clauza LIBRARY si va apela rutina respectiva.
Oracle8i permite utilizarea de rutine externe scrise in Java. De asemenea. prin utilizarea clauzei AS LANGUAGE, un wrapper poate include specificatii de apelare. De fapt, aceste specificatii permit apelarea rutinelor externe scrise in orice limbaj. De exemplu, o procedura scrisa intr-un limbaj diferit de C sau Java poate fi utilizata in SQL sau PL/SQL daca procedura respectiva este apelabila din C. In felul acesta, biblioteci standard scrise in alte limbaje de programare pot fi apelate din programe PL/SQL
Procedura PL/SQL executata pe server-ul Oracle poate apela o rutina externa scrisa in C care este depusa intr-o biblioteca partajata.
Procedura C se executa intr-un spatiu adresa diferit de cel al server-ului Oracle, in timp ce unitatile PL/SQL si metodele Java se executa in spatiul de adresa al server-ului. JVM (Java Virtual Machine) de pe pe server va executa metoda Java direct, fara a fi necesar procesul extproc.
Maniera de a incarca depinde de limbajul in care este scrisa rutina (C sau Java).
Pentru a apela rutine externe C, server-ul trebuie sa cunoasca pozitionarea bibliotecii dinamice DLL. Acest lucru este furnizat de alias-ul bibliotecii din clauza AS LANGUAGE.
Pentru apelarea unei rutine externe Java se va incarca clasa Java in baza de date. Este necesara doar crearea unui wrapper care directioneaza spre codul extern. Spre deosebire de rutinele externe C, nu este necesara nici biblioteca si nici setarea conexiunii spre rutina externa.
Clauza LANGUAGE din cadrul comenzii de creare a unui subprogram, specifica limbajul in care este scrisa rutina (procedura externa C sau metoda Java) si are urmatoarea forma:
LANGUAGE
Pentru o procedura C sunt date informatii referitoare la numele acesteia (clauza NAME); alias-ul bibliotecii in care se gaseste (clauza LIBRARY); optiuni referitoare la tipul, pozitia, lungimea, modul de transmitere (prin valoare sau prin referinta) al parametrilor (clauza PARAMETERS); posibilitatea ca rutina externa sa acceseze informatii despre parametri, exceptii, alocarea memoriei utilizator (clauza WITH CONTEXT).
LIBRARY nume_biblioteca [NAME nume_proc_c] [WITH CONTEXT]
[PARAMETERS (parametru_extern [, parametru_extern .] ) ]
Pentru o metoda Java, in clauza trebuie specificata doar signatura metodei (lista tipurilor parametrilor in ordinea aparitiei).
Exemplu
CREATE OR REPLACE FUNCTION calc (x IN REAL) RETURN NUMBER
AS LANGUAGE C
LIBRARY biblioteca
NAME 'c_calc'
PARAMETERS (x BY REFERENCES);
Scrierea 'c_calc' este corecta, iar ' ' implica ca stocarea este case sensitive, altfel implicit se depune numele cu litere mari.
Procedura poate fi apelata dintr-un bloc PL/SQL:
DECLARE
emp_id NUMBER;
procent NUMBER;
BEGIN
.
calc(emp_id, procent);
.
END;
Rutina externa nu este apelata direct, ci se apeleaza subprogramul PL/SQL care refera rutina externa.
Apelarea poate sa apara in: blocuri anonime, subprograme independente sau apartinand unui pachet, metode ale unui tip obiect, declansatori baza de date, comenzi SQL care apeleaza functii (in acest caz trebuie utilizata pragma RESTRICT_REFERENCES).
De remarcat ca o metoda Java poate fi apelata din orice bloc PL/SQL, subprogram sau pachet.
JDBC (Java Database Connectivity), care reprezinta interfata Java standard pentru conectare la baze de date relationale si SQLJ permit apelarea de blocuri PL/SQL din programe Java. SQLJ face posibila incorporarea operatiilor SQL in codul Java. Standardul SQLJ acopera doar operatii SQL statice. Oracle9i SQLJ include extensii pentru a suporta direct SQL dinamic.
O alta modalitate de a incarca programe Java este folosirea interactiva in iSQL*Plus a comenzii: CREATE JAVA instructiune.
Functii tabel
O functie tabel (table function) returneaza drept rezultat un set de linii (de obicei, sub forma unei colectii). Aceasta functie poate fi interogata direct printr-o comanda SQL, ca si cum ar fi un tabel al bazei de date. In felul acesta, functia poate fi utilizata in clauza FROM a unei cereri.
O functie tabel conducta (pipelined table function) este similara unei functii tabel, dar returneaza datele iterativ, pe masura ce acestea sunt obtinute, nu toate deodata. Aceste functii sunt mai eficiente deoarece informatia este returnata imediat cum este obtinuta.
Conceptul de functie tabel conducta a fost introdus in versiunea Oracle9i. Utilizatorul poate sa defineasca astfel de functii. De asemenea, este posibila executia paralela a functiilor tabel (evident si a celor clasice). In acest caz, functia trebuie sa contina in declaratie optiunea PARALLEL_ENABLE.
Functia tabel conducta accepta orice argument pe care il poate accepta o functie obisnuita si trebuie sa returneze o colectie (nested table sau varray). Un parametru input poate fi vector, tabel PL/SQL, REF CURSOR. Ea este declarata specificand cuvantul cheie PIPELINED in comanda CREATE OR REPLACE FUNCTION. Functia tabel conducta trebuie sa se termine printr-o comanda RETURN simpla, care nu intoarce nici o valoare.
Pentru a returna un element individual al colectiei este folosita comanda PIPE ROW, care poate sa apara numai in corpul unei functii tabel conducta, in caz contrar generandu-se o eroare. Comanda poate fi omisa daca functia tabel conducta nu returneaza nici o linie.
Dupa ce functia a fost creata, ea poate fi apelata dintr-o cerere SQL utilizand operatorul TABLE. Cererile referitoare la astfel de functii pot sa includa cursoare si referinte la cursoare, respectandu-se semantica de la cursoarele clasice.
Functia tabel conducta nu poate sa apara in comenzile INSERT, UPDATE, DELETE. Totusi, pentru a realiza o reactualizare, poate fi creata o vizualizare relativa la functia tabel si folosit un declansator INSTEAD OF.
Exemplu
CREATE FUNCTION ff(p SYS_REFCURSOR)
RETURN cartype PIPELINED IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN . END;
In timpul executiei paralele, fiecare instanta a functiei tabel va crea o tranzactie independenta.
Urmatoarele comenzi sunt incorecte.
UPDATE ff(CURSOR (SELECT * FROM tab))
SET col = valoare;
INSERT INTO ff(.)
VALUES('orice', 'vrei');
Exemplu
Sa se obtina o instanta a unui tabel ce contine informatii referitoare la denumirea zilelor saptamanii.
Problema este rezolvata in doua variante. Prima reprezinta o solutie clasica, iar a doua varianta implementeaza problema cu ajutorul unei functii tabel conducta.
Varianta 1
CREATE TYPE t_linie AS OBJECT (
idl NUMBER, sir VARCHAR2(20));
CREATE TYPE t_tabel AS TABLE OF t_linie;
CREATE OR REPLACE FUNCTION calc1 RETURN t_tabel
AS
v_tabel t_tabel;
BEGIN
v_tabel := t_tabel (t_linie (1, 'luni'));
FOR j IN 2..7 LOOP
v_tabel.EXTEND;
IF j = 2
THEN v_tabel(j) := t_linie (2, 'marti');
ELSIF j = 3
THEN v_tabel(j) := t_linie (3, 'miercuri');
ELSIF j = 4
THEN v_tabel(j) := t_linie (4, 'joi');
ELSIF j = 5
THEN v_tabel(j) := t_linie (5, 'vineri');
ELSIF j = 6
THEN v_tabel(j) := t_linie (6, 'sambata');
ELSIF j = 7
THEN v_tabel(j) := t_linie (7, 'duminica');
END IF;
END
RETURN v_tabel;
END calc1;
Functia calc1 poate fi invocata in clauza FROM a unei comenzi SELECT:
SELECT *
FROM TABLE (CAST (calc1 AS t_tabel));
Varianta
CREATE OR REPLACE FUNCTION calc2 RETURN t_tabel PIPELINED
AS
v_linie t_linie;
BEGIN
FOR j IN 1..7 LOOP
v_linie :=
CASE j
WHEN 1 THEN t_linie (1, 'luni')
WHEN 2 THEN t_linie (2, 'marti')
WHEN 3 THEN t_linie (3, 'miercuri')
WHEN 4 THEN t_linie (4, 'joi')
WHEN 5 THEN t_linie (5, 'vineri')
WHEN 6 THEN t_linie (6, 'sambata')
WHEN 7 THEN t_linie (7, 'duminica')
END;
PIPE ROW (v_linie);
END
RETURN;
END calc2;
Se observa ca tabelul este implicat doar in tipul rezultatului. Pentru apelarea functiei calc2 este folosita sintaxa urmatoare:
SELECT * FROM TABLE (calc2);
Functiile tabel sunt folosite frecvent pentru conversii de tipuri de date. Oracle9i introduce posibilitatea de a crea o functie tabel care returneaza un tip PL/SQL (definit intr-un bloc). Functia tabel care furnizeaza (la nivel de pachet) drept rezultat un tip de date trebuie sa fie de tip conducta. Pentru apelare este utilizata sintaxa simplificata (fara CAST).
Procesarea tranzactiilor autonome
Tranzactia este o unitate logica de lucru, adica o secventa de comenzi care trebuie sa se execute ca un intreg pentru a mentine consistenta bazei. In mod uzual, o tranzactie poate sa cuprinda mai multe blocuri, iar intr-un bloc pot sa fie mai multe tranzactii.
O tranzactie autonoma este o tranzactie independenta inceputa de alta tranzactie, numita tranzactie principala. Tranzactia autonoma permite suspendarea tranzactiei principale, executarea de comenzi SQL, commit-ul si rollback-ul acestor operatii.
Odata inceputa, tranzactia autonoma este independenta in sensul ca nu imparte blocari, resurse sau dependente cu tranzactia principala.
In felul acesta, o aplicatie nu trebuie sa cunoasca operatiile autonome ale unei proceduri, iar procedura nu trebuie sa cunoasca nimic despre tranzactiile aplicatiei.
Pentru definirea unei tranzactii autonome trebuie sa se utilizeze pragma AUTONOMOUS_TRANSACTION care informeaza compilatorul PL/SQL sa marcheze o rutina ca fiind autonoma. Prin rutina se intelege: bloc anonim de cel mai inalt nivel (nu incuibarit); procedura sau functie locala, independenta sau impachetata; metoda a unui tip obiect; declansator baza de date.
CREATE PACKAGE exemplu AS
.
FUNCTION autono(x INTEGER) RETURN real;
END exemplu;
CREATE PACKAGE BODY exemplu AS
.
FUNCTION autono(x INTEGER) RETURN real IS
PRAGMA AUTONOMOUS_TRANSACTION;
z real;
BEGIN
.
END;
END exemplu;
Codul PRAGMA AUTONOMOUS_TRANSACTION poate marca numai rutine individuale ca fiind independente. Nu pot fi marcate toate subprogramele unui pachet sau toate metodele unui tip obiect ca autonome. Prin urmare, pragma nu poate sa apara in partea de specificatie a unui pachet. Codul PRAGMA AUTONOMOUS_TRANSACTION se specifica in partea declarativa a rutinei.
Observatii:
Declansatorii autonomi, spre deosebire de cei clasici pot contine comenzi LCD (de exemplu, COMMIT, ROLLBACK).
Exceptiile declansate in tranzactii autonome genereaza un rollback la nivel de tranzactie, nu la nivel de instructiune.
Cand se intra in sectiunea executabila a unei tranzactii autonome, tranzactia principala se suspenda.
Cu toate ca o tranzactie autonoma este inceputa de alta tranzactie, ea nu este o tranzactie incuibarita deoarece:
nu partajeaza resurse cu tranzactia principala;
nu depinde de tranzactia principala (de exemplu, daca tranzactia principala este rollback, atunci tranzactiile incuibarite sunt de asemenea rollback, dar tranzactia autonoma nu este rollback);
schimbarile commit din tranzactii autonome sunt vizibile imediat altor tranzactii, pe cand cele de la tranzactii incuibarite sunt vizibile doar dupa ce tranzactia principala este commit.
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 2962
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
