Utilizzo di cursori e cicli in Transact-SQL. Cursori nelle procedure memorizzate MySQL Cursori nel server SQL

Il comando DECLARE CURSOR consente di recuperare record da una tabella riga per riga per la manipolazione. Ciò consente l'elaborazione riga per riga invece della tradizionale elaborazione del set di dati eseguita da SQL.

In prima approssimazione, quando si lavora con il cursore vengono utilizzati i seguenti passaggi.

Il cursore viene creato con il comando DECLARE. Il cursore si apre con il comando OPEN.

Le operazioni con il cursore vengono eseguite utilizzando il comando FETCH. Il cursore viene chiuso con il comando CLOSE.

Il comando DECLARE CURSOR specifica un'istruzione SELECT. Ogni riga restituita dall'istruzione SELECT può essere recuperata ed elaborata singolarmente. Il seguente esempio Oracle dichiara un cursore in un blocco di dichiarazione insieme a diverse altre variabili. Successivamente nel blocco BEGIN...END successivo il cursore viene aperto, viene effettuata una selezione e il cursore viene chiuso.

CURSOR title_price_cursor IS SELECT titolo, prezzo DA titoli

DOVE IL PREZZO NON È NULLO; titolo_prezzo_val titolo_prezzo_cursor ROWTYPE; nuovo_prezzo NUMERO(10.2);

APERTO titolo_prezzo_Cursore;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 INSERISCI IN new_title_price VALORI

(title_price_val.title, new_price) CHIUDI title_price_cursor; FINE;

Poiché questo esempio utilizza PL/SQL, non spiegheremo gran parte del codice in questo libro. Tuttavia, il blocco DECLARE mostra chiaramente la dichiarazione del cursore. In un blocco eseguibile PL/SQL, il cursore viene inizializzato con il comando OPEN, i valori vengono recuperati con il comando FETCH e infine il cursore viene chiuso con il comando CLOSE.

L'istruzione SELECT è la base del cursore, quindi è buona norma testarla accuratamente prima di includerla nell'istruzione DECLARE CURSOR. L'istruzione SELECT può operare sulla tabella o vista sottostante. Pertanto, i cursori di sola lettura possono funzionare con visualizzazioni non aggiornabili. Un'istruzione SELECT può contenere clausole come ORDER BY, GROUP BY e HAVING purché queste clausole non aggiornino la tabella di origine. Se il cursore è definito come FOR UPDATE, si consiglia di rimuovere tali clausole dall'istruzione SELECT.

I cursori locali vengono spesso utilizzati come parametri di output per le procedure memorizzate. Pertanto, è possibile definire e popolare un cursore in una procedura memorizzata e passarlo al lavoro batch o alla procedura memorizzata che lo ha richiamato.

Nel seguente semplice esempio DB2, dichiareremo un cursore che cerca numeri di reparto, nomi di reparto e numeri di manager nell'admin_group "XO1".

DICHIARA cursore_dept CURSOR

PER SELEZIONARE numero_dip, nome_dip, numero_mgr

DOVE gruppo_admin="X01"

ORDINA PER d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;

Il seguente esempio di Microsoft SQL Server dichiara e apre un cursore per la tabella degli editori. Il cursore seleziona il primo record che corrisponde all'istruzione SELECT dalla tabella degli editori e lo inserisce in un'altra tabella. Si passa quindi al record successivo, poi a quello successivo, finché tutti i record non sono stati elaborati. Infine, il cursore viene chiuso e la memoria viene liberata (il comando DEALLOCATE viene utilizzato solo in Microsoft SQL Server).

DICHIARARE @nome_editore VARCHAR(20)

DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM editori WHERE paese "USA"

PRENDI AVANTI DA pub_cursor IN editore_nome

MENTRE @s>FETCH_STATUS=0

INSERT INTO foreign_publishers VALUES("j>nome_editore)

CHIUDI pub_cursor DEALLOCATE pub_cursor

In questo esempio puoi vedere il cursore che si sposta attraverso una serie di record. (Questo esempio ha solo lo scopo di dimostrare l'idea, poiché in realtà esiste Il modo migliore soluzione a questo problema, vale a dire l'istruzione INSERT, SELECT.)

SI APPLICA A: Base SQL Server (dal 2008). Dati SQL Azure SQL Data WarehouseParallel Data Warehouse

Definisce gli attributi di un cursore del server Transact-SQL, ad esempio le proprietà di visualizzazione e la query utilizzata per creare il set di risultati su cui opera il cursore. L'istruzione DECLARE CURSOR supporta sia la sintassi standard ISO sia la sintassi che utilizza il set di estensioni del linguaggio Transact-SQL.

Sintassi ISO DECLARE nome_cursore [ INSENSITIVE ] [ SCROLL ] CURSOR FOR istruzione_selezione [ FOR ( SOLA LETTURA | UPDATE [ OF nome_colonna [ ,...n ] ] ) ] [;] Sintassi estesa Transact-SQL DICHIARARE nome_cursore CURSORE [LOCALE | GLOBALE ] [SOLO AVANTI | SCORRIMENTO ] [ STATICO | TASTO | DINAMICO | AVANTI_VELOCE ] [ SOLA_LETTURA | SCROLL_LOCKS | OTTIMISTICO ] [ TYPE_WARNING ] FOR istruzione_selezione [ FOR UPDATE [ OF nome_colonna [ ,...n ] ] ] [;]

nome_cursore
nome_cursore

INSENSIBILE
tempdb; pertanto, le modifiche alle tabelle sottostanti non si riflettono nei dati restituiti dalle selezioni di questo cursore e questo cursore non è modificabile. Quando si utilizza la sintassi ISO, a meno che non venga specificata l'opzione INSENSITIVE, gli aggiornamenti e le eliminazioni confermate apportate alle tabelle di base verranno visualizzati nelle selezioni successive.

SCORRERE
Indica che tutte le opzioni di campionamento sono disponibili (PRIMO, ULTIMO, PRIOR, NEXT, RELATIVE, ABSOLUTE). Se l'istruzione ISO DECLARE CURSOR non specifica un'opzione SCROLL, è supportata solo l'opzione NEXT fetch. Non è possibile specificare l'opzione SCROLL con l'opzione FAST_FORWARD.

select_statement
Un'istruzione SELECT standard che specifica il set di risultati di un cursore. Le parole chiave FOR BROWSE e INTO non sono consentite select_statement dichiarazione del cursore.

select_statement conflitto con un cursore del tipo richiesto.

SOLA LETTURA

Aggiornamento ]
nome_colonna [, .. .N], solo le colonne elencate consentono modifiche. Se l'istruzione UPDATE viene utilizzata senza un elenco di colonne, l'aggiornamento è possibile per tutte le colonne.

nome_cursore
Il nome Transact-SQL del cursore del server specifico. nome_cursore deve seguire le regole per gli identificatori.

LOCALE
Indica che il cursore è locale rispetto al pacchetto, alla procedura memorizzata o al trigger in cui è stato creato. Il nome del cursore è valido solo all'interno di quest'area. È possibile fare riferimento a un cursore tramite variabili locali del pacchetto, procedure memorizzate, trigger o il parametro di output di una procedura memorizzata. Il parametro OUTPUT viene utilizzato per passare un cursore locale al pacchetto chiamante, alla procedura memorizzata o al trigger, che può quindi assegnare il parametro a una variabile cursore per il successivo accesso al cursore dopo il completamento della procedura memorizzata. Il cursore viene rilasciato implicitamente quando il batch, la procedura memorizzata o il trigger completa l'esecuzione, a meno che il cursore non sia stato passato al parametro OUTPUT. Se il cursore è stato passato al parametro OUTPUT, il cursore viene rilasciato quando tutte le variabili che fanno riferimento ad esso vengono liberate o quando si esce dall'ambito.

