Oracle Cursore PL/SQL: implicito, esplicito, ciclo For con esempio

Cos'è il CURSORE in PL/SQL?

Un cursore è un puntatore a quest'area di contesto. Oracle crea un'area di contesto per l'elaborazione di un'istruzione SQL che contiene tutte le informazioni sull'istruzione.

PL/SQL consente al programmatore di controllare l'area di contesto tramite il cursore. Un cursore contiene le righe restituite dall'istruzione SQL. L'insieme di righe mantenute dal cursore viene definito insieme attivo. È inoltre possibile assegnare un nome a questi cursori in modo che possano essere referenziati da un'altra posizione del codice.

Il cursore è di due tipi.

• Cursore implicito
• Cursore esplicito

Cursore implicito

Ogni volta che si verifica un'operazione DML nel database, viene creato un cursore implicito che contiene le righe interessate in quella particolare operazione. Questi cursori non possono essere nominati e quindi non possono essere controllati o referenziati da un'altra posizione del codice. Possiamo fare riferimento solo al cursore più recente attraverso gli attributi del cursore.

Cursore esplicito

I programmatori possono creare un'area di contesto denominata per eseguire le proprie operazioni DML per ottenere un maggiore controllo su di essa. Il cursore esplicito dovrebbe essere definito nella sezione dichiarazioni del file Blocco PL/SQLe viene creato per l'istruzione 'SELECT' che deve essere utilizzata nel codice.

Di seguito sono riportati i passaggi necessari per lavorare con i cursori espliciti.

• Dichiarare il cursore Dichiarare il cursore significa semplicemente creare un'area di contesto denominata per l'istruzione 'SELECT' definita nella parte della dichiarazione. Il nome di quest'area di contesto è uguale al nome del cursore.
• Cursore di aperturaL'apertura del cursore darà istruzioni al PL / SQL per allocare la memoria per questo cursore. Renderà il cursore pronto per recuperare i record.
• Recupero dei dati dal cursoreIn questo processo, viene eseguita l'istruzione 'SELECT' e le righe recuperate vengono archiviate nella memoria allocata. Questi sono ora chiamati set attivi. Il recupero dei dati dal cursore è un'attività a livello di record, il che significa che possiamo accedere ai dati in modo record per record. Ciascuna istruzione fetch recupererà un set attivo e conterrà le informazioni di quel particolare record. Questa istruzione è uguale all'istruzione 'SELECT' che recupera il record e lo assegna alla variabile nella clausola 'INTO', ma non genererà alcuna eccezione.
• Chiusura del cursoreUna volta recuperato tutto il record, dobbiamo chiudere il cursore in modo che la memoria allocata a quest'area di contesto venga rilasciata.

Sintassi

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

• Nella sintassi precedente, la parte di dichiarazione contiene la dichiarazione del cursore e la variabile cursore in cui verranno assegnati i dati recuperati.
• Il cursore viene creato per l'istruzione 'SELECT' fornita nella dichiarazione del cursore.
• Nella parte di esecuzione, il cursore dichiarato viene aperto, recuperato e chiuso.

Attributi del cursore

Sia il cursore implicito che quello esplicito dispongono di determinati attributi a cui è possibile accedere. Questi attributi forniscono ulteriori informazioni sulle operazioni del cursore. Di seguito sono riportati i diversi attributi del cursore e il loro utilizzo.

Esempio di cursore esplicito:

In questo esempio vedremo come dichiarare, aprire, recuperare e chiudere il cursore esplicito.

Proietteremo tutto il nome del dipendente dalla tabella emp utilizzando un cursore. Utilizzeremo anche l'attributo del cursore per impostare il ciclo per recuperare tutto il record dal cursore.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN guru99_det;

LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT);
CLOSE guru99_det;
END:
/

Uscita

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY 
Total rows fetched is 3

Spiegazione del codice

• Riga di codice 2: Dichiara il cursore guru99_det per l'istruzione 'SELECT emp_name FROM emp'.
• Riga di codice 3: Dichiarazione della variabile lv_emp_name.

• Riga di codice 5: Apertura del cursore guru99_det.
• Riga di codice 6: Impostazione dell'istruzione loop Basic per recuperare tutti i record nella tabella 'emp'.
• Riga di codice 7: Recupera i dati guru99_det e assegna il valore a lv_emp_name.
• Riga di codice 9: Utilizzando l'attributo del cursore '%NOTFOUND' per verificare se tutto il record nel cursore è stato recuperato. Se recuperato, restituirà "TRUE" e il controllo uscirà dal ciclo, altrimenti il ​​controllo continuerà a recuperare i dati dal cursore e stamperà i dati.
• Riga di codice 11: Condizione EXIT per l'istruzione del ciclo.
• Riga di codice 12: Stampa il nome del dipendente recuperato.
• Riga di codice 14: Utilizzando l'attributo del cursore '%ROWCOUNT' per trovare il numero totale di record interessati/recuperati nel cursore.
• Riga di codice 15: All'uscita dal ciclo il cursore viene chiuso e la memoria allocata viene liberata.

FOR Dichiarazione del cursore del ciclo

L'istruzione "FOR LOOP" può essere utilizzata per lavorare con i cursori. Possiamo fornire il nome del cursore invece del limite dell'intervallo nell'istruzione del ciclo FOR in modo che il ciclo funzioni dal primo record del cursore all'ultimo record del cursore. La variabile del cursore, l'apertura del cursore, il recupero e la chiusura del cursore verranno eseguiti implicitamente dal ciclo FOR.

Sintassi

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
  FOR I IN <cursor_name>
  LOOP
  .
  .
  END LOOP;
END;

• Nella sintassi precedente, la parte di dichiarazione contiene la dichiarazione del cursore.
• Il cursore viene creato per l'istruzione 'SELECT' fornita nella dichiarazione del cursore.
• Nella parte di esecuzione, il cursore dichiarato è impostato nel ciclo FOR e la variabile del ciclo 'I' si comporterà come variabile cursore in questo caso.

Oracle Cursore per esempio di loop:
In questo esempio, proietteremo tutti i nomi dei dipendenti dalla tabella emp utilizzando un ciclo cursore-FOR.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp; 
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
/

Uscita

Employee Fetched:BBB 
Employee Fetched:XXX
Employee Fetched:YYY

Spiegazione del codice

• Riga di codice 2: Dichiara il cursore guru99_det per l'istruzione 'SELECT emp_name FROM emp'.
• Riga di codice 4: Costruisce il ciclo 'FOR' per il cursore con la variabile del ciclo lv_emp_name.
• Riga di codice 5: Stampa del nome del dipendente in ogni iterazione del ciclo.
• Riga di codice 8: Esci dal giro

Nota: Nel ciclo Cursor-FOR, gli attributi del cursore non possono essere utilizzati poiché l'apertura, il recupero e la chiusura del cursore vengono eseguiti implicitamente da Ciclo FOR.