Utilizzo di cursori e cicli in Transact-SQL. Cursori nelle stored procedure MySQL Cursori in sql server

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

In primissima approssimazione, quando si lavora con un cursore, vengono utilizzati i seguenti passaggi.

Il cursore viene creato con il comando DECLARE. Il cursore viene aperto con il comando OPEN.

Le operazioni del 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 da un'istruzione SELECT può essere recuperata ed elaborata singolarmente. Nel seguente esempio Oracle, il cursore viene dichiarato in un blocco di dichiarazione insieme a diverse altre variabili. Successivamente, nel successivo blocco BEGIN…END, il cursore viene aperto, viene effettuata una selezione su di esso e il cursore viene chiuso.

CURSOR title_price_cursor IS SELECT titolo, prezzo FROM titoli

DOVE il prezzo NON È NULLO; title_price_val title_price_cursor ROWTYPE; nuovo_prezzo NUMERO(10.2);

APRI title_price_Cursor;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES

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

Poiché questo esempio utilizza PL/SQL, la maggior parte del codice non verrà spiegata in questo libro. Tuttavia, la dichiarazione del cursore è chiaramente visibile nel blocco DECLARE. In un blocco eseguibile PL/SQL, il cursore viene inizializzato con un'istruzione OPEN, i valori vengono recuperati con un'istruzione FETCH e infine il cursore viene chiuso con un'istruzione CLOSE.

L'istruzione SELECT è il cuore del cursore, quindi è consigliabile testarla accuratamente prima di includerla in un'istruzione DECLARE CURSOR. L'istruzione SELECT può operare su una tabella o vista sottostante. Pertanto, i cursori di sola lettura possono funzionare con viste non aggiornabili. L'istruzione SELECT può contenere clausole come ORDER BY, GROUP BY e HAVING purché tali 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 delle stored procedure. Pertanto, in una stored procedure, è possibile definire e popolare un cursore e passarlo al processo batch o alla stored procedure che lo ha richiamato.

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

DECLARE dept_cursor CURSORE

FOR SELECT dept_nbr, dept_name, mgr_nbr

DOVE admin_group="X01"

ORDER BY d "ept_name ASC, dept_nbr DESC, mgr_nbr DESC;

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

DECLARE @nome_editore VARCHAR(20)

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

FETCH NEXT FROM pub_cursor INTO publisher_name

WHILE @s>FETCH_STATUS=0

