Sunete și muzică 

Ce este un cursor sql. Cursore. Modificarea și ștergerea rândurilor prin cursoare


Cursorul este o legătură către zona de memorie contextuală. În unele implementări ale limbajului de programare SQL (Oracle, Microsoft SQL Server) - setul de rezultate obținut la executarea unei interogări și indicatorul de înregistrare curent asociat. Aș spune că un cursor este un tabel virtual care reprezintă o stocare alternativă a datelor. În acest caz, cursorul vă permite să accesați datele sale ca și cum ar fi datele unui tablou obișnuit.
Cursoarele sunt utilizate în procedurile stocate. Ajunge teorie, să ne uităm la un exemplu:
Avem o bază de date (baza de date este puțin bună, aceasta este una de-al mele munca de laborator, dar profesorul nostru de baze de date a insistat asupra unei astfel de structuri)
/*informații bancare*/
CREATE TABLE `bank` (

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


CHEIE PRIMARĂ (`BankId`)

)MOTOR=InnoDB
SET DE CARACTERE "utf8" COLLATE "utf8_bin" ;
/*date despre depozite */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
CHEIE PRIMARĂ(`BankId`, `ClientId`),
CHEIE `BankId` (`BankId`),
CHEIE `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` CHEIE străină (`BankId`) REFERINȚE `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` CHEIE străină (`ClientId`) REFERINȚE `client` (`ClientId`)
)MOTOR=InnoDB
/*date despre investitori*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NU NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL,
`Nume` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Nume` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Adresă` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
CHEIE PRIMARĂ(`ClientId`, `CreditCardId`),
CHEIE `ClientId` (`ClientId`)

)MOTOR=InnoDB
AUTO_INCREMENT=11 SET DE CARACTERE „utf8” COLLATE „utf8_bin”

Să presupunem că trebuie să primim fiecare bancă pe rând și să efectuăm câteva acțiuni cu ea, următoarea interogare ne-ar putea ajuta în acest sens

Selectați `bank`.* DIN NUMĂRUL LIMIT DE `bank` DE NUMĂRUL_DE_ÎNREGISTRĂRI_DE CARE NE trebuie,1
. Astfel, folosind LIMIT WE NEED_RECORD NUMBER, 1, extragem fiecare înregistrare într-un ciclu din tabelul bancar pe rând și efectuăm cu ea acțiunile de care avem nevoie, în timp ce creștem valoarea lui WE NEED_RECORD NUMBER cu 1. Acum vom face același lucru, dar folosind un cursor
ÎNCEPE
/* variabile de unde extragem datele */
Declarați vBankId întreg;
Declarați vBankName VARCHAR(50);
Declarați vAddress VARCHAR(50);
Declara vPhone VARCHAR (50);
/* variabila hadler - a*/
Declarați întregul implicit 0;
/*Declarația cursorului*/
Declarare BankCursor Cursor pentru Selectați `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` unde 1;
/*Scopul HANDLER, care va fi explicat mai jos*/
DECLARE CONTINUE HANDLER PENTRU SQLSTATE „02000” SET done=1;
/* deschide cursorul */
Deschide BankCursor;
/*preluare date*/
ÎN CÂT timp este terminat = 0 DO

luăm acțiunile de care avem nevoie
SFÂRȘIT CÂND ;
/*închiderea cursorului*/
Închide BankCursor;
SFÂRŞIT ;

* Acest cod sursă a fost evidențiat cu Sursa de evidențiere a codului.

Eroare: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Mesaj: Nu există date - zero rânduri preluate, selectate sau procesate

SQLSTATE: 02000 Se declanșează când se ajunge la sfârșitul cursorului sau când selectează sau actualizează returnează un șir gol.

Următoarea linie am declarat cursorul DECLARE cursor_name CURSOR FOR select_statement;
Deschide cursorul Deschide cursor_name;
Apoi, până ajungem la capătul cursorului (WHILE done = 0 DO), extragem datele și le procesăm.
Trebuie să închideți cursorul înainte de a părăsi procedura stocată. Închide cursor_name;

Nu pare complicat. Dar există multe capcane asociate cu SQLSTATE „02000”.

ÎN CÂT timp este terminat = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;

Selectați (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution unde BankId = vBankId limită 1;
facem niste actiuni
SFÂRȘIT CÂND ;

* Acest cod sursă a fost evidențiat cu Sursa de evidențiere a codului.


Totul este bine și corect din punct de vedere al sintaxei. Dar din punct de vedere logic, nu. Se poate întâmpla ca deponenții să nu fi deschis conturi în vreo bancă, apoi pentru Select (ContributeAmount) INTO vContributeAmountSUM FROM distribuție bancară unde BankId = vBankId limită 1; SQLSTATE: 02000 se va declanșa, variabila terminată va fi setată la 1, iar bucla while se va încheia mai devreme decât ne așteptam. Acest lucru poate fi evitat făcând următoarele
ÎN CÂT timp este terminat = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extrage pentru bancă suma oricăruia dintre depozitele sale */


dacă (vContributeAmountSUM > 0) atunci
/* extrage pentru bancă suma oricăruia dintre depozitele sale */

termina daca ;
facem niste actiuni
SFÂRȘIT CÂND ;

* Acest cod sursă a fost evidențiat cu Sursa de evidențiere a codului.


Cu prima solicitare, am verificat dacă există contribuții (dacă nu există, atunci vContributeAmountSUM == 0) și numai dacă există, recuperăm datele.

Acum să presupunem că trebuie să recuperăm suma totală în conturi din diferite bănci pentru fiecare client
Declarați cursorul ClientSummCursor pentru Selectare suma

Declarare ClientSummCursor Cursor pentru Selectare sumă (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) unde 1 grup după `bankdistribution`. `ClientId`;

Deschideți ClientSummCursor;
ÎN CÂT timp este terminat = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extrage pentru bancă suma oricăruia dintre depozitele sale */
Selectați Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution unde BankId = vBankId limită 1;
/* verifică dacă există într-adevăr depozite în această bancă */
dacă (vContributeAmountSUM > 0) atunci
/* extrage pentru bancă suma oricăruia dintre depozitele sale */
Selectați ContributeAmount INTO vContributeAmountSUM FROM bankdistribution unde BankId = vBankId limită 1;
termina daca ;


facem niste actiuni.
SFÂRȘIT CÂND ;

* Acest cod sursă a fost evidențiat cu Sursa de evidențiere a codului.

Aceeași situație poate apărea atunci când datele din cursorul ClientSummCursor se termină mai devreme decât datele din BankCursor, sunt declanșate SQLSTATE: 02000, variabila finalizată este setată la 1 și bucla while se termină mai devreme decât ne așteptam. Acest lucru poate fi evitat făcând următoarele

Deschideți ClientSummCursor;
ÎN CÂT timp este terminat = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extrage pentru bancă suma oricăruia dintre depozitele sale */
Selectați Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution unde BankId = vBankId limită 1;
/* verifică dacă există într-adevăr depozite în această bancă */
dacă (vContributeAmountSUM > 0) atunci
/* extrage pentru bancă suma oricăruia dintre depozitele sale */
Selectați ContributeAmount INTO vContributeAmountSUM FROM bankdistribution unde BankId = vBankId limită 1;
termina daca ;
/* înainte de a extrage date de pe al doilea cursor, amintiți-vă starea sqlstate */
SET vechi_status = terminat;
/* extrage datele de care avem nevoie */
FETCH ClientSummCursor INTO vSum,vClientId;
/* verifică dacă datele au fost preluate și dacă sqlstate 0200 a eșuat */
dacă (terminat = 0) atunci
facem niste actiuni.
termina daca ;
/* înainte de sfârșitul timpului, restabiliți valoarea variabilei terminate */
set done = vechi_status;
SFÂRȘIT CÂND ;

* Acest cod sursă a fost evidențiat cu Sursa de evidențiere a codului.

Mulțumesc tuturor celor care au citit până aici, sper că acest lucru va fi de folos cuiva.

Un cursor este un obiect care vă permite să procesați individual rânduri din setul de rezultate returnat de o instrucțiune SELECT. În continuare ne vom uita la cursoarele acceptate în limbă Transact-SQL. Acestea sunt cursoare pe partea serverului care există ca obiecte pe partea serverului bazei de date. Există, de asemenea, cursoare client care sunt folosite la crearea aplicațiilor de bază de date client.

Literatura de specialitate notează 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 mai multor rânduri. Prin urmare, se recomandă utilizarea cursoarelor 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 din prima înregistrare a cursorului în variabile;
  • deplasarea în jurul cursorului (de obicei într-o buclă) și procesarea intrărilor cursorului;
  • cursorul de închidere;
  • 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 acest operator acceptă atât sintaxa standardului ISO SQL (versiunea standardului nu este specificată în documentație), cât și sintaxa folosind un set de extensii de limbaj Transact-SQL CURSOR

FOR select_statement

Sintaxă Transact-SQL extinsă:

DECLARE cursor_name CURSOR

FOR select_statement

]][;]

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

Un cursor „local”, indiferent dacă este creat implicit sau prin specificarea explicită LOCAL, este disponibil numai în lotul de joburi, procedura stocată sau declanșatorul în care a fost creat. Acest cursor este eliberat implicit atunci când lotul, procedura stocată sau declanșatorul finalizează execuția. Excepția este atunci când cursorul este trecut printr-un parametru OUTPUT al unei proceduri stocate. Apoi cursorul este eliberat când toate variabilele care îl fac referire sunt eliberate sau când iese din domeniul de aplicare.

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 funcționează ca un cursor DINAMIC (vezi mai jos). Dacă nu este specificat nici FORWARD_ONLY, nici SCROLL și dacă niciunul dintre cuvintele cheie STATIC, KEYSET sau DYNAMIC nu este specificat, atunci valoarea implicită este FORWARD_ONLY.

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

STATIC înseamnă că cursorul nu poate fi 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 reflectate în cursor după aceasta.

KEYSET - y de acest tip cursor, un set de valori cheie care identifică înregistrările selectate este stocat într-un tabel temporar. Pe măsură ce vă deplasați prin cursor, valorile atributelor non-cheie sunt preluate din tabelele corespunzătoare, astfel încât modificările la coloanele non-cheie vor fi vizibile când mutați cursorul. Dacă rândul care se află în cursor a fost deja eliminat din tabel în momentul în care este preluat de operatorul FETCH, variabila de serviciu @@ FETCH_STATUS va returna valoarea -2. Rândurile adăugate la tabele după deschiderea cursorului nu sunt vizibile în cursor. Dacă interogarea care generează cursorul implică cel puțin un tabel care nu are un index unic, cursorul de tip KEYSET este convertit în tipul STATIC.

DYNAMIC este tipul de cursor care consumă cel mai mult resurse, care afișează toate modificările de date efectuate în rândurile setului de rezultate, inclusiv rândurile nou introduse. Valorile datelor, ordinea și apartenența la rând în fiecare selecție pot varia. FETCH ABSOLUTE nu poate fi utilizat 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 (atunci când cuvintele cheie opționale sunt omise). Este echivalent cu un cursor declarat cu parametrii 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 prin acel tip de cursor.

Un cursor declarat cu cuvântul cheie OPTIMISTIC nu necesită o blocare a rândului și permite modificarea datelor. Dacă au avut loc modificări ale tabelului de bază după ce datele au fost citite în cursor, încercarea de a modifica acele date prin cursor are ca rezultat o eroare.

TYPE_WARNING specifică că atunci când un cursor este convertit implicit din tipul solicitat în altul (de exemplu, conversia cursorului KEYSET în STATIC descrisă mai sus când nu există un index unic pe tabel), un avertisment va fi trimis clientului.

Select_statement – ​​declarația SELECT care formează setul de rezultate al cursorului.

Instrucțiunea FOR UPDATE specifică coloanele din cursor care trebuie actualizate. 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 unei declarații de cursor cu parametrul READ_ONLY.

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

OPEN (( cursor_name) I @cursor_variable)

La deschidere, cursorul poate fi specificat prin nume (cursor_name) sau printr-o variabilă de tip CURSOR (@cursor_variable). Parametrul GLOBAL specifică faptul că cursor_name este un cursor global.

Pentru a vă deplasa prin setul de date al cursorului și a prelua datele ca valori variabile, utilizați instrucțiunea FETCH:

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 are ca rezultat un salt la prima înregistrare a cursorului.

Tabelul 10.10

Navigarea unui set de date cursor

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

O – acțiune finalizată cu succes;

  • -1 – execuția operatorului a eșuat, sau linia a fost în afara setului de rezultate (cursorul s-a încheiat);
  • -2 – rândul selectat lipsește, de exemplu, dacă în timp ce lucrați cu un cursor de tip „sensibil la modificare”, înregistrarea curentă a fost ștearsă din baza de date.

Instrucțiunea CLOSE închide un cursor deschis, eliberând memoria folosită pentru stocarea setului de date. Recuperarea datelor și deplasarea în jurul unui cursor închis este imposibil pentru a face acest lucru, acesta trebuie redeschis.

ÎNCHIS (( cursor_name)|@cursor_variable )

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

DEALLOCATE (( cursor_name] | @cursor_variable) Să luăm în considerare un exemplu simplu de utilizare a unui cursor. Aici, autorii și titlurile cărților publicate nu mai devreme de 2000 sunt selectați din tabel, după care datele sunt scoase în buclă la instrucțiunile SELECT - de fiecare dată o înregistrare cu propriul titlu. Explicații suplimentare sunt date prin comentarii în cod:

/*declar variabile*/

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

UNDE >= 2000

/*deschide cursorul și „cură” prin el, afișând autorul și titlul folosind o instrucțiune SELECT separată*/

FETCH NEXT FROM cursor ÎN @auth, @title

ÎN CAZUL SSFETCH_STATUS = 0

FETCH NEXT FROM cursorl Î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 numelui cursorului. Mai jos este un cod similar folosind aceste variabile:

DECLARE South varchar(50), Stitle varchar(50)

/*declară o variabilă de tip cursor*/

DECLARE Scurl CURSOR

DECLARE cursorl CURSOR FAST_FORWARD

SELECT Autor, Titlu FROM dbo.Bookl

UNDE >= 2000

/*atribuiți o valoare unei variabile de tip cursor*/

SET Scurl = cursor

ÎN CAZUL SSFETCH_STATUS = 0

FETCH NEXT FROM Scurl INTO South, Stitle

Un cursor explicit este o comandă SELECT definită în mod explicit în secțiunea de declarare a unui program. Când declari un cursor explicit, i se dă un nume. Cursoarele explicite nu pot fi definite pentru comenzile INSERT, UPDATE, MERGE și DELETE.

Prin definirea comenzii SELECT ca un cursor explicit, programatorul are control asupra etapelor majore ale regăsirii informațiilor din baza de date Oracle. Acesta determină când să deschideți cursorul (OPEN), când să selectați rânduri din acesta (FETCH), câte rânduri să selectați și când să închideți cursorul folosind comanda CLOSE. Informațiile despre starea curentă a cursorului sunt disponibile prin atributele acestuia. Această granularitate ridicată a controlului face din cursoarele explicite un instrument de neprețuit pentru programator.

Să ne uităm la un exemplu:

1 FUNCȚIE jealousy_level (2 NAME_IN IN friends.NAME%TYPE) RETURN NUMBER 3 AS 4 CURSOR jealousy_cur 5 IS 6 SELECT location FROM friends 7 WHERE NAME = UPPER (NAME_IN); 8 8 jealousy_rec gelousy_cur%ROWTYPE; 9 NUMĂR retval; 10 BEGIN 11 DESCHIS gelozie_cur; 13 12 FETCH jealousy_cur INTO gelozie_rec; 15 13 IF jealousy_cur%FOUND 14 THEN 15 IF jealousy_rec.location = "PUERTO RICO" 16 THEN retval:= 10; 17 ELSIF jealousy_rec.location = "CHICAGO" 18 THEN retval:= 1; 19 END IF; 20 END IF; 24 21 ÎNCHIS gelozia_cur; 26 22 RETURN retval; 23 EXCEPȚIA 24 CÂND ALȚII ATUNCI 25 DACĂ gelousy_cur%ISOPEN ATUNCI 26 ÎNCHIS jealousy_cur; 27 END IF; 28 SFÂRȘIT;

Următoarele câteva secțiuni discută în detaliu fiecare dintre aceste operațiuni. Termenul „cursor” din ele se referă la cursoare explicite, cu excepția cazului în care textul prevede altfel în mod explicit.

Declararea unui cursor explicit

Pentru a putea folosi un cursor explicit, acesta trebuie să fie declarat în secțiunea de declarare a blocului sau pachetului PL/SQL:

CURSOR nume_cursor [ ([ parametru [, parametru...]) ] [ RETURN spec_reEirn ] IS SELECT_command ];

Aici numele cursorului este numele cursorului declarat; spiifiction_te?it - secțiune opțională RETURN; KOMaHdaSELECT - orice comandă SQL SELECT validă. Parametrii pot fi, de asemenea, trecuți la cursor (consultați secțiunea „Parametrii cursorului” de mai jos). În cele din urmă, după comanda SELECT...FOR UPDATE, puteți specifica o listă de coloane de actualizat (vezi și mai jos). După declarație, cursorul este deschis cu comanda OPEN și rândurile sunt preluate din acesta folosind comanda FETCH.

Câteva exemple de declarații explicite ale cursorului.

  • Cursor fără parametri. Setul de rânduri rezultat din acest cursor este setul de ID-uri de companie selectate din toate rândurile din tabel:
CURSOR company_cur IS SELECT company_id FROM company;
  • Cursor cu parametri. Setul de rânduri rezultat al acestui cursor conține un singur rând cu numele companiei corespunzător valorii parametrului transmis:
CURSOR name_cur (company_id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = company_id_in;
  • Cursor cu clauză RETURN. Setul de rânduri rezultat al acestui cursor conține toate datele din tabelul de angajați pentru ID-ul departamentului 10:
CURSOR emp_cur RETURNE angajați%ROWTYPE IS SELECT * FROM angajați WHERE Department_id = 10;

Numele cursorului

Un nume explicit de cursor trebuie să aibă maximum 30 de caractere și să respecte aceleași reguli ca și alți identificatori PL/SQL. Numele cursorului nu este o variabilă - este identificatorul indicatorului către cerere. Numelui cursorului nu i se atribuie o valoare și nu poate fi folosit în expresii. Cursorul este utilizat numai în comenzile OPEN, CLOSE și FETCH și pentru a califica atributul cursorului.

Declararea unui cursor într-un pachet

Cursoarele explicite sunt declarate în secțiunea de declarare a unui bloc PL/SQL. Un cursor poate fi declarat la nivel de pachet, dar nu într-o anumită procedură sau funcție de pachet. Un exemplu de declarare a două cursoare într-un pachet:

PACKAGE book_info IS CURSOR titles_cur IS SELECT title FROM books;

CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; SFÂRŞIT;

Primul cursor titles_cur returnează numai titluri de cărți. Al doilea, books_cur , returnează toate rândurile din tabelul de cărți în care numele cărților se potrivesc cu modelul specificat ca parametru al cursorului (de exemplu, „Toate cărțile care conțin șirul „PL/SQL”). Rețineți că al doilea cursor folosește o secțiune RETURN, care declară structura de date returnată de comanda FETCH.

  • Secțiunea RETURN poate conține oricare dintre următoarele structuri de date:
  • O înregistrare definită dintr-un rând de tabel de date folosind atributul %ROWTYPE.
  • O intrare definită dintr-un alt cursor, declarat anterior, folosind, de asemenea, atributul %rowtype.

Numărul de expresii din lista de selecție a cursorului trebuie să se potrivească cu numărul de coloane din table_name%ROWTYPE, Kypcop%ROWTYPE sau înregistrarea tipului de înregistrare. Tipurile de date ale elementelor trebuie să fie, de asemenea, compatibile. De exemplu, dacă al doilea element al listei de selectare este de tip NUMBER, atunci a doua coloană a intrării din secțiunea RETURN nu poate fi de tip VARCHAR2 sau BOOLEAN.

Înainte de a trece la o examinare detaliată a secțiunii RETURN și a avantajelor acesteia, să înțelegem mai întâi de ce ar putea fi necesar să declarăm cursori într-un pachet? De ce să nu declari un cursor explicit în programul în care este utilizat - într-o procedură, funcție sau bloc anonim?

Răspunsul este simplu și convingător. Prin definirea unui cursor într-un pachet, puteți reutiliza interogarea specificată în acesta fără a repeta același cod în locuri diferite aplicatii. Implementarea interogării într-un singur loc simplifică modificarea acesteia și întreținerea codului. Unele economii de timp sunt realizate prin reducerea numărului de cereri procesate.

De asemenea, merită să luați în considerare crearea unei funcții care returnează o variabilă cursor bazată pe REF CURSOR . Programul apelant preia rânduri printr-o variabilă cursor. Pentru mai multe informații, consultați secțiunea „Variabile de cursor și CURSOR REF”.

Când declarați cursoare în pachete reutilizabile, există un lucru important de luat în considerare. Toate structurile de date, inclusiv cursoarele, declarate la „nivel de pachet” (nu în interiorul unei anumite funcții sau proceduri), își păstrează valorile pe tot parcursul sesiunii. Aceasta înseamnă că cursorul batch va rămâne deschis până când îl închideți în mod explicit sau până la sfârșitul sesiunii. Cursorele declarate în blocuri locale sunt închise automat când acele blocuri sunt finalizate.

Acum să ne uităm la secțiunea RETURN. Un lucru interesant despre declararea unui cursor într-un pachet este că antetul cursorului poate fi separat de corpul acestuia. Acest antet, mai mult ca un antet de funcție, conține informații de care programatorul trebuie să lucreze: numele cursorului, parametrii acestuia și tipul de date returnate. Corpul cursorului este comanda SELECT. Această tehnică este demonstrată în versiune noua declarații ale cursorului books_cur din pachetul book_info:

PACHETUL book_info ESTE CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE; SFÂRŞIT; PACKAGE BODY book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; SFÂRŞIT;

Toate caracterele dinaintea cuvântului cheie IS formează o specificație, iar după IS vine corpul cursorului. Împărțirea declarației cursorului poate servi în două scopuri.

  • Ascunderea informațiilor. Cursorul din pachet este o „cutie neagră”. Acest lucru este convenabil pentru programatori, deoarece nu trebuie să scrie sau chiar să vadă comanda SELECT. Este suficient să știți ce înregistrări returnează acest cursor, în ce ordine și ce coloane conțin. Un programator care lucrează cu pachetul folosește cursorul ca orice alt element gata făcut.
  • Recopilare minimă. Prin ascunderea definiției interogării în corpul pachetului, se pot face modificări la comanda SELECT fără a modifica antetul cursorului din specificația pachetului. Acest lucru permite ca codul să fie îmbunătățit, corectat și recompilat fără a recompila specificația pachetului, astfel încât programele care depind de acel pachet nu vor fi marcate ca nevalide și nu vor trebui să fie recompilate.

Deschiderea unui cursor explicit

Folosirea unui cursor începe cu definirea acestuia în secțiunea declarații. Apoi, cursorul declarat trebuie deschis. Sintaxa pentru instrucțiunea OPEN este foarte simplă:

OPEN cursor_name [ (argument [, argument...]) ];

Aici, cursorname este numele cursorului declarat anterior, iar argument este valoarea transmisă cursorului dacă acesta este declarat cu o listă de parametri.

Oracle acceptă, de asemenea, sintaxa FOR la ​​deschiderea unui cursor, care este folosită atât pentru variabilele cursorului (consultați secțiunea „Variabilele cursorului și REF CURSOR”), cât și pentru SQL dinamic încorporat.

Când PL/SQL deschide un cursor, acesta execută interogarea pe care o conține. În plus, identifică setul de date activ - rândurile tuturor tabelelor care participă la interogare care corespund criteriului WHERE și condiției de îmbinare. Comanda OPEN nu preia date - aceasta este sarcina comenzii FETCH.

Indiferent de momentul în care are loc prima preluare a datelor, modelul de integritate a datelor Oracle asigură că toate operațiunile de preluare returnează date în starea în care a fost deschis cursorul. Cu alte cuvinte, de la deschiderea și până la închiderea cursorului, la preluarea datelor din acesta, operațiunile de inserare, actualizare și ștergere efectuate în acest timp sunt complet ignorate.

Mai mult, dacă comanda SELECT conține o secțiune FOR UPDATE, toate rândurile identificate de cursor sunt blocate atunci când cursorul este deschis.

Dacă încercați să deschideți un cursor care este deja deschis, PL/SQL va afișa următorul mesaj de eroare:

ORA-06511: PL/SQL: cursorul deja deschis

Prin urmare, înainte de a deschide cursorul, ar trebui să verificați starea acestuia folosind valoarea atributului %este deschis:

DACĂ NU company_cur%ISOPEN ALOR DESCHIDE company_cur; ENDIF;

Atributele cursoarelor explicite sunt descrise mai jos în secțiunea dedicată acestora.

Dacă programul se execută bucla FOR folosind un cursor, acest cursor nu trebuie să fie deschis în mod explicit (preluare date, închidere). Motorul PL/SQL face acest lucru automat.

Preluarea datelor de la un cursor explicit

Comanda SELECT creează un tabel virtual - un set de rânduri definite de o clauză WHERE cu coloane definite de o listă de coloane SELECT. Astfel, cursorul reprezintă acest tabel în programul PL/SQL. Scopul principal al unui cursor în programele PL/SQL este de a selecta rânduri pentru procesare. Preluarea rândurilor cursorului se face cu comanda FETCH:

FETCH cursor_name INTO record_or_variable_list;

Aici, numele cursorului este numele cursorului din care este selectată înregistrarea, iar înregistrarea sau lista de variabile este structurile de date PL/SQL în care este copiat următorul rând al setului de înregistrări activ. Datele pot fi plasate într-o înregistrare PL/SQL (declarată cu atributul %ROWTYPE sau declarația TYPE) sau în variabile (variabile PL/SQL sau variabile bind - cum ar fi elementele Oracle Forms).

Exemple de cursoare explicite

Următoarele exemple demonstrează căi diferite mostre de date.

  • Preluarea datelor de la un cursor într-o înregistrare PL/SQL:
DECLARE CURSOR company_cur is SELECT ...;
  • company_rec company_cur%ROWTYPE; BEGIN OPEN company_cur;
FETCH company_cur INTO company_rec;
  • Preluarea datelor de la un cursor într-o variabilă:
FETCH new_balance_cur INTO new_balance_dollars;

Preluarea datelor de la un cursor într-un rând de tabel PL/SQL, variabilă și variabilă de legare Oracle Forms:

FETCH emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary;

Datele preluate de la un cursor ar trebui să fie întotdeauna plasate într-o înregistrare declarată sub același cursor cu atributul %ROWTYPE; Evitați selectarea listelor de variabile. Preluarea într-o înregistrare face codul mai compact și mai flexibil, permițându-vă să schimbați lista de preluare fără a modifica comanda FETCH.

Eșantionarea după procesarea ultimului rând

Odată ce deschideți cursorul, selectați rândurile din acesta una câte una până când toate sunt epuizate. Cu toate acestea, puteți încă lansa comanda FETCH după aceasta.

Instrucțiunea SELECT din declarația cursorului specifică lista de coloane pe care o returnează. Alături de numele coloanelor din tabel, această listă poate conține expresii numite coloane calculate sau virtuale.

Un alias de coloană este un nume alternativ specificat în comanda SELECT pentru o coloană sau expresie. Specificând aliasuri adecvate în SQL*Plus, puteți afișa rezultatele unei interogări arbitrare într-o formă care poate fi citită de om. În aceste situații, pseudonimele nu sunt necesare. Pe de altă parte, când se utilizează cursoare explicite, sunt necesare aliasuri de coloană calculate în următoarele cazuri:

  • la preluarea datelor dintr-un cursor într-o înregistrare declarată cu atributul %ROWTYPE bazat pe același cursor;
  • când un program conține o referință la o coloană calculată.

Luați în considerare următoarea interogare. Comanda SELECT selectează numele tuturor companiilor care au comandat bunuri în cursul anului 2001, precum și cantitatea totală de comenzi (presupunând că masca de formatare implicită pentru instanța curentă a bazei de date este DD-MON-AAAA):

SELECTează numele companiei, SUM (inv_amt) FROM firma c, factură i WHERE c.company_id = i.company_id AND i.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001";

Rularea acestei comenzi în SQL*Plus va produce următoarea ieșire:

NUMELE COMPANIEI SUM (INV_AMT)
ACME TURBO INC. 1000
WASHINGTON HAIR CO. 25.20

După cum puteți vedea, antetul coloanei SUM (INV_AMT) nu este potrivit pentru un raport, dar pentru vizualizarea simplă a datelor este destul de potrivit. Acum să rulăm aceeași interogare într-un program PL/SQL folosind un cursor explicit și să adăugăm un alias de coloană:

DECLARE CURSOR comp_cur IS SELECT c.name, SUM (inv_amt) total_sales FROM firma C, factură I WHERE C.company_id = I.company_id AND I.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001";

comp_rec comp_cur%ROWTYPE; BEGIN OPEN comp_cur;

FETCH comp_cur INTO comp_rec; SFÂRŞIT;

Fără alias, nu voi putea face referință la coloana din structura de înregistrare comp_rec. Dacă aveți un alias, puteți lucra cu o coloană calculată la fel ca cu orice altă coloană de interogare:

IF comp_rec.total_sales > 5000 THEN DBMS_OUTPUT.PUT_LINE ("Ați depășit limita de credit de 5000 USD prin " || TO_CHAR (comp_rec.total_sales - 5000, "9999 USD")); ENDIF;

Pe vremuri, în copilărie, am fost învățați să facem curățenie după noi înșine, iar acest obicei a rămas cu noi (deși nu tuturor) pentru tot restul vieții. Se pare că această regulă joacă un rol extrem de important în programare și mai ales când vine vorba de gestionarea cursoarelor. Nu uita niciodată să închizi cursorul când nu mai ai nevoie de el!

Sintaxa comenzii CLOSE:

CLOSE cursor_name;

Mai jos sunt câteva sfaturi și considerații importante legate de închiderea cursoarelor explicite.

  • Dacă un cursor este declarat și deschis într-o procedură, asigurați-vă că îl închideți când ați terminat cu el; altfel codul tău va pierde memorie. În teorie, un cursor (ca orice structură de date) ar trebui să fie automat închis și distrus atunci când iese din domeniul de aplicare. De obicei, la ieșirea dintr-o procedură, o funcție sau un bloc anonim, PL/SQL închide de fapt toate cursoarele deschise din el. Dar acest proces necesită resurse intensive, așa că, din motive de eficiență, PL/SQL întârzie uneori identificarea și închiderea cursoarelor deschise. Cursoarele de tip REF CURSOR, prin definiție, nu pot fi închise implicit. Singurul lucru de care poți fi sigur este că atunci când blocul PL/SQL „cel mai exterior” se completează și controlul este returnat către SQL sau alt program de apel, PL/SQL va închide implicit toate cursoarele deschise de acel bloc sau blocurile imbricate, cu excepția REF CURSOR . Articolul „Reutilizarea cursorului în SQL static PL/SQL” din Oracle Technology Network oferă o analiză detaliată a modului și când PL/SQL închide cursoarele. Blocurile anonime imbricate sunt un exemplu de situație în care PL/SQL nu închide implicit cursoarele. Informații interesante Pentru mai multe informații despre acest subiect, consultați articolul lui Jonathan Gennick „Închide PL/SQL implicit cursorii?”
  • Dacă un cursor este declarat într-un pachet la nivel de pachet și este deschis într-un bloc sau program, acesta va rămâne deschis până când îl închideți în mod explicit sau până la sfârșitul sesiunii. Prin urmare, după ce ați terminat de lucrat cu un cursor la nivel de lot, ar trebui să îl închideți imediat cu comanda CLOSE (și, apropo, același lucru ar trebui făcut în secțiunea de excepții):
ÎNCEPE DESCHIDE my_package.my_cursor;
  • ... Lucrul cu cursorul CLOSE my_package.my_cursor; EXCEPȚIE CÂND ALȚII APOI DACĂ my_package.my_cursor%ISOPEN, atunci ÎNCHISE my_package.my_cursor;
IF company_cur%ISOPEN THEN CLOSE company_cur; ENDIF;
  • Dacă au rămas prea mulți cursori deschisi în program, numărul de cursoare poate depăși valoarea parametrului bazei de date OPEN_CURSORS. Dacă primiți un mesaj de eroare, asigurați-vă mai întâi că cursoarele declarate în pachete sunt închise atunci când nu mai sunt necesare.

Atribute explicite ale cursorului

Oracle acceptă patru atribute (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) pentru a obține informații despre starea unui cursor explicit. O referință de atribut are următoarea sintaxă: cursor%attribute

Aici cursor este numele cursorului declarat.

Valorile returnate de atributele explicite ale cursorului sunt afișate în tabel. 1.

Tabelul 1. Atribute explicite ale cursorului

Valorile atributelor cursorului înainte și după efectuarea diferitelor operațiuni cu acestea sunt prezentate în tabel. 2.

Când lucrați cu atribute explicite ale cursorului, luați în considerare următoarele:

  • Dacă încercați să accesați atributul %FOUND, %NOTFOUND sau %ROWCOUNT înainte ca cursorul să fie deschis sau după ce acesta este închis, Oracle lansează o excepție INVALID CURSOR (ORA-01001).
  • Dacă prima dată când comanda FETCH este executată, setul de rânduri rezultat este gol, atributele cursorului returnează următoarele valori: %FOUND = FALSE , %NOTFOUND = TRUE și %ROWCOUNT = 0 .
  • Când se utilizează BULK COLLECT, atributul %ROWCOUNT returnează numărul de rânduri preluate în colecțiile date.

Masa 2. Valorile atributelor cursorului

Operațiune %GĂSITE %NU A FOST GĂSIT %ESTE DESCHIS %ROWCOUNT
Înainte de DESCHIS Excepție
ORA-01001		 Excepție
ORA-01001		 FALS Excepție
ORA-01001
După DESCHIS NUL NUL ADEVĂRAT 0
Înainte de prima mostră FETCH NUL NUL ADEVĂRAT 0
După prima probă
FETCH		 ADEVĂRAT FALS ADEVĂRAT 1
Înainte de ulterior
FETCH		 ADEVĂRAT FALS ADEVĂRAT 1
După FETCH ulterioară ADEVĂRAT FALS ADEVĂRAT Depinde de date
Înainte de ultima mostră FETCH ADEVĂRAT FALS ADEVĂRAT Depinde de date
După ultima mostră FETCH ADEVĂRAT FALS ADEVĂRAT Depinde de date
Înainte de ÎNCHIS FALS ADEVĂRAT ADEVĂRAT Depinde de date
După ÎNCHIS Excepție Excepție FALS Excepție

Utilizarea tuturor acestor atribute este demonstrată în următorul exemplu:

Anterior, blogurile au oferit în mod repetat exemple de utilizare a parametrilor de procedură și funcție. Parametrii sunt un mijloc de transmitere a informațiilor către și de la un modul de program. Când sunt utilizate corect, acestea fac modulele mai utile și mai flexibile.

PL/SQL vă permite să treceți parametri la cursoare. Ele îndeplinesc aceleași funcții ca și parametrii modulelor software, precum și câteva altele suplimentare.

  • Extinderea capacităților de reutilizare a cursorului. În loc să codificați valorile care definesc condițiile de selecție a datelor în clauza WHERE, puteți utiliza parametrii pentru a trece noi valori în clauza WHERE de fiecare dată când cursorul este deschis.
  • Depanarea problemelor cu domeniul cursorului. Dacă interogarea folosește parametri în loc de valori codificate, setul rezultat de rânduri de cursor nu este legat de un anumit variabila programului sau bloc. Dacă programul dumneavoastră are blocuri imbricate, puteți defini un cursor la nivelul superior și îl puteți utiliza în blocuri imbricate cu variabile declarate în ele.

Numărul de parametri ai cursorului este nelimitat. Când este apelat OPEN, toți parametrii (cu excepția celor care au valori implicite) trebuie să fie specificați pentru cursor.

Când un cursor necesită parametri? Regula generală aici este aceeași ca și pentru proceduri și funcții: dacă se așteaptă ca cursorul să fie utilizat în locuri diferite și cu valori diferite în secțiunea UNDE, ar trebui definit un parametru pentru acesta. Să comparăm cursoarele cu și fără parametru. Exemplu de cursor fără parametri:

CURSOR joke_cur IS SELECT numele, categoria, ultima_data_utilizată FROM Jokes;

Setul de rezultate al cursorului include toate intrările din tabelul de glume. Dacă avem nevoie doar de un anumit subset de rânduri, secțiunea WHERE este inclusă în interogare:

CURSOR joke_cur IS SELECT nume, categorie, data_ultimei_utilizate FROM jokes WHERE categorie = "SOȚUL";

Pentru a efectua această sarcină, nu am folosit parametri și nu sunt necesari. În acest caz, cursorul returnează toate rândurile care aparțin unei anumite categorii. Dar ce se întâmplă dacă categoria se schimbă de fiecare dată când accesezi acest cursor?

Cursore cu parametri

Desigur, nu am defini un cursor separat pentru fiecare categorie - care ar fi complet inconsecvent cu proiectarea aplicațiilor bazate pe date. Avem nevoie de un singur cursor, dar unul pentru care am putea schimba categoria - și tot ar returna informațiile necesare. Și cea mai bună (deși nu singura) soluție la această problemă este definirea unui cursor parametrizat:

PROCEDURA explica_gluma (categoria_principala_in IN joke_category.category_id%TYPE) IS /* || Cursor cu o listă de parametri formată din || dintr-un singur parametru șir.

Între numele cursorului și cuvântul cheie IS există acum o listă de parametri. Valoarea HSBAND codificată în clauza WHERE a fost înlocuită cu o referință la parametrul UPPER (category_in). Când deschideți cursorul, puteți seta valoarea la SOȚ, Soț sau Soț - cursorul va funcționa în continuare. Numele categoriei pentru care cursorul ar trebui să returneze rândurile tabelului de glume este specificat în instrucțiunea OPEN (în paranteze) ca literal, constantă sau expresie. Când cursorul este deschis, comanda SELECT este analizată și parametrul este asociat cu valoarea. Setul rezultat de rânduri este apoi determinat și cursorul este gata pentru preluare.

Deschiderea unui cursor cu opțiuni

Se poate deschide un nou cursor indicând orice categorie:

OPEN joke_cur(Jokes_pkg.category); OPEN joke_cur("soț"); OPEN joke_cur("politician"); OPEN joke_cur (Jokes_pkg.relation || "-IN-LAW");

Parametrii cursorului sunt folosiți cel mai adesea în clauza WHERE, dar pot fi referiți în altă parte în instrucțiunea SELECT:

DECLARE CURSOR joke_cur (category_in IN ARCHAR2) IS SELECT name, category_in, last_used_date FROM joke WHERE categorie = UPPER (category_in);

În loc să citim categoria din tabel, pur și simplu înlocuim parametrul categorie_in în lista de selectare. Rezultatul rămâne același deoarece clauza WHERE restricționează categoria eșantionului la valoarea parametrului.

Domeniul parametrului cursorului

Sfera de aplicare a unui parametru de cursor este limitată la acel cursor. Un parametru de cursor nu poate fi referit în afara comenzii SELECT asociată cursorului. Următorul fragment PL/SQL nu se compila deoarece program_name nu este o variabilă locală în bloc. Acesta este un parametru formal de cursor care este definit numai în interiorul cursorului:

DECLARE CURSOR scariness_cur (nume_program VARCHAR2) IS SELECT SUM (nivel_înfricoșător) total_scary_level FROM tales_from_the_crypt WHERE prog_name = program_name; BEGIN program_name:= „MUMIA CU RESPIRAȚIE”; /* Link nevalid */ OPEN scariness_cur (nume_program);

.... ÎNCHIS scariness_cur; SFÂRŞIT;

Sintaxa pentru parametrii cursorului este foarte asemănătoare cu cea a procedurilor și funcțiilor - cu excepția faptului că parametrii cursorului pot fi doar parametri IN. Parametrii cursorului nu pot fi setați în modurile OUT sau IN OUT. Aceste moduri permit transmiterea și returnarea valorilor din proceduri, ceea ce nu are sens pentru un cursor. Există o singură modalitate de a obține informații de la cursor: preluarea unei înregistrări și copierea valorilor din lista de coloane din secțiunea INTO

Valori implicite ale parametrilor

Parametrilor cursorului li se pot atribui valori implicite. Un exemplu de cursor cu o valoare implicită a parametrului:

CURSOR emp_cur (emp_id_in NUMBER:= 0) IS SELECT employee_id, emp_name FROM employee WHERE employee_id = emp_id_in;

Deoarece parametrul emp_id_in are o valoare implicită, acesta poate fi omis în comanda FETCH. În acest caz, cursorul va returna informații despre angajat cu codul 0.

În codul meu T-SQL folosesc întotdeauna operații 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 în privința cursorului. Indiferent de poziția dvs. pe cursoare, probabil că ai 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 problemei în cauză, pentru a lua o decizie dacă procesarea bazată pe cursor este sau nu " facem următoarele:

  • 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:

  1. Mai întâi, declarați variabilele de care aveți nevoie în logică.
  2. Î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.
  3. În al treilea rând, obțineți o înregistrare de pe cursor pentru a începe procesarea datelor.
  4. În al patrulea rând, este procesul de date care este unic pentru fiecare set de logică.
  5. 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.
  6. Î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.
  7. Î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) -- numele fișierului 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 parte a instrucțiunii WHILE
  • Declarație WHILE - Condiție pentru începerea și continuarea procesării datelor
    • 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 într-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, copiile de siguranță 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 are scopul de a servi ca o 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ă. De obicei, logica bazată pe SET este mai mult decât fezabilă și rareori sunt necesare cursoare.
  • 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 în mod 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:
    • 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 să fie 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 performanțe mai bune 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 sau nu să aibă un loc în aplicația sau procesele dumneavoastră operaționale. 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 ar putea 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ă. Cursor în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente. Zona specificată în memorie este denumită și este accesibilă programelor de aplicație.

Conform standardului SQL atunci când lucrați cu cursoare pot fi identificate următoarele principale actiuni:

  • creație sau declarația cursorului ;
  • deschiderea cursorului, acestea. umplerea acestuia cu date care sunt stocate în memoria cu mai multe niveluri;
  • selectare din cursorȘi Schimbare folosirea liniilor de date;
  • închiderea cursorului, după care devine inaccesibil pentru programele utilizatorului;
  • eliberând cursorul, adică stergere în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente ca obiect, deoarece acesta închidere nu eliberează neapărat memoria asociată acestuia.

SQL Server acceptă trei tip de cursoare:

  • cursoare SQL este folosit în principal în declanșatoare, proceduri stocate și scripturi;
  • cursoare serverele acţionează asupra serverului şi implementează interfata software aplicații pentru ODBC, OLE DB, DB_Library;
  • cursoare 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ă procesarea datelor prin reducerea timpului pierdut petrecut cu operațiunile de rețea.

Gestionarea cursorului în mediul MS SQL Server

Controlul cursorului implementat prin executarea următoarelor comenzi:

  • DECLARE - crearea sau declarația cursorului ;
  • DESCHIS - cursorul de deschidere, adică completarea cu date;
  • FETCH selectare din cursorȘi Schimbare rânduri de date folosind un cursor;
  • ÎNCHIDE - închiderea cursorului ;
  • DEALOCARE – eliberând cursorul, adică ștergerea cursorului ca obiect.

Declarația cursorului

În standardul SQL pentru creare în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente Este furnizată următoarea comandă:

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

Folosind cuvântul cheie INSENSITIVE va crea cursor static. Modificări ale datelor nu sunt permise, în plus, nu sunt afișate schimbări, realizat de alți utilizatori. Dacă cuvânt cheie INSENSITIVE lipsă, creat cursor dinamic.



Când specificați cuvântul cheie SCROLL, creat cursor poate fi derulat în orice direcție, permițându-vă să aplicați orice comenzi mostre. Dacă acest argument este omis, atunci cursor se dovedește 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 interogare SELECT, care este folosit pentru a determina setul de rânduri rezultat în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente.

Specificarea argumentului FOR READ_ONLY creează cursor„numai în citire” și nu este permisă nicio modificare a datelor. Este diferit de static, deși și acesta din urmă nu vă permite să schimbați datele. Poate fi declarat ca cursor numai pentru citire cursor dinamic, care vă va permite să afișați schimbări, realizat de alt utilizator.

Creare în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente cu argumentul FOR UPDATE vă permite să executați în modificarea datelor cursorului fie în coloanele specificate, fie, în absența argumentului OF column_name, în toate coloanele.

În mediul MS SQL Server, următoarea sintaxă pentru comanda de creare este acceptată în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente:

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

Utilizarea cuvântului cheie LOCAL va crea un local cursor, care este vizibil numai în domeniul de aplicare al pachetului, declanșatorului, procedurii stocate sau funcției definite de utilizator care l-a creat. Când se finalizează un pachet, declanșare, procedură sau funcție cursor este implicit distrus. Pentru a transfera conținut în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recenteîn afara constructului care l-a creat, trebuie să atribuiți un argument OUTPUT parametrului său.

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

Specificarea FORWARD_ONLY creează cursor serial ; probă datele pot fi procesate numai în direcția de la prima linie la ultima.

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ă pentru în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente READ_ONLY specificați argumentul FAST_FORWARD apoi creat cursor va fi optimizat pentru acces rapid la date. Acest argument nu poate fi utilizat împreună cu argumentele FORWARD_ONLY sau OPTIMISTIC.

ÎN cursor, creat cu argumentul OPTIMISTIC, este interzis SchimbareȘi ștergerea rândurilor care au fost schimbate după deschizând cursorul.

Când se specifică argumentul TYPE_WARNING, serverul va informa utilizatorul despre modificarea implicită a tipului în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente dacă este incompatibil cu interogarea SELECT.

Deschiderea cursorului

Pentru deschizând cursorulși completarea acestuia cu date din cea specificată în timpul creării în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente Interogarea SELECT folosește următoarea comandă:

OPEN ((nume_cursor) |@nume_variabilă_cursor)

După deschizând cursorul Se execută instrucțiunea SELECT asociată, a cărei ieșire este stocată în memoria cu mai multe niveluri.

Preluarea datelor de pe un cursor

Imediat dupa deschizând cursorul puteți selecta conținutul acestuia (rezultatul executării interogării corespunzătoare) folosind următoarea comandă:

FETCH [ FROM ]((cursor_name)| @cursor_variable_name) ]

Specificarea FIRST va returna primul rând al setului complet de rezultate în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente, care devine linia curentă.

Când este specificat LAST, cel mai mult ultima linie în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente. Devine și linia actuală.

Specificarea NEXT returnează rândul imediat după cel curent în setul complet de rezultate. Acum devine actual. În mod implicit, comanda FETCH folosește această metodă. mostre linii.

Cuvântul cheie PRIOR returnează rândul înainte de cel curent. Devine curent.

ABSOLUT (row_number | @row_number_variable) returnează rândul după numărul de index absolut din setul complet de rezultate în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente. Numărul liniei poate fi specificat folosind o constantă sau ca numele unei variabile în care este stocat numărul liniei. Variabila trebuie să fie un tip de date întreg. Sunt indicate atât valorile pozitive, cât și cele negative. Când se specifică o valoare pozitivă, șirul este numărat de la începutul setului, în timp ce o valoare negativă este numărată de la sfârșit. Linia selectată devine linia curentă. Dacă este specificată o valoare nulă, nu se returnează niciun rând.

Argumentul RELATIVE (număr de rânduri | @număr variabil de rânduri) returnează rândul care este numărul specificat de rânduri după cel curent. Dacă specificați un număr negativ de rânduri, rândul care este numărul specificat de rânduri înainte de cel curent va fi returnat. Specificarea unei valori nule va returna rândul curent. Rândul returnat devine rândul curent.

La deschide cursorul global, trebuie să specificați cuvântul cheie GLOBAL înaintea numelui său. Nume în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente poate fi specificat și folosind o variabilă.

Construcția INTO @variable_name [,...n] specifică o listă de variabile în care vor fi stocate valorile coloanei corespunzătoare ale șirului returnat. Ordinea variabilelor trebuie să se potrivească cu ordinea coloanelor în cursor, iar tipul de date al variabilei este tipul de date din coloană în SQL, aceasta este o zonă din memoria bazei de date care este concepută pentru a stoca cele mai recente. Dacă constructul INTO nu este specificat, atunci comportamentul comenzii FETCH se va asemăna cu comportamentul comenzii SELECT - datele sunt afișate pe ecran.