Documente online.
Zona de administrare documente. Fisierele tale
Am uitat parola x Creaza cont nou
 HomeExploreaza
upload
Upload






























Interactiunea cu Serverul Oracle

Informatica


Interactiunea cu Serverul Oracle

Obiective

Dupa 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

Scopul lectiei

În aceasta lectie, veti învata sa corelati sintaxe SQL ca SELECT, INSERT, UPDATE si DELETE în blocuri PL/SQL.

Sintaxe SQL în PL/SQL

Extragerea 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.

Când este necesara extragerea de informatii din sau efectuarea de modificari asupra bazelor de date, trebuie folosit SQL. PL/SQL suporta limbaj de manipulare de date întregi si comenzi de control a tranzactiei din SQL. Se pot folosi sintaxe SELECT pentru a da variabilelor valori specificate într-o ilnie dintr-un tabel. Comenyile DML pot procesa linii multiple.

Compararea tipurilor de sintaxa în SQL si PL/SQL

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/SQL

Returnarea datei din baza de date cu SELeCT

Syntaxa

SELECT select_list

INTO

FROM table

WHERE condition;

Returnarea datelor folosind PL/SQL

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 este RECORD-ul PL/SQL în care se pastreaza valorile returnate.

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/SQL

Clauza INTO este necesara

Exemplu

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

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/SQL

Returnarea 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/SQL



Returnarea 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/SQL

Modificarea bazezor de date folosind comenzi DM:

INSERT

UPDATE

DELETE

Manipularea datelor folosind PL/SQL

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 datelor

Adaugarea de noi angajati în tabela emp.

Exemplu

DECLARE

V_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;

Inserarea datelor

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.

Actualizarea

Marirea 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 datelor

Stergerea liniilor care apartin departamentului 10 din tabela emp

Exemplu

DECLARE

V_deptno emp.deptno%TYPE := 10;

BEGIN

DELETE FROM emp

WHERE deptno = v_deptno;

END;

Stergera datelor

Stergera unei comenzi specificate

DECLARE



v_ordid emp.ordid%TYPE := 605;

BEGIN

DELETE FROM item

WHERE ordid = v_ordid;

END; 

Conventii de numire

Folosirea 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 numire

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

END;

SQL >/

DECLARE

ERROR at line 1;

ORA-01403: no data found

ORA-06512: at line 6

Conventii de numire

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 ROLLBACK

Initierea 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.





Document Info


Accesari: 2336
Apreciat: hand-up

Comenteaza documentul:

Nu esti inregistrat
Trebuie sa fii utilizator inregistrat pentru a putea comenta


Creaza cont nou

A fost util?

Daca documentul a fost util si crezi ca merita
sa adaugi un link catre el la tine in site


in pagina web a site-ului tau.




eCoduri.com - coduri postale, contabile, CAEN sau bancare

Politica de confidentialitate | Termenii si conditii de utilizare




Copyright © Contact (SCRIGROUP Int. 2024 )