Cursore. Reguli generale pentru comanda DECLARE CURSOR Cursore sql pentru tranzacții

În codul meu T-SQL folosesc întotdeauna operațiuni bazate pe set. Mi s-a spus că aceste tipuri de operațiuni sunt ceea ce SQL Server este proiectat să proceseze și ar trebui să fie mai rapidă decât procesarea în serie. Știu că există cursoare, dar nu sunt sigur cum să le folosesc. Puteți oferi câteva exemple de cursor? Puteți oferi vreo îndrumare cu privire la când să folosiți cursoarele? Presupun că Microsoft le-a inclus în SQL Server dintr-un motiv, așa că trebuie să aibă un loc unde să poată fi utilizate într-un mod eficient.

soluţie

În unele cercuri, cursorele nu sunt niciodată folosite, în altele sunt o ultimă soluție, iar în alte grupuri sunt folosite în mod regulat. În fiecare dintre aceste tabere au motive diferite pentru a se poziționa pe cursor. Indiferent de poziția dvs. pe cursoare, probabil că au un loc în anumite circumstanțe și nu în altele. Deci, se rezumă la înțelegerea ta a tehnicii de codare, apoi la înțelegerea ta a problemei în cauză pentru a lua o decizie dacă procesarea bazată pe cursor este sau nu adecvată sau nu. urmează următoarele instrucțiuni:

Uită-te la un exemplu de cursor
Descompune componentele cursorului
Furnizați exemple suplimentare de cursor
Analizați avantajele și dezavantajele utilizării cursorului

Cum se creează un cursor SQL Server

Crearea unui cursor SQL Server este un proces consecvent, așa că, odată ce învățați pașii, puteți să-i duplicați cu ușurință cu diverse seturi de logică pentru a parcurge datele. Să parcurgem pașii:

Mai întâi, declarați variabilele de care aveți nevoie în logică.
În al doilea rând, declarați cursorul cu un nume specific pe care îl veți folosi în întreaga logică. Aceasta este urmată imediat de deschiderea cursorului.
În al treilea rând, obțineți o înregistrare de pe cursor pentru a începe procesarea datelor.
În al patrulea rând, este procesul de date care este unic pentru fiecare set de logică. Aceasta ar putea fi inserarea, actualizarea, ștergerea etc. pentru fiecare rând de date care a fost preluat. Acesta este cel mai important set de logică în timpul acestui proces, care este efectuat pe fiecare rând.
În al cincilea rând, obțineți următoarea înregistrare de pe cursor așa cum ați făcut la pasul 3 și apoi pasul 4 este repetat din nou prin procesarea datelor selectate.
În al șaselea rând, odată ce toate datele au fost procesate, închideți cursorul.
Ca pas final și important, trebuie să dealocați cursorul pentru a elibera toate resursele interne pe care SQL Server le deține.

De aici, consultați exemplele de mai jos pentru a începe să știți când să utilizați cursoarele SQL Server și cum să faceți acest lucru.

Exemplu de cursor SQL Server

Iată un exemplu de cursor de la tipul Script simplu pentru a face backup pentru toate bazele de date SQL Server în care backup-urile sunt emise în serie:

DECLARE @name VARCHAR(50) -- numele bazei de date DECLARE @path VARCHAR(256) -- calea pentru fișierele de rezervă DECLARE @fileName VARCHAR(256) -- nume de fișier pentru backup DECLARE @fileDate VARCHAR(20) -- folosit pentru numele fișierului SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ("master","model ","msdb","tempdb") OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + "_" + @fileDate + ".BAK" BACKUP DATABASE @ name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

Componentele cursorului SQL Server

Pe baza exemplului de mai sus, cursoarele includ aceste componente:

Instrucțiuni DECLARE - Declarați variabilele utilizate în blocul de cod
Instrucțiuni SET\SELECT - Inițializați variabilele la o anumită valoare
Instrucțiunea DECLARE CURSOR - Completați cursorul cu valori care vor fi evaluate
- NOTĂ - Există un număr egal de variabile în instrucțiunea DECLARE CURSOR FOR ca și în instrucțiunea SELECT. Aceasta ar putea fi una sau mai multe variabile și coloane asociate.
Instrucțiune OPEN - Deschideți cursorul pentru a începe procesarea datelor
Instrucțiuni FETCH NEXT - Atribuiți valorile specifice de pe cursor variabilelor
- NOTĂ - Această logică este utilizată pentru populația inițială înainte de instrucțiunea WHILE și apoi din nou în timpul fiecărei bucle din proces ca o parte a instrucțiunii WHILE
Declarație WHILE - Condiție pentru începerea și continuarea procesării datelor
Instrucțiune BEGIN...END - Începutul și sfârșitul blocului de cod
- NOTĂ - Pe baza procesării datelor pot fi utilizate mai multe instrucțiuni BEGIN...END
Procesarea datelor - În acest exemplu, această logică este de a face backup unei baze de date pe o anumită cale și nume de fișier, dar aceasta ar putea fi aproape orice DML sau logică administrativă
Instrucțiune CLOSE - Eliberează datele curente și blocările asociate, dar permite redeschiderea cursorului
Instrucțiunea DEALLOCATE - Distruge cursorul

Lectură recomandată

Aflați mai multe despre cursorii și alternativele SQL Server:

Exemple suplimentare de cursor SQL Server

În exemplul de mai sus, backup-urile sunt emise prin intermediul unui cursor, consultați aceste alte sfaturi care folosesc logica bazată pe cursor:

Script pentru a crea comenzi pentru a dezactiva, activa, elimina și recrea constrângerile cheii externe în SQL Server

Analiza cursorului SQL Server

Analiza de mai jos este menită să servească drept perspectivă asupra diferitelor scenarii în care logica bazată pe cursor poate fi sau nu benefică:

Procesarea tranzacțiilor online (OLTP) - În majoritatea mediilor OLTP, logica bazată pe SET are cel mai mult sens pentru tranzacțiile scurte. Echipa noastră a întâlnit o aplicație terță parte care folosește cursoare pentru întreaga sa procesare, ceea ce a cauzat probleme, dar acest lucru a fost o întâmplare rar întâlnită. De obicei, logica bazată pe SET este mai mult decât fezabilă și cursoarele sunt rareori necesare.
Raportare - Pe baza designului rapoartelor și a designului de bază, cursoarele nu sunt de obicei necesare. Cu toate acestea, echipa noastră s-a confruntat cu cerințe de raportare în care integritatea referențială nu există în baza de date de bază și este necesar să folosiți un cursor pentru a calcula corect valorile de raportare. Am avut aceeași experiență atunci când am avut nevoie de agregare a datelor pentru procesele din aval, o abordare bazată pe cursor a fost rapid dezvoltată și realizată într-un mod acceptabil pentru a satisface nevoia.
Procesare serializată - Dacă aveți nevoie să finalizați un proces într-o manieră serializată, cursoarele sunt o opțiune viabilă.
Sarcini administrative - Multe sarcini administrative trebuie să fie executate într-o manieră în serie, care se încadrează bine în logica bazată pe cursor, dar există și alte obiecte bazate pe sistem pentru a îndeplini nevoia. În unele dintre aceste circumstanțe, cursoarele sunt folosite pentru a finaliza procesul.
Seturi mari de date - Cu seturi mari de date, puteți întâlni una sau mai multe dintre următoarele:
- Este posibil ca logica bazată pe cursor să nu fie scalată pentru a satisface nevoile de procesare.
- Cu operațiuni mari bazate pe seturi pe servere cu o cantitate minimă de memorie, datele pot fi paginate sau monopoliza serverul SQL, ceea ce necesită mult timp, poate provoca dispute și probleme de memorie. Ca atare, o abordare bazată pe cursor poate satisface nevoia.
- Unele instrumente memorează în mod inerent datele într-un fișier sub coperte, astfel încât procesarea datelor în memorie poate sau nu este cazul.
- Dacă datele pot fi procesate într-o bază de date SQL Server, impactul asupra mediului de producție este doar atunci când datele finale sunt procesate. Toate resursele de pe serverul de staging pot fi utilizate pentru procesele ETL, apoi datele finale pot fi importate.
- SSIS acceptă loturi de seturi de date care pot rezolva nevoia generală de a împărți un set mare de date în dimensiuni mai ușor de gestionat și de a performa mai bine decât o abordare rând cu rând cu un cursor.
- În funcție de modul în care este codificată cursorul sau logica SSIS, este posibil să se repornească în punctul de defecțiune pe baza unui
- Repetați un lot cu comanda GO
Pasii urmatori
- Când vă confruntați cu o decizie de prelucrare a datelor, determinați unde vă aflați cu utilizarea cursorului SQL Server. Ele pot avea sau nu un loc în aplicația sau procesele operaționale dvs. Există multe modalități de a finaliza o sarcină, așa că utilizarea unui cursor poate fi o alternativă rezonabilă sau nu. Tu fii judecătorul.
- Dacă întâmpinați probleme cu o altă tehnică de codare și trebuie să faceți ceva rapid, utilizarea unui cursor poate fi o alternativă viabilă. Procesarea datelor poate dura mai mult, dar timpul de codare poate fi mult mai mic. Dacă aveți un proces unic sau o procesare pe noapte, acest lucru ar putea face smecheria.
- Dacă cursoarele sunt evitate în mediul dvs., asigurați-vă că selectați o altă alternativă viabilă. Doar asigurați-vă că procesul nu va cauza alte probleme. De exemplu, dacă se folosește un cursor și se procesează milioane de rânduri, va șterge toate datele din cache și va provoca o dispută suplimentară? Sau, cu un set mare de date, datele vor fi paginate pe disc sau vor fi scrise într-un director temporar?
- Pe măsură ce evaluați o abordare bazată pe cursor față de alte alternative, faceți o comparație corectă a tehnicilor în ceea ce privește timpul, disputa și resursele necesare. Sperăm că acești factori vă vor conduce la tehnica potrivită.