INSERT INTO editori_esterni VALUES("j>nome_editore)

CHIUDI pub_cursor DEALLOCATE pub_cursor

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

SI APPLICA A: Base SQL Server (dal 2008). Dati SQL AzureSQL AzureParallel Data Warehouse

Specifica gli attributi di un cursore Transact-SQL sul lato server, ad esempio le proprietà di ricerca 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 [INSENSITIVO] [SCORRIMENTO] CURSORE FOR istruzione_seleziona [FOR ( SOLA LETTURA | AGGIORNA [ OF nome_colonna [ ,...n ] ] ) ] [;] Sintassi estesa Transact-SQL DECLARE nome_cursore CURSORE [LOCALE | GLOBALE ] [ SOLO_INVIO | SCORRI ] [ STATICO | IMPOSTAZIONE CHIAVI | DINAMICO | AVANTI_VELOCE] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

nome_cursore
nome_cursore

INSENSIBILE
tempdb; pertanto, le modifiche alle tabelle di base non si riflettono nei dati restituiti dalle selezioni di questo cursore e questo cursore non viene modificato. Quando si utilizza la sintassi ISO, se l'opzione INSENSITIVE non è specificata, gli aggiornamenti confermati e le eliminazioni apportate alle tabelle di base si riflettono nelle selezioni successive.

SCORRERE
Specifica che sono disponibili tutte le opzioni di campionamento (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Se l'istruzione ISO DECLARE CURSOR non specifica l'opzione SCROLL, è supportata solo l'opzione NEXT fetch. L'opzione SCROLL non può essere specificata con l'opzione FAST_FORWARD.

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

select_statement conflitto con un cursore del tipo richiesto.

SOLA LETTURA

Aggiornamento ]
nome_colonna [, .. .N] è specificato, 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 devono rispettare le regole per gli identificatori.

LOCALE
Indica che il cursore è locale rispetto al pacchetto, alla stored procedure o al trigger in cui è stato creato. Il nome del cursore è valido solo all'interno di quest'area. È possibile fare riferimento al cursore tramite variabili locali del pacchetto, procedure memorizzate, trigger o un parametro di output della 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 del cursore per accedere al cursore dopo il completamento della procedura memorizzata. Il cursore viene rilasciato in modo implicito quando un batch, una stored procedure o un trigger completa l'esecuzione, a meno che il cursore non sia stato passato al parametro OUTPUT. Se è stato passato un cursore al parametro OUTPUT, il cursore viene rilasciato quando tutte le variabili che vi fanno riferimento vengono rilasciate o quando si esce dall'ambito.

GLOBALE
Specifica che il cursore è globale rispetto alla connessione. Il nome del cursore può essere utilizzato da qualsiasi stored procedure o pacchetto che viene eseguito sulla connessione. Il cursore viene liberato in modo implicito solo se la connessione viene interrotta.

INOLTRA_SOLO
Specifica che il cursore può essere fatto scorrere solo dalla prima riga all'ultima. È supportata solo l'opzione di recupero FETCH NEXT. Se FORWARD_ONLY viene specificato senza le parole chiave STATIC, KEYSET o DYNAMIC, il cursore si comporta come un cursore DYNAMIC. Se non viene specificato né l'argomento FORWARD_ONLY né l'argomento SCROLL, l'argomento FORWARD_ONLY viene utilizzato per impostazione predefinita a meno che non siano presenti le parole chiave STATIC, KEYSET o DYNAMIC. Per impostazione predefinita, i cursori STATIC, KEYSET e DYNAMIC sono SCROLL. A differenza delle API di database come ODBC e ADO, la modalità FORWARD_ONLY è supportata dai seguenti cursori del linguaggio Transact-SQL: STATIC, KEYSET e DYNAMIC.

STATICO
Definisce un cursore che crea una copia temporanea dei dati per l'utilizzo da parte del cursore. Tutte le query del cursore fanno riferimento alla tabella temporanea specificata in tempdb; pertanto, le modifiche alle tabelle di base non si riflettono nei dati restituiti dalle selezioni di questo cursore e questo cursore non viene modificato.

MAZZO DI CHIAVI
Specifica che l'appartenenza o l'ordine delle righe nel cursore rimane invariato quando viene aperto. Un insieme di chiavi che identificano in modo univoco le righe è incorporato in una tabella in tempdb chiamato chiavi.

Le modifiche ai valori non chiave nelle tabelle di base apportate dal proprietario del cursore o eseguite da altri utenti vengono visualizzate quando il proprietario visualizza il cursore. Le modifiche apportate da altri utenti non vengono visualizzate (non è possibile apportare modifiche utilizzando un cursore del server Transact-SQL). Se una riga viene eliminata, un tentativo di recupero delle righe restituisce @@FETCH_STATUS -2. Gli aggiornamenti ai valori chiave dall'esterno del cursore sono simili all'eliminazione di una vecchia riga e quindi all'inserimento di una nuova riga. La riga con i nuovi valori non è visibile e i tentativi di recuperare la riga con i vecchi valori restituiscono @@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 delle righe in ogni campione possono cambiare. L'opzione di selezione ASSOLUTO non è supportata dai cursori dinamici.

AVANTI VELOCE
Specifica un cursore FORWARD_ONLY, READ_ONLY per il quale è abilitata l'ottimizzazione delle prestazioni. 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 impostazione ha la precedenza sulla capacità predefinita di aggiornare il cursore.

SCROLL_LOCKS
Specifica che l'esito positivo degli aggiornamenti posizionati o delle eliminazioni eseguite dal cursore è garantito. 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 eseguite dal cursore non verranno eseguiti se la riga è stata aggiornata da quando è stata letta nel cursore. SQL Server non blocca le righe mentre vengono lette nel cursore. Invece, vengono utilizzati i confronti timestamp valori di colonna o checksum se la tabella non contiene timestamp colonna per determinare se la riga è stata modificata da quando è stata letta nel cursore. Se la riga è stata modificata, i tentativi di aggiornamento o eliminazione posizionati falliranno. L'opzione OPTIMISTIC non può essere specificata con l'opzione FAST_FORWARD.

TIPO_ATTENZIONE
Indica 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 definisce un set di risultati del cursore. Le parole chiave COMPUTE, COMPUTE BY, FOR BROWSE e INTO non sono consentite select_statement dichiarazione del cursore.

SQL Server converte in modo implicito un cursore in un tipo diverso se le clausole in select_statement conflitto con un cursore del tipo richiesto. Per ulteriori informazioni, consulta Conversioni implicite del cursore.

PER AGGIORNAMENTO]
Specifica le colonne aggiornabili nel cursore. se DI nome_colonna [, ... N], solo le colonne elencate consentono modifiche. Se un'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 sul lato server Transact-SQL, ad esempio le proprietà di ricerca e la query utilizzata per costruire 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 impostare le opzioni del cursore. La seconda forma dell'istruzione DECLARE CURSOR utilizza le estensioni Transact-SQL per definire i cursori utilizzando gli stessi tipi di quelli utilizzati nelle funzioni del cursore dell'API del database come ODBC e ADO.

Non puoi mescolare queste due forme. Se si specifica SCROLL o Ignora le parole chiave prima della parola chiave CURSOR, non è possibile utilizzare le parole chiave tra CURSOR e anche per select_statement parole chiave. Quando si specificano parole chiave tra CURSOR e anche for select_statement parole chiave, non è possibile specificare SCROLL o INSENSITIVE prima della parola chiave CURSOR.

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

