· Dacă compilația a avut succes, codul executabil (p-code) va fi stocat în dicționarul bazei de date și funcția poate fi apelată
· Dacă compilația a avut succes și a fost generat codul p, funcția stocată poate fi apelată de la orice instrument care suportă PL / SQL. O funcție stocată poate fi apelată ca parte a unei expresii PL / SQL sau a unei instrucțiuni SQL.
SQL> CREAȚI SAU REPLACE FUNCȚIA emp_sal (v_job IN VARCHAR2)
2 NUMĂRUL RETURNĂ
4 v_sal_inner emp.sal% TYPE;
6 SELECT sal INTO v_sal_inner FROM unde WHERE LOWER (job) = LOWER (v_job);
7 RETURN v_sal_inner;
SQL> VARIABLE h_sal NUMBER
SQL> EXECUTE: h_sal: = emp_sal ("Președinte")
Procedura PL / SQL a fost finalizată cu succes.
SQL> PRINT h_sal
Apelarea funcțiilor stocate în SQL
Funcții memorate pot fi apelate ca parte expresii SQL pentru a efectua calcule complexe, crescând interogarea de performanță SQL (limitarea liniilor de eșantionare cu funcțiile stocate utilizate în clauza WHERE și nu în cerere) sau manipulare complexă de date caracter (de exemplu, codificarea datelor) . Funcțiile stocate pot fi utilizate ori de câte ori pot fi utilizate funcții SQL integrate:
· În lista de selectare SELECT
· În clauzele WHERE, HAVING
· În clauzele ORDER BY, GROUP BY
· În clauza VALUES a comenzii INSERT
· În clauza SET a comenzii UPDATE
SQL> CREATE OR REPLACE FUNCTION pres_sal (v_sal IN NUMBER)
2 NUMĂRUL RETURNĂ
4 v_sal_inner emp.sal% TYPE;
6 SELECT sal INTO v_sal_inner FROM emp unde jos (job) = 'presedinte';
7 RETURN v_sal_inner * 0,1 + v_sal;
SQL> SELECT ename, sal, pres_sal (sal) FROM emp ÎN CARE LOWER (job) = 'funcționar';
ENAME SAL PRES_SAL (SAL)
ADAMS 1100 1600
MILLER 1300 1800
Cu toate acestea, funcțiile stocate utilizate în instrucțiunile SQL trebuie să îndeplinească anumite condiții:
· SQL poate folosi doar funcții memorate, nu proceduri
· Funcția memorată trebuie să fie o linie, nu o grupă
· O funcție stocată poate avea numai parametrii IN. OUT și IN OUT nu sunt permise
· Tipurile de date cu parametri trebuie să fie CHAR, DATE, NUMBER. Tipurile de date BOOLEAN, TABLE, RECORD nu sunt permise
· Tipul retur trebuie să fie un tip de date intern Oracle
· Metoda de transfer a parametrilor poate fi doar pozițională
· Funcția stocată nu poate modifica datele din tabelele bazei de date. Comenzile de manipulare a datelor nu sunt permise INSERT, UPDATE, DELETE
· Nu este permisă apelarea subrutinelor care încalcă condițiile de mai sus
Eliminarea unei funcții stocate
Pentru a șterge o funcție stocată, utilizați comanda FUNCTION DROP DROP.
SQL> DROP FUNCTION emp_sal;
SQL> FUNCȚIE DROP pres_sal;
Compararea procedurilor și funcțiilor
Procedurile stocate, cum ar fi funcțiile stocate, pot returna valorile calculate în mediul de apel. Prin urmare, este necesar să înțelegeți în mod clar diferențele dintre procedură și funcție pentru a alege strategia potrivită la planificarea logicii programului. Creați o procedură pentru a stoca secvența de acțiuni pentru o utilizare ulterioară și a crea o funcție pentru a calcula valoarea care ar trebui returnată mediului de apel. O procedură care conține un parametru OUT poate fi ușor rescrisă ca o funcție
Poate fi apelat din instrucțiunile SQL
Proprietățile procedurilor și funcțiilor stocate
Îmbunătățiți performanța aplicațiilor. Instrucțiunile SQL care apar în rutinele stocate au aceeași sintaxă. Prin urmare, la execuția repetată, expresia SQL deja analizată va fi găsită în zona SQL partajată, ceea ce va reduce semnificativ timpul de răspuns. În plus, rutinele stocate sunt analizate la timpul de compilare, mai degrabă decât execuția. Acest lucru, de asemenea, scurtează timpul de execuție al subrutinei.
Suport îmbunătățit pentru aplicații. Aceeași subrutină poate fi utilizată în diferite aplicații, deci este suficient să modificați această unitate de program în loc să fixați toate aplicațiile în care este utilizată.
Securitatea și integritatea datelor. Pentru a permite unui utilizator să modifice datele printr-o subrutină, trebuie să acordați doar privilegiul de a rula această unitate de program. Acest lucru vă permite să împiedicați accesul direct neautorizat la date, ocolind logica programului aplicației. În plus, subrutina vă permite să efectuați automat acțiuni conexe pentru actualizarea datelor într-o singură tranzacție, care protejează datele împotriva încălcărilor integrității.
Gestionarea subrutinelor stocate
Pentru a crea, executa, modifica și șterge unitățile de programe stocate, trebuie să aveți privilegiile corespunzătoare ale sistemului și ale obiectului.
Pentru ca un utilizator să creeze unități de program în propria schemă, este necesar un privilegiu de sistem: Unitățile de program din propria schemă pot fi recompilate, executate sau șterse, nu sunt necesare privilegii pentru aceasta.
· CREATE (ANY) PROCEDURE - dreptul de a crea unități de programe stocate în propria (sau orice) schemă
· MODIFICAREA ORICE PROCEDURĂ - dreptul de a recompila unitățile de program în orice schemă
· DROP orice procedură - dreptul de a șterge unități de program în orice schemă
· EXECUTAȚI ORICE PROCEDURĂ - dreptul de a executa orice unitate de program
Utilizatorul care a creat subrutina poate acorda cuiva un privilegiu de obiect pentru a executa această unitate de program.
În mod implicit, rutinele stocate și instrucțiunile SQL care sunt apelate din unitatea de program stocată sunt executate cu privilegiile utilizatorului care la creat. Din acest motiv, privilegiile de obiect pe obiectele accesate de subrutine nu sunt necesare dacă obiectele sunt în aceeași schemă ca și subrutina însăși (proprietarul obiectelor are întotdeauna privilegii asupra lor). Pe de o parte, acest lucru este bun, pentru că Pentru a manipula obiecte printr-o unitate de program, este necesar un singur privilegiu pentru a executa această subrutină. Pe de altă parte, astfel de subrutine sunt strâns legate de schema în care sunt stocate, ceea ce poate provoca unele inconveniente. De exemplu, același tabel de dept este localizat simultan în două scheme: scott și blake. și procedura stocată create_dept este creată în schema scott: