| CATEGORII DOCUMENTE |
Scrierea cursorilor expliciti
ObiectiveSa faceti diferenta intre un cursor implicit si unul explicit Sa utilizati o variabila de tip inregistrare PL/SQL Sa scrieti un ciclu FOR pentru un cursor Sa scrieti un cursor cu parametri |
In aceasta lectie veti vedea care sunt diferentele intre un cursor implicit si unul explicit. Veti afla deasemenea cand si de ce sa folositi un cursor explicit.
In practica este nevoie sa folositi comanda SELECT ce intoarce mai multe articole in PL/SQL pentru a parcurge mai multe linii. Pentru a realiza asta trebuie sa declarati si sa controlati cursori expliciti, care sunt folositi in instructiuni repetitive, inclusiv ciclul FOR pentru cursori.
Despre cursoriOrice secventa de instructiuni SQL executata de Serverul Oracle are un cursor asociat cu ea: Cursori impliciti: Declarati pentru toate instructiunile DML si interogarile PL/SQL. Cursori expliciti: Declarati si denumiti de programator. |
Server-ul Oracle foloseste anumite zone de lucru, numite zone private SQL, pentru a executa instructiuni SQL si pentru a stoca informatiile de procesare. Puteti folosi cursorii PL/SQL pentru a numi o zona privata SQL si pentru a accesa informatiile stocate in ea.
|
Tipul cursorului |
Descriere |
|
Implicit |
Cursorii impliciti sunt declarati de PL/SQL in mod implicit pentru toate instructiunile DML si SELECT-urile PL/SQL, inclusiv pentru cele care returneaza o singura inregistrare. |
|
Explicit |
Pentru interogari care returneaza mai mult de o inregistrare. Cursorii expliciti sunt declarati si denumiti de programator si sunt utilizati prin intermediul unor instructiuni specifice in blocul PL/SQL. |
Server-ul Oracle deschide in mod implicit un cursor pentru a procesa fiecare instructiune SQL ce nu a fost asociata cu un cursor declarat explicit. PL/SQL permite referirea la cei mai recent utilizati cursori ca cursori SQL.
Nu puteti folosi instructiunile OPEN, FETCH, si CLOSE pentru a controla cursorii SQL, dar puteti folosi atributele cursorului pentru a obtine informatii legate de cea mai recenta instructiune SQL executata.
SMITH CLERKJONES MANAGER SCOTT ANALYST ADAMS CLERK FORD ANALYST |
|
Articolul curent |
Folositi cursorii expliciti pentru a procesa fiecare articol returnat de o instructiune SELECT cu rezultate multiple.
Setul de articole returnat de o interogare cu rezultate multiple se numeste set de rezultate. Dimensiunea sa este numarul de articole care s-au potrivit criteriului de cautare. Diagrama din figura arata cum un cursor explicit marcheaza linia curenta din setul de rezultate. Aceasta permite programului dumneavoastra sa proceseze fiecare linie, pe rand.
Un program PL/SQL deschide un cursor, proceseaza liniile returnate de interogare, si apoi inchide cursorul. Cursorul marcheaza pozitia curenta intr-un set de rezultate.
Pot procesa articolele returnate de o interogare unul cate unul
Marcheaza care este linia curenta ce este procesata
Permite programatorului sa le controleze manual intr-un bloc PL/SQL
|
DECLARE |
Revine la FETCH daca mai exista linii
Controlarea
cursorilor expliciti
Curosrii expliciti( continuare )
Acum ca am inteles cursorii, din punct de vedere conceptual, sa revedem pasii necesari pentru a-i utiliza. Sintaxa pentru fiecare dintre pasi poate fi gasita in urmatoarele pagini.
Declarati cursorul ,denumindu-l si definind structura interogarii ce trebuie sa fie realizata in interiorul lui.
Deschideti cursorul. Comanda OPEN executa interogarea si leaga toate variabilele folosite. Liniile returnate de interogare sunt numite setul activ si sunt acum disponibile pentru incarcare.
Incarcati datele din cursor. Comanda FETCH incarca linia curenta din cursor in variabile. Fiecare incarcare face cursorul sa se deplaseze la urmatoarea linie din setul activ. Astfel fiecare incarcare acceseaza o linie diferita returnata de interogare. In diagrama fiecare incarcare testeaza cursorul pentru a vedea daca mai exista linii disponibile. Daca mai sunt articole, incarca articolul curent in variabile; altfel inchide cursorul.
Inchide cursorul. Comanda CLOSE distruge setul activ de inregistrari. Acum este posibil sa redeschidem cursorul pentru a aduce un set activ nou.
|
|
|
|
|
|
|
|
Cursori expliciti( continuare )
Folositi instructiunile OPEN, FETCH, si CLOSE pentru a controla un cursor. Instructiunea OPEN executa interogarea asociata cursorului, identifica setul de rezultate, si pozitioneaza cursorul inainte de prima linie. Instructiunea FETCH aduce inregistrarea curenta si avanseaza cursorul la urmatoarea linie. Cand ultima intregistrare a fost procesata, instructiunea CLOSE dezactiveaza cursorul.
|
|
Folositi instructiunea CURSOR pentru a declara un cursor explicit. Puteti utiliza variabile in interiorul interogarii, dar trebuie sa le declarati inainte de instructiunea CURSOR.
In sintaxa:
nume cursor este un identificator PL/SQL
instructiune select este o instructiune SELECT fara cluza INTO
Nota: A nu se include clauza INTO in declaratia cursorului ea apare mai tarziu in instructiunea FETCH.
|
|
Aducerea angajatilor unul cate unul.
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR c1 IS
SELECT empno, ename
FROM emp;
Nota: Puteti folosi variabile in interogare, dar trebuie sa le declarati inainte de instructiunea CURSOR.
|
|
Deschide un cursor pentru a executa o interogare si a identifica setul de rezultate, care contine toate inregistrarile care intrunesc criteriile de cautare. Acum cursorul pointeaza la prima linie din setul de rezultate.
In sintaxa,
nume cursor este numele cursorului declarat anterior
OPEN este o instructiune ce realizeaza urmatoarele operatii:
Aloca memorie dinamic pentru o zona context care poate contine, eventual, informatii de procesare critice.
Parcurge instructiunea SELECT.
Leaga variabilele de intrare.
Identifica setul de rezultate -setul de inregistrari ce satisface criteriile de cautare. Inregistrarile din setul rezultat nu sunt incarcate in variabile cand instructiunea OPEN e executata. Abia instructiunea FETCH incarca inregistrarile.
Pozitioneaza pointerul chiar inainte de prima linie din setul activ.
Nota: Daca interogarea nu are nici un rezultat atunci cand cursorul este deschis, PL/SQL nu arunca exceptie. Totusi se poate testa starea cursorului dupa fiecare incarcare.
Pentru cursorii declarati folosind clauza FOR UPDATE, instructiunea OPEN blocheaza aceste inregistrari.
|
|
Instructiunea FETCH incarca articolele din setul de rezultate cate unul. Dupa fiecare incarcare, cursorul avanseaza la urmatoarea linie din setul de rezultate.
In sintaxa,
nume cursor este numele cursorului declarat anterior
variabila este o variabila de iesire
nume inregistrare este numele inregistrarii in care datele incarcate sunt depuse. Variabila inregistrare poate fi declarata folosind atributul %ROWTYPE.
Incarca valorile inregistrarii curente in variabilele de iesire.
Se introduc acelasi numar de variabile in clauza INTO a instructiunii FETCH ca numarul de coloane de iesire a instructiunii SELECT si se asigura ca tipurile de date sunt compatibile.
Se pozitioneaza fiecare variabila astfel incat sa corespunda coloanelor.
Alternativ, se defineste o inregistarea pentru cursor si referentiaza inregistrarea in clauza FETCH INTO.
Se testeaza daca acel cursor contine linii. Daca o incarcare nu aduce nici o valoare, nu mai sunt articole de procesat in setul activ si nu s-a inregistrat nici o eroare.
Nota: Instructiunea FETCH realizeaza urmatoarele operatii:
Avanseaza pointerul la urmatoarea inregistrare la setul activ.
Citeste datele pentru inregistrarea curenta in variabilele PL/SQL de iesire.
Iese din ciclul FOR al cursorului daca pointerul este pozitionat la sfarsitul setului activ.
|
OPEN defined cursor LOOP FETCH defined_cursor INTO defined_variables EXIT WHEN . . .; . . . Proceseaza datele incarcate . . . END; |
Folositi instructiunea FETCH pentru a incarca valorile inregistrarii curente in variabilele de iesire. Dupa incarcare, puteti utiliza variabilele in alte instructiuni. Pentru fiecare coloana returnata de interogarea asociata cursorului, trebuie sa fie o valoare corespunzatoare in lista INTO. Deasemenea, tipurile lor de data trebuie sa fie compatibile.
Incarca primii zece angajati unul cate unul.
DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; i NUMBER := 1; CURSOR c1 IS SELECT empno ,
ename FROM emp; BEGIN OPEN c1; FOR i IN 1..10 LOOP FETCH c1 INTO
v_empno, v_ename; END END ;
|
|
Instructiunea CLOSE inchide cursorul, si setul de rezultate devine nedefinit. Inchideti cursorul dupa ce ati terminat de procesat instructiunea SELECT. Acest pas permite cursorului sa fie redeschis daca este necesar. Astfel se poate stabili un set activ de cateva ori.
In sintaxa,
cursor name este numele cursorului declarat anterior
Nu incercati sa incarcati date dintr-un cursor dupa ce a fost inchis, deoarece va fi aruncata exceptia INVALID CURSOR.
Nota: Instructiunea CLOSE elibereaza zona de context.
Desi este posibil de terminat un bloc PL/SQL fara a inchide cursorul ar trebui sa va obisnuiti sa inchideti cursorii declarati explicit pentru a elibera resursele.
Exista un numar maxim de cursori deschisi pe utilizator, numar ce este determinat de parametrul OPEN CURSORS in campul de parametri ai bazei de date. Standard OPEN_CURSORS = 50.
FOR i IN 1..10 LOOP
FETCH c1 INTO v_empno, v_ename;
.
END
CLOSE c1;
END;
Atributele cursorilor explicitiObtinerea informatiilor de stare despre un cursor
|
Ca si la cursorii impliciti, exista patru atribute pentru obtinerea informatiilor de starepentru un cursor. Cand sunt adaugate la un cursor sau o variabila de tip cursor, aceste atribute returneaza informatii importante despre executia unei instructiuni de manipulare a datelor.
Nota: Nu puteti apela atributele cursorilor direct in instructiuni SQL.
Controlarea incarcarilor multipleProcesati cateva inregistrari dintr-un cursor explicit utilizand un ciclu. Incarcati o inregistrare cu fiecare iteratie. Folositi atributul %NOTFOUND pentru a face testul de incarcare esuata. Folositi un atribut de cursor explicit pentru a testa succesul incarcarii. |
Pentru a procesa mai multe linii dintr-un cursor explicit, deobicei definiti un ciclu care sa realizeze incarcarea pe fiecare iteratie. In final toate liniile sun procesate si incarcarea esuata seteaza %NOTFOUND pe TRUE. Folositi atributele cursorului explicit pentru a testa succesul fiecarei incarcari inainte de a mai face vreo referire la cursorul respectiv. Daca omiteti un criteriu de iesire va rezulta un ciclu infinit.
|
|
Puteti incarca inregistrari numai cand cursorul este deschis. Folositi atributul %ISOPEN pentru a testa daca cursorul este deschis.
Incarcati liniile intr-un ciclu. Folositi atributele cursorului pentru a determina iesirea din ciclu.
Folositi atributul %ROWCOUNT pentru a determina numarul exact de inregistrari, incarcati inregistrarile intr-un FOR numeric, sau incarcati inregistrarile intr-un ciclu simplu si determinati cand trebuie iesit.
Nota: %ISOPEN returneaza starea cursoruluis TRUE daca este deschis, FALSE daca nu.
Atributele %NOTFOUND si %ROWCOUNTFolositi %ROWCOUNT pentru a obtine numarul exact de linii. Folositi %NOTFOUND pentru a determina cand sa iesiti din ciclu. |
Aduceti primii zece angajati unul cate unul.
DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; CURSOR c1 IS SELECT empno, ename FROM emp; BEGIN OPEN c1; LOOP FETCH c1 INTO v_empno,
v_ename; EXIT WHEN c1%ROWCOUNT
> 10 OR c1%NOTFOUND; . . . END LOOP; CLOSE c1; END ;
Nota: Inainte de prima incarcare %NOTFOUND are valoarea NULL. Deci daca FETCH nu se executa cu succes ciclul nu se mai termina. Pentru siguranta folositi urmatoarea instructiune:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
Daca folositi %ROWCOUNT, adaugati un test pentru cazul cand nu exista linii in cursor folosind atributul %NOTFOUND, pentru ca numaratorul de linii nu este incrementat daca FETCH nu incarca nici o linie.
|
CURSOR c1 IS SELECT empno, ename FROM emp; emp record c1%ROWTYPE; BEGIN OPEN c1; FETCH c1 INTO emp record; |
Procesati inregistrarile din setul activ in mod convenabil incarcand valorile intr-o inregistrare PL/SQL.
Exemplu
Ati vazut deja ca puteti defini inregistrari care sa foloseasca structura coloanelor dintr-o tabela. Puteti deasemenea defini o inregistrare bazata pe lista coloanelor selectate intr-un cursor explicit. Acesta este un mod convenabil de a procesa liniile setului activ pentru ca le incarcati direct in inregistrare.
|
|
Un ciclu FOR pentru cursori proceseaza articolele dintr-un cursor explicit. Este o modalitate mai simpla pentru ca cursorul este deschis , articolele sunt incarcate cate unul pentru fiecare iteratie din ciclu, si cursorul este automat inchis atunci cand articolele au fost procesate. Ciclul se termina automat cand ultimul articol a fost incarcat.
In sintaxa,
record name este numele inregistrarii declarate implicit
cursor name este un identificator PL/SQL pentru cursorul anterior declarat
Nu declarati inregistrarea care controleaza ciclul. Domeniul de valabilitate este numai in cadrul ciclului.
Testati atributele cursorului in cadrul ciclului daca este necesar.
Folositi parametri pentru cursor, daca sunt necesari, in parantezele ce urmeza dupa numele cursorului din instructiunea FOR.
Nu folositi cilul FOR pentru cursori atunci cand operatiile cursorului trebuie realizate manual.
Nota: Puteti defini o interogare la inceputul ciclului. Expresia interogarii reprezinta o instructiune SELECT, si cursorul este intern pentru ciclul FOR. Pentru ca cursorul nu a fost declarat cu un nume nu puteti sa-i testati atributele.
|
|
|
|
Sintaxa :
Da valori parametrilor unui cursor atunci cand cursorul este deschis si interogarea este realizata.
Deschide un cursor explicit de cateva ori, cu un set activ diferit de fiecare data.
CURSOR cursor_name [(parameter_name
datatype, .)] IS Select_statement;
Parametri transmiterea valorilor unui cursor atunci cand acesta este deschis si sa fie folosite intr-o interogare atunci cand se executa. Aceasta inseamna ca puteti deschide si inchide de cateva ori un cursor explicit intr-un bloc, returnand de fiecare data un set activ diferit.
Fiecare parametru formal declarat in cursor trebuie sa aiba un parametru actual corespondent in OPEN. Tipurile de date ale parametrilor sunt la fel ca cele pentru variabile scalare, dar nu le veti da aceleasi dimensiuni. Numele parametrilor sunt pentru referirile din expresia interogarii cursorului.
In sintaxa,
Parameter name este numele parametrului. Parametrul este folosit in urmatoarea sintaxa.
Cursor_parameter_name [IN] datatype [ exprt]
datatype este un tip scalar al parametrului
select statement este un SELECT fara clauza INTO.
Atunci cand cursorul este deschis, dati valori fiecarui parametru in ordinea pozitionarii. Nota: notarea parametrului nu ofera o mare functionalitate: iti permite doar sa specifici valorile introduse simplu si clar. Aceasta este folositoare atunci cand acelasi cursor este referit in mod repetat.
Tipurile parametrilor sunt la fel cu cele pentru variabile scalare, dar nu le dati dimensiunea. Numele parametrilor sunt pentru referiri in interogarea cursorului.
In exemplul urmator, sunt declarate doua variabile si un cursor. Cursorul este definit cu doi parametri.
Job_emp emp.job%TYPE := 'CLERK';
V_ename emp.ename%TYPE;
CURSOR c1 (v_deptno NUMBER, v_job VARCHAR2) IS
SELECT.
Urmatoarele propozitii deschid cursorul
OPEN c1 (20, 'ANALYST');
Puteti transmite parametri unui cursor folosit intr-o bucla FOR.
Subinterogari
O subinterogare este o interogare (de obicei cuprinsa de paranteze) care apare impreuna cu alte instructiuni SQL.
Subinterogarile sunt de obicei folosite in clauza WHERE.
Pot fi folosite subinterogari sau subinterogari corelate.
SumarTipuri de cursoare Cursoare implicite: Folosite pentru toate instructiunile DML si interogarile cu un singur articol ca rezultat Cursori expliciti: Utilizati pentru interogari ce au ca rezultate zero, unul, sau mai multe articole Utilizarea cursorilor expliciti. Evaluarea starii cursorului folosind atributele cursorilor. Utilizarea ciclurilor FOR penrtu cursori. Utilizarea cursorilor cu parametri |
Practice overviewDeclararea si utilizarea cursorilor expliciti pentru a interoga articolele dintr-o tabela. Utilizarea ciclurilor FOR penrtu cursori. Aplicarea atributelor cursorilor pentru a le verifica starea |
Aceasta parte practica aplica cunostintele despre cursori pentru a procesa un numar de articole dintr-o tabela si a popula o alta tabela cu rezultate, folosind un ciclu FOR pentru cursori.
Practica
Creati un bloc PL/SQL care sa determine primii angajati in ordinea salariilor.
a. Introduceti un numar n ca intrare de la utilizator cu un parametru de substitutie PL/SQL.
b. In ciclu, luati ultimele nume si salari a primilor n persoane relativ la salarii din tabela EMP.
c. Puneti numele si salariile in tabela TOP DOGS.
d. Presupuneti ca nu exista doi angajai cu acelasi salar.
e. Testati o varietate de cazuri speciale cum ar fi: n=0 , cand n este mai mare decat numarul de angajati din tabela EMP. Goliti tabela TOP_DOGS dupa fiecare test.
|
Va rugam introduceti numarul de angajati din topul celor mai bine platiti angajati: NAME SALARY ----- ----- ----- KING 5000 FORD 3000 SCOTT 3000 JONES 2975 BLAKE 2850 |
Considerati cazul in care cativa angajati au acelasi salar. Daca o persoana este afisata, atunci toate persoanele cu acelasi salar trebuie deasemenea afisate.
|
Va rugam introduceti numarul de angajati din topul celor mai bine platiti angajati: 2 NAME SALARY ----- ----- ----- KING 5000 FORD 3000 SCOTT 3000 |
|
Va rugam introduceti numarul de angajati din topul celor mai bine platiti angajati: 3 NAME SALARY ----- ----- ----- KING 5000 FORD 3000 SCOTT 3000 JONES 2975 |
3. Scrieti o interogare pentru a extrage toate departamentele si angajatii din fiecare departament. Introduceti rezultatele in tabela MESSAGES. Folositi un cursor pentru a extrage numarul departamentului si dati numarul departamentului unui cursor pentru a extrage angajatii din acel departament.
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 1625
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