Se l'istruzione SELECT non supporta gli aggiornamenti (o non ci sono autorizzazioni sufficienti o quando si accede a tabelle remote che non supportano gli aggiornamenti, ecc.), al cursore viene assegnata l'opzione READ_ONLY.

Per impostazione predefinita, i cursori STATIC e FAST_FORWARD sono READ_ONLY.

Per impostazione predefinita, i cursori DYNAMIC e KEYSET sono OPTIMISTIC.

I cursori possono essere referenziati solo da altre istruzioni Transact-SQL. Le funzioni API del database non possono fare riferimento ai cursori. Ad esempio, una volta che un cursore è stato dichiarato, le funzioni ei metodi OLE DB, ODBC o ADO non possono fare riferimento al suo nome. Le righe del cursore non possono essere selezionate utilizzando le funzioni e i metodi API appropriati; A questo scopo è necessario utilizzare le istruzioni FETCH Transact-SQL.

Le seguenti stored procedure possono essere utilizzate per definire le proprietà del cursore dopo che è stato dichiarato.

Le variabili possono essere utilizzate come parte select_statement Oggetto in cui viene dichiarato il cursore. I valori delle variabili del cursore dopo la sua dichiarazione non vengono modificati.

Per impostazione predefinita, le autorizzazioni DECLARE CURSOR vengono concesse a tutti gli utenti che dispongono dell'autorizzazione SELECT per le viste, le tabelle e le 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. Utilizzo di un semplice cursore e sintassi

Il set di risultati creato all'apertura di questo cursore include tutte le righe e le colonne della tabella. Questo cursore è aggiornabile, tutti gli aggiornamenti e le eliminazioni sono rappresentati nella selezione per questo cursore. FETCH``NEXT è solo un recupero perché SCROLL non è stato specificato.

DECLARE vend_cursor CURSOR FOR SELECT * FROM Acquisti.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;

B. Utilizzo di cursori nidificati per visualizzare un report

L'esempio seguente utilizza i cursori nidificati per visualizzare un report complesso. Per ogni provider viene dichiarato un cursore interno.

IMPOSTA NOCOUNT ON ; DECLARE @vendor_id int , @vendor_name nvarchar (50 ), @message varchar (80 ), @product nvarchar (50 ); STAMPA" -------- Rapporto sui prodotti del fornitore --------"; DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Acquisti.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @messaggio = "----- Prodotti dal venditore: "+ @vendor_name STAMPA @messaggio -- Dichiara un cursore interno basato -- su vendor_id dal cursore esterno. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Acquisti.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Valore variabile dal cursore esterno APRI 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 product_cursor -- Ottieni il fornitore successivo. FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name END CHIUDI vendor_cursor; DEALLOCATE vendor_cursor;

Cursore: un collegamento all'area di memoria del contesto. 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 esso associato. direbbe che il cursore è una tabella virtuale che è un archivio dati alternativo. Allo stesso tempo, il cursore ti consente di accedere ai tuoi dati come se fosse un normale array.
I cursori vengono utilizzati nelle stored procedure. Basta teoria, vediamo un esempio:
Abbiamo un database (il database non è un po' buono, questo è uno dei miei lavoro di laboratorio, ma il nostro insegnante di database ha insistito su una tale struttura)

/* coordinate bancarie */
CREATE TABLE `banca` (

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


CHIAVE PRIMARIA (`BankId`)

) MOTORE=InnoDB
SET DI CARATTERI "utf8" COLLATE "utf8_bin" ;
/*deposita i dati */
CREATE TABLE `distribuzione bancaria` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributoImporto` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
CHIAVE PRIMARIA (`BankId`, `ClientId`),
CHIAVE `BankId` (`BankId`),
CHIAVE `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) RIFERIMENTI `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) RIFERIMENTI `client` (`ClientId`)
) MOTORE=InnoDB
/*dati sui contributori*/
CREATE TABLE `cliente` (
`ClientId` INTEGER (3) NOT 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` INTEGER (5) NOT NULL ,
CHIAVE PRIMARIA (`ClientId`, `CreditCardId`),
CHIAVE `ClientId` (`ClientId`)

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

Supponiamo di dover ricevere a turno ciascuna banca ed eseguire alcune azioni con essa, una tale richiesta potrebbe aiutarci in questo

Seleziona "banca".* FROM "banca" LIMIT RECORD_NUMBER_WEED,1

. Pertanto, utilizzando LIMIT NUMBER_NECESSARY_US_RECORD,1 estraiamo in un ciclo dalla tabella della banca ogni record a turno ed eseguiamo le azioni di cui abbiamo bisogno con esso, aumentando il valore NUMBER_NECESSARY_US_RECORD di 1. Ora faremo lo stesso ma utilizzando il cursore

Inizio
/* variabili in cui recuperiamo i dati */
Dichiarare vBankId integer ;
Dichiara vBankName VARCHAR(50);
Dichiara vAddress VARCHAR(50);
Dichiarare vPhone VARCHAR (50);
/* variabile hadler - a*/
dichiarare fatto numero intero predefinito 0;
/*Dichiarazione del cursore*/
Dichiara BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*assegnazione HANDLER, che verrà spiegata di seguito */
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1;
/* apre il cursore */
OpenBankCursor;
/* estrai i dati */
WHILE fatto = 0 DO

facciamo quello che dobbiamo fare
FINE MENTRE ;
/* chiude il cursore */
Chiudi BankCursor;
FINE ;

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

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 istruzione_selezionata;
Apri cursore Apri nome_cursore;
Inoltre, finché non raggiungiamo la fine del cursore (WHILE done = 0 DO), estraiamo i dati e li elaboriamo.
Chiudere il cursore prima di uscire dalla stored procedure. chiudi nome_cursore;

Sembra niente di complicato. Ma ci sono molte insidie ​​associate a SQLSTATE "02000".

WHILE fatto = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;

Seleziona (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limit 1;
fare qualche azione
FINE MENTRE ;

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

Tutto è buono e corretto dal punto di vista della sintassi. Ma logicamente no. Può succedere che i contributori non abbiano aperto conti in nessuna banca, allora per Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limit 1; SQLSTATE: verrà attivato 02000, la variabile done verrà impostata su 1 e il ciclo while terminerà prima del previsto. Questo può essere evitato facendo quanto segue

WHILE fatto = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* estrae l'importo di uno qualsiasi dei suoi depositi per la banca */


if (vContributeAmountSUM > 0) allora
/* estrae l'importo di uno qualsiasi dei suoi depositi per la banca */

finisci se ;
fare qualche azione
FINE MENTRE ;

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

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

Ora diciamo che dobbiamo recuperare l'importo totale sui conti in diverse banche per ogni cliente
Dichiara ClientSummCursor Cursore per Seleziona somma

Dichiara ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) dove 1 gruppo per `bankdistribution`. `IdCliente`;

Apri ClientSummCursor;
WHILE fatto = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* estrae l'importo di uno qualsiasi dei suoi depositi per la banca */
Select Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limit 1;
/* controlla se ci sono davvero depositi in questa banca */
if (vContributeAmountSUM > 0) allora
/* estrae l'importo di uno qualsiasi dei suoi depositi per la banca */
Selezionare ContributeAmount INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limit 1;
finisci se ;


facciamo qualche azione.
FINE MENTRE ;

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

La stessa situazione può verificarsi quando i dati in 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 facendo quanto segue

Apri ClientSummCursor;
WHILE fatto = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* estrae l'importo di uno qualsiasi dei suoi depositi per la banca */
Select Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limit 1;
/* controlla se ci sono davvero depositi in questa banca */
if (vContributeAmountSUM > 0) allora
/* estrae l'importo di uno qualsiasi dei suoi depositi per la banca */
Selezionare ContributeAmount INTO vContributeAmountSUM FROM bankdistribution dove BankId = vBankId limit 1;
finisci se ;
/* prima di recuperare i dati dal secondo cursore, ricorda lo stato di sqlstate */
SET old_status = fatto;
/* estraiamo i dati di cui abbiamo bisogno */
FETCH ClientSummCursor INTO vSum,vClientId;
/* verifica se i dati sono stati recuperati, se sqlstate 0200 non è riuscito */
se (fatto = 0) allora
facciamo qualche azione.
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 Source Code Highlighter .

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

L'implementazione di un cursore in un database è come una classe Java che ha un insieme di dati e metodi per elaborarlo. In cui cursore sql utilizza i dati come un normale array. I cursori possono essere utilizzati in trigger, stored procedure e funzioni.

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

• dichiarazione del cursore;
• apertura di un cursore con lettura dei dati;
• selezione riga per riga dei dati dal cursore;
• modificare con il cursore i dati della riga;
• chiudendo il cursore, dopodiché diventa inaccessibile;
• liberare il cursore, ad es. rimuovendo il cursore dalla memoria, poiché la sua chiusura non libera necessariamente la memoria ad esso associata.

In diverse implementazioni, la definizione cursore potrebbe avere alcune differenze. Quindi, ad esempio, a volte è necessario rilasciare esplicitamente la memoria allocata per il cursore. Quando il cursore viene liberato, viene liberata anche la memoria ad esso associata. Ciò consente di riutilizzare il nome del cursore. In altre implementazioni, quando il cursore è chiuso, la memoria viene deallocata implicitamente.

In alcuni casi, l'uso di un cursore è indispensabile. Tuttavia, se possibile, dovresti evitare di utilizzare un cursore e lavorare con i comandi di elaborazione dati standard: SELECT, UPDATE, INSERT, DELETE. Ciò è dovuto al fatto che i cursori non consentono di modificare le operazioni sull'intera quantità di dati e la velocità delle operazioni di elaborazione dei dati utilizzando un cursore è notevolmente inferiore a quella di mezzi standard SQL.

Se il programma può modificare i dati caricati nel cursore, allora si chiama modificabile. Parlando di cursori, non dobbiamo dimenticare l'isolamento delle transazioni. Un utente modifica un record con un cursore mentre un altro utente legge il record con il proprio cursore. Inoltre, può modificare lo stesso record, il che richiede l'integrità dei dati.

Dichiarazione del cursore, dichiara il cursore

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

Dichiara cursor_name cursore per select_statement ])]

Questa espressione dichiara un cursore dichiara il cursore con il nome "cursor_name".

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

Quando si utilizza la parola chiave SCORRERE il cursore generato può essere fatto scorrere in qualsiasi direzione, consentendo l'applicazione di qualsiasi comando di selezione. Se questo argomento viene omesso, il cursore sarà sequenziale, cioè la sua visione sarà possibile solo in una direzione, dall'inizio alla fine.

Espressione select_statement punta a un costrutto per la lettura di informazioni di tipo select ... from ... . Non deve contenere l'operatore in, perché il cursore ha il proprio operatore andare a prendere per popolare le variabili con i dati del cursore.

Quando si dà una discussione PER SOLA_LETTURA verrà creato un cursore di sola lettura e non sono consentite modifiche ai dati. Un cursore di sola lettura può essere dichiarato come cursore dinamico per riflettere le modifiche apportate da un altro utente.

Creazione di un cursore con un argomento PER AGGIORNAMENTO consente di modificare i 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, 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, recupero del cursore

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 recupera i dati di un cursore aperto nelle variabili che si trovano dopo in e sposta il cursore nella posizione successiva.

Cursore vicino, cursore vicino

Operatore vicino chiude il cursore. Se l'istruzione non è specificata in modo esplicito, 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 rilasciati tutti i blocchi impostati durante l'azionamento del cursore. Solo i cursori aperti possono essere chiusi. Un cursore chiuso ma non liberato può essere riaperto. Non è consentito chiudere un cursore non aperto.

Ogni DBMS ha le sue peculiarità nell'uso di un cursore.

Caratteristiche dell'utilizzo dei cursori in Oracle

Ci sono quattro attributi del cursore in PL/SQL %TROVATO, %NON TROVATO, %È APERTO E %ROWCOUNT. Gli attributi del cursore sono dichiarati come le istruzioni %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 %ROWCOUNTè un attributo numerico che restituisce il numero di righe lette dal cursore in un determinato momento.

Esempio di cursore SQL in Oracle DBMS

Dichiara v_id manager.id %TYPE; v_name manager.name%TYPE; v_comm manager.comm%TYPE; cursore crs per select id, name, sum(comm) as comm from manager where data between "2014-11-01" and "2014-11-30" group by id, name; iniziare crs aperto; loop EXIT WHEN crs%NOTFOUND; FETCH crs in v_id, v_name, v_comm; inserire in bonus(id, name, comm) i valori (crs.id, crs.name, crs.comm); ciclo finale; commettere; chiudere crs; FINE;

Funzionalità dell'utilizzo dei cursori in SQL Server

I cursori utilizzati in MSSQL possono essere sequenziali e 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 navigare su una riga arbitraria nel set di risultati del cursore.

SQL Server supporta cursori statici, dinamici, sequenziali e basati su keyset.

In uno schema con un cursore statico, le informazioni vengono archiviate come istantanee a partire da un determinato momento. Pertanto, le modifiche apportate al database da un altro utente non sono visibili. Per la durata dell'apertura del cursore, il server acquisisce un blocco su tutte le righe incluse nel set di risultati completo. Un cursore statico non cambia dopo la creazione e visualizza sempre il set di dati che esisteva al momento dell'apertura. Se altri utenti modificano i dati inclusi nel cursore nella tabella originale, ciò non influirà in alcun modo sul cursore statico. Un cursore statico non può essere modificato, quindi si apre sempre in modalità di sola lettura.

Un cursore dinamico richiede un sovraccarico di rete aggiuntivo e risorse software. Quando si utilizzano i cursori dinamici, non viene creata una copia completa dei dati, ma viene effettuata una selezione 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 al set di risultati completo del cursore sarà visibile nel cursore. Tuttavia, una volta che i dati vengono recuperati dal cursore, le modifiche apportate da un altro utente non si rifletteranno più nel cursore.

Un cursore guidato da keyset cade tra statico e dinamico nelle proprietà. I record vengono identificati al momento del campionamento e quindi le modifiche vengono monitorate. Questo tipo di cursore è utile quando si scorre indietro. In questo caso, le aggiunte e le eliminazioni di dati non sono visibili fino a quando le informazioni non vengono aggiornate e il cursore seleziona nuova versione registra se sono state apportate modifiche ad esso.

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 è migliore per recuperare grandi quantità di dati. Nei sistemi di acquisto elettronico o di prenotazione di oggetti (posti a sedere, biglietti), è necessario percepire dinamicamente informazioni aggiornate man mano che vengono apportate modifiche. In tali 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 di recuperare i dati all'indietro, 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 eseguito un recupero del cursore, che consente di riflettere dinamicamente tutte le modifiche apportate dagli utenti al database utilizzando i comandi INSERT, UPDATE, DELETE. Il cursore legge lo stato più recente dei dati.

Dichiarazione del cursore

Dichiara cursor_name cursore per SELECT_statement ]]

Quando si utilizza la parola chiave LOCALE verrà creato un cursore locale visibile solo all'interno di un blocco, trigger, stored procedure o funzione definita dall'utente. Parola chiave GLOBALE, definisce un cursore globale che esiste finché la connessione corrente non viene chiusa.

Operatore INOLTRA_SOLO definisce un cursore sequenziale che consente di recuperare i dati solo nella direzione dalla prima all'ultima riga. 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:

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

Se per il cursore SOLA LETTURA specificare un argomento AVANTI VELOCE, quindi il cursore generato sarà ottimizzato per accesso veloce ai dati. Questo argomento non può essere utilizzato insieme agli argomenti INOLTRA_SOLO E OTTIMISTA.

Se il cursore è stato creato con l'istruzione OTTIMISTA, è vietato modificare ed eliminare le righe che sono state modificate dall'apertura del cursore.

Quando si dà una discussione TIPO_ATTENZIONE il server riporterà una modifica implicita del tipo di cursore se non è compatibile con una query SELECT.

Recupero dei dati da un cursore, fetch

Subito dopo aver aperto il cursore, puoi ottenere il suo contenuto con 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 specifichi 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 modo di 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 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 che memorizza il numero di riga. La variabile deve avere un tipo di dati intero. Sono indicati sia valori positivi che negativi. Se specifichi un valore positivo, la stringa viene contata dall'inizio del set, un valore negativo - dalla fine. La riga selezionata diventa la riga corrente. Se viene specificato null, non viene restituita alcuna stringa.

Discussione RELATIVO (numero_riga | @numero_riga_variabile) restituisce la stringa compensata dal numero specificato di righe dopo quella corrente. Se si specifica un valore negativo per il numero di righe, verrà restituita la riga corrispondente al numero di righe specificato prima di quella corrente. Specificando null verrà restituita la riga corrente. La riga restituita diventa quella corrente.

Per aprire un cursore globale, far precedere il suo nome da una parola chiave GLOBALE. Il nome del cursore può anche essere specificato utilizzando una variabile.

Nell'espressione INTO @nome_variabile [,...n] viene definito un elenco 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.

Modificare ed eliminare i dati utilizzando il cursore

Per modificare i dati utilizzando un cursore, è necessario emettere 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.

Deallocazione della memoria, deallocazione

Per rimuovere un cursore dalla memoria, utilizzare il comando

Dealloca nome_cursore;

@@FETCH_STATUS attributo

Per determinare la presenza di righe nel cursore, utilizzare la variabile globale @@FETCH_STATUS, che assume un valore diverso da zero se non ci sono più righe nel cursore. Se il set di righe non è ancora stato esaurito, @@FETCH_STATUS è zero.

Esempio di cursore in SQL Server

Dichiara @company varchar(50), @manager varchar(50), @message varchar(256); dichiara crs_clients cursor local for select company, manager from customers where city = "Mosca" order by company, manager; print "Elenco clienti"; apri crs_clients; fetch next da crs_clients in @company, @manager; while @@FETCH_STATUS = 0 begin select @message = "Company " + @company + " manager " + @manager; stampa@messaggio; -- passa alla voce successiva fetch next da crs_clients in @company, @manager; FINE; chiudi crs_clients; deallocare crs_clients;

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

Concetto di cursore

Una query di database relazionale in genere restituisce più righe (record) di dati, ma l'applicazione elabora solo un record alla volta. Anche se si tratta di più righe contemporaneamente (ad esempio, emettendo dati sotto forma di fogli di calcolo), il loro numero è ancora limitato. Inoltre, durante la modifica, l'eliminazione o l'aggiunta di dati, l'unità di lavoro è la riga. 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 dedicata alla memorizzazione dell'ultima istruzione SQL. Se l'istruzione corrente è una query di database, anche la stringa di dati della query, denominata valore corrente o riga del cursore corrente, viene archiviata in memoria. L'area di memoria specificata è denominata ed è disponibile per i programmi applicativi.

In genere, i cursori vengono utilizzati per selezionare un sottoinsieme delle informazioni memorizzate in un database. Una riga del cursore può essere controllata dall'applicazione alla volta. I cursori sono spesso usati in istruzioni SQL integrato nei programmi applicativi scritti in linguaggi procedurali. Alcuni di essi sono creati implicitamente dal server del database, mentre altri sono definiti dai programmatori.

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

• creazione o dichiarazione del cursore;
• apertura del cursore, cioè. riempiendolo con i dati che sono immagazzinati nella memoria multilivello;
• recupera dal cursore e modificando le righe di dati con esso;
• chiusura del cursore, dopodiché diventa inaccessibile ai programmi utente;
• liberando il cursore, cioè. cancellando il cursore come oggetto, poiché la sua chiusura non libera necessariamente la memoria ad esso associata.

In diverse implementazioni, la definizione del cursore potrebbe presentare alcune differenze. Quindi, ad esempio, a volte lo sviluppatore deve rilasciare esplicitamente la memoria allocata per il cursore. Dopo liberando il cursore viene liberata anche la memoria ad esso associata. Questo rende possibile riutilizzare il suo nome. In altre implementazioni, chiusura del cursore la memoria viene deallocata implicitamente. Subito dopo il recupero, diventa disponibile per altre operazioni: aprendo un altro cursore eccetera.

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

Implementazione di cursori in ambiente MS SQL Server

SQL Server supporta tre tipi di cursori:

• I cursori SQL vengono utilizzati principalmente all'interno di trigger, stored procedure 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 del client sono implementati sul client stesso. Prendono l'intero set di risultati di righe dal server e lo archiviano localmente, il che velocizza le operazioni di elaborazione dei dati riducendo lo spreco delle operazioni di rete.

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

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

I cursori si dividono in due categorie: consecutivo e scorrevole. Sequenziale consentono di selezionare i dati in una sola direzione, dall'inizio alla fine. Cursori scorrevoli fornire maggiore libertà di azione: puoi spostarti in entrambe le direzioni e saltare a una riga arbitraria del set di risultati del cursore.Se il programma è in grado di modificare i dati puntati dal cursore, viene chiamato scorrevole e modificabile. A proposito di cursori, non bisogna dimenticare l'isolamento delle transazioni. Quando un utente modifica un record, un altro lo legge con il proprio cursore e inoltre può modificare lo stesso record, il che rende necessario mantenere l'integrità dei dati.

SQL Server supporta statico, dinamico, consecutivo e controllato da un mazzo di chiavi.

Nello schema con cursore statico le informazioni vengono lette dal database una volta e archiviate come un'istantanea (a partire da un certo momento), quindi le modifiche apportate al database da un altro utente non sono visibili. Per un po apertura del cursore il server acquisisce un blocco su tutte le righe incluse nel set di risultati completo. Cursore statico non cambia dopo la creazione e visualizza sempre il set di dati che esisteva 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 puoi apportare modifiche, quindi si apre sempre in modalità di sola lettura.

Cursore dinamico mantiene vivi i dati, ma richiede risorse di rete e software. Usando cursori dinamici non viene creata una copia completa dei dati di origine, ma viene effettuata 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 al set di risultati completo 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 un set di tasti, si trova nel mezzo tra questi estremi. I record vengono identificati al momento del campionamento e quindi le modifiche vengono monitorate. Questo tipo di cursore è utile per implementare lo scorrimento all'indietro in modo che le aggiunte e le eliminazioni di righe non siano visibili fino a quando le informazioni non vengono aggiornate e il driver seleziona una nuova versione della voce se sono state apportate modifiche.

Cursori sequenziali non consentire il recupero dei dati nella direzione inversa. L'utente può selezionare solo le righe dall'inizio alla fine del cursore. Cursore sequenziale 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 "magazzino" delle informazioni: applicazioni per sistemi di reporting o per scopi statistici e analitici. Oltretutto, cursore statico meglio di altri affronta il campionamento di una grande quantità di dati. Al contrario, nei sistemi di acquisto elettronico o prenotazione di biglietti è necessario percepire dinamicamente informazioni aggiornate man mano che vengono apportate modifiche. In tali casi, utilizzare cursore dinamico. In queste applicazioni, la quantità di dati trasferiti è generalmente ridotta e l'accesso ad essi viene effettuato a livello di righe (record individuali). L'accesso di gruppo è molto raro.

Controllo del cursore in ambiente MS SQL Server

Controllo del cursore implementato eseguendo i seguenti comandi:

• DICHIARARE - creare o dichiarazione del cursore;
• APRIRE- apertura del cursore, cioè. riempirlo di dati;
• ANDARE A PRENDERE- recupera dal cursore e modifica delle righe di dati con un cursore;
• VICINO- chiusura del cursore;
• DEALLOCATE- liberando il cursore, cioè. eliminando il cursore come oggetto.

Dichiarazione del cursore

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

L'uso della parola chiave INSENSITIVE creerà cursore statico. Modifiche ai dati non sono consentiti, inoltre, non vengono visualizzate le modifiche apportate da altri utenti. Se la parola chiave INSENSITIVE non è presente, an cursore dinamico.

Specificando la parola chiave SCROLL, il cursore generato può essere fatto scorrere in qualsiasi direzione, consentendo l'applicazione di qualsiasi comando di selezione. Se questo argomento viene omesso, il cursore sarà coerente, cioè. la sua visione sarà possibile solo in una direzione, dall'inizio alla fine.

L'istruzione SELECT specifica il corpo della query SELECT, che viene utilizzata per determinare il set di risultati delle righe del cursore.

La specifica dell'argomento FOR READ_ONLY crea un cursore di sola lettura e non consente alcuna modifica ai dati. È distinto da statico, sebbene anche quest'ultimo non consenta la modifica dei dati. Come cursore di sola lettura può essere dichiarato cursore dinamico, che visualizzerà 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 CURSORE FOR SELECT_istruzione ]]

L'utilizzo della parola chiave LOCAL creerà un cursore locale visibile solo all'interno del pacchetto, del trigger, della procedura memorizzata o della 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 di un 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 crea cursore sequenziale; i dati possono essere campionati solo nella direzione dalla prima riga all'ultima.

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

Specificando STATIC crea cursore statico.

Specificando KEYSET si crea un cursore chiave.

Specificando DYNAMIC si crea cursore dinamico.

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

Un cursore creato con l'argomento OPTIMISTIC impedisce la modifica e l'eliminazione delle righe che sono state modificate successivamente apertura del cursore.

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

Apertura del cursore

Per apertura del cursore e riempiendolo con i dati specificati durante la creazione del cursore di query SELECT, viene utilizzato il seguente comando:

Dopo apertura del cursore viene eseguita l'istruzione SELECT associata, il cui output viene archiviato nella memoria stratificata.

Recupero di dati da un cursore

Subito dopo apertura del cursore puoi selezionarne il contenuto (il risultato della query corrispondente) con il seguente comando:

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

Specificando LAST si restituisce la riga più recente del cursore. Diventa anche la linea corrente.

Specificando NEXT viene restituita la riga immediatamente successiva alla riga corrente nel set di risultati completo. Ora diventa attuale. Per impostazione predefinita, il comando FETCH utilizza questo metodo di recupero delle 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 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 che memorizza il numero di riga. La variabile deve avere un tipo di dati intero. Sono indicati sia valori positivi che negativi. Se specifichi un valore positivo, la stringa viene contata dall'inizio del set, un valore negativo - dalla fine. La riga selezionata diventa la riga corrente. Se viene specificato null, non viene restituita alcuna stringa.

Discussione RELATIVO (numero_riga | @numero_riga_variabile) restituisce la stringa che è il numero specificato di righe dopo quella corrente. Se si specifica un valore negativo per il numero di righe, verrà restituita la riga corrispondente al numero di righe specificato prima di quella corrente. Specificando null verrà restituita la riga corrente. La riga restituita diventa quella corrente.

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

Nel disegno INTO @nome_variabile [,...n] viene specificato un elenco 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. Se il costrutto INTO non è specificato, il comportamento del comando FETCH sarà simile al comportamento del comando SELECT: i dati vengono visualizzati sullo schermo.

Modifica e cancellazione dei dati

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

Più colonne della riga corrente del cursore possono essere modificate 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.

Cursore vicino

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

Liberare il cursore

Cursore vicino facoltativamente rilascia la memoria ad essa associata. In alcune implementazioni, devi deallocarlo in modo esplicito con l'istruzione DEALLOCATE. Dopo liberando il cursore anche la memoria viene liberata e il nome del cursore può essere riutilizzato.

Per controllare quando viene 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 recupero di 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 ID 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 " [e-mail protetta]+ "Azienda"+ @firm PRINT @message SELECT @message="Nome prodotto Data acquisto Prezzo" PRINT @message DECLARE tovar_cursor CURSORE FOR SELECT Nome.oggetto, Data.scambio, Prezzo.articolo*Quantità.scambio AS Costo FROM Articolo INNER JOIN Transazione su merci. ItemCode=Deal.ItemCode WHERE Deal.CustomerCode [e-mail protetta] _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=" " [e-mail protetta]+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @messaggio SET @ [e-mail protetta][e-mail protetta] 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 DA klient_cursor A @id_kl, @firm, @fam FINE CHIUDI klient_cursor DEALLOCATE klient_cursor Esempio 13.6. Cursore per visualizzare un elenco di prodotti 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, rimuovi il cliente con quel numero e nella prima voce del cursore, sostituisci la prima cifra nel numero di telefono con 4.

DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "Lista dei clienti" DECLARE klient_cursor CURSORE GLOBAL SCROLL KEYSET FOR SELECT Società, Cognome, Telefono FROM Cliente WHERE Città ="Mosca" ORDINA PER Azienda, Cognome FOR UPDATE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client" [e-mail protetta]+ "Azienda" [e-mail protetta]" Telefono "+ @tel PRINT @message -- se il numero di telefono inizia con 1 -- cancella il cliente con quel numero IF @tel LIKE '1%' DELETE Cliente WHERE CURRENT OF klient_cursor ELSE -- vai al cliente successivo 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 in -- numero di telefono con 4 UPDATE Client SET Phone='4' + RIGHT( @tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client " [e-mail protetta]+" 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 di una procedura. La procedura restituisce un set di dati: un elenco di merci.

Il richiamo della procedura e la stampa dei dati dal cursore di output si effettuano come segue:

DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @ [e-mail protetta] _cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @n END CLOSE @my_cur DEALLOCATE @my_cur