GLOBALE
Indica che il cursore è globale rispetto alla connessione. Il nome del cursore può essere utilizzato da qualsiasi procedura memorizzata o pacchetto in esecuzione sulla connessione. Il cursore viene rilasciato implicitamente solo se la connessione viene interrotta.

AVANTI_SOLO
Specifica che il cursore può essere visualizzato solo dalla prima all'ultima riga. È supportata solo l'opzione di recupero FETCH NEXT. Se si specifica FORWARD_ONLY senza le parole chiave STATIC, KEYSET o DYNAMIC, il cursore si comporta come un cursore DYNAMIC. Se non vengono specificati né l'argomento FORWARD_ONLY né l'argomento SCROLL, il valore predefinito è l'argomento FORWARD_ONLY a meno che non siano presenti le parole chiave STATIC, KEYSET o DYNAMIC. I cursori STATIC, KEYSET e DYNAMIC hanno il valore predefinito SCROLL. A differenza delle API di database come ODBC e ADO, la modalità FORWARD_ONLY è supportata dai seguenti cursori Transact-SQL: STATIC, KEYSET e DYNAMIC.

STATICO
Definisce un cursore che crea una copia temporanea dei dati utilizzabili dal cursore. Tutte le query al cursore accedono alla tabella temporanea specificata in tempdb; pertanto, le modifiche alle tabelle sottostanti non si riflettono nei dati restituiti dalle selezioni di questo cursore e questo cursore non è modificabile.

MAZZO DI CHIAVI
Indica che l'appartenenza o l'ordine delle righe nel cursore rimane invariato quando viene aperto. Nella tabella è integrato un set di chiavi che identificano in modo univoco le righe tempdb chiamato chiavi.

Le modifiche ai valori non chiave nelle tabelle di base apportate dal proprietario del cursore o confermate da altri utenti vengono visualizzate quando il proprietario del cursore le visualizza. Le modifiche apportate da altri utenti non vengono riflesse (non è possibile apportare modifiche utilizzando il cursore del server Transact-SQL). Se una riga viene eliminata, il tentativo di recuperare le righe restituisce @@FETCH_STATUS -2. Gli aggiornamenti ai valori chiave oltre i limiti del cursore sono simili all'eliminazione di una vecchia riga e all'inserimento di una nuova riga. La riga con i nuovi valori non è visibile e tenta di recuperare la riga con i vecchi valori restituisce @@FETCH_STATUS -2. Gli aggiornamenti sono immediatamente visibili se vengono effettuati tramite il cursore utilizzando la clausola WHERE CURRENT OF.

DINAMICO
Definisce un cursore che visualizza tutte le modifiche ai dati apportate alle righe nel set di risultati durante la visualizzazione di questo cursore. I valori dei dati, l'ordine e l'appartenenza alle righe in ciascuna selezione possono variare. L'opzione di selezione ASSOLUTO non è supportata dai cursori dinamici.

AVANTI VELOCE
Indica un cursore FORWARD_ONLY, READ_ONLY con l'ottimizzazione delle prestazioni abilitata. L'opzione FAST_FORWARD non può essere specificata con le opzioni SCROLL o FOR_UPDATE.

SOLA LETTURA
Impedisce le modifiche apportate tramite questo cursore. La clausola WHERE CURRENT OF non può fare riferimento a un cursore in un'istruzione UPDATE o DELETE. Questa opzione ha la precedenza sulla funzionalità di aggiornamento del cursore predefinita.

SCROLL_LOCKS
Indica che gli aggiornamenti posizionati o le eliminazioni effettuate utilizzando il cursore hanno sicuramente esito positivo. SQL Server blocca le righe mentre vengono lette nel cursore per garantire che tali righe siano disponibili per modifiche successive. L'opzione SCROLL_LOCKS non può essere specificata con l'opzione FAST_FORWARD o STATIC.

OTTIMISTA
Specifica che gli aggiornamenti posizionati o le eliminazioni effettuate utilizzando il cursore falliranno se la riga è stata aggiornata da quando è stata letta nel cursore. SQL Server non blocca le righe mentre vengono lette nel cursore. Vengono invece utilizzati i confronti timestamp valori di colonna o checksum, se non nella tabella timestamp colonna per determinare se la riga è cambiata da quando è stata letta nel cursore. Se la riga è stata modificata, i tentativi di aggiornamento o eliminazione del posizionamento falliranno. L'opzione OPTIMISTIC non può essere specificata con l'opzione FAST_FORWARD.

TIPO_ATTENZIONE
Specifica che verrà inviato un avviso al client se il cursore viene convertito in modo implicito da un tipo richiesto a un altro.

select_statement
Un'istruzione SELECT standard che specifica il set di risultati di un cursore. Le parole chiave COMPUTE, COMPUTE BY, FOR BROWSE e INTO non sono consentite select_statement dichiarazione del cursore.

SQL Server converte implicitamente il cursore in un altro tipo se le clausole in select_statement conflitto con un cursore del tipo richiesto. Per ulteriori informazioni, vedere Conversioni implicite del cursore.

PER AGGIORNAMENTO]
Definisce le colonne nel cursore da aggiornare. Se OF nome_colonna [, ... N], solo le colonne elencate consentono modifiche. Se l'istruzione UPDATE viene utilizzata senza un elenco di colonne, l'aggiornamento è possibile per tutte le colonne a meno che non sia stata specificata l'opzione di concorrenza READ_ONLY.

L'istruzione DECLARE CURSOR definisce gli attributi di un cursore del server Transact-SQL, ad esempio le proprietà di visualizzazione e la query utilizzata per creare il set di risultati su cui opera il cursore. L'istruzione OPEN popola il set di risultati e l'istruzione FETCH ne restituisce una riga. L'istruzione CLOSE cancella il set di risultati corrente associato al cursore. L'istruzione DEALLOCATE rilascia le risorse utilizzate dal cursore.

La prima forma dell'istruzione DECLARE CURSOR utilizza la sintassi ISO per specificare i parametri del cursore. La seconda forma dell'istruzione DECLARE CURSOR utilizza estensioni del linguaggio Transact-SQL che consentono di definire i cursori utilizzando gli stessi tipi utilizzati nelle funzioni cursore delle API di database come ODBC e ADO.

Queste due forme non possono essere mescolate. Se si specifica SCROLL o si omettono parole chiave prima della parola chiave CURSOR, non è possibile utilizzare parole chiave tra CURSOR e anche per select_statement parole chiave. Quando si specificano parole chiave tra CURSOR e for select_statement parole chiave, non è possibile specificare SCROLL o INSENSITIVE prima della parola chiave CURSOR.

Se si utilizza la sintassi Transact-SQL per l'istruzione DECLARE CURSOR e non si specificano le opzioni READ_ONLY, OPTIMISTIC o SCROLL_LOCKS, viene presupposto il seguente valore predefinito.

Se l'istruzione SELECT non supporta gli aggiornamenti (o dispone di autorizzazioni insufficienti oppure accede a tabelle remote che non supportano gli aggiornamenti e così via), il cursore è impostato su READ_ONLY.

I cursori STATIC e FAST_FORWARD hanno come impostazione predefinita READ_ONLY.

I cursori DYNAMIC e KEYSET sono impostati automaticamente su OPTIMISTIC.

È possibile fare riferimento ai cursori solo da altre istruzioni Transact-SQL. Le funzioni API del database non possono fare riferimento ai cursori. Ad esempio, una volta dichiarato un cursore, le funzioni e i metodi OLE DB, ODBC o ADO non possono fare riferimento al suo nome. Non è possibile selezionare le righe del cursore utilizzando le funzioni e i metodi API corrispondenti; A questo scopo è necessario utilizzare le istruzioni FETCH Transact-SQL.

È possibile utilizzare le seguenti procedure memorizzate per definire le proprietà di un cursore dopo che è stato dichiarato.

Le variabili possono essere utilizzate come parte select_statement, in cui viene dichiarato il cursore. I valori delle variabili del cursore non cambiano dopo la dichiarazione.

Per impostazione predefinita, le autorizzazioni DECLARE CURSOR vengono concesse a tutti gli utenti che dispongono dell'autorizzazione SELECT sulle visualizzazioni, tabelle e colonne utilizzate dal cursore.

Non è possibile utilizzare cursori o trigger su una tabella con un indice columnstore cluster. Questa limitazione non si applica agli indici non cluster; È possibile utilizzare cursori e trigger su una tabella con un indice columnstore non cluster.

A. Utilizzando un cursore e una sintassi semplici

Il set di risultati creato quando si apre questo cursore include tutte le righe e le colonne della tabella. Questo cursore può essere aggiornato, tutti gli aggiornamenti e le cancellazioni sono rappresentati nella selezione di questo cursore. FETCH``NEXT viene recuperato solo perché il parametro SCROLL non è stato specificato.

DICHIARA vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;

B. Utilizzo di cursori nidificati per visualizzare un report

Nell'esempio seguente vengono utilizzati cursori nidificati per visualizzare un report complesso. Per ciascun provider viene dichiarato un cursore interno.

IMPOSTA NESSUN CONTEGGIO SU ; DICHIARARE @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 ); STAMPA" -------- Rapporto sui prodotti del fornitore --------"; DICHIARA Vendor_cursor CURSOR FOR SELECT VendorID, Nome FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN fornitore_cursor FETCH NEXT FROM fornitore_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 INIZIA STAMPA " " SELEZIONA @messaggio = "----- Prodotti dal fornitore: "+ @nome_venditore STAMPA @messaggio -- Dichiara un cursore interno basato -- su seller_id dal cursore esterno. DICHIARA cursore_prodotto CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Valore variabile dal cursore esterno OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 STAMPA "<>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Ottieni il fornitore successivo. FETCH NEXT FROM fornitore_cursor INTO @vendor_id, @vendor_name FINE CHIUDI venditore_cursore; DEALLOCATE cursore_venditore;

Il cursore è un collegamento all'area della memoria contestuale. In alcune implementazioni del linguaggio di programmazione SQL (Oracle, Microsoft SQL Server) - il set di risultati ottenuto durante l'esecuzione di una query e il puntatore del record corrente ad essa associato. Direi che un cursore è una tabella virtuale che rappresenta un archivio dati alternativo. In questo caso, il cursore consente di accedere ai suoi dati come se fossero i dati di un normale array.
I cursori vengono utilizzati nelle procedure memorizzate. Basta teoria, vediamo un esempio:
Abbiamo un database (il database non è buono, questo è uno dei miei lavoro di laboratorio, ma il nostro insegnante di database ha insistito su tale struttura)

/*informazioni bancarie*/
CREA TABELLA `banca` (

`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,


CHIAVE PRIMARIA (`BankId`)

)MOTORE=InnoDB
SET DI CARATTERI "utf8" COLLATE "utf8_bin" ;
/*dati sui depositi */
CREA TABELLA `bankdistribution` (
`BankId` INTERO (11) NON NULL ,
"Persent" INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTERO (11) NON NULL ,
CHIAVE PRIMARIA(`IdBanca`, `IdCliente`),
CHIAVE `BankId` (`BankId`),
CHIAVE `ClientId` (`ClientId`),
VINCOLO `bankdistribution_fk` CHIAVE ESTERA (`BankId`) RIFERIMENTI `bank` (`BankId`),
VINCOLO `bankdistribution_fk1` CHIAVE ESTERA (`ClientId`) RIFERIMENTI `client` (`ClientId`)
)MOTORE=InnoDB
/*dati sugli investitori*/
CREA TABELLA `client` (
`ClientId` INTERO (3) NON NULL AUTO_INCREMENT,
"CreditCardId" BIGINT(10) NOT NULL ,
`Cognome` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Nome` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Nome` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Telefono` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Indirizzo` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTERO (5) NON NULL ,
CHIAVE PRIMARIA(`ClientId`, `CreditCardId`),
CHIAVE `ClientId` (`ClientId`)

)MOTORE=InnoDB
AUTO_INCREMENT=11 SET DI CARATTERI "utf8" COLLATE "utf8_bin"

Diciamo che dobbiamo ricevere ciascuna banca a turno ed eseguire alcune azioni con essa, la seguente query potrebbe aiutarci in questo

Seleziona `bank`.* DA `bank` NUMERO LIMITE DI_RECORD_CHE ABBIAMO BISOGNO,1

. Pertanto, utilizzando LIMIT WE NEED_RECORD NUMBER, 1, estraiamo ciascun record in un ciclo dalla tabella bank ed eseguiamo con esso le azioni necessarie, aumentando al contempo il valore di WE NEED_RECORD NUMBER di 1. Ora faremo lo stesso ma utilizzando un cursore

Inizio
/* variabili da cui estraiamo i dati */
Dichiara vBankId intero;
Dichiara vBankName VARCHAR(50);
Dichiarare vAddress VARCHAR(50);
Dichiarare vPhone VARCHAR (50);
/* variabile hadler - a*/
Dichiara fatto intero predefinito 0;
/*Dichiarazione del cursore*/
Dichiara BankCursor Cursore per selezionare `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` dove 1;
/*Scopo dell'HANDLER, che verrà spiegato di seguito*/
DICHIARA CONTINUA GESTORE PER SQLSTATE "02000" SET done=1;
/* apre il cursore */
Apri BancaCursore;
/*recupera i dati*/
MENTRE fatto = 0 DO

prendiamo le azioni di cui abbiamo bisogno
FINE MENTRE;
/*chiude il cursore*/
Chiudi CursoreBanca;
FINE ;

* Questo codice sorgente è stato evidenziato con Evidenziatore codice sorgente.

Errore: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Messaggio: Nessun dato: zero righe recuperate, selezionate o elaborate

SQLSTATE: 02000 si attiva quando viene raggiunta la fine del cursore o quando select o update restituisce una stringa vuota.

Nella riga successiva abbiamo dichiarato il cursore DECLARE nome_cursore CURSOR FOR select_statement;
Apri il cursore Apri nome_cursore;
Quindi, fino a raggiungere la fine del cursore (WHILE done = 0 DO), estraiamo i dati e li elaboriamo.
È necessario chiudere il cursore prima di uscire dalla procedura memorizzata. Chiudi nome_cursore;

Non sembra complicato. Ma ci sono molte insidie ​​​​associate a SQLSTATE "02000".

MENTRE fatto = 0 DO
FETCH BankCursor IN vBankId,vBankName,vAddress,vPhone;

Seleziona (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limite 1;
facciamo alcune azioni
FINE MENTRE;

* Questo codice sorgente è stato evidenziato con Evidenziatore codice sorgente.

Tutto va bene e corretto dal punto di vista della sintassi. Ma da un punto di vista logico no. Può succedere che i depositanti non abbiano aperto conti in qualche banca, quindi per Select (ContributeAmount) INTO vContributeAmountSUM FROM distribuzione bancaria dove BankId = vBankId limite 1; SQLSTATE: 02000 verrà attivato, la variabile done verrà impostata su 1 e il ciclo while terminerà prima del previsto. Questo può essere evitato procedendo come segue

MENTRE fatto = 0 DO
FETCH BankCursor IN vBankId,vBankName,vAddress,vPhone;
/* estrae per la banca l'importo di qualsiasi suo deposito */


se (vContributeAmountSUM > 0) allora
/* estrae per la banca l'importo di qualsiasi suo deposito */

finisci se ;
facciamo alcune azioni
FINE MENTRE;

* Questo codice sorgente è stato evidenziato con Evidenziatore codice sorgente.

Con la prima richiesta controlliamo se ci sono contributi (se non ce ne sono, allora vContributeAmountSUM == 0) e solo se ce ne sono, recuperiamo i dati.

Ora diciamo che dobbiamo rimuovere l'importo totale dai conti di banche diverse per ciascun cliente
Dichiara ClientSummCursor Cursore per Seleziona somma

Dichiara ClientSummCursor Cursore per Seleziona somma (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client su (client.ClientId = bankdistribution.`ClientId`) dove 1 gruppo per "bankdistribution". "IdCliente";

ApriClientSummCursor;
MENTRE fatto = 0 DO
FETCH BankCursor IN vBankId,vBankName,vAddress,vPhone;
/* estrae per la banca l'importo di qualsiasi suo deposito */
Seleziona Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limite 1;
/* controlla se ci sono davvero depositi in questa banca */
se (vContributeAmountSUM > 0) allora
/* estrae per la banca l'importo di qualsiasi suo deposito */
Seleziona ContributeAmount IN vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limite 1;
finisci se ;


facciamo alcune azioni.
FINE MENTRE;

* Questo codice sorgente è stato evidenziato con Evidenziatore codice sorgente.

La stessa situazione può verificarsi quando i dati nel cursore ClientSummCursor terminano prima dei dati in BankCursor, viene attivato SQLSTATE: 02000, la variabile done è impostata su 1 e il ciclo while termina prima del previsto. Questo può essere evitato procedendo come segue

ApriClientSummCursor;
MENTRE fatto = 0 DO
FETCH BankCursor IN vBankId,vBankName,vAddress,vPhone;
/* estrae per la banca l'importo di qualsiasi suo deposito */
Seleziona Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limite 1;
/* controlla se ci sono davvero depositi in questa banca */
se (vContributeAmountSUM > 0) allora
/* estrae per la banca l'importo di qualsiasi suo deposito */
Seleziona ContributeAmount IN vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limite 1;
finisci se ;
/* prima di estrarre i dati dal secondo cursore, ricorda lo stato sqlstate */
SET vecchio_stato = fatto;
/* estraiamo i dati che ci servono */
FETCH ClientSummCursor INTO vSum,vClientId;
/* controlla se i dati sono stati recuperati e se sqlstate 0200 ha avuto esito negativo */
se (fatto = 0) allora
facciamo alcune azioni.
finisci se ;
/* prima della fine del while, ripristina il valore della variabile done */
set fatto = old_status;
FINE MENTRE;

* Questo codice sorgente è stato evidenziato con Evidenziatore codice sorgente.

Grazie a tutti coloro che hanno letto fin qui, spero che possa essere utile a qualcuno.

L'implementazione di un cursore in un database assomiglia a una classe Java che dispone di un insieme di dati e metodi per elaborarli. In cui cursore sql utilizza i dati come un array regolare. I cursori possono essere utilizzati nei trigger, nelle procedure memorizzate e nelle funzioni.

In conformità con lo standard SQL, quando si lavora con i cursori, vengono eseguite le seguenti azioni di base:

• dichiarazione del cursore;
• aprire un cursore con la lettura dei dati;
• campionamento riga per riga dei dati dal cursore;
• modifica dei dati della riga utilizzando il cursore;
• chiudere il cursore, dopodiché diventa inaccessibile;
• rilasciando il cursore, ad es. rimuovere un cursore dalla memoria perché chiudendolo non necessariamente si libera la memoria ad esso associata.

In diverse implementazioni la definizione cursore potrebbero avere alcune differenze. Ad esempio, a volte è necessario liberare esplicitamente la memoria allocata per un cursore. Una volta liberato il cursore, viene liberata anche la memoria ad esso associata. Ciò rende possibile riutilizzare il nome del cursore. In altre implementazioni, quando il cursore viene chiuso, la memoria viene liberata implicitamente.

In alcuni casi, non puoi fare a meno dell'uso del cursore. Tuttavia, se possibile, dovresti evitare di utilizzare un cursore e lavorare con i comandi standard di elaborazione dati: SELECT, UPDATE, INSERT, DELETE. Ciò è dovuto al fatto che i cursori non consentono operazioni di modifica sull'intero volume di dati e la velocità di esecuzione delle operazioni di elaborazione dei dati utilizzando un cursore è notevolmente inferiore a quella di mezzi standard SQL.

Se un programma può modificare i dati caricati nel cursore, viene definito modificabile. Quando parliamo di cursori, non dovremmo dimenticare l'isolamento delle transazioni. Un utente modifica un record utilizzando un cursore, mentre un altro utente legge il record utilizzando il proprio cursore. Inoltre, può modificare lo stesso record, il che rende necessario mantenere l'integrità dei dati.

Dichiarazione di un cursore

I cursori devono essere dichiarati prima di poter essere utilizzati. Lo standard SQL utilizza la seguente sintassi per creare un cursore:

Dichiara cursore_nome_cursore per select_statement ])]

Questa espressione dichiara un cursore dichiarare il cursore con il nome "nome_cursore".

INSENSIBILE viene creato un cursore statico che non consente di apportare modifiche. Inoltre, le modifiche apportate da altri utenti non vengono visualizzate. Se manca la parola chiave INSENSITIVE, viene creato un cursore dinamico.

Quando si utilizza una parola chiave SCORRERE il cursore creato può essere fatto scorrere in qualsiasi direzione, consentendo di applicare eventuali comandi di selezione. Se questo argomento viene omesso, il cursore sarà sequenziale, ovvero la sua visione sarà possibile solo in una direzione: dall'inizio alla fine.

Espressione select_statement indica una struttura per leggere informazioni come select... from... . Non deve contenere l'operatore in, poiché il cursore ha il proprio operatore andare a prendere per riempire le variabili con i dati del cursore.

Quando si specifica un argomento PER SOLA_LETTURA verrà creato un cursore di sola lettura e non sarà consentita alcuna modifica ai dati. Un cursore dinamico può essere dichiarato come cursore di sola lettura, consentendo la visualizzazione delle modifiche apportate da un altro utente.

Creazione di un cursore con un argomento PER AGGIORNAMENTO consente di apportare modifiche ai dati nel cursore nelle colonne specificate o, in assenza di un argomento OF nome_colonna, in tutte le colonne.

È possibile dichiarare più cursori in una subroutine. Ma ogni cursore deve avere un nome univoco. Per aprire un cursore è necessario utilizzare l'operatore aprire che apre il cursore precedentemente dichiarato:

Cursore aperto

SQL definisce la seguente sintassi per l'apertura di un cursore "cursor open":

Apri nome_cursore;

Recupero dei dati da un cursore, cursore fetch

La sintassi per leggere i dati da un cursore in alcune variabili è la seguente:

Recupera nome_cursore in nome_var [, nome_var] ...;

Operatore andare a prendere seleziona i dati del cursore aperto nelle variabili posizionate dopo in e sposta il cursore nella posizione successiva.

Cursore chiuso

Operatore vicino chiude il cursore. Se l'operatore non viene specificato esplicitamente, il cursore si chiude automaticamente alla chiusura del blocco di programma corrispondente.

Chiudi nome_cursore;

Dopo la chiusura, il cursore diventa inaccessibile. Alla chiusura vengono sbloccate tutte le serrature installate mentre il cursore era in movimento. È possibile chiudere solo i cursori aperti. Un cursore chiuso ma non rilasciato può essere riaperto. Non è consentito chiudere un cursore non aperto.

Ogni DBMS ha le proprie peculiarità nell'utilizzo di un cursore.

Funzionalità dell'utilizzo dei cursori in Oracle

Ci sono quattro attributi del cursore in PL/SQL %TROVATO, %NON TROVATO, %È APERTO E %CONTO RIGHE. Gli attributi del cursore vengono dichiarati come gli operatori %TYPE e %ROWTYPE, a destra del nome del cursore.

Attributo %TROVATO

Attributo %NOTFOUND

L'attributo %NOTFOUND è l'esatto opposto di %FOUND.

Attributo %ISOPEN

L'attributo %ISOPEN indica solo se il cursore è aperto o meno.

Attributo %ROWCOUNT

Attributo %CONTO RIGHEè un attributo numerico che restituisce il numero di righe lette dal cursore in un particolare momento.

Esempio di cursore SQL in un DBMS Oracle

Dichiara v_id manager.id %TYPE; v_nome gestori.nome%TYPE; v_comm gestori.comm%TYPE; cursore crs per selezionare id, nome, somma (comm) come comm dai gestori dove i dati tra "2014-11-01" e "2014-11-30" vengono raggruppati per id, nome; iniziare ad aprire CRS; loop ESCI QUANDO crs%NOTFOUND; RECUPERA crs in v_id, v_name, v_comm; inserire nei valori bonus(id, name, comm) (crs.id, crs.name, crs.comm); fine ciclo; commettere; chiudi crs; FINE;

Funzionalità dell'utilizzo dei cursori nel server SQL

I cursori utilizzati in MSSQL possono essere sequenziali o scorrevoli. Sequenziale consente di selezionare i dati in una sola direzione, dall'inizio alla fine. I cursori scorrevoli consentono il movimento in entrambe le direzioni e consentono di passare a una riga arbitraria nel set di risultati del cursore.

SQL Server supporta cursori statici, dinamici, sequenziali e gestiti da keyset.

Nella progettazione di un cursore statico, le informazioni vengono archiviate come istantanea in un determinato momento. Pertanto, le modifiche apportate al database da un altro utente non sono visibili. Mentre il cursore viene aperto, il server imposta un blocco su tutte le righe incluse nel set completo di risultati. Un cursore statico non cambia dopo la creazione e visualizza sempre il set di dati esistente al momento dell'apertura. Se altri utenti modificano i dati inclusi nel cursore nella tabella di origine, ciò non influirà sul cursore statico. È impossibile apportare modifiche a un cursore statico, quindi si apre sempre in modalità di sola lettura.

Un cursore dinamico richiede un sovraccarico di rete e risorse software aggiuntivi. Quando si utilizzano cursori dinamici non viene creata una copia completa dei dati, ma le selezioni dalle tabelle di origine vengono eseguite solo quando l'utente accede a determinati dati. Durante il recupero, il server blocca le righe e qualsiasi modifica apportata dall'utente al set completo di risultati del cursore sarà visibile nel cursore. Tuttavia, una volta che il cursore ha recuperato i dati, le modifiche apportate da un altro utente non si rifletteranno più nel cursore.

Un cursore controllato da una serie di tasti ha proprietà tra statiche e dinamiche. I record vengono identificati al momento del campionamento e quindi le modifiche vengono tracciate. Questo tipo di cursore è utile quando si implementa lo scorrimento all'indietro. In questo caso, le aggiunte e le eliminazioni dei dati non sono visibili finché le informazioni non vengono aggiornate e il cursore non seleziona nuova versione registra se sono state apportate modifiche ad essi.

I cursori statici sono utilizzati al meglio per i sistemi di elaborazione delle informazioni, ad es. per sistemi di reporting o per scopi statistici e analitici. Un cursore statico è più efficace nel recuperare grandi quantità di dati. Nei sistemi di acquisto elettronico o prenotazione di oggetti (posti, biglietti), è necessario percepire dinamicamente le informazioni aggiornate man mano che vengono apportate modifiche. In questi casi viene utilizzato un cursore dinamico. In queste applicazioni, la quantità di dati trasferiti è generalmente ridotta e l'accesso avviene a livello di singolo record.

I cursori sequenziali non consentono il recupero dei dati nella direzione inversa, ma solo dall'inizio alla fine del cursore. Un cursore sequenziale non memorizza un insieme di tutte le righe di dati. Vengono letti dal database non appena viene effettuata una selezione nel cursore, il che consente di riflettere dinamicamente tutte le modifiche apportate dagli utenti al database utilizzando i comandi INSERT, UPDATE, DELETE. Il cursore legge lo stato dei dati più recente.

Dichiarazione del cursore

Dichiara cursore nome_cursore per SELECT_statement ]]

Quando si utilizza una parola chiave LOCALE Verrà creato un cursore locale visibile solo all'interno del blocco, del trigger, della procedura memorizzata o della funzione definita dall'utente. Parola chiave GLOBALE, definisce un cursore globale che esiste finché la connessione corrente non viene chiusa.

Operatore AVANTI_SOLO definisce un cursore sequenziale che consente di recuperare i dati solo nella direzione dalla prima riga all'ultima. Quando si utilizza l'operatore SCORRERE viene creato un cursore scorrevole che consente l'accesso ai dati in qualsiasi ordine e in qualsiasi direzione.

Il tipo di cursore è determinato dagli operatori:

• STATICO - creazione di un cursore statico;
• DINAMICO - creazione di un cursore dinamico;
• KEYSET - creazione di un cursore chiave.

Se per il cursore SOLA LETTURA specificare l'argomento AVANTI VELOCE, il cursore creato verrà ottimizzato per accesso veloce ai dati. Questo argomento non può essere utilizzato insieme agli argomenti AVANTI_SOLO E OTTIMISTA.

Se il cursore viene creato con l'operatore OTTIMISTA, non è consentito modificare o eliminare le righe modificate dopo l'apertura del cursore.

Quando si specifica un argomento TIPO_ATTENZIONE il server segnalerà una modifica implicita del tipo di cursore se è incompatibile con la query SELECT.

Recupero dei dati da un cursore, fetch

Immediatamente dopo aver aperto il cursore, puoi ottenerne il contenuto utilizzando il seguente comando:

Quando si utilizza l'operatore PRIMO verrà restituita la prima riga del set di risultati del cursore, che diventa la riga corrente. Quando specificato SCORSO verrà restituita l'ultima riga del cursore. Diventa anche la linea corrente.

Quando si specifica un operatore PROSSIMO verrà restituita la riga immediatamente successiva a quella corrente nel set di risultati. Questa linea diventa la linea corrente. Comando predefinito ANDARE A PRENDERE utilizza esattamente questo metodo per recuperare le righe.

Quando si specifica un operatore PRIMA verrà restituita la riga precedente a quella corrente. Questa linea diventa la linea corrente.

Operatore ASSOLUTO (numero_riga | @numero_riga_variabile) restituisce una riga in base al suo numero ordinale assoluto nel set di risultati completo del cursore. Il numero di riga può essere specificato utilizzando una costante o come nome di una variabile in cui è memorizzato il numero di riga. La variabile deve essere un tipo di dati intero. Sono indicati sia i valori positivi che quelli negativi. Quando si specifica un valore positivo, la stringa viene conteggiata dall'inizio del set, mentre un valore negativo viene conteggiato dalla fine. La linea selezionata diventa la linea corrente. Se viene specificato un valore null, non viene restituita alcuna riga.

Discussione RELATIVO (numero di righe | @numero variabile di righe) restituisce l'offset della riga del numero specificato di righe dopo quella corrente. Se si specifica un numero negativo di righe, verrà restituita la riga corrispondente al numero di righe specificato prima di quella corrente. Specificando un valore null verrà restituita la riga corrente. La riga restituita diventa la riga corrente.

Per aprire un cursore globale, è necessario specificare una parola chiave prima del suo nome GLOBALE. Il nome del cursore può essere specificato anche utilizzando una variabile.

Nell'espressione INTO @nome_variabile [,...n] viene definita una lista di variabili in cui verranno memorizzati i valori di colonna corrispondenti della riga restituita. L'ordine delle variabili deve corrispondere all'ordine delle colonne nel cursore e il tipo di dati della variabile deve corrispondere al tipo di dati nella colonna del cursore.

Modifica ed eliminazione dei dati utilizzando un cursore

Per modificare i dati utilizzando un cursore, è necessario eseguire un comando UPDATE nel seguente formato:

In un'unica operazione è possibile modificare i valori di più colonne della riga corrente del cursore, ma devono appartenere tutte alla stessa tabella.

Per eliminare i dati utilizzando un cursore, utilizzare il comando DELETE nel seguente formato:

Di conseguenza, la riga corrente nel cursore verrà eliminata.

Liberare memoria, deallocare

Per rimuovere un cursore dalla memoria, utilizzare il comando

Deallocare nome_cursore;

Attributo @@FETCH_STATUS

Per determinare la presenza di righe nel cursore, dovresti utilizzare una variabile globale @@FETCH_STATUS, che assume un valore diverso da zero se non sono presenti più righe nel cursore. Se l'insieme di righe non è ancora esaurito, allora @@FETCH_STATUS è uguale a zero.

Esempio di cursore nel server SQL

Dichiara @company varchar(50), @manager varchar(50), @message varchar(256); dichiarare crs_clients cursore locale per l'azienda selezionata, manager da clienti dove città = "Mosca" ordine per azienda, manager; print "Elenco clienti"; apri crs_client; recupera successivamente da crs_clients in @company, @manager; while @@FETCH_STATUS = 0 inizia seleziona @message = "Azienda " + @azienda + " manager " + @manager; stampa @messaggio; -- passa al record successivo, recupera successivamente da crs_clients a @company, @manager; FINE; chiudi crs_client; deallocare crs_clients;

Viene fornita la definizione del cursore. Viene fornita una descrizione dei suoi tipi e del suo comportamento: cursori statici, dinamici, sequenziali e chiave. Vengono descritti i principi del controllo del cursore: creazione e apertura di un cursore, lettura di dati, chiusura di un cursore. Vengono forniti esempi di programmazione del cursore.

Concetto di cursore

Una query su un database relazionale restituisce in genere più righe (record) di dati, ma l'applicazione elabora solo un record alla volta. Anche se si tratta di più righe contemporaneamente (ad esempio, visualizzazione di dati sotto forma di fogli di calcolo), il loro numero è comunque limitato. Inoltre, quando si modificano, cancellano o aggiungono dati, l'unità di lavoro è la serie. In questa situazione viene in primo piano il concetto di cursore e, in questo contesto, il cursore è un puntatore a una riga.

Un cursore in SQL è un'area nella memoria del database progettata per contenere l'ultima istruzione SQL. Se l'istruzione corrente è una query di database, in memoria viene archiviata anche una riga di dati della query denominata valore corrente o riga del cursore corrente. L'area specificata nella memoria è denominata ed è accessibile ai programmi applicativi.

Tipicamente, i cursori vengono utilizzati per selezionare da un database un sottoinsieme delle informazioni in esso memorizzate. In qualsiasi momento, una riga del cursore può essere controllata dal programma applicativo. I cursori vengono spesso utilizzati in Istruzioni SQL, integrato in programmi applicativi scritti in linguaggi procedurali. Alcuni di essi sono creati implicitamente dal server del database, mentre altri sono definiti dai programmatori.

Secondo lo standard SQL, quando si lavora con i cursori, si possono distinguere le seguenti azioni principali:

• creazione o dichiarazione del cursore;
• cursore di apertura, cioè. riempiendolo con i dati archiviati nella memoria multilivello;
• selezione dal cursore e modificando le righe di dati con esso;
• chiudendo il cursore, dopodiché diventa inaccessibile ai programmi utente;
• liberando il cursore, cioè. eliminare il cursore come oggetto perché chiudendolo non necessariamente si libera la memoria ad esso associata.

La definizione di cursore può variare leggermente a seconda delle implementazioni. Ad esempio, a volte uno sviluppatore deve liberare in modo esplicito la memoria allocata per un cursore. Dopo rilasciare il cursore anche la sua memoria associata viene liberata. Ciò rende possibile riutilizzare il suo nome. In altre implementazioni quando chiudendo il cursore la liberazione della memoria avviene implicitamente. Subito dopo il ripristino diventa disponibile per altre operazioni: aprendo un altro cursore eccetera.

In alcuni casi, l'uso del cursore è inevitabile. Tuttavia, se possibile, questo dovrebbe essere evitato e funzionare con i comandi standard di elaborazione dati: SELECT, UPDATE, INSERT, DELETE. Oltre al fatto che i cursori non consentono operazioni di modifica sull'intero volume di dati, la velocità di esecuzione delle operazioni di elaborazione dei dati utilizzando un cursore è notevolmente inferiore a quella degli strumenti SQL standard.

Implementazione dei cursori in ambiente MS SQL Server

SQL Server supporta tre tipi di cursori:

• I cursori SQL vengono utilizzati principalmente all'interno di trigger, procedure memorizzate e script;
• i cursori del server operano sul server e implementano l'interfaccia di programmazione dell'applicazione per ODBC, OLE DB, DB_Library;
• I cursori client sono implementati sul client stesso. Recuperano l'intero set di righe di risultati dal server e lo archiviano localmente, il che accelera l'elaborazione dei dati riducendo il tempo sprecato impiegato nelle operazioni di rete.

Diversi tipi di applicazioni multiutente richiedono diversi tipi di accesso parallelo ai dati. Alcune applicazioni richiedono l'accesso immediato alle informazioni sulle modifiche apportate al database. Questo è tipico dei sistemi di prenotazione dei biglietti. In altri casi, come nei sistemi di reporting statistico, la stabilità dei dati è importante perché se vengono costantemente modificati, i programmi non saranno in grado di visualizzare le informazioni in modo efficace. Applicazioni diverse richiedono implementazioni diverse dei cursori.

In SQL Server i tipi di cursore variano in base alle funzionalità fornite. Il tipo di cursore è determinato nella fase della sua creazione e non può essere modificato. Alcuni tipi di cursori possono rilevare le modifiche apportate da altri utenti alle righe incluse nel set di risultati. Tuttavia, SQL Server tiene traccia delle modifiche apportate a tali righe solo durante l'accesso alla riga e non consente la modifica delle modifiche dopo che la riga è già stata letta.

I cursori sono divisi in due categorie: sequenziale e scorrevole. Consecutivo consentono di selezionare i dati in una sola direzione, dall'inizio alla fine. Cursori scorrevoli fornire maggiore libertà di azione: è possibile spostarsi in entrambe le direzioni e saltare su una riga arbitraria del set di risultati del cursore. Se il programma è in grado di modificare i dati a cui punta il cursore, viene chiamato scorrevole e modificabile. Parlando di cursori, non dovremmo dimenticare l'isolamento delle transazioni. Quando un utente modifica un record, un altro lo legge utilizzando il proprio cursore e inoltre può modificare lo stesso record, il che rende necessario mantenere l'integrità dei dati.

SQL Server supporta statici, dinamici, sequenziale e controllato da un mazzo di chiavi.

Nello schema con cursore statico le informazioni vengono lette dal database una volta e archiviate come istantanea (a partire da un certo punto nel tempo), quindi le modifiche apportate al database da un altro utente non sono visibili. Per un po aprendo il cursore il server imposta un blocco su tutte le righe incluse nel set completo di risultati. Cursore statico non cambia dopo la creazione e visualizza sempre il set di dati esistente al momento della sua apertura.

Se altri utenti modificano i dati inclusi nel cursore nella tabella di origine, ciò non influirà sul file cursore statico.

IN cursore statico Non è possibile apportare modifiche, quindi si apre sempre in modalità di sola lettura.

Cursore dinamico mantiene i dati in uno stato "live", ma ciò richiede risorse di rete e software. Utilizzando cursori dinamici non viene creata una copia completa dei dati di origine, ma viene eseguita una selezione dinamica dalle tabelle di origine solo quando l'utente accede a determinati dati. Durante il recupero, il server blocca le righe e qualsiasi modifica apportata dall'utente all'intero set di risultati del cursore sarà visibile nel cursore. Tuttavia, se un altro utente ha apportato modifiche dopo che il cursore ha recuperato i dati, queste non si rifletteranno nel cursore.

Cursore controllato da una serie di tasti, si trova nel mezzo tra questi estremi. I record vengono identificati al momento del campionamento e quindi le modifiche vengono tracciate. Questo tipo di cursore è utile quando si implementa lo scorrimento indietro: quindi le aggiunte e le eliminazioni di righe non sono visibili finché le informazioni non vengono aggiornate e il driver seleziona una nuova versione del record se sono state apportate modifiche.

Cursori sequenziali non sono autorizzati a recuperare i dati nella direzione opposta. L'utente può selezionare solo le righe dall'inizio alla fine del cursore. Cursore seriale non memorizza un insieme di tutte le righe. Vengono letti dal database non appena vengono selezionati nel cursore, il che consente di riflettere dinamicamente tutte le modifiche apportate dagli utenti al database utilizzando i comandi INSERT, UPDATE, DELETE. Il cursore mostra lo stato più recente dei dati.

Cursori statici fornire una visione stabile dei dati. Sono utili per i sistemi di "warehousing" delle informazioni: applicazioni per sistemi di reporting o per scopi statistici e analitici. Oltretutto, cursore statico gestisce meglio di altri il campionamento di grandi quantità di dati. Al contrario, i sistemi elettronici di acquisto o di prenotazione dei biglietti richiedono la percezione dinamica delle informazioni aggiornate man mano che vengono apportate modifiche. In questi casi viene utilizzato cursore dinamico. In queste applicazioni, la quantità di dati trasferiti è generalmente ridotta e l'accesso avviene a livello di riga (record individuale). L'accesso di gruppo è molto raro.

Gestione del cursore in ambiente MS SQL Server

Controllo del cursore implementato eseguendo i seguenti comandi:

• DICHIARARE - creazione o dichiarazione del cursore;
• APRIRE - cursore di apertura, cioè. riempiendolo di dati;
• ANDARE A PRENDERE selezione dal cursore e modifica delle righe di dati utilizzando il cursore;
• VICINO - chiudendo il cursore;
• DEALLOCARE – liberando il cursore, cioè. eliminando il cursore come oggetto.

Dichiarazione del cursore

Lo standard SQL fornisce il seguente comando per creare un cursore:

Utilizzando la parola chiave INSENSITIVE creerà cursore statico. Modifiche ai dati non sono consentite, inoltre, le modifiche apportate da altri utenti non vengono visualizzate. Se manca la parola chiave INSENSITIVE, a cursore dinamico.

Quando si specifica la parola chiave SCROLL, il cursore creato può essere fatto scorrere in qualsiasi direzione, consentendo di utilizzare qualsiasi comando di selezione. Se questo argomento viene omesso, il cursore lo sarà coerente, cioè. la sua visione sarà possibile solo in una direzione: dall'inizio alla fine.

L'istruzione SELECT specifica il corpo della richiesta SELECT, che determina l'insieme di righe risultante per il cursore.

Specificando FOR READ_ONLY viene creato un cursore di sola lettura e non consente alcuna modifica ai dati. Si differenzia da quello statico, sebbene anche quest'ultimo non consenta la modifica dei dati. Può essere dichiarato come cursore di sola lettura cursore dinamico, che consentirà di visualizzare le modifiche apportate da un altro utente.

La creazione di un cursore con un argomento FOR UPDATE consente di eseguire nel cursore modifica dei dati nelle colonne specificate o, in assenza dell'argomento OF nome_colonna, in tutte le colonne.

Nell'ambiente MS SQL Server è accettata la seguente sintassi per il comando di creazione del cursore:

<создание_курсора>::= DECLARE nome_cursore CURSOR FOR istruzione_SELECT ]]

L'utilizzo della parola chiave LOCAL creerà un cursore locale visibile solo nell'ambito del pacchetto, trigger, procedura memorizzata o funzione definita dall'utente che lo ha creato. Quando un pacchetto, un trigger, una procedura o una funzione termina, il cursore viene implicitamente distrutto. Per passare il contenuto del cursore all'esterno del costrutto che lo ha creato, è necessario assegnare un argomento OUTPUT al suo parametro.

Se viene specificata la parola chiave GLOBAL, viene creato un cursore globale; esiste finché la connessione corrente non viene chiusa.

Specificando FORWARD_ONLY si crea cursore seriale; I dati possono essere campionati solo nella direzione dalla prima riga all'ultima.

Specificando SCROLL si crea cursore scorrevole; È possibile accedere ai dati in qualsiasi ordine e in qualsiasi direzione.

Specificando STATIC si crea cursore statico.

Specificando KEYSET si crea un cursore chiave.

Specificando DINAMICO si crea cursore dinamico.

Se si specifica l'argomento FAST_FORWARD per un cursore READ_ONLY, il cursore creato verrà ottimizzato per l'accesso rapido ai dati. Questo argomento non può essere utilizzato insieme agli argomenti FORWARD_ONLY o OPTIMISTIC.

Un cursore creato con l'argomento OPTIMISTIC impedisce la modifica o l'eliminazione delle righe modificate successivamente aprendo il cursore.

Specificando l'argomento TYPE_WARNING, il server informerà l'utente di una modifica implicita al tipo di cursore se è incompatibile con la query SELECT.

Apertura del cursore

Per aprendo il cursore e riempiendolo con i dati della query SELECT specificata durante la creazione del cursore, utilizzare il seguente comando:

Dopo aprendo il cursore Viene eseguita l'istruzione SELECT associata, il cui output viene archiviato nella memoria multilivello.

Recupero dei dati da un cursore

Subito dopo aprendo il cursore puoi selezionarne il contenuto (il risultato dell'esecuzione della query corrispondente) utilizzando il seguente comando:

Specificando FIRST verrà restituita la primissima riga del set completo di risultati del cursore, che diventa la riga corrente.

Specificando LAST viene restituita la riga più recente del cursore. Diventa anche la linea corrente.

Specificando NEXT viene restituita la riga immediatamente successiva a quella corrente nell'intero set di risultati. Adesso diventa attuale. Per impostazione predefinita, il comando FETCH utilizza questo metodo per recuperare le righe.

La parola chiave PRIOR restituisce la riga precedente a quella corrente. Diventa attuale.

Discussione ASSOLUTO (numero_riga | @numero_riga_variabile) restituisce una riga in base al suo numero ordinale assoluto nel set di risultati completo del cursore. Il numero di riga può essere specificato utilizzando una costante o come nome di una variabile in cui è memorizzato il numero di riga. La variabile deve essere un tipo di dati intero. Sono indicati sia i valori positivi che quelli negativi. Quando si specifica un valore positivo, la stringa viene conteggiata dall'inizio del set, mentre un valore negativo viene conteggiato dalla fine. La linea selezionata diventa la linea corrente. Se viene specificato un valore null, non viene restituita alcuna riga.

Discussione RELATIVO (numero di righe | @numero variabile di righe) restituisce la riga corrispondente al numero specificato di righe successive a quella corrente. Se si specifica un numero negativo di righe, verrà restituita la riga corrispondente al numero di righe specificato prima di quella corrente. Specificando un valore null verrà restituita la riga corrente. La riga restituita diventa la riga corrente.

A aprire il cursore globale, è necessario specificare la parola chiave GLOBAL prima del suo nome. Il nome del cursore può essere specificato anche utilizzando una variabile.

Nella progettazione INTO @nome_variabile [,...n] viene specificato un elenco di variabili in cui verranno memorizzati i valori di colonna corrispondenti della riga restituita. L'ordine di specifica delle variabili deve corrispondere all'ordine delle colonne nel cursore e il tipo di dati della variabile deve corrispondere al tipo di dati nella colonna del cursore. Se il costrutto INTO non è specificato, il comportamento del comando FETCH assomiglierà al comportamento del comando SELECT: i dati verranno visualizzati sullo schermo.

Modifica ed eliminazione dei dati

Per apportare modifiche utilizzando un cursore, è necessario eseguire un comando UPDATE nel seguente formato:

È possibile modificare diverse colonne della riga corrente del cursore in un'unica operazione, ma devono appartenere tutte alla stessa tabella.

Per eliminare i dati utilizzando un cursore, utilizzare il comando DELETE nel seguente formato:

Di conseguenza, la riga impostata corrente nel cursore verrà eliminata.

Chiusura del cursore

Dopo la chiusura, il cursore diventa inaccessibile agli utenti del programma. Quando è chiuso, tutti i blocchi installati durante il suo funzionamento vengono rimossi. La chiusura può essere applicata solo ai cursori aperti. Chiuso ma no cursore liberato potrebbe essere riaperto. Non è consentito chiudere un cursore non aperto.

Rilascia il cursore

Chiusura del cursore non libera necessariamente la memoria ad esso associata. Alcune implementazioni devono deallocarlo esplicitamente utilizzando l'istruzione DEALLOCATE. Dopo rilasciare il cursore Viene inoltre liberata memoria, rendendo possibile riutilizzare il nome del cursore.

Per controllare se è stata raggiunta la fine del cursore, si consiglia di utilizzare la funzione: @@FETCH_STATUS

La funzione @@FETCH_STATUS restituisce:

0 se il recupero ha avuto successo;

1 se il recupero non è riuscito a causa di un tentativo di recuperare una riga all'esterno del cursore;

2 se il recupero non è riuscito a causa di un tentativo di accesso a una riga eliminata o modificata.

DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Lista della spesa" DECLARE klient_cursor CURSOR LOCAL FOR SELECT Codice cliente, Azienda, Cognome FROM Cliente WHERE Città="Mosca" ORDER BY Azienda, Cognome OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 BEGIN SELECT @message="Cliente "+@fam+ "Azienda "+ @azienda PRINT @message SELECT @message="Nome prodotto Data di acquisto Costo" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Nome.Prodotto, Data.Transazione, Prodotto .Prezzo* Transazione.Quantità AS Costo DA Prodotto INNER JOIN Transazione SU Prodotto. Codice prodotto=Transazione.Codice prodotto DOVE Transazione.Codice cliente=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "Nessun acquisto" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Costo totale "+ CAST(@s AS CHAR(6)) PRINT @message -- passa al cliente successivo-- FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam FINE CHIUDI klient_cursor DEALLOCATE klient_cursor Esempio 13.6. Cursore per visualizzare l'elenco dei beni acquistati dai clienti di Mosca e il loro costo totale.

Esempio 13.7. Sviluppa un cursore scorrevole per i clienti di Mosca. Se il numero di telefono inizia con 1, cancellate il cliente con quel numero e nella prima voce del cursore sostituite la prima cifra del numero di telefono con 4.

DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "Elenco clienti" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Azienda, Cognome, Telefono DA Cliente DOVE Città ="Mosca" ORDINA PER Azienda, Cognome PER AGGIORNAMENTO APRI klient_cursor RICEVI SUCCESSIVO DA klient_cursor IN @firm, @fam, @tel WHILE @@FETCH_STATUS=0 INIZIA SELEZIONA @message="Cliente "+@fam+ " Azienda "+ @firm " Phone "+ @tel PRINT @message -- se il numero di telefono inizia con 1, -- elimina il cliente con quel numero IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- passa al successivo client FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- nella prima voce, sostituisci la prima cifra del numero di telefono con 4 UPDATE Client SET Phone ='4' + DESTRA(@ tel,LEN(@tel)-1)) DOVE CORRENTE DI klient_cursor SELECT @message="Cliente "+@fam+" Azienda "+ @azienda "Telefono "+ @tel STAMPA @messaggio CHIUDI klient_cursor DEALLOCATE klient_cursor Esempio 13.7. Cursore scorrevole per i clienti di Mosca.

Esempio 13.8. Utilizzo cursore come parametro di output della procedura. La procedura restituisce un set di dati: un elenco di prodotti.

La chiamata alla procedura e la stampa dei dati dal cursore di output si effettua come segue:

DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) INIZIA FETCH NEXT FROM @my_cur INTO @n SELECT @n FINE CHIUDI @my_cur DEALLOCATE @my_cur