 |
|
 |  |
|
|
| |
 | Biologie | Chimie | Didactica | Fizica | Geografie | Informatica |
| Istorie | Literatura | Matematica | Psihologie |
Procedure Builder este un instrument pe care il putem folosi la crearea, executarea si detecta erorile programelor PL/SQL. Acesta contine un editor pentru crearea sau editarea subprogramelor (procedurilor si functiilor), in care putem compila, testa si verifica codul nostru.
Putem apela procedurile sau functiile create anterior din diferite medii: SQL*Plus, Developer/2000.
O procedura este un bloc PL/SQL, care poate contine parametrii si poate fi apelata pentru a realiza o actiune. Orice procedura are un header, o parte declarativa, o parte executabila si o parte de tratare a exceptiilor.
Sintaxa pentru crearea procedurilor este:
CREATE [OR REPLACE] PROCEDURE procedure_name
(argumentl [model] datatypel,
argument2 [mode2] datatype2,
.
IS [AS]
PL/SQL Block;
unde:
procedure_name -este numele procedurii;
argument -este numele unei variable PL/SQL, a carei valoare ii este atribuita;
mode -este tipul argumentului, si poate fi:
IN (default) - pentru parametri de intrare (este implicit).
OUT - pentru parametri de iesire (trebuie specificat).
IN OUT - pentru parametri de intrare iesire, (trebuie specificat).
datatype -este tipul de data al argumentului.
PL/SQL block -este corpul procedural ce definesc actiunea desfasurata de procedura.
Optiunea REPLACE indica faptul ca, daca procedura exista, ea va fi stearsa si inlocuita cu noua versiune creata.
Crearea unei proceduri se face fie folosind Procedure Builder fie SQL*PLUS. Utilizand SQL*PLUS vom putea crea proceduri si functii stocate pe server. Procedure Builder-ul este o unealta care poate fi folosita pentru a crea, a executa si a depana procedurile si functiile. Deasemenea contine un editor pentru a crea sau a edita subprogramele. Procedure Builder-ul este un mediu de dezvoltare integrat, permitand crearea de proceduri si functii atat pe client cat si pe server.
Pentru a crea proceduri stocate pe server folosind Procedure Builder-ul se parcurg urmatorii pasi:
Se alege File->Connect, se introduce numele utilizatorului, parola si numele bazei de date.
Se expandeaza nodul Database Objects din Object Navigator.
Se expandeaza numele schemei tale.
Se selecteaza nodul Stored Program Units din acea schema.
Se selecteaza optiunea Create din Object Navigator.
Se introduce numele noi proceduri pe care dorim sa o creem in fereastra de dialog New Program Unit.
Se selecteaza butonul OK.
Se introduce codul sursa al procedurii si se salveaza procedura.
Pentru a crea proceduri stocate pe client folosind Procedure Builder-ul se parcurg urmatorii pasi:
Se alege nodul Program Units din Object Navigator.
Se selecteaza optiunea Create din Object Navigator.
Se introduce numele noi proceduri pe care dorim sa o creem in fereastra de dialog New Program Unit. Implicit tipul de subprogram selectat este procedura, deci se poate selecta si altceva de exemplu functia.
Se introduce codul sursa, se compileaza si se elimina daca este cazul eventualele erori.
Se salveaza codul sursa intr-un fisier.
Exemplu: Crearea unei proceduri stocate pe server utilizand SQL*PLUS. Procedura primeste ca si parametru de intrare codul angajatului si modifica salariul tuturor angajatilor cu codul respectiv.
Apelarea acestei proceduri in SQL*PLUS se face utilizand comanda EXECUTE astfel:
SQL> EXECUTE raise_salary (7369)
Acest exemplu prezinta o procedura cu un parametru de intrare IN. Executia acestei proceduri in SQL*PLUS creaza procedura stocata RAISE_SALARY.
Apelul proceduri RAISE_SALARY in Procedure Builder se face direct prin numele procedurii urmat de parametri.
PL/PLUS>raise_salary(7369)
Exemplu: Crearea unei proceduri stocate pe server utilizand SQL*PLUS. Procedura va contine trei parametri de iesire (OUT) si un parametru de intrare. Procedura va afisa numele, salariul si comisionul unui angajat specificat prin codul acestuia (v_id).
Procedura va fi salvata in fiserul emp_query.sql. Pentru a crea pocedura se va lansa urmatoarea comanda:
SQL> START emp_query.sql
Procedure created.
Pentru a afisa valorile parametrilor de iesire vom declara trei variabile in PL*SQL utilizand sintaxa VARIABLE, astfel:
SQL> VARIABLE g_name varchar2(15)
SQL> VARIABLE g_salary number
SQL> VARIABLE g_comm number
Apelam procedura query_emp in PL*SQL folosind comanda EXECUTE, astfel:
SQL> EXECUTE query_emp (7654, :g_name, :g_salary, : g_comm)
PL/SQL procedure successfully completed.
Se observa prefixarea cu : a variabilelor gazda declarate anterior.Vizualizarea valori unei astfel de variabile in PL*SQL se face utilizand comanda PRINT.
SQL> PRINT g_name
G_NAME
MARTIN
Variabilele sunt create in PL*SQL folosind sintaxa VARIABLE, iar referirea lor se face prin prefixarea lor cu ":" in sintaxa EXECUTE.
Crearea variabilelor in Procedure Builder se face folosind sintaxa .CREATE, iar referirea lor se face prin ":". Afisarea valorilor variabilelor se face folosind procedura TEXT_IO din pachetul PUT_LINE. De exemplu, vom crea trei variabile pe care le fom folosi ca si parametri de iesire in procedura raise_salary, dupa care vom afisa valorile acestor variabile.
PL/SQL> .CREATE CHAR g_name LENGTH 10
PL/SQL> .CREATE NUMBER g_salary PRECISION 4
PL/SQL> .CREATE NUMBER g _comm PRECISION 4
PL/SQL> RAISE_SALARY (7654, :g_name, :g_sal, : g_comm) ;
PL/SQL> TEXT_IO.PUT_LINE (:g_name || ' earns ' ||
+> TO_CHAR(:g_sal) || ' and a commission of ' || TO CHAR(:g_comm));
Rezulatul ar fi:
MARTIN earns 1250 and a commission of 1400
Exemplu: Se creaza o procedura cu numele format_phone care are un parametru de intrare/iesire (IN OUT). Procedura primeste un sir de caractere ce contine 10 numere si intoarce un numar de telefon care are urmatorul format (800) NNN-NNNN.
SQL> CREATE OR REPLACE PROCEDURE format_phone
(v_phone_no IN OUT VARCHAR2)
IS
BEGIN
v_phone_no : = '(' || SUBSTR(v_phone_no, l, 3) || ')'
|| SUBSTR(v_phone_no, 4, 3) || ' -' || SUBSTR (v_phone_no, 7) ;
END format_phone:
/
Apelarea proceduri FORMAT_PHONE din SQL*PLUS se realizeaza astfel:
SQL>VARIABLE g_phone_no varchar2(15)
SQL> BEGIN :g_phone_no : '8006330575'; END;
/
PL/SQL procedure successfully completed.
SQL> EXECUTE format_phone (:g_phone_no)
PL/SQL procedure successfully completed.
SQL> PRINT g_phone_no
G_PHONE_NO
Apelarea proceduri FORMAT_PHONE din Procedure Builder se realizeaza astfel:
PL/SQL> .CREATE CHAR g_phone_no LENGTH 15
PL/SQL> BEGIN
+> :g_phone_no : '8006330575';
+> END;
PL/SQL> FORMAT_PHONE (:g_phone_no);
PL/SQL> TEXT_IO.PUT_LINE (:g_phone_no);
Exemplu: Definirea proceduri ADD_DEPT care adauga un tuplu (o inregistare) in tabela DEPT.
SQL>CREATE OR REPLACE PROCEDURE add_dept
(v_name IN dept.dname%TYPE DEFAULT 'unknown'
v_loc IN dept.loc%TYPE DEFAULT 'unknown'
IS
BEGIN
INSERT INTO dept
VALUES (dept_deptno.NEXTVAL,v_name, v_loc)
END add_dept;
Apelarea proceduri ADD_DEPT din SQL*PLUS se realizeaza astfel:
SQL>begin
add_dept ('TRAINING', 'NEW YORK');
add_dept (v_loc => 'DALLAS', v_name => 'EDUCATION');
add_dept (v_loc => 'BOSTON');
end;
PL/SQL procedure successfully completed.
Apelarea proceduri raise_salary dintr-un bloc anonim se realizeaza astfel:
DECLARE
v_id NUMBER :=7900;
BEGIN
raise_salary(v_id); -- se apeleaza procedura
COMMIT;
END;
Stergerea unei proceduri se poate realiza fie utilizand SQL*PLUS fie utilizand Procedure Builder-ul. Utilizand SQL*PLUS putem sterge proceduri stocate pe server iar utilizand Procedure Builder-ul putem sterge atat proceduri stocate pe server cat si pe client.
Stergerea unei proceduri din SQL*PLUS se realizeaza folsind sintaxa:
DROP PROCEDURE procedure_name
Exemplu:
SQL> DROP PROCEDURE raise_salary;
Procedure dropped.
Un rolllback nu mai este posibil dupa executarea unei comenzi DDL (data definition language) cum ar fi DROP PROCEDURE.
O functie stocata este un bloc PL/SQL care poate contine parametrii formali si poate fi apelata. Functiile si procedurile au structuri asemanatoare, singura exceptie fiind aceea ca o functie trebuie sa intoarca o valoare prin apelul ei. Sintaxa pentru crearea unei functii este:
CREATE [OR REPLACE] FUNCTION function_name
(argumentl [model] datatypel,
argument2 [mode2] datatype2,
.
RETURN datatype
IS [AS]
PL/SQL Block;
unde:
function_name - este numele functiei;
argument - este numele unei variable PL/SQL;
mode - este tipul argumentului, poate fi numai IN.
datatype - este tipul de data al argumentului;
RETURN datatype - este tipul de data al valorii returnate de functie.
PL/SQL block - este corpul functiei si defineste actiunea executata de functie.
Optiunea REPLACE indica faptul ca, daca functia exista, ea va fi stearsa si inlocuita cu noua versiune creata.
Crearea unei functi stocate se face fie prin introducerea codului sursa intr-un editor de text si salvat intr-un fisier script (cu extensia .sql) dupa care din SQL*PLUS se ruleaza acel fisier script, fie utilizand Procedure Builder-ul ceea ce permite introducerea codului sursa a functiei in Program Unit Editor, dupa care se compileaza si se salveaza.
Exemplu: Crearea unei functii stocate folosind SQL*PLUS. Functia are denumirea get_sal si contine un parametru de intrare (IN) si va returna un numar. Functia va fi salvata in fisierul get_salary.sql.
SQL> CREATE OR REPLACE FUNCTION get_sal
(v_id IN emp.empno%TYPE)
RETURN NUMBER
IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal
INTO v_salary
FROM emp
WHERE empno = v_id;
11 RETURN (v_salary);
12 END get_sal;
Se ruleaza fisierul get_salary.sql care contine functia get_sal folosind comanda START.
SQL> START get_salary.sql
Procedure created.
Se creeaza o variabila care va contine valoarea pe care o returneza functia get_sal cu comanda VARIABLE in SQL*PLUS.
SQL> VARIABLE g_salary number
Se apeleaza functia get_sal prin comanda EXECUTE.
SQL> EXECUTE :g_salary := get_sal(7934)
PL/SQL procedure successfully completed.
Se afiseaza valoarea returnata de functie in SQL*PLUS pin comanda PRINT.
SQL> PRINT g_salary
G_SALARY
Se observa ca valoarea intoarsa de functie este memorata in variabila g_salary, ce este referita prin ":". Afisarea rezultatului functiei apelate se face folosind sintaxei PRINT.
Utilizand Procedure Builder-ul putem crea functii atat pe latura client cat si pe server. Deasemenea pot fi mutate functii intre client si server si invers.
Exemplu: Crearea unei functii folosind Procedure Builder. Se va crea o functie cu numele tax care are un parametru de intrare si care returneaza un intreg.
Pasii parcursi pentru creerea unei functii folosind Procedure Builder-ul sunt:
Se selecteaza nodul Program Units din Object Navigator.
Se selecteaza optiunea CREATE.
Se introduce numele functiei in fereastra de dialog New Program Units.SE selecteaza radio butonul function.
Se selecteaza OK pentru a valida datele introduse
Se introduce codul sursa al functiei , se compileaza si se salveaza.
FUNCTION tax
(v_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (v_value * .08);
END tax;
Exemplu: Executarea functiilor in Procedure Builder. Executarea functiei tax din Procedure Builder va necesiata parcurgerea urmatorilor pasi:.
Crearea unei variabile folosind comanda CREATE.
PL/SQL> .CREATE NUMBER x PRECISION 4
Apelarea functiei tax intr-o expresie. Valoarea returata de functia tax va fi atribuita variabilei x.
PL/SQL> :x := tax(1000);
Afisarea valori returnate de functia tax folosind procedura PUT_LINE din pachetul TEXT_IO.
PL/SQL> TEXT_IO.PUT_LINE (TO CHAR(:x));
Avantajele functiilor definite de utilizator sunt:
extinde SQL acolo unde activitatile sunt prea complexe, dificile sau neaccesibile cu SQL;
creste eficienta interogarilor: functiile folosite in clauza WHERE pot filtra datele;
manipuleaza siruri de caractere;
ofera executia paralela a interogarilor (utilizand optiunea Parallel Query).
Apelarea unei functii poate fi facuta in:
lista de valori a comenzi SELECT.
in clauzele WHERE sau HAVING.
in clauzele CONNECT BY, START WITH, ORDER BY si GROUP BY.
in clauza VALUES a comenzi INSERT.
in clauza SET a comenzi UPDATE.
Restrictii ale functiilor definite de utilizator in expresiile SQL sunt:
numai functiile stocate pot fi apelate in expresiile SQL;
o functie definita de utilizator trebuie sa fie o functie ROW, si nu o functie GROUP;
o functie definita de utlizator poate lua numai parametrii IN, si nu OUT sau IN OUT;
tipul returnat de functie trebuie sa fie un tip intern serverului Oracle;
functiile nu pot modifica continutul bazei de date, nu se pot executa comenzile INSERT, UPDATE sau DELETE.
apelurile subprogramelor care incalca restrictiile nu sunt permise.
Utilizand SQL*PLUS putem:
sterge o functie de pe server.
Utilizand Procedure Builder putem:
sterge o functie de pe server;
sterge o functie de pe client.
Sintaxa SQL*PLUS pentru stergerea unei functii este:
DROP FUNCTION function_name
Exemplu:Stergerea functiei get_salary.
SQL> DROP FUNCTION get_salary;
Function dropped.
Avantajele functiilor si procedurilor stocate sunt:
imbunatatesc perfomanta;
imbunatatesc mentenanta;
asigura securitatea si integritatea datelor.
Folosind tabelele create in lucrarea 1 si anume DEPT, EMP si SALGRADE sa se scrie folosind limbajul PL/SQL urmatoarele proceduri si functii:
a) Scrie o procedura care primeste ca si parametru de intrare numele angajatului si va returna prin parametri de iesire salarul acestuia.
b) Scrie o procedura care va calcula salariul anual si comisionul anual pentru un angajat specificat dupa nume.
c) Scrie o functie care va returna numarul de ani lucrati pentru o persoana specificata dupa nume.
d) Scrie o procedura care va afisa angajatul cu cel mai mare salar din departamentul lui.
Copyright © 2024 - Toate drepturile rezervate
