Interactiunea cu Serverul Oracle
ObiectiveDupa completarea acestei lectii, veti putea realiza urmatoarele: Scrierea unei sintaxe SELECTcorecte Declararea dinamica a tipului datei si dimensiunii a unei variabile în PL/SQL Scrierea unei sintaxe DML în PL/SQL Controlarea tranzactiilor în PL/SQL Determinarea efectului sintaxelor SQL DML |
În aceasta lectie, veti învata sa corelati sintaxe SQL ca SELECT, INSERT, UPDATE si DELETE în blocuri PL/SQL.
Sintaxe SQL în PL/SQLExtragerea unei linii de date din baza de date folosind comanda SELECT. Doar un singur set de valori pot fi returnate. Modificarea liniilor din baza de date folosind comenzi DML Controlarea unei tranzactii cu ajutorul comenzilor COMMIT, ROLLBACK sau SAVEPOINT. Determinarea efectului DML cu cursoare implicite. |
Un bloc PL/SQL nu este o unitate de tranzactie. COMMIT, SAVEPOINT si ROLLBACK-urile sunt independente de blocuri dar se pot intercala in interiorul unui block.
PL/SQL nu suporta limbaj de definitie de date (DDL), cum ar fi: CREATE TABLE, ALETR TABLE sau DROP TABLE.
PL/SQL nu suporta limbaj de control de date (DCL), cum ar fi: GRANT sau REVOKE.
Sintaxe SELECT în PL/SQLReturnarea datei din baza de date cu SELeCTSyntaxa SELECT select_list INTO FROM table WHERE condition; |
Folosirea sintaxie SELECT pentru returnar 12512t1915m ea datei din baza de date:
În sintaxa,
select_list impune o lista de cel putin o coloana, si poate include expresii SQL, functii linie sau functii grup.
variable_name este variabila scalara în care se pastreaza valoarea returnata.
record_name
table specifica numele de tabelului bazei de date.
condition este compusa din nume de coloane, expresii, constante si operatori de comparatie, incluzând variabile si constante PL/SQL.
Observatie: Trebuie profitat de întraga gama a sintaxei Oracle Server pentru SELECT.
Trebuie retinut faptul ca variabilele host trebuie prefixate cu o coloana.
Sintaxe SELECT în PL/SQLClauza INTO este necesaraExemplu DECLARE V_deptno NUMBER(2); V_LOC VARCHAR(15); BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = 'SALES'; END; |
Clauza INTO este obligatorie si apare între clauzele SELECT si FROM. Este folosita pentru a specifica numele variabilelor care retin valorile pe care SQL le returneaza din clauza SELECT. Trebuie data o variabila pentru fiecare obiect selectat, si ordinea lor trebuie sa corespunda cu cea a obiectelor selectate.
Se foloseste clauza INTO pentru a da valori si variabilelor PL/SQL si variabilelor host.
Întrebarile trebuie sa returneze o linie si numai una
Atentie PL/SQL rezolva aceste erori crescând exceptiile standard, care se pot cuprinde în sectiunea de exceptii a blocului cu exceptiile NO_DATA_FOUND si TOO_MANZ_ROWS (mânuirea exceptiilor este tratata într-o lectie anterioara). Se impune codarea sintaxelor SELECT astfel încât acestea sa returneze o singura linie.
Returnarea datelor în PL/SQLReturnarea datei order si datei ship pentru ordinea specificata. Exemplu: DECLARE V_orderdate emp.orderdate%TYPE; V_shipdate emp.shipdate%TYPE; BEGIN SELECT orderdate, shipdate INTO v_orderdate,v_shipdate FROM emp WHERE id = 157; END; |
Algoritm
Se termina fiecare sintaxa SQL cu o semicoloana (
Clauza INTO este necesara pentru sintaxa SELECT când este inclusa în PL/SQL.
Clauza WHERE este optionala si poate fi folosita pentru a specifica variabilele,constantele,comentariile sau expresiile PL/SQL de intrare.
Se specifica acelasi numar de variabile de iesire în clauza INTO ca cel de coloane de date din clauza select. Trebuie asigurata corespondenta pozitionala si compatibilitatea tipurilor de date.
Returnarea datelor în PL/SQLReturnarea sumei salariilor pentru toti angajatii în departamentul specificat Exemplu: DECLARE V_sum_sal emp.sal%TYPE; V_deptno NUMBER NOT NULL := 10; BEGIN SELECT SUM(sal) - group function INTO v_sum_sal FROM emp WHERE deptno = v_deptno; END; |
Algoritm(continuare)
Pentru a ne asigura ca tipurile de date a identificatorilor se potrivesc cu tipurile de date a coloanelor se foloseste atributul %TYPE. Tipul de date si numarul de variabile din clauza INTO se potrivesc celor din lista SELECT.
Se folosesc functii de grupare, ca SUM, într-o sintaxa SQL, deoarece functiile de grupare se aplica grupurilor de linii într-un tabel.
Nota functiile de grupare nu pot fi folosite in sintaxa PL/SQL, ele sunt folosite în sintaxe SQL în ineriorul unui bloc PL/SQL.
Manipularea datelor folosind PL/SQLModificarea bazezor de date folosind comenzi DM: INSERT UPDATE DELETE |
Manipularea datelor în baze de date se realizeaza folosind comenzi (manipulari de date) DML. Se pot folosi comenzi DML ca INSERT, UPDATE si DELETE în PL/SQL. Incluzând sintaxe COMMIT sau ROLLBACK în codul PL/SQL, blocarile liniilor (si blocarile tabelelor) sunt deblocate.
INSERT adauga linii de date noi în tabel.
UPDATE modifica liniile existente în tabel.
DELETE îndeparteza liniile nedorite în tabel.
Inserarea datelorAdaugarea de noi angajati în tabela emp. Exemplu DECLAREV_empno emo.empno%TYPE; BEGIN SELECT empno_sequence.NEXTVAL INTO v_empno FROM dual; INSERT INTO emp(empno, ename, job, deptno) VALUES(v_empno, 'HARDING', 'CLERC', 10); END; |
Folosind functii SQL, cum ar fi USER si SYSDATE.
Generarea valorilor cheie primare folosind secvente de baze de date.
Derivarea valorilor în blocul PL/SQL.
Adaugarea valorilor de pe coloana.
Nota Nu exista nici o posibilitate de ambiguitate cu identificatorii si cu numele de coloana în sintaxa INSERT. Orice identificator din clauza INSERT trebuie sa fie un nume de coloana din baza de date.
ActualizareaMarirea salariilor tuturor salariatilor din tabela emp care sunt Analisti. Exemplu: DECLARE V_sal_increse emp.sal%TYPE := 2000; BEGIN UPDATE SET sal = sal +v_sal_increase WHERE job = 'ANALYST'; END; |
Actualizarea si stergrea datelor
Poate exista ambiguitate în clauza SET din sintaxa UPDATE deoarece desi identificatorul din stânga operatorului de alocare este întotdeauna o coloana din baza de date, identificatorul din dreapta poate fi fie o coloana din baza de date fie o variabila PL/SQL.
De retinut este faptul ca clauza WHERE este folosita pentru a determina care linie afectata. Daca nici o linie nu este modificata, nu apare nici o eroare, spre deosebire de SELECT în PL/SQL.
Nota Alocarile de variabile în PL/SQL folosesc întotdeauna iar alocarile de coloane folosesc întotdeauna =. De retinut este faptul ca daca numele coloanelor si numele identificatorilor sunt identice în clauza WHERE, serverul Oracle cauta dupa nume mai întâi în baza de date.
Stergera datelorStergerea liniilor care apartin departamentului 10 din tabela empExemplu DECLARE V_deptno emp.deptno%TYPE := 10; BEGIN DELETE FROM emp WHERE deptno = v_deptno; END; |
Stergera unei comenzi specificate
DECLARE
v_ordid emp.ordid%TYPE := 605;
BEGIN
DELETE FROM item
WHERE ordid = v_ordid;
END;
Conventii de numireFolosirea unei conventii de numire pentru a evita ambiguitatea în clauza WHERE Coloanele si identificatorii trebuie sa aiba nume distincte Erorile de sintaxa pot aparea deoarece PL/SQL verifica mai întâi baza de date pentru o coloana în tabela |
Ambiguitatea din clauza WHERE este evitata aderând la o conventie de numire care diferentiaza numele coloanelor din baza de date de numele variabilelor PL/SQL.
Exemplu
Returnarea datei comenzii si datei de onorare a comenzii din tabela ord unde data de onorare este astazi. Acest exemplu creste o exceptie necontrolata a timpului de executie.
|
DECLARE order_date ord.orderdate%TYPE;ship_date ord.shipdate%TYPE; v_date DATE := SYSDATE; BEGIN SELECT orderdate, shipdate INTO order_date, ship_date FROM ord WHERE shipdate = v_date; --unhandled exception: NO_DATA_FOUND END; SQL >/ DECLARE ERROR at line 1; ORA-01403: no data found ORA-06512: at line 6 |
Conventii de numireDECLARE order_date ord.orderdate%TYPE;ship_date ord.shipdate%TYPE; v_date DATE := SYSDATE; BEGIN SELECT orderdate, shipdate INTO order_date, ship_date FROM ord WHERE shipdate = v_date END; SQL >/ DECLARE ERROR at line 1; ORA-01403: no data found ORA-06512: at line 6 |
Evitarea ambiguitatii în clauza WHERE se face prin aderarea la o conventie de numire care diferentiaza numele coloanelor din baza de date de numele variabilelor PL/SQL.
Identificatorii si coloanele bazelor de date trebuie sa aiba nume distincte.
Pot apare erori de sintaxa deoarece PL/SQL verifica mai întâi baza de date pentru o coloana din tabela.
Exemplul de mai sus este definit dupa cum urmeaza: returneaza data comenzii si data de onorare a comenzii din tabela ord unde data de onorare este astazi. Acest exemplu creste o exceptie necontrolata a timpului de executie.
Atentie PL/SQL verifica daca un identificator este o coloana în baza da date daca nu, este catalogat ca fiind un identificator PL/SQL.
Nota Nu exista nici o posibilitate de ambiguitate în clauza SELECT deoarece orice identificator din clauza SELECT trebuie sa fie un nume de coloana din baza de date. Nu exista nici o posibilitate de ambiguitate în clauza INTO deoarece orice identificator din clauza INTO trebuie sa fie o variabila PL/SQL. Doar în cadrul clauzei WHERE exista posibilitatea confuzei.
Mai multe informatie asupra NO_DATA_FOUND si asupra altor exceptii sunt acoperite într-o lectie precedenta.
Sintaxele COMMIT si ROLLBACKInitierea unei trnzactii cu prima comanda DML pentru a urmari un COMMIT sau un ROLLBACK. Folosirea sintaxelor SQL COMMIT si ROLLBACK pentru a termina explicitarea unei tranzactii |
Tranzactii controlate
Se poate controla logica unei tranzactii cu sintaxe COMMIT si ROLLBACK, mentinând permanente câteva grupuri de schimbari în baza de date în timp ce se renunta la altele. În cazul serverului Oracle, tranzactiile DML pornesc la prima comanda urmarind un COMMIT sau ROLLBACK si se termina la urmatorul COMIT sau ROLLBACK reusit. Aceste actiuni pot apare în interiorul unui bloc PL/SQL sau ca rezultat a unor evenimente în mediul gazda (de exemplu, închiderea a unei sesiuni SQL Plus automat duce la realizarea tranzactiei dinainte).
Sintaxa COMMIT
COMMIT termina tranzactia curenta facând permanente toate schimbarile dinainte din baza de date.
Sintaxa
|
COMMIT [WORK]; |
|
ROLLBACK [WORK]; |
unde WORK este în conformitate cu standardele ANSI.
Nota Comenzile de control ale tranzactiei sunt toate valide în PL/SQL, desi mediul gazda poate pune unele restrictii asupra folosirii lor.
Se pot include de asemeni comenzi de încuiere (ca LOCK TABLE si SELECT . FOR UPDATE) într-un bloc (o lectie viitoare va contine mai multa informatie asupra comenzii FOR_UPDATE). Aceste comenzi au efect pâna la sfârsitul tranzactiei. De asemeni. Un bloc PL/SQL nu implica în mod necesar o tranzactie.
|
Cursorul SQL Un cursor este o arie de lucru SQL privata Exista doua tipurin de cursor Cursoare implicite Cursoare explicite Serverul Oracle foloseste cursoare implicite pentru a analiza si executa sintaxele SQL. Cursoarele explicite sunt explicit declarate de catre programator. |
Cursorul SQL
Când se executa o sintaxa SQL, serverul Oracle deschide o arie de memorie în fiecare comanda care este analizata si executata. Aceasta arie se numeste cursor.
Când partea executabila a unui bloc emite o sintaxa SQL, PL/SQL creaza un cursor implicit, care are identificatorul SQL. PL/SQL manevreza acest cursor automat. Un cursor explicit este explicit declarat de catre programator.Exista patru atribute disponibile în PL/SQL care pot fi aplicate cursoarelor.
Nota Mai multe informatii asupra cursoarelor explicite sunt cuprinse într-o lectie viitoare.
Bibliografie Pentru mai multe informatii, vezi PL/SQL User s Guide and Reference, Release 8, Interaction with Oracle.
|
Atributele cursoarelor SQL Folosind atributele cursoarelor SQL, se poate testa rezultatele sintaxelor SQL. SQL%ROWCOUNT Numarul de linii afectate de cea mai recenta sintaxa SQL (o valoare întreaga). SQL%FOUND Atributul boolean care este TRUE daca cea mai recenta sintaxa SQL afecteaza una sau mai multe linii. SQL%NOT FOUND Atributul boolean care este TRUE daca cea mai recenta sintaxa SQL nu afecteaza nici o linie. SQL%ISOPEN Este întotdeauna FALSE deoarece PL/SQL închide automat cursoarele implicite imediat dupa ce sunt executate. |
Atributele cursorului SQL
Atributele cursorului SQL sa evaluam ce se întâmpla când cursorul implicit a fost folosit ultimul. Aceste atribute se folosesc în PL/SQL ca functii. Nu pot fi folosite în sintaxe SQL.
Se pot folosi atributele SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND si SQL%ISOPEN în sectiunea de exceptii a unui bloc pentru a aduna informatii despre executia unei sintaxe de manipulare de date. PL/SQL nu considera ca o sintaxa DML care nu afecteaza nici o linie a esuat, spre deosebire de sintaxa SELECT, care returneaza o exceptie.
|
Atributele cursoarelor SQL Stergerea liniilor care au numarul de ordine specificat în tabela ITEM. Afisarea numarului de linii sterse. Exemplu VARIABLE rows_deleted DECLARE V_ordin NUMBER := 605; BEGIN DELETE FROM item WHERE ordin = v_ordin; Rows_deleted := SQL&ROWCOUNT ||' rows_deleted.'); END; PRINT rows_deleted |
|
Sumar Încadrarea SQL în blocuri PL/SQL SELECT, INSERT, UPDATE, DELETE. Încadrarea sintaxelor de control a tranzactiilor într-un bloc PL/SQL COMMIT, ROLLBACK, SAVEPOINT |
|
Sumar Exista doua tipuri de cursor: implicit si explicit. Atributele cursoarelor implicite verifica realizarea sintaxelor DML SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND SQL%ISOPEN Cursoarele explicite sunt definite de catre programator. |
|
Aplicatii practice Crearea unui bloc PL/SQL pentru a selecta date dintr-o tabela Crearea unui bloc PL/SQL pentru a insera date într-o tabela Crearea unui bloc PL/SQL pentru a face actualizarea datelor dintr-o tabela Crearea unui bloc PL/SQL pentru a sterge o înregistrare dintr-o tabela |
Aplicatii practice
Creati un bloc PL/SQL care sa selecteze numarul maxim al departamentului din tabela DEPT si stocarea într-o variabila SQL*plus. Afisati rezultatul pe ecran. Salvati blocul PL/SQL într-un fisier numit p181.sql.
Creati un bloc PL/SQL care sa insereze un nou departament în tabela DEPT. Salvati blocul PL/SQL într-un fisier numit p182.sql.
a) Folositi numarul departamentului returnat la punctul 1.si adunati 10 la acest numar ca fiind numarul de input al departamentului pentru noul departament.
b) Folositi un parametru pentru numele departamentului.
c) Lasati lucatia nula deocamdata.
d) Executati blocul PL/SQL.
e) Afisati noul departament creat.
Creati un bloc PL/SQL care sa actualizeze locatia pentru un departament existent. Salvati blocul PL/SQL într-un fisier numit p18q3.sql.
a) Folositi un parametru pentru numarul departamentului.
b) Folositi un parametru pentru locatia departamentului.
c) Testati blocul PL/SQL.
d) Afisati numarul departamentului, numele departamentului si locatia pentru departamentul updatatat.
e) Afisati departamentul actualizat.
Creati un bloc PL/SQL care sa stearga departamentul creat în exercitiul 2. Salvati blocul PL/SQL într-un fisier numit p18q4.sql.
a) Folosisi un parametru pentru numarul departamentului.
b) Afisati pe ecran numarul liniilor afectate.
c) Testati blocul PL/SQL.
d) Ce se întâmpla daca introduceti un numar de departament care nu exista
e) Verificati daca departamentul a fost sters.
|