Se poate întâmpla ca răspunsul la o simplă solicitare a clientului să fie o selecție de sute de mii de rânduri, care este indigerabilă pentru majoritatea clienților. În acest caz, soluția la problema interacțiunii cu clienții este utilizarea cursoarelor ca mecanism universal de schimb de date între server și client. Cursoarele lucrează cu setul de date rezultat (rezultatul execuției interogării), oferind utilizatorilor opțiuni suplimentare pentru procesarea datelor:

Cursorele vă permit să lucrați cu rânduri de tabel prin specificarea numărului lor de serie în setul de date;

Cursorele vă permit să implementați operațiuni complexe de modificare a datelor, de exemplu, atunci când modificarea valorii unei coloane necesită accesarea în mod repetat a valorilor altor coloane.

Ciclul de viață al cursorului:

Crearea cursorului: DECLARA<имя курсора>[INSENSIBIL] [SCROLL] CURSOR PENTRU< SELECT -оператор>PENTRU (NUMAI CITIT | ACTUALIZARE)

Aici, cuvântul cheie INSENSITIVE înseamnă că cursorul va fi static (un instantaneu al datelor), în timp ce implicit cursorul este creat dinamic (selectarea se face de fiecare dată când este accesat rândul). Cuvântul cheie SCROLL înseamnă că cursorul poate fi derulat în orice direcție, altfel cursorul este creat „secvențial”.

Deschiderea cursorului: DESCHIS[GLOBAL]<имя курсора>. Un cursor specificat ca GLOBAL nu este șters automat când procedura sau pachetul din care a fost chemat se termină.

Citinddate : FETCH [[ NEXT | ANTERIOR | PRIMUL | ultima | ABSOLUT n | RELATIV n ] DE LA ] [ GLOBAL ]<имя курсора>[ INTO @ variabila _ nume , …]. SQL Server 2000 permite citirea unui singur rând de pe un cursor. PRIMUL cuvânt cheie este să returneze primul rând al cursorului; LAST - ultima linie a cursorului; NEXT - următoarea linie după cea curentă, linia returnată devine cea curentă; ANTERIOR - precedentul înaintea celui actual; ABSOLUT n - returnează un șir după numărul de serie absolut din cursor; RELATIV - n linii după cea curentă. Datele coloanei vor fi stocate în fiecare dintre variabilele specificate în ordinea în care sunt listate.

Modificarea datelor: execută o comandă UPDATE cu sintaxă concepută pentru a funcționa cu cursoare.

Ștergerea datelor: execută o comandă DELETE cu sintaxă concepută pentru a funcționa cu cursoare.

Închiderea cursorului:ÎNCHIS[GLOBAL]<имя курсора>

Eliberarea cursorului: DEALOCATE [ GLOBAL ]<имя курсора>

Exemplu de cursor:

DECLARE fo_curs CURSOR STATIC FOR

SELECTAȚI nume_eng din fo ORDER BY nume_eng

DECLARE @nume varchar(50)

FETCH FIRST FROM fo_curs ÎN @name

ÎN CAZUL @@FETCH_STATUS=0

FETCH NEXT FROM fo_curs ÎN @name

DEALLOCATE fo_curs

2.7. Asigurarea securității și integrității datelor în Microsoft SQL Server. Managementul bazei de date. Roluri. Atribuirea drepturilor utilizatorilor (GRANT, DENY, REVOKE). Metode și tehnologii de protecție a datelor în SQL Server.

Administrare și securitate SQL Server. .

Sarcina principală a SGBD este de a asigura integritatea și consistența datelor în domeniul selectat. Unul dintre factorii care împiedică sistemul să rezolve această problemă este acțiunile utilizatorilor care încearcă accidental sau deliberat să distrugă structura datelor sau să schimbe datele în sine. Prin urmare, SGBD-ul trebuie protejat nu numai de defecțiunile fizice, ci și de utilizatorii care sunt inadecvați pentru sarcinile implementate. Pentru a face acest lucru, este necesar să proiectați și să conectați un sistem de securitate la baza de date care să împiedice utilizatorii să efectueze acțiuni care depășesc autoritatea lor.

Managementul bazei de date

Pentru a crea o bază de date folosind TSQL, se folosește comanda CREATE DATABASE, dar de obicei se folosește SQL Server Management Studio în acest scop. Există destul de multe operațiuni de bază de date definite în SQL Server: creșterea (scăderea) dimensiunilor fișierelor, modificarea configurației (comanda ALTER), atașarea și detașarea, transferul proprietății, schimbarea numelui, vizualizarea proprietăților și, în final, ștergerea (DROP DATABASE).

