Cursore PL / SQL

Riepilogo: in questo tutorial imparerai a conoscere il cursore PL / SQL e il suo utilizzo.

Un cursore è un puntatore che punta a un risultato di una query. PL / SQL ha due tipi di cursori: cursori impliciti e cursori espliciti.

Cursori impliciti

Ogni volta che Oracle esegue un’istruzione SQL come SELECT INTO, INSERT, UPDATE e DELETE, crea automaticamente un cursore implicito.

Oracle gestisce internamente l’intero ciclo di esecuzione dei cursori impliciti e rivela solo le informazioni e gli stati del cursore come SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND e SQL%NOTFOUND.

Il cursore implicito non è elegante quando la query restituisce zero o più righe che causano rispettivamente NO_DATA_FOUND o TOO_MANY_ROWS eccezione.

Cursori espliciti

Un cursore esplicito è un’istruzione SELECT dichiarata esplicitamente nella sezione della dichiarazione del blocco corrente o di una specifica del pacchetto.

Per un cursore esplicito, hai cont rol nel ciclo di esecuzione da OPEN, FETCH e CLOSE.

Oracle definisce un ciclo di esecuzione che esegue un’istruzione SQL e vi associa un cursore.

La seguente illustrazione mostra il ciclo di esecuzione di un cursore esplicito:

Esaminiamo ogni passaggio in dettaglio.

Dichiara un cursore

Prima di usare un cursore esplicito, devi dichiararlo nella sezione di dichiarazione di un blocco o pacchetto come segue:

In questa sintassi :

• Innanzitutto, specifica il nome del cursore dopo la parola chiave CURSOR.
• In secondo luogo, definisci una query per recuperare i dati dopo la parola chiave IS.

Apri un cursore

Prima di iniziare a caricare le righe dal cursore, devi aprirlo. Per aprire un cursore, utilizza la seguente sintassi:

In questa sintassi, cursor_name è il nome del cursore dichiarato nella sezione della dichiarazione.

Quando si apre un cursore, Oracle analizza la query, associa le variabili ed esegue l’istruzione SQL associata.

Oracle determina anche un piano di esecuzione , associa le variabili host e i parametri del cursore con i segnaposto nell’istruzione SQL, determina il set di risultati e imposta il cursore sulla prima riga nel set di risultati.

Ulteriori informazioni sul cursore parametrizzato nel prossimo tutorial.

Recupera da un cursore

L’istruzione FETCH inserisce il contenuto della riga corrente in variabili. La sintassi dell’istruzione FETCH è la seguente:

Per recuperare tutte le righe in un set di risultati, è necessario per recuperare ogni riga fino all’ultima.

Chiusura di un cursore

Dopo aver recuperato tutte le righe, è necessario chiudere il cursore con CLOSE istruzione:

La chiusura di un cursore indica a Oracle di rilasciare la memoria allocata al momento opportuno.

Se dichiari un cursore in un blocco, procedura o funzione anonimi, il cursore verrà automaticamente chiuso al termine dell’esecuzione di questi oggetti.

Tuttavia, è necessario chiudere esplicitamente i cursori basati sui pacchetti. Nota che se chiudi un cursore che non è stato ancora aperto, Oracle solleverà un’eccezione INVALID_CURSOR.

Attributi espliciti del cursore

Un cursore ha quattro attributi a cui puoi fare riferimento nel seguente formato:

dove cursor_name è il nome del cursore esplicito.

1)% ISOPEN

Questo attributo è TRUE se il cursore è aperto o FALSE se non lo è.

2)% FOUND

Questo attributo ha quattro valori:

• NULL prima del primo recupero
• TRUE se un record è stato recuperato correttamente
• FALSE se non viene restituita alcuna riga
• INVALID_CURSOR se il cursore non è aperto

3)% NOTFOUND

Questo attributo ha quattro valori:

• NULL prima del primo recupero
• FALSE se un record è stato recuperato con successo essfully
• TRUE se non viene restituita alcuna riga
• INVALID_CURSOR se il cursore non è aperto

3)% ROWCOUNT

L’attributo %ROWCOUNT restituisce il numero di righe recuperate dal cursore. Se il cursore non è aperto, questo attributo restituisce INVALID_CURSOR.

Esempio di cursore PL / SQL

Useremo le tabelle orders e order_items da il database di esempio per la dimostrazione.

La seguente dichiarazione crea una visualizzazione che restituisce i ricavi delle vendite da parte dei clienti:

I valori della colonna del credito sono il 5% dei ricavi totali delle vendite.

Supponi di dover sviluppare un anonimo bloccalo:

1. Reimposta i limiti di credito di tutti i clienti a zero.
2. Recupera i clienti ordinati per vendite in ordine decrescente e assegna loro nuovi limiti di credito da un budget di 1 milione.

Il seguente blocco anonimo illustra la logica:

Nella sezione dichiarazione, dichiariamo tre variabili.

La prima è l_budget il cui valore iniziale è 1,000,000.

La seconda variabile è una variabile cursore esplicita denominata c_sales il cui recupera i dati dalla sales vista:

La terza variabile è una record basato su cursore denominato c_sales.

Nella sezione di esecuzione, eseguiamo quanto segue:

• Innanzitutto, reimposta i limiti di credito di tutti i clienti a zero utilizzando un’istruzione UPDATE.
• Secondo, apri il cursore c_sales.
• Terzo, recupera ogni riga dal cursore. In ogni iterazione del ciclo, aggiorniamo il limite di credito e ridotto il budget. Il ciclo termina quando non ci sono righe da recuperare o il budget è esaurito.
• Infine, chiudi il cursore.

La seguente query recupera i dati da customers tabella per verificare l’aggiornamento:

Risultato:

Come puoi vedere chiaramente dal risultato, solo i primi pochi clienti hanno i limiti di credito. Se si sommano tutti i limiti di credito, il totale dovrebbe essere 1 milione come mostrato di seguito:

Ora , dovresti comprendere i cursori PL / SQL, inclusi i cursori impliciti ed espliciti, e come utilizzarli in modo efficace per elaborare i dati, riga per riga, da una tabella.