Ca și în cazul majorității serverelor de baze de date, există un utilizator în SQL Server cu privilegii administrative complete - aceasta este Administrator de sistem sau „sa”. După instalarea inițială a serverului, parola sa este goală. Utilizatorul care creează o nouă bază de date devine automat proprietarul acesteia ('dbo" - Proprietarul bazei de date). În momentul creării bazei de date, este definit și utilizatorul „invitat”. Dacă contul de utilizator nu este mapat în mod explicit la un anumit utilizator al bazei de date. , utilizatorului i se acordă acces implicit folosind numele de oaspete guest.Guest este de obicei interzis.

Utilizatorul care a creat obiectul în baza de date devine automat proprietarul obiectului și nimeni, inclusiv dbo și sa , nu poate folosi obiectul până când proprietarul le acordă drepturi asupra acestuia. Dar pentru ca utilizatorul să creeze un obiect, proprietarul bazei de date trebuie să îi acorde mai întâi drepturile corespunzătoare.

Rol vă permite să combinați utilizatori care îndeplinesc aceleași funcții pentru a simplifica administrarea. Rolurile sunt încorporate și personalizate. Rolurile încorporate sunt implementate la nivel de server și la nivel de bază de date. Mai jos este un tabel cu rolurile încorporate pentru o bază de date:

db_owner. Are toate drepturile în baza de date

db_accessadmin. Poate adăuga sau elimina utilizatori

db_securityadmin. Gestionează toate permisiunile, obiectele, rolurile și utilizatorii

db_ddladmin. Poate executa toate comenzile DDL, cu excepția GRANT, DENY, REVOKE

db_backupoperator. Poate executa comenzi de arhivare. date

db_datareader. Poate vizualiza. orice date din orice tabel

db_datawriter. Poate o modificare. orice date din orice tabel

db_denydatareader. Interzis vedere dragoste date în orice Mese

db_denydatawriter. Interziceți modificarea oricăror date din orice tabel

Atribuiți drepturi utilizatorilor. Fundamentul securității SQL Server este (1) conturile; (2) utilizatori (utilizatori); (3) roluri; (4) grupuri.

Atunci când un utilizator se conectează la SQL Server, acțiunile pe care le poate efectua sunt determinate de drepturile care îi sunt acordate ca utilizator și ca membru al unui rol. Drepturile sunt acordate de administratorul SGBD, proprietarul bazei de date sau proprietarul unui anumit obiect al bazei de date. Drepturile bazei de date pot fi împărțite în trei categorii: (1) drepturi de acces la obiectele bazei de date; (2) drepturi de a executa comenzi TSQL; (3) drepturi implicite. Serverul vă permite să transferați proprietatea de la un utilizator la altul.

Următoarele comenzi sunt utilizate pentru a gestiona permisiunile utilizatorilor pentru a accesa obiectele bazei de date:

ACORDA( TOATE |< вид действия >,…}

( PE (<имя таблицы или представления>} [(<имя столбца>,…)]

| PE (< имя хранимой процедуры >}

| PE (< имя пользовательской функции >}

CĂTRE ( PUBLIC |<имя объекта системы безопасности>,…}

[ LA FEL DE<имя группы> | <имя роли>]

– atribuie drepturi utilizatorilor, Unde

ALL - utilizatorului i se acordă toate permisiunile posibile, altfel specificați

<вид действия>– drepturi asupra acțiunilor disponibile utilizatorului și anume:

SELECT - pentru vizualizare, pentru o coloană de tabel și pentru un tabel (vizualizare)

INSERT - pentru adăugare, pentru tabel (vizualizare) în ansamblu

UPDATE - pentru modificare, pentru o coloană de tabel și pentru un tabel (vizualizare)

DELETE - pentru ștergere, pentru tabel (vizualizare) în ansamblu

EXECUTE - pentru a executa proceduri stocate

REFERINȚE - capacitatea de a face referire la obiectul specificat (să fie inclus în cheia externă).

<имя объекта системы безопасности>– Conturi SQL Server, utilizatori domeniului Windows; PUBLIC - pentru toți utilizatorii.

WITH GRANT OPTION - Permite utilizatorului acordat în prezent să atribuie drepturi de acces la obiect altor utilizatori.

LA FEL DE<имя группы> | <имя роли>– participarea unui utilizator într-un rol căruia i se oferă posibilitatea de a acorda drepturi altor utilizatori.

Acordați SELECTARE AUTORILOR PENTRU public

Acordați INSERTARE, ACTUALIZARE, ȘTERGERE pe autorii lui Mary, John, Tom

GRANT SELECT ON Plan_Data LA Contabilitate WITH GRANT OPTION

GRANT SELECT ON Plan_Data TO Jack AS Accounting

Jack nu este în rolul Contabil, dar cineva în acel rol poate acorda dreptul

NEGA( TOATE |< вид действия >,…}

( PE (<имя таблицы или представления>} [(<имя столбца>,…)]

| PE (<имя хранимой процедуры>}

| PE (<имя пользовательской функции>}

CĂTRE ( PUBLIC |<имя объекта системы безопасности>,…}

– acces interzis utilizatori la obiectele bazei de date. CASCADE revoca drepturile nu numai acestui utilizator, ci și tuturor celor cărora le-a acordat drepturi.

Exemplu (pe dezactivați execuția comenzii TSQL):

DENY CREATE TABLE LUI Jack CASCADE

Comanda REVOCA folosit pentru a refuza implicit accesul la obiectele bazei de date. Sintaxa este aceeași cu comanda DENY. Refuzarea implicită este similară cu refuzul accesului, cu diferența că are efect doar la nivelul la care este definit. Exemplu: utilizatorului Jack , care este membru al rolului GoodUsers, i s-a acordat acces la tabelul XFiles. Dacă rolul GoodUsers refuză accesul la acest tabel cu REVOKE, utilizatorul Jack poate accesa în continuare acest tabel, deoarece drepturile pentru el sunt definite în mod explicit. Dacă îi aplicați personal REVOKE, acesta va pierde dreptul de a accesa XFiles.

Permisiunile acordate rolurilor sunt moștenite de membrii acestora. Dacă unui utilizator i se acordă acces la un obiect prin apartenența la un rol, dar i se refuză la altul, atunci conflictul de acces este întotdeauna rezolvat în favoarea unei respingeri.

Tehnologii de protecție a datelor în MS SQL Server

1.Mecanism puncte de control– puncte de control care sunt generate după ~60 s pentru a scrie pagini actualizate pe disc (punctul de control poate fi forțat de comanda CHECKPOINT).

2. Mecanisme încorporate și externe pentru verificarea integrității bazei de date (lansate automat sau, ca utilitarul DBCC - Database Consistency Checker - manual).

3. Dublarea fizică (dacă este permisă) a fișierelor bazei de date prin intermediul sistemului de operare (inclusiv mecanismul hard disk-urilor în oglindă).

4. Copiere de rezervă a bazelor de date și a jurnalelor de tranzacții - prin scrierea unui dump de bază de date pe un dispozitiv de rezervă (bandă magnetică sau hard disk).

5. Replicare - posibilitatea de duplicare a informațiilor prin transferul său periodic (în unele cazuri - sincron) de la un server SQL la altul.

6. Criptarea traficului dintre client și server, precum și criptarea codurilor utilizate pentru a lucra cu obiectele bazei de date (proceduri stocate, declanșatoare etc.)

Cursorul este definit. Se oferă o descriere a tipurilor și comportamentului acestuia: cursore statice, dinamice, secvențiale și cheie. Sunt descrise principiile controlului cursorului: crearea și deschiderea unui cursor, citirea datelor, închiderea unui cursor. Sunt date exemple de programare a cursorului.

Conceptul de cursor

O interogare de bază de date relațională returnează de obicei mai multe rânduri (înregistrări) de date, dar aplicația procesează doar o înregistrare la un moment dat. Chiar dacă se ocupă de mai multe rânduri în același timp (de exemplu, scoaterea de date sub formă de foi de calcul), numărul acestora este încă limitat. În plus, atunci când modificați, ștergeți sau adăugați date, unitatea de lucru este rândul. În această situație, conceptul de cursor vine în prim-plan, iar în acest context, cursorul este un pointer către un rând.

Un cursor în SQL este o regiune din memoria bazei de date care este dedicată stocării ultimei instrucțiuni SQL. Dacă instrucțiunea curentă este o interogare de bază de date, șirul de date de interogare, numit valoarea curentă sau rândul curent al cursorului, este de asemenea stocat în memorie. Zona specificată în memorie este denumită și disponibilă pentru programele de aplicație.

De obicei, cursoarele sunt folosite pentru a selecta un subset de informații stocate într-o bază de date. O linie de cursor poate fi verificată de aplicație la un moment dat. Cursoarele sunt adesea folosite în instrucțiunile SQL încorporate în programe de aplicație scrise în limbaje procedurale. Unele dintre ele sunt create implicit de serverul bazei de date, în timp ce altele sunt definite de programatori.

În conformitate cu standardul SQL, atunci când lucrați cu cursoare, pot fi distinse următoarele acțiuni principale:

creație sau declarația cursorului;
deschiderea cursorului, adică umplerea acestuia cu date care sunt stocate în memoria cu mai multe niveluri;
prelua de la cursorși schimbarea rândurilor de date cu acesta;
cursorul închide, după care devine inaccesibil pentru programele utilizatorului;
eliberând cursorul, adică ștergerea cursorului ca obiect, deoarece închiderea acestuia nu eliberează neapărat memoria asociată acestuia.

În diferite implementări, definiția cursorului poate avea unele diferențe. Deci, de exemplu, uneori dezvoltatorul trebuie să elibereze în mod explicit memoria alocată pentru cursor. După eliberând cursorul memoria asociată acestuia este de asemenea eliberată. Acest lucru face posibilă reutilizarea numelui său. În alte implementări, cursorul închide memoria este dealocată implicit. Imediat după recuperare, acesta devine disponibil pentru alte operațiuni: deschiderea unui alt cursor etc.

În unele cazuri, utilizarea unui cursor este inevitabil. Cu toate acestea, dacă este posibil, acest lucru ar trebui evitat și să funcționeze cu comenzi standard de procesare a datelor: SELECT , UPDATE , INSERT , DELETE . Pe lângă faptul că cursoarele nu vă permit să efectuați operațiuni de modificare a întregii cantități de date, viteza de efectuare a operațiunilor de prelucrare a datelor folosind un cursor este vizibil mai mică decât cea a instrumentelor SQL standard.

Implementarea cursoarelor in mediul MS SQL Server

SQL Server acceptă trei tipuri de cursore:

Cursoarele SQL sunt utilizate în principal în declanșatoare, proceduri stocate și scripturi;
cursoarele serverului operează pe server și implementează interfața de programare a aplicației pentru ODBC, OLE DB, DB_Library;
cursoarele client sunt implementate pe clientul însuși. Ei preiau întregul set de rânduri de rezultate de pe server și îl stochează local, ceea ce accelerează operațiunile de procesare a datelor prin reducerea pierderii operațiunilor de rețea.

Diferite tipuri de aplicații multi-utilizator necesită diferite tipuri de acces paralel la date. Unele aplicații au nevoie de acces imediat la informații despre modificările aduse bazei de date. Acest lucru este tipic pentru sistemele de rezervare a biletelor. În alte cazuri, de exemplu, în sistemele de raportare statistică, stabilitatea datelor este importantă, deoarece dacă acestea sunt modificate în mod constant, programele nu vor putea afișa în mod eficient informațiile. Aplicațiile diferite au nevoie de implementări diferite de cursoare.

În mediul SQL Server, tipurile de cursoare diferă în ceea ce privește capabilitățile pe care le oferă. Tipul de cursor este determinat în etapa creării sale și nu poate fi schimbat. Unele tipuri de cursor pot detecta modificările făcute de alți utilizatori asupra rândurilor incluse în setul de rezultate. Cu toate acestea, SQL Server urmărește modificările aduse astfel de rânduri numai în etapa de accesare a rândului și nu permite modificarea modificărilor după ce rândul a fost deja citit.

Cursorele se împart în două categorii: consecutivși derulabil. Secvenţial vă permit să selectați datele într-o singură direcție - de la început până la sfârșit. Cursore care pot fi derulate oferiți mai multă libertate de acțiune - vă puteți deplasa în ambele direcții și puteți sări la o linie arbitrară a setului de rezultate al cursorului.Dacă programul este capabil să modifice datele indicate de cursor, acesta se numește derulabil și modificabil. Vorbind de cursoare, nu ar trebui să uităm de izolarea tranzacțiilor. Când un utilizator modifică o înregistrare, altul o citește cu propriul cursor și, în plus, poate modifica aceeași înregistrare, ceea ce face necesară menținerea integrității datelor.

SQL Server acceptă statice, dinamice, consecutivși controlat de un set de chei.

În schema cu cursor static informațiile sunt citite din baza de date o singură dată și stocate ca un instantaneu (la un moment dat), astfel încât modificările aduse bazei de date de către un alt utilizator nu sunt vizibile. Pentru o vreme deschiderea cursorului serverul obține o blocare pe toate rândurile incluse în setul complet de rezultate. Cursor static nu se modifică după creare și afișează întotdeauna setul de date care exista la momentul deschiderii acestuia.

Dacă alți utilizatori modifică datele incluse în cursorul din tabelul sursă, acest lucru nu va afecta cursor static.

ÎN cursor static Nu puteți face modificări, așa că se deschide întotdeauna în modul numai citire.

Cursor dinamic menține datele vii, dar necesită resurse de rețea și software. Folosind cursoare dinamice nu se creează o copie completă a datelor sursă, ci se realizează o selecție dinamică din tabelele sursă doar atunci când utilizatorul accesează anumite date. În timpul preluării, serverul blochează rândurile și orice modificări pe care utilizatorul le face la setul complet de rezultate ale cursorului vor fi vizibile în cursor. Cu toate acestea, dacă alt utilizator a făcut modificări după ce cursorul a preluat datele, acestea nu vor fi reflectate în cursor.

Cursor controlat de un set de taste, se află la mijloc între aceste extreme. Înregistrările sunt identificate în momentul eșantionării și, astfel, modificările sunt urmărite. Acest tip de cursor este util pentru implementarea derulării înapoi, astfel încât adăugările și ștergerile rândurilor să nu fie vizibile până când informațiile sunt actualizate, iar driverul selectează o nouă versiune a intrării dacă i s-au făcut modificări.

Cursore secvențiale nu permite preluarea datelor în sens invers. Utilizatorul poate selecta doar rânduri de la începutul până la sfârșitul cursorului. Cursor secvenţial nu stochează un set de toate rândurile. Acestea sunt citite din baza de date de îndată ce sunt selectate în cursor, ceea ce vă permite să reflectați în mod dinamic toate modificările făcute de utilizatori în baza de date folosind comenzile INSERT , UPDATE , DELETE. Cursorul arată cea mai recentă stare a datelor.

Cursore statice oferă o vedere stabilă a datelor. Sunt bune pentru sistemele „de depozit” de informații: aplicații pentru sisteme de raportare sau în scopuri statistice și analitice. In afara de asta, cursor static mai bine decât alții face față eșantionării unei cantități mari de date. Dimpotrivă, în sistemele de achiziții electronice sau rezervări de bilete este necesară perceperea dinamică a informațiilor actualizate pe măsură ce se fac modificări. În astfel de cazuri, utilizați cursor dinamic. În aceste aplicații, cantitatea de date transferată este de obicei mică, iar accesul la acestea se realizează la nivel de rânduri (înregistrări individuale). Accesul în grup este foarte rar.

Controlul cursorului în mediul MS SQL Server

Controlul cursorului implementat prin executarea următoarelor comenzi:

DECLARE - crea sau declarația cursorului;
DESCHIS- deschiderea cursorului, adică completarea cu date;
FETCH- prelua de la cursorși schimbarea rândurilor de date cu un cursor;
ÎNCHIDE- cursorul închide;
DEALOCATE- eliberând cursorul, adică ștergerea cursorului ca obiect.

Declarație cursor

Standardul SQL oferă următoarea comandă pentru a crea un cursor:

Folosind cuvântul cheie INSENSITIVE va crea cursor static. Modificări ale datelor nu sunt permise, în plus, modificările efectuate de alți utilizatori nu sunt afișate. Dacă cuvântul cheie INSENSITIVE nu este prezent, an cursor dinamic.

Prin specificarea cuvântului cheie SCROLL, cursorul generat poate fi derulat în orice direcție, permițând aplicarea oricăror comenzi de selecție. Dacă acest argument este omis, cursorul va fi consistent, adică vizualizarea sa va fi posibilă doar într-o singură direcție - de la început până la sfârșit.

Instrucțiunea SELECT specifică corpul interogării SELECT, care este utilizată pentru a determina setul de rezultate de rânduri de cursor.

Specificarea argumentului FOR READ_ONLY creează un cursor numai pentru citire și nu permite nicio modificare a datelor. Este diferit de static, deși acesta din urmă nu permite nici modificarea datelor. Ca un cursor numai pentru citire poate fi declarat cursor dinamic, care va afișa modificările făcute de un alt utilizator.

Crearea unui cursor cu un argument FOR UPDATE vă permite să executați în cursor modificarea datelor fie în coloanele specificate, fie, în absența argumentului OF nume_coloană , în toate coloanele.

În mediul MS SQL Server, următoarea sintaxă pentru comanda de creare a cursorului este acceptată:

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

Utilizarea cuvântului cheie LOCAL va crea un cursor local care este vizibil numai în pachetul, declanșatorul, procedura stocată sau funcția definită de utilizator care l-a creat. Când un pachet, declanșator, procedură sau funcție se termină, cursorul este implicit distrus. Pentru a trece conținutul unui cursor în afara constructului care l-a creat, trebuie să atribuiți un argument OUTPUT parametrului său.

Dacă este specificat cuvântul cheie GLOBAL, este creat un cursor global; acesta există până când conexiunea curentă este închisă.

Specificarea FORWARD_ONLY creează cursor secvenţial; datele pot fi eșantionate numai în direcția de la primul rând la ultimul.

Specificarea SCROLL creează cursor care poate fi derulat; datele pot fi accesate în orice ordine și în orice direcție.

Specificarea STATIC creează cursor static.

Specificarea KEYSET creează un cursor cheie.

Specificarea DYNAMIC creează cursor dinamic.

Dacă specificați argumentul FAST_FORWARD pentru un cursor READ_ONLY, cursorul generat va fi optimizat pentru acces rapid la date. Acest argument nu poate fi utilizat împreună cu argumentele FORWARD_ONLY și OPTIMISTIC.

Un cursor creat cu argumentul OPTIMISTIC previne modificarea și ștergerea rândurilor care au fost modificate de la deschiderea cursorului.

Cu argumentul TYPE_WARNING, serverul va informa utilizatorul despre o modificare implicită a tipului de cursor dacă este incompatibil cu o interogare SELECT.

Deschiderea cursorului

Pentru deschiderea cursoruluiși umplendu-l cu datele specificate la crearea cursorului de interogare SELECT, se folosește următoarea comandă:

După deschiderea cursorului este executată instrucțiunea SELECT asociată, a cărei ieșire este stocată în memoria stratificată.

Preluarea datelor de pe un cursor

Imediat dupa deschiderea cursorului puteți selecta conținutul acestuia (rezultatul interogării corespunzătoare) cu următoarea comandă:

Specificarea FIRST va returna primul rând din setul complet de rezultate ale cursorului, care devine rândul curent.

Specificarea LAST returnează cel mai recent rând al cursorului. Devine și linia actuală.

Specificarea NEXT returnează rândul imediat după rândul curent din setul complet de rezultate. Acum devine actual. În mod implicit, comanda FETCH folosește această metodă de preluare a rândurilor.

Cuvântul cheie PRIOR returnează linia anterioară celei curente. Ea devine actuală.

Argument ABSOLUT (număr_linie | @variabilă_număr_linie) returnează un rând după ordinalul său absolut în setul complet de rezultate al cursorului. Numărul liniei poate fi specificat folosind o constantă sau ca nume al unei variabile care stochează numărul liniei. Variabila trebuie să aibă un tip de date întreg. Sunt indicate atât valorile pozitive, cât și cele negative. Dacă specificați o valoare pozitivă, șirul este numărat de la începutul setului, o valoare negativă - de la sfârșit. Linia selectată devine linia curentă. Dacă este specificat null, nu este returnat niciun șir.

Argument RELATIV (row_number | @row_number_variable) returnează șirul care este numărul specificat de linii după cel curent. Dacă specificați o valoare negativă pentru numărul de linii, atunci linia care este numărul specificat de linii înainte de cea curentă va fi returnată. Specificarea null va returna rândul curent. Rândul returnat devine cel curent.

La deschide cursorul global, necesită cuvântul cheie GLOBAL înaintea numelui său. Numele cursorului poate fi specificat și folosind o variabilă.

In constructie INTO @variable_name [,...n] este specificată o listă de variabile în care vor fi stocate valorile coloanei corespunzătoare ale rândului returnat. Ordinea variabilelor trebuie să se potrivească cu ordinea coloanelor din cursor, iar tipul de date al variabilei trebuie să se potrivească cu tipul de date din coloana cursorului. Dacă constructul INTO nu este specificat, atunci comportamentul comenzii FETCH se va asemăna cu comportamentul comenzii SELECT - datele sunt afișate pe ecran.

Modificarea și ștergerea datelor

Pentru a face modificări folosind cursorul, trebuie să lansați o comandă UPDATE în următorul format:

Mai multe coloane din rândul curent al cursorului pot fi modificate într-o singură operație, dar toate trebuie să aparțină aceluiași tabel.

Pentru a șterge date folosind un cursor, utilizați comanda DELETE în următorul format:

Ca rezultat, linia setată curentă în cursor va fi ștearsă.

Închiderea cursorului

După închidere, cursorul devine inaccesibil pentru utilizatorii programului. La închidere, toate încuietorile setate în timpul funcționării sale sunt îndepărtate. Închiderea poate fi aplicată numai cursoarelor deschise. închis dar nu cursorul eliberat poate fi redeschis. Nu este permisă închiderea unui cursor nedeschis.

Eliberarea cursorului

Închiderea cursorului eliberează opțional memoria asociată acestuia. În unele implementări, trebuie să îl dealocați în mod explicit cu instrucțiunea DEALLOCATE. După eliberând cursorul memoria este de asemenea eliberată, iar numele cursorului poate fi reutilizat.

Pentru a controla când se ajunge la sfârșitul cursorului, se recomandă utilizarea funcției: @@FETCH_STATUS

Funcția @@FETCH_STATUS returnează:

0 dacă preluarea a avut succes;

1 dacă preluarea a eșuat din cauza unei încercări de a prelua un rând în afara cursorului;

2 dacă preluarea a eșuat din cauza unei încercări de a accesa un rând șters sau modificat.

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 de cumpărături” DECLARE klient_cursor CURSOR LOCAL PENTRU SELECT ID-ul clientului, Compania, Nume FROM Client WHERE Oraș="Moscova" COMANDA PENTRU Companie, Nume DESCHIS klient_cursor FETCH NEXT FROM klient_cursor ÎN @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 BEGIN SELECT @message="Client" [email protected]+ „Companie”+ @firm PRINT @message SELECT @message="Nume produs Data achiziției Preț" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Item.Name, Trade.Date, Item.Price*Trade.Quantity AS Cost FROM Item INNER JOIN Tranzacție PE Bunuri. ItemCode=Deal.ItemCode WHERE Deal.CustomerCode [email protected] _kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT " Fără achiziții" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" " [email protected]+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @ [email protected][email protected] FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Cost total "+ CAST(@s AS CHAR(6)) PRINT @message -- mutați la următorul client-- FETCH NEXT FROM klient_cursor ÎN @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor Exemplul 13.6. Cursor pentru afișarea unei liste de bunuri achiziționate de clienții din Moscova și a costului total al acestora.

Exemplul 13.7. Dezvoltați un cursor care poate fi derulat pentru clienții din Moscova. Dacă numărul de telefon începe cu 1, eliminați clientul cu acel număr și, în prima intrare a cursorului, înlocuiți prima cifră din numărul de telefon cu 4.

DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "Lista clienților" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Company, Last Name, Phone FROM Client WHERE Oraș ="Moscova" ORDER BY Firm, LastName PENTRU ACTUALIZARE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client" [email protected]+ „Firmă” [email protected]„Telefon”+ @tel PRINT @message -- dacă numărul de telefon începe cu 1 -- ștergeți clientul cu acel număr IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- mergeți la clientul următor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- în prima intrare, înlocuiți prima cifră în -- numărul de telefon cu 4 UPDATE Client SET Phone='4' + RIGHT( @tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client " [email protected]+" Firma "+ @firma " Telefon "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Exemplul 13.7. Cursor derulabil pentru clienții din Moscova.

Exemplul 13.8. Utilizare cursorul ca parametru de ieșire al unei proceduri. Procedura returnează un set de date - o listă de bunuri.

Apelarea procedurii și imprimarea datelor de pe cursorul de ieșire se efectuează după cum urmează:

DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @ [email protected] _cur IEȘIȚI PRELUAREA URMĂTORUL DE LA @my_cur ÎN @n SELECTAȚI @n ÎN CÂND (@@FETCH_STATUS=0) ÎNCEPEȚI PRELUAREA URMATĂ DE LA @my_cur ÎN @n SELECTAȚI @n SFÂRȘIT ÎNCHIS @my_cur DEALLOCATE @my_cur

Rebeca M. Riordan „Cursori în Transact-SQL”

Universitatea de Tehnologia Informației pe Internet

http://www.INTUIT.ru

Curs de formare: „Programare în Microsoft SQL Server 2000”

Un cursor este un obiect SQL temporar special conceput pentru a fi utilizat în programe și proceduri stocate. Cu acesta, puteți parcurge setul de rezultate de rânduri de interogare, citind și procesând individual fiecare dintre rândurile sale. În procedurile stocate, puteți utiliza cursoare pentru a efectua calcule complexe care sunt dificil de exprimat folosind sintaxa instrucțiunii SELECT. Materialul teoretic mare al lecției este completat de exemple foarte bune. În special, sunt luate în considerare aplicarea funcției CURSOR_STATUS, descrierea variabilelor @@CURSOR_ROWS și @@FETCH_STATUS și multe altele.

O sa inveti:

declara un cursor;
deschide cursorul;
închide cursorul;
eliberați cursorul;
utilizați o comandă FETCH simplă;
aduce șir în variabile;
selectați un șir după poziția sa absolută;
selectați un rând după poziția relativă;
efectuați modificarea poziției;
efectuați ștergerea pozițională;
utilizați variabila globală @@CURSOR_ROWS pentru a determina numărul de rânduri din setul de cursor;
utilizați variabila globală @@FETCH_STATUS pentru a determina rezultatele comenzii FETCH;
utilizați funcția CURSOR_STATUS pentru a interoga starea cursorului.

Una dintre proprietățile caracteristice ale bazelor de date relaționale este că acțiunile sunt efectuate pe seturi de rânduri. Un set poate fi gol sau conține un singur rând, dar este totuși considerat un set. Aceasta este o proprietate necesară și utilă pentru operațiuni relaționale, dar uneori poate fi incomod pentru aplicații.

De exemplu, deoarece nu există nicio modalitate de a indica un anumit rând dintr-un set, prezentarea rândurilor pe rând utilizatorului poate fi dificilă. Chiar dacă extensiile oferite de Transact-SQL la limbajul SQL standard permit o putere de programare mult mai mare, există totuși operațiuni care sunt dificile, consumatoare de timp sau chiar imposibil de realizat pe baza unor principii stabilite.

Pentru a face față unor astfel de situații, SQL oferă cursoare. Un cursor este un obiect care indică un anumit rând dintr-un set. În funcție de natura cursorului pe care l-ați creat, puteți muta cursorul în cadrul setului și puteți modifica sau șterge datele.

Conceptul de cursoare

Microsoft SQL Server acceptă de fapt două tipuri diferite de cursoare: cursoare Transact-SQL și cursoare API (cursoare de interfață de programare a aplicațiilor). Cursoarele API sunt create într-o aplicație care utilizează Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC sau DB-Library. Fiecare dintre aceste API-uri acceptă funcționalități ușor diferite și utilizează o sintaxă diferită. Nu vom discuta despre API-ul Cursors în detaliu aici; dacă intenționați să le utilizați, vă rugăm să consultați documentația relevantă pentru API și limbajul de programare pe care intenționați să îl utilizați.

Cursoarele Transact-SQL sunt create folosind comanda DECLARE CURSOR. Atât obiectul cursor, cât și setul de rânduri către care indică trebuie să existe pe server. Astfel de cursoare se numesc cursore server. Dacă utilizați un cursor de server dintr-o aplicație conectată la SQL Server printr-o rețea, fiecare operație de cursor necesită o comunicare în rețea bidirecțională. Bibliotecile Cursor API care acceptă cursoare de server acceptă, de asemenea, un cursor client care există pe sistemul client și memorează în cache șirurile pe care le procesează pe client.

Setul de rânduri indicat de cursor este determinat cu ajutorul comenzii SELECT. Când creați un cursor Transact-SQL, există mai multe restricții asupra instrucțiunii SELECT:

comanda SELECT nu poate returna mai multe seturi de rezultate;

comanda SELECT nu poate conține o clauză INTO pentru a crea un tabel nou;

comanda SELECT nu poate conține o clauză COMPUTE sau COMPUTE BY utilizată pentru agregarea rezultatelor. (Cu toate acestea, poate conține funcții de agregare, cum ar fi AVG.)

Caracteristicile cursorului

Transact-SQL acceptă mai multe tipuri diferite de cursoare. Identificarea diferitelor caracteristici ale fiecărui cursor este o sarcină destul de plictisitoare, dar poate fi ușoară dacă luăm în considerare pentru fiecare tip de cursor trei caracteristici mai mult sau mai puțin independente: capacitatea de a reflecta modificările în datele sursă, capacitatea de a defila prin mai multe rânduri și capacitatea de a modifica mai multe linii.

Reflectarea schimbării

Capacitatea unui cursor de a reflecta modificările în date se numește sensibilitate a cursorului. Să presupunem că ați creat un cursor pentru o declarație:

SELECT * FROM Oils WHERE Left(OilName, 1) = "B" Baza de date aromoterapie va returna patru rânduri, așa cum se arată în Figura 1. Dacă, în timp ce utilizați cursorul, cineva adaugă o valoare Description pentru elementul Bergamot sau adaugă un șir pentru elementul Bayberry, ce se întâmplă cu setul de șiruri către care indică cursorul?

Orez. 1. Baza de date aromoterapie conține patru rânduri care încep cu litera B.

Când vă creați cursorul, două tipuri de sensibilitate pot fi definite în mod independent: modificările la care rânduri sunt incluse în set (setul de membru) și dacă modificările sunt reflectate în rândurile originale.

defilare

A doua caracteristică a cursorului este capacitatea de a derula atât înainte, cât și înapoi, sau numai înainte. Există o dilemă veche de programare aici: viteză versus flexibilitate. Cursoarele secvențiale (numai înainte) sunt mult mai rapide, dar au mai puțină flexibilitate.

Actualizați

Ultima caracteristică folosită pentru clasificarea cursoarelor este capacitatea de a actualiza rândurile de către cursor. Din nou, cursoarele numai pentru citire sunt de obicei mai performante, dar mai puțin flexibile.

Tipuri de cursore

Transact-SQL acceptă patru tipuri diferite de cursore: statice, cheie, dinamice și cu acces rapid sau cursore „firehose”. Fiecare tip de cursor stochează date diferite despre rândurile către care indică, iar fiecare tip de cursor are diferite combinații ale caracteristicilor discutate în secțiunea anterioară.

Cursore statice

Un cursor static ia un instantaneu al datelor specificate de instrucțiunea SELECT și o stochează în baza de date tempdb. Nu „simte” modificări ale structurii sau ale valorilor datelor și, deoarece orice modificări se vor reflecta doar în copie, acest cursor este întotdeauna deschis în modul doar citire. Cu toate acestea, cursoarele statice pot fi declarate secvențiale sau defilabile.

Cursore cheie

Cursorul cheie copiază numai acele coloane în tempdb care identifică în mod unic fiecare rând. Pentru a putea declara un cursor cheie, fiecare tabel inclus în definiția instrucțiunii SELECT trebuie să aibă un index unic care specifică setul de copiat - cheia.

Cursoarele cheie pot fi modificabile sau numai pentru citire. Ele pot fi, de asemenea, defilabile sau secvenţiale.

Apartenența la un cursor cheie este fixată în momentul în care cursorul este declarat. Dacă în timpul stării deschise a cursorului se adaugă un șir care satisface condiția de selecție, acesta nu va fi adăugat la set. În exemplul nostru anterior, unde s-a folosit LEFT(OilName, 1) = „B” ca condiție de filtru, un rând nou cu valoarea câmpului OilName „Bayberry” nu va fi adăugat la rândurile aferente domeniului cursorului.

În mod similar, dacă se face o modificare la un șir care nu ar satisface condiția de membru al setului, cum ar fi schimbarea „Basil” în „Kumquat”, șirul va rămâne în continuare un membru al setului. Chiar dacă un rând este șters, acesta este în continuare membru al setului, dar SQL Server returnează NULL pentru toate valorile coloanei.

Deși apartenența setată a cursorului rămâne fixă după deschiderea cursorului, modificările valorilor datelor făcute în tabelele sursă sunt totuși reflectate. De exemplu, modificarea valorii câmpului Descriere pentru șirul Bergamot va fi returnată de cursor. Cu toate acestea, modificările la valorile seturilor de taste sunt reflectate în cursoare numai dacă apar în interiorul cursorului. Continuând cu exemplul anterior, dacă valoarea câmpului OilName a fost schimbată din „Basil” în „Kumquat” în interiorul cursorului, cursorul ar returna „Kumquat”. Dacă modificarea a fost făcută de un alt utilizator, cursorul va returna totuși „Basil”.

Sfat. După cum vom vedea în secțiunea următoare, crearea unui cursor și deschiderea unui cursor sunt operații diferite. Pentru a actualiza conținutul unui cursor cheie, îl puteți închide și redeschide.

Cursore dinamice

Un cursor dinamic se comportă ca și cum instrucțiunea SELECT este re-execută de fiecare dată când este accesat rândul. (De fapt, lucrurile funcționează puțin diferit, dar această reprezentare vă permite să judeci mai bine funcționarea cursoarelor dinamice.) Cursoarele dinamice reflectă modificări legate atât de apartenența, cât și de valorile datelor sursă, indiferent dacă aceste modificări sunt făcute în interiorul cursor sau introdus de alt utilizator.

Există o limitare cu cursoarele dinamice: instrucțiunea SELECT utilizată pentru a defini cursorul poate conține doar o clauză ORDER BY dacă există un index care include coloanele utilizate în clauza ORDER BY. Dacă declarați un cursor cheie utilizând o clauză ORDER BY care nu operează pe un index, SQL Server convertește cursorul într-un cursor cheie.

Cursore cu acces rapid

SQL Server acceptă o formă specială, optimizată, a unui cursor care nu poate fi derulat, numai pentru citire. Acest tip de cursor este declarat utilizând cuvântul cheie FAST_FORWARD și este denumit cel mai frecvent cursor „furtun de incendiu”.

Cursoarele de foc sunt foarte eficiente, dar există două limitări importante ale utilizării lor. În primul rând, dacă ați folosit coloane text, ntext sau imagine și o clauză TOP într-o instrucțiune SELECT pentru definirea cursorului, SQL Server convertește cursorul într-un cursor cheie.

În al doilea rând, dacă instrucțiunea SELECT pe care ați folosit-o pentru a defini cursorul conține tabele care au declanșatori și tabele care nu au, cursorul este convertit într-unul static. Declanșatorii sunt scripturi Transact-SQL care sunt executate automat de server atunci când sunt executate pe un tabel de instrucțiuni DML (Data Manipulation Language). Ne vom uita mai detaliat la declanșatorii în Lecția 29, dar deocamdată să ne concentrăm asupra următorului punct: dacă cineva adaugă un declanșator la unul dintre tabelele utilizate de cursor, aplicația dvs. se poate opri brusc, deoarece SQL Server convertește un declanșator mai rapid. cursorul la unul mai lent.

Utilizarea cursorelor

Folosirea cursoarelor este ca și cum ați folosi variabile locale - le declarați, setați o valoare și apoi le utilizați. Totuși, spre deosebire de variabilele locale, care sunt automat distruse atunci când ies din sfera de aplicare, trebuie să eliberați în mod explicit rândurile utilizate de cursor și apoi să distrugeți cursorul.

Crearea cursorelor

Primul pas în folosirea unui cursor este să-l creați. Cursoarele Transact-SQL sunt create folosind instrucțiunea DECLARE CURSOR.

Atenţie! SQL Server acceptă două metode diferite pentru crearea cursoarelor: folosind sintaxa SQL-92 și folosind sintaxa Transact-SQL. Sintaxa SQL-92 este conformă cu standardul ANSI, dar are mai puține funcționalități decât sintaxa Transact-SQL discutată aici.

Instrucțiunea DECLARE CURSOR are următoarea sintaxă:

DECLARE cursor_name CURSOR

[vizibilitate]

[sul]

[Lacăt]

PENTRU select_operator

Vă rugăm să rețineți că toți parametrii care definesc caracteristicile cursorului - vizibilitate, tip etc. - sunt optionale. Valorile implicite pentru aceste opțiuni sunt complexe și pot specifica sau nu cum să interacționeze cu înregistrările sau vizualizările sursă sau opțiunile de lucru cu baza de date. Pentru ca operatorul să fie mai ușor de citit, este mai bine să setați în mod explicit toți parametrii de care aveți nevoie. Astfel vei ști exact ce vei obține.

Vizibilitatea cursorului este definită folosind cuvintele cheie LOCAL sau GLOBAL, care au același efect ca și cuvintele cheie @local_table sau @@global_table atunci când se declară tabelele temporare.

Sfat. SQL Server va închide și va elibera cursorul local când iese din domeniul de aplicare (vizibilitate), dar cel mai bine este să faceți întotdeauna acest lucru în mod explicit.

Parametrul scroll permite utilizarea cuvintelor cheie FORWARD_ONLY și SCROLL, care specifică, respectiv, capacitatea de a se deplasa doar de la început până la sfârșit, sau în orice direcție.

Parametrul tip specifică tipul de cursor de creat. Cuvintele cheie valide sunt STATIC, KEYSET, DYNAMIC și FAST_FORWARD. Parametrul de tip FAST_FORWARD și parametrul de defilare FORWARD_ONLY se exclud reciproc.

Opțiunea de blocare determină dacă rândurile pot fi modificate de cursor și, dacă da, dacă pot fi modificate de alți utilizatori. Dacă este folosit cuvântul cheie READ_ONLY, cursorul nu poate face nicio modificare datelor originale. Cu toate acestea, alți utilizatori pot modifica datele sau o puteți face singur folosind instrucțiunea UPDATE. Dacă blocarea este setată la SCROLL_LOCKS, actualizările pot fi efectuate numai de cursor. Toate celelalte instrucțiuni UPDATE, fie în cadrul aceluiași pachet, fie furnizate de alți utilizatori, vor eșua.

Ultima opțiune de blocare, OPTIMISTIC, permite actualizări de rând atât în interiorul, cât și în exteriorul cursorului. Aceasta este cea mai flexibilă opțiune, dar există întotdeauna șansa ca o modificare făcută de cursor să eșueze dacă linia a fost modificată de când a fost citită de cursor.

Parametrul TYPE_WARNING indică SQL Server să trimită un mesaj de avertizare către client dacă tipul de cursor este convertit de la tipul specificat în alt tip. Acest lucru este posibil dacă declarați un cursor care nu acceptă instrucțiunea SELECT dată.

Este necesar parametrul select_operator specificat în clauza FOR. Specifică rândurile care vor fi incluse în setul de cursor.

Clauza FOR UPDATE este opțională. Cursoarele sunt modificabile în mod implicit, cu excepția cazului în care este setat READ_ONLY, dar este totuși o idee bună să folosiți această expresie pentru a fi sigur de rezultat. Puteți folosi clauza OF din column_names pentru a specifica anumite rânduri pentru care permiteți modificarea. Dacă omiteți secțiunea OF a column_names, modificarea poate fi efectuată pe toate coloanele specificate în instrucțiunea SELECT.

Variabilele cursorului

Transact-SQL vă permite să declarați variabile de tip CURSOR. În acest caz, sintaxa standard DECLARE nu creează un cursor; trebuie să setați în mod explicit variabila pentru cursor folosind cuvântul cheie SET.

DECLARE myCursor CURSOR

PENTRU SELECTARE OilName DIN Uleiuri

DECLARE @myCursorVariable CURSOR

SET @myCursorVariable = myCursor

Această sintaxă este utilă dacă doriți să creați variabile care pot fi alocate unor cursore diferite. Acest lucru poate fi necesar dacă creați o procedură generică pentru a lucra cu seturi de rezultate diferite.

Puteți declara o variabilă de cursor și apoi o puteți utiliza pentru a crea direct un cursor.

DECLARE @myCursorVariable CURSOR SET @myCursorVariable = CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils

Cu această sintaxă, cursorul nu are identificator și poate fi referit doar printr-o variabilă.

Deschiderea cursorului

O declarație de cursor creează un obiect cursor, dar nu creează un set de înregistrări pe care cursorul le va manipula (setul de cursor). Setul de cursor nu este creat până când deschideți cursorul. După sintaxa destul de complicată a instrucțiunii DECLARE CURSOR, sintaxa instrucțiunii pare destul de transparentă:

OPEN cursor_or_variable

Cuvântul cheie GLOBAL ajută la evitarea conflictelor: dacă un cursor declarat cu cuvântul cheie LOCAL și un cursor declarat cu cuvântul cheie GLOBAL au același identificator, referințele la cursor vor fi implicit la cursorul local, dacă nu utilizați cuvântul cheie GLOBAL. Ca și în alte cazuri similare, cel mai bine este să specificați în mod explicit cuvântul cheie dacă deschideți un cursor global.

Închiderea cursorului

Când ați terminat de utilizat cursorul, trebuie să îl închideți. Instrucțiunea CLOSE eliberează resursele folosite pentru a menține setul de cursor și, de asemenea, eliberează orice blocare de rând dacă ați folosit opțiunea SCROLLLOCKS în instrucțiunea DECLARE. Sintaxa comenzii CLOSE este aproape identică cu sintaxa instrucțiunii OPEN - doar cuvântul cheie se modifică:

CLOSE cursor_or_variable

Eliberarea cursorului

Ultima instrucțiune din secvența de creare a cursorului este instrucțiunea DEALLOCATE. Sintaxa sa este, de asemenea, simplă:

DEALLOCATE cursor_or_variable

Totuși, există o subtilitate aici: instrucțiunea DEALLOCATE șterge un identificator sau o variabilă de cursor, dar nu șterge neapărat cursorul în sine. Cursorul în sine nu este șters până când toți identificatorii care se referă la el nu sunt fie eliberați, fie expiră (când sunt în afara domeniului de aplicare). Luați în considerare următoarele exemple:

Creați un cursor DECLARE myCursor CURSOR KEYSET READ_ONLY FOR SELECT * FROM Oils -- Creați o variabilă de cursor DECLARE @cursorVariable CURSOR -- Creați un set de intrări de cursor OPEN myCursor -- Atribuiți o variabilă unui cursor SET @cursorVariable = myCursor -- Eliberați a cursor DEALLOCATE myCursor

După ce cursorul este eliberat, identificatorul myCursor nu mai este asociat cu setul de cursor, dar deoarece setul de cursor este încă referit de variabila @cursorVariable, cursorul și setul de cursor nu sunt eliberate. Dacă nu eliberați în mod explicit și variabila cursorului, cursorul și setul de cursor vor exista până când variabila expiră.

Manipularea șirurilor de caractere cu cursorul

Cursoarele în sine nu ar fi de niciun interes dacă nu ai putea face lucruri cu ele. Transact-SQL acceptă trei comenzi diferite de cursor: FETCH, UPDATE și DELETE.

Comanda FETCH preia șirul specificat din setul de rânduri de cursor. În forma sa cea mai simplă, comanda FETCH are următoarea sintaxă:

FETCH cursor_or_variable

Acest format de înregistrare returnează linia la poziția cursorului (linia curentă).

Utilizați o comandă FETCH simplă

Navigați la folderul SQL 2000 Step by Step din directorul rădăcină, evidențiați scriptul numit SimpleCursor și faceți clic pe butonul Deschidere.
Query Analyzer va încărca scriptul în fereastra de interogare.

Sfat. Este posibil să fi observat că acest script durează mai mult decât instrucțiunea SELECT corespunzătoare. Faptul este că crearea și deschiderea unui cursor necesită timp suplimentar. Nu utilizați niciodată un cursor dacă o instrucțiune SELECT este suficientă pentru a finaliza sarcina.

Comanda FETCH nu numai că poate returna un șir direct, dar vă permite și să stocați valorile din coloana returnată în variabile. Pentru a stoca rezultatele unei comenzi FETCH într-o variabilă, utilizați următoarea sintaxă:

FETCH cursor_or_variable INTO variable_list

Lista_variabile este o listă de identificatori de variabile separate prin virgulă. Înainte de a executa comanda FETCH, trebuie să declarați variabile. Lista de valori trebuie să conțină o variabilă pentru fiecare coloană care apare în instrucțiunea SELECT care definește cursorul. Tipul de date al variabilei trebuie fie să se potrivească, fie să fie compatibil cu tipul de date al coloanei.

Selectați linii cu scrierea în variabile

În toate exemplele anterioare, instrucțiunea FETCH a fost folosită pentru a returna rândul curent. Sintaxa instrucțiunii FETCH oferă, de asemenea, un număr de cuvinte cheie pentru specificarea unui alt rând. Când utilizați aceste cuvinte cheie, instrucțiunea FETCH va returna rândul dat și va face din acesta rândul curent.

Aceste cuvinte cheie vă permit să specificați o poziție absolută în setul de cursor. Cuvintele cheie FIRST și LAST returnează primul și, respectiv, ultimul rând, în timp ce ABSOLUT n specifică rândul n rânduri de la începutul (dacă n este pozitiv) sau de la sfârșitul (dacă n este negativ) al setului de înregistrări al cursorului. Valoarea lui n poate fi exprimată ca o constantă (3) sau ca o variabilă (@theRow).

Selectați rândurile după poziția lor absolută

Evidențiați scriptul numit FetchAbsolute și faceți clic pe butonul Deschidere. Query Analyzer va încărca scriptul în fereastra de interogare.

Pe lângă cuvintele cheie care vă permit să preluați rânduri după poziția lor absolută, instrucțiunea FETCH oferă trei cuvinte cheie care vă permit să preluați rânduri după poziția lor față de rândul curent. Operatorul FETCH NEXT returnează rândul următor, operatorul FETCH PRIOR returnează rândul anterior, iar operatorul FETCH RELATIVE n returnează rândul n rânduri din rândul curent. La fel ca instrucțiunea FETCH ABSOLUTE n, instrucțiunea FETCH RELATIVE n poate specifica rânduri înainte de rândul curent dacă n este negativ și rânduri după rândul curent dacă n este pozitiv.

Selectați rândurile după poziția lor relativă

Evidențiați scriptul numit FetchRelative și faceți clic pe butonul Deschidere. Query Analyzer va încărca scriptul în fereastra de interogare.

Dacă cursorul este de tip FORWARD_ONLY sau PAST_FORWARD, numai cuvântul cheie NEXT poate fi folosit pentru a indica poziția. De fapt, dacă cursorul este unul dintre aceste tipuri, cuvântul cheie NEXT nu este necesar. SQL Server presupune că fiecare instrucțiune FETCH este de fapt o instrucțiune FETCH NEXT.

Utilizați instrucțiunea FETCH NEXT pentru un cursor rapid

Evidențiați scriptul numit FetchFirehose și faceți clic pe butonul Deschidere. Query Analyzer va încărca scriptul în fereastra de interogare.

Faceți clic pe butonul Run Query din bara de instrumente Query Analyzer. Query Analyzer va executa interogarea.

Modificarea și ștergerea rândurilor prin cursoare

Dacă cursorul este modificabil. Modificarea valorilor inițiale în setul de cursor este destul de simplă. Există o formă specială a clauzei WHERE care acceptă modificarea printr-un cursor:

UPDATE table_or_view SET list_to_modify WHERE CURRENT OF cursor_or_variable

Aceasta se numește actualizare pozițională. Transact-SQL acceptă, de asemenea, ștergerea pozițională, care are următoarea notație:

DELETE table_or_view WHERE CURRENT OF cursor_or_variable

Efectuați o actualizare de poziție

Evidențiați scriptul numit PositionedUpdate și faceți clic pe butonul Deschidere. Query Analyzer va încărca scriptul în fereastra de interogare.

Faceți clic pe butonul Execute Query din bara de instrumente Query Analyzer. Query Analyzer va executa interogarea. Rețineți că sunt afișate două panouri grilă. Prima este creată de instrucțiunea FETCH și conține conținutul inițial al coloanelor. Al doilea este rezultatul instrucțiunii SELECT și conține valoarea câmpului Description după modificare.

Monitorizarea cursorelor Transact-SQL

Transact-SQL oferă două variabile globale și o funcție pentru a vă ajuta să controlați funcționarea și starea cursorului. Variabila @@CURSOR_ROWS returnează numărul de rânduri din setul ultimului cursor deschis pe conexiune. Valorile returnate de @@CURSOR_ROWS sunt prezentate în Tabelul 1.

Variabila @@FETCH_STATUS returnează informații despre execuția ultimei comenzi FETCH. Tabelul 2 arată valorile returnate de variabila @@FETCH_STATUS.

În cele din urmă, Transact-SQL oferă funcția CURSOR_STATUS. Această funcție are următoarea sintaxă:

CURSOR_STATUS(type, cursor_or_variable) Tipul poate fi „local”, „global” sau „variabil”, iar cursor_or_variable este identificatorul cursorului sau al variabilei cursorului care trebuie preluat. Rezultatele returnate de funcția CURSOR_STATUS sunt prezentate în Tabelul 3.

Utilizați funcțiile de monitorizare a cursorului

Evidențiați scriptul StatusFunctions și faceți clic pe butonul Deschidere. Query Analyzer va încărca scriptul în fereastra de interogare.

versiune tipărită

Un cursor este un obiect care vă permite să procesați individual rânduri din setul de rezultate returnat de o instrucțiune SELECT. Cursoarele acceptate în limbajul Transact-SQL vor fi discutate în continuare. Acestea sunt cursoare de server care există ca obiecte pe partea serverului bazei de date. Există, de asemenea, cursoare pe partea clientului care sunt utilizate pentru a crea aplicații de bază de date pe partea client.

Se remarcă în literatură că procesarea rând cu rând a unui set de date folosind un cursor în marea majoritate a cazurilor este semnificativ mai lentă decât acțiunile similare efectuate de instrumentele SQL pentru procesarea seturi de rânduri. Prin urmare, se recomandă utilizarea cursorelor numai în cazurile în care descrierea acțiunilor necesare prin operațiuni cu seturi de rânduri este în mod clar ineficientă sau chiar imposibilă.

Lucrul cu un cursor implică de obicei următorii pași:

declararea cursorului;
cursorul de deschidere;
citirea valorilor atributelor în variabile de la prima intrare a cursorului;
deplasarea peste cursor (de obicei într-o buclă) și procesarea intrărilor cursorului;
închide cursorul;
eliberând memoria alocată cursorului.

Un cursor este declarat folosind instrucțiunea DECLARE, al cărei format este prezentat mai jos. Trebuie remarcat faptul că în SQL Server această instrucțiune acceptă atât sintaxa standardului ISO SQL (versiunea standardului nu este specificată în documentație), cât și sintaxa folosind setul CURSOR de extensii de limbaj Transact-SQL.

FOR select_statement

Sintaxă Transact-SQL extinsă:

DECLARE cursor_name CURSOR

FOR select_statement

]][;]

Specificarea cuvântului cheie GLOBAL înseamnă că cursorul care este declarat este disponibil în orice lot de job, declanșator sau procedură stocată care se execută în conexiunea curentă la server. Cursorul este implicit eliberat doar dacă conexiunea este întreruptă.

Un cursor „local”, creat implicit sau când LOCAL este specificat explicit, este disponibil numai în lotul de joburi, procedura stocată sau declanșatorul în care a fost creat. Un astfel de cursor este implicit eliberat atunci când lotul, procedura stocată sau declanșatorul finalizează execuția. Excepția este atunci când cursorul este trecut printr-un parametru de ieșire (OUTPUT) al unei proceduri stocate. Apoi cursorul este eliberat când toate variabilele care se referă la el sunt eliberate sau când „sfera” este ieșită.

FORWARD_ONLY înseamnă că poți doar să „deplasezi” de-a lungul cursorului înainte (este disponibilă doar comanda FETCH NEXT, vezi mai jos), adică. fiecare intrare din cursor poate fi procesată cel mult o dată. Dacă este specificat FORWARD ONLY fără cuvintele cheie STATIC, KEYSET sau DYNAMIC, atunci cursorul se comportă ca un cursor DINAMIC (vezi mai jos). Dacă nu este specificată niciuna dintre opțiunile FORWARD_ONLY sau SCROLL și dacă niciunul dintre cuvintele cheie STATIC, KEYSET sau DYNAMIC nu este specificat, opțiunea FORWARD_ONLY este setată implicit.

SCROLL înseamnă că vă puteți „deplasa” peste cursor în orice direcție (în operatorul FETCH sunt disponibile FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Opțiunea SCROLL nu poate fi specificată cu opțiunea FAST_FORWARD. Cursorele STATIC, KEYSET și DYNAMIC sunt implicit SCROLL.

STATIC înseamnă că cursorul nu este actualizat. Setul de date rezultat al unui astfel de cursor este preluat din baza de date și stocat în baza de date pentru obiectele temporare tempdb. Modificările aduse tabelelor care servesc ca bază pentru cursor nu vor fi afișate în cursor după aceea.

KEYSET - pentru acest tip de cursor, un set de valori cheie care identifică înregistrările selectate este stocat într-un tabel temporar. Când treceți peste cursor, valorile atributelor non-cheie sunt preluate din tabelele corespunzătoare, astfel încât modificările coloanelor non-cheie vor fi vizibile atunci când lucrați cu cursorul. Dacă rândul din cursor a fost deja eliminat din tabel în momentul în care este preluat de instrucțiunea FETCH, variabila de serviciu @@ FETCH_STATUS va returna valoarea -2. Rândurile adăugate în tabele după ce cursorul a fost deschis nu sunt vizibile în cursor. Dacă interogarea care generează cursorul folosește cel puțin un tabel care nu are un index unic, cursorul KEYSET este convertit în STATIC.

DYNAMIC este tipul de cursor care consumă cel mai mult resurse, care afișează toate modificările de date efectuate asupra rândurilor din setul de rezultate, inclusiv rândurile nou introduse. Valorile datelor, ordinea și apartenența rândurilor din fiecare eșantion se pot modifica. Nu puteți utiliza FETCH ABSOLUTE cu cursoare dinamice.

FAST_FORWARD este cel mai rapid tip de cursor, permițându-vă să vă deplasați de la o linie la alta doar „înainte”. Acesta este tipul implicit de cursor (când cuvintele cheie opționale sunt omise). Este echivalent cu un cursor declarat cu opțiunile FORWARD_ONLY și READ_ONLY.

READ_ONLY - definește un cursor „numai în citire”: modificările în baza de date nu pot fi făcute printr-un astfel de cursor.

SCROLL_LOCKS înseamnă că SQL Server blochează rândurile pe măsură ce sunt citite în cursor, asigurându-se că pot fi actualizate sau șterse printr-un cursor de acest tip.

Un cursor declarat cu cuvântul cheie OPTIMISTIC nu solicită o blocare a rândului și permite modificarea datelor. Dacă apar modificări ale tabelului de bază după ce datele sunt citite în cursor, o încercare de a modifica acele date prin cursor duce la o eroare.

TYPE_WARNING specifică că dacă un cursor este convertit implicit din tipul solicitat în altul (de exemplu, conversia cursorului KEYSET în STATIC descrisă mai sus în absența unui index unic în tabel), un avertisment va fi trimis clientului.

Select_statement este o instrucțiune SELECT care generează setul de rezultate al cursorului.

Declarația FOR UPDATE specifică ce coloane să fie actualizate în cursor. Dacă OF nume_coloană [, . . . n], atunci numai coloanele listate vor fi disponibile pentru modificări. Dacă nu există o listă de coloane, actualizarea este posibilă pentru toate coloanele, cu excepția cazului în care cursorul este declarat cu parametrul READ_ONLY.

Pentru a deschide și a umple cursorul, utilizați comanda

OPEN ((cursor_name) I @cursor_variable)

Când este deschis, cursorul poate fi specificat după nume (cursor_name) sau printr-o variabilă de tip CURSOR (@cursor_variable). Parametrul GLOBAL specifică faptul că cursor_name este un cursor global.

Instrucțiunea FETCH este utilizată pentru a vă deplasa prin setul de date cursor și pentru a prelua datele ca valori variabile:

FETCH[

(( cursor_name] I @cursor_variable]

Comenzile care determină direcția de mișcare de-a lungul cursorului sunt descrise în Tabel. 10.10. După cum sa menționat mai devreme, în funcție de tipul de cursor, este posibil ca unele comenzi pentru un anumit cursor să nu fie aplicabile.

Este important de reținut că, dacă cursorul tocmai a fost deschis, prima execuție a FETCH NEXT va sări la prima intrare din cursor.

Tabelul 10.10

Navigarea setului de date cursor

Variabila globală @@FETCH_STATUS vă permite să cunoașteți rezultatul ultimei execuții a instrucțiunii FETCH:

О – acțiunea a fost finalizată cu succes;

-1 – executarea instrucțiunii a eșuat, sau șirul a fost în afara limitelor setului de rezultate (cursor terminat);
-2 – nu există un rând selectabil, de exemplu, dacă înregistrarea curentă a fost ștearsă din baza de date în timpul lucrului cu cursorul de tip „sensibil la modificare”.

Instrucțiunea CLOSE închide un cursor deschis, eliberând memoria folosită pentru stocarea setului de date. Selectarea datelor și mutarea peste un cursor închis nu este posibilă - pentru aceasta trebuie redeschis.

ÎNCHIS (( cursor_name)|@cursor_variable)

Instrucțiunea DEALLOCATE elimină asocierea dintre un cursor și numele sau variabila acestuia. Dacă este numele de familie sau variabila care face referire la cursor, cursorul în sine este șters și orice resurse pe care le folosește sunt eliberate:

DEALLOCATE (( cursor_name] | @cursor_variable) Să ne uităm la un exemplu simplu de utilizare a cursorului, în care autorii și titlurile cărților publicate după 2000 sunt selectați dintr-un tabel, iar datele sunt trecute în buclă prin instrucțiunile SELECT, de fiecare dată când o înregistrare cu propriul titlu. dat de comentariile din cod:

/*declararea variabilelor*/

DECLARE @auth varchar(50), @title varchar(50)

UNDE >= 2000

/*deschide cursorul și „rulează”-l, afișând autorul și titlul într-o instrucțiune SELECT separată*/

FETCH NEXT FROM cursor ÎN @auth, @title

WHILESFETCH_STATUS=0

FETCH NEXT FROM cursor ÎN @auth, Stitle

/* închide cursorul și eliberează-l */

DEALLOCATE cursorul

După cum sa menționat mai sus, o variabilă de tip CURSOR poate fi utilizată în locul unui nume de cursor. Mai jos este un cod similar folosind astfel de variabile:

DECLARE Sauth varchar(50), Stitle varchar(50)

/*declararea unei variabile de tip cursor*/

DECLARE Scurl CURSOR

DECLARE cursorl CURSOR FAST_FORWARD

SELECTEAZĂ Autorul, Titlul FROM dbo.Bookl