Používanie kurzorov a slučiek v Transact-SQL. Kurzory v uložených procedúrach MySQL Kurzory na serveri SQL
Príkaz DECLARE CURSOR vám umožňuje získať záznamy z tabuľky riadok po riadku na manipuláciu. To umožňuje spracovanie riadok po riadku namiesto tradičného spracovania množiny údajov, ktoré robí SQL.
Ako úplne prvé priblíženie sa pri práci s kurzorom používajú nasledujúce kroky.
Kurzor sa vytvorí príkazom DECLARE. Kurzor sa otvorí príkazom OPEN.
Operácie s kurzorom sa vykonávajú pomocou príkazu FETCH. Kurzor sa zatvorí príkazom CLOSE.
Príkaz DECLARE CURSOR špecifikuje príkaz SELECT. Každý riadok vrátený príkazom SELECT je možné získať a spracovať samostatne. Nasledujúci príklad Oracle deklaruje kurzor v deklaračnom bloku spolu s niekoľkými ďalšími premennými. Potom sa v nasledujúcom bloku ZAČIATOK...KONIEC otvorí kurzor, urobí sa z neho výber a kurzor sa zatvorí.
CURSOR title_price_cursor IS SELECT title, price FROM titles
KDE cena NIE JE NULL; title_price_val title_price_cursor ROWTYPE; nova_cena NUMBER(10,2);
OPEN title_price_Cursor;
FETCH title_price_cur-sor INTO title_price_val;
new_price:= "title_price_val.price" * 1,25 INSERT INTO new_title_price VALUES
(title_price_val.title, new_price) CLOSE title_price_cursor; KONIEC;
Pretože tento príklad používa PL/SQL, nevysvetlíme veľa z kódu v tejto knihe. Blok DECLARE však jasne zobrazuje deklaráciu kurzora. V spustiteľnom bloku PL/SQL sa kurzor inicializuje príkazom OPEN, hodnoty sa načítajú príkazom FETCH a nakoniec sa kurzor zatvorí príkazom CLOSE.
Príkaz SELECT je základom kurzora, preto je dobrým zvykom ho pred zahrnutím do príkazu DECLARE CURSOR dôkladne otestovať. Príkaz SELECT môže fungovať so základnou tabuľkou alebo zobrazením. Preto kurzory len na čítanie môžu pracovať s neaktualizovateľnými zobrazeniami. Príkaz SELECT môže obsahovať klauzuly ako ORDER BY, GROUP BY a HAVING, pokiaľ tieto klauzuly neaktualizujú zdrojovú tabuľku. Ak je kurzor definovaný ako FOR UPDATE, potom sa odporúča odstrániť takéto klauzuly z príkazu SELECT.
Lokálne kurzory sa často používajú ako výstupné parametre uložených procedúr. Preto môžete definovať a naplniť kurzor v uloženej procedúre a odovzdať ho volajúcej dávkovej úlohe alebo uloženej procedúre.
V nasledujúcom jednoduchom príklade DB2 deklarujeme kurzor, ktorý vyhľadá čísla oddelení, názvy oddelení a čísla manažérov v skupine admin_group "XO1".
DECLARE dept_cursor CURSOR
FOR SELECT dept_nbr, dept_name, mgr_nbr
WHERE admin_group="X01"
ORDER BY d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;
Nasledujúci príklad Microsoft SQL Server deklaruje a otvorí kurzor pre tabuľku vydavateľov. Kurzor vyberie prvý záznam, ktorý zodpovedá príkazu SELECT z tabuľky vydavateľov a vloží ho do inej tabuľky. Potom sa presunie na ďalší záznam, potom na ďalší, kým sa nespracujú všetky záznamy. Nakoniec sa kurzor zatvorí a pamäť sa uvoľní (príkaz DEALLOCATE sa používa iba v Microsoft SQL Server).
DECLARE @publisher_name VARCHAR(20)
DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM publishers WHERE country "USA"
FETCH NEXT FROM pub_cursor DO publisher_name
WHILE @s>FETCH_STATUS=0
INSERT INTO VALUES zahraničných_vydavateľov("j>názov_vydavateľa)
ZATVORIŤ pub_cursor DEALLOCATE pub_cursor
V tomto príklade môžete vidieť pohyb kurzora cez množinu záznamov. (Tento príklad je určený len na demonštráciu myšlienky, pretože v skutočnosti existuje Najlepšia cesta riešenie tohto problému, konkrétne inštrukcia INSERT, SELECT.)
VZŤAHUJE SA NA: SQL Server (od roku 2008) Base SQL dáta Azure SQL Data WarehouseParalelný dátový sklad
Definuje atribúty kurzora servera Transact-SQL, ako sú vlastnosti zobrazenia a dotaz používaný na zostavenie sady výsledkov, na ktorej kurzor funguje. Príkaz DECLARE CURSOR podporuje syntax štandardu ISO aj syntax, ktorá používa sadu rozšírenia jazyka Transact-SQL.
Syntax ISO DECLAREcursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR (LEN NA ČÍTANIE | AKTUALIZÁCIA [OF názov stĺpca [,...n]])] [;] Rozšírená syntax Transact-SQL DECLARE meno_kurzora CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | SÚPRAVA KĽÚČOV | DYNAMICKÝ | FAST_FORWARD ] [ LEN NA ČÍTANIE | SCROLL_LOCKS | OPTIMISTICKÉ ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
názov_kurzora
názov_kurzora
NECITLIVÝ
tempdb; takže zmeny základných tabuliek sa neodrážajú v údajoch vrátených výberom tohto kurzora a tento kurzor sa nedá zmeniť. Ak používate syntax ISO, pokiaľ nie je zadaná voľba INSENSITIVE, potvrdené aktualizácie a vymazania vykonané v základných tabuľkách sa objavia v nasledujúcich výberoch.
SCROLL
Označuje, že sú dostupné všetky možnosti vzorkovania (PRVÝ, POSLEDNÝ, PREDCHÁDZAJÚCI, ĎALŠÍ, RELATÍVNY, ABSOLÚTNY). Ak príkaz ISO DECLARE CURSOR nešpecifikuje voľbu SCROLL, je podporovaná iba voľba NEXT fetch. Možnosť SCROLL nie je možné zadať pomocou možnosti FAST_FORWARD.
select_statement
Štandardný príkaz SELECT, ktorý špecifikuje množinu výsledkov kurzora. Kľúčové slová FOR BROWSE a INTO nie sú povolené select_statement deklarácia kurzora.
select_statement konflikt s kurzorom požadovaného typu.
IBA NA ČÍTANIE
Aktualizovať ]
názov_stĺpca [,
.. .n], zmeny umožňujú len uvedené stĺpce. Ak sa príkaz UPDATE použije bez zoznamu stĺpcov, aktualizácia je možná pre všetky stĺpce.
názov_kurzora
Názov Transact-SQL konkrétneho kurzora servera. názov_kurzora musia dodržiavať pravidlá pre identifikátory.
MIESTNE
Označuje, že kurzor je lokálny pre balík, uloženú procedúru alebo spúšťač, v ktorom bol vytvorený. Názov kurzora je platný iba v tejto oblasti. Na kurzor môžu odkazovať lokálne premenné balíka, uložené procedúry, spúšťače alebo výstupný parameter uloženej procedúry. Parameter OUTPUT sa používa na odovzdanie lokálneho kurzora volajúcemu balíku, uloženej procedúre alebo spúšťaču, ktorý potom môže priradiť parameter premennej kurzora na následný prístup ku kurzoru po dokončení uloženej procedúry. Kurzor sa implicitne uvoľní, keď dávka, uložená procedúra alebo spúšťač dokončí vykonanie, pokiaľ kurzor nebol odovzdaný parametru OUTPUT. Ak bol kurzor odovzdaný parametru OUTPUT, kurzor sa uvoľní, keď sa uvoľnia všetky premenné, ktoré naň odkazujú, alebo keď sa opustí rozsah.
GLOBAL
Označuje, že kurzor je pre pripojenie globálny. Názov kurzora môže byť použitý ľubovoľnou uloženou procedúrou alebo balíkom, ktorý beží na pripojení. Kurzor sa implicitne uvoľní iba vtedy, ak je spojenie prerušené.
FORWARD_ONLY
Určuje, že kurzor je možné zobraziť iba od prvého riadka po posledný. Podporovaná je iba možnosť načítania FETCH NEXT. Ak je zadané FORWARD_ONLY bez kľúčových slov STATIC, KEYSET alebo DYNAMIC, kurzor sa bude správať ako DYNAMICKÝ kurzor. Ak nie je zadaný ani argument FORWARD_ONLY, ani argument SCROLL, predvolený je argument FORWARD_ONLY, pokiaľ nie sú prítomné kľúčové slová STATIC, KEYSET alebo DYNAMIC. Kurzory STATIC, KEYSET a DYNAMIC majú predvolenú hodnotu SCROLL. Na rozdiel od databázových rozhraní API, ako sú ODBC a ADO, režim FORWARD_ONLY podporujú nasledujúce kurzory Transact-SQL: STATIC, KEYSET a DYNAMIC.
STATIC
Definuje kurzor, ktorý vytvorí dočasnú kópiu údajov na použitie kurzorom. Všetky dotazy na kurzor pristupujú k zadanej dočasnej tabuľke v tempdb; takže zmeny základných tabuliek sa neodrážajú v údajoch vrátených výberom tohto kurzora a tento kurzor sa nedá zmeniť.
KEYSET
Označuje, že členstvo alebo poradie riadkov v kurzore sa po otvorení nezmení. Do tabuľky je zabudovaná sada kľúčov, ktoré jednoznačne identifikujú riadky tempdb volal kľúče.
Zmeny nekľúčových hodnôt v základných tabuľkách vykonané vlastníkom kurzora alebo potvrdené inými používateľmi sa zobrazia pri zobrazení vlastníka kurzora. Zmeny vykonané inými používateľmi sa neprejavia (zmeny nie je možné vykonať pomocou kurzora servera Transact-SQL). Ak je riadok odstránený, pokus o načítanie riadkov vráti @@FETCH_STATUS -2. Aktualizácie hodnôt kľúča cez hranice kurzora sú podobné ako vymazanie starého riadka a následné vloženie nového riadka. Riadok s novými hodnotami nie je viditeľný a pokúsi sa načítať riadok so starými hodnotami, vráti @@FETCH_STATUS -2. Aktualizácie sú viditeľné okamžite, ak sú vykonané cez kurzor pomocou klauzuly WHERE CURRENT OF.
DYNAMICKÝ
Definuje kurzor, ktorý zobrazuje všetky zmeny údajov vykonané v riadkoch v sade výsledkov pri prezeraní tohto kurzora. Hodnoty údajov, poradie a členstvo v riadkoch v každom výbere sa môžu líšiť. Možnosť ABSOLÚTNEHO výberu nie je podporovaná dynamickými kurzormi.
RÝCHLO VPRED
Označuje kurzor FORWARD_ONLY, READ_ONLY, ktorý má povolenú optimalizáciu výkonu. Možnosť FAST_FORWARD nie je možné zadať pomocou možností SCROLL alebo FOR_UPDATE.
IBA NA ČÍTANIE
Zabráni zmenám vykonaným cez tento kurzor. Klauzula WHERE CURRENT OF nemôže odkazovať na kurzor v príkaze UPDATE alebo DELETE. Táto možnosť má prednosť pred predvolenou funkciou obnovenia kurzora.
SCROLL_LOCKS
Označuje, že umiestnené aktualizácie alebo vymazania vykonané pomocou kurzora budú zaručene úspešné. SQL Server zamyká riadky pri ich čítaní do kurzora, aby sa zabezpečilo, že tieto riadky budú dostupné pre následné zmeny. Voľba SCROLL_LOCKS nemôže byť špecifikovaná s voľbou FAST_FORWARD alebo STATIC.
OPTIMISTICKÝ
Určuje, že umiestnené aktualizácie alebo vymazania vykonané pomocou kurzora zlyhajú, ak bol riadok aktualizovaný odkedy bol načítaný do kurzora. SQL Server nezamyká riadky, keď sa čítajú do kurzora. Namiesto toho sa používajú prirovnania časová značka hodnoty stĺpca alebo kontrolné súčty, ak nie je v tabuľke časová značka na určenie, či sa riadok od načítania do kurzora zmenil. Ak bol riadok upravený, pokusy o umiestnenú aktualizáciu alebo odstránenie zlyhajú. Možnosť OPTIMISTIC nemôže byť špecifikovaná s voľbou FAST_FORWARD.
TYPE_WARNING
Určuje, že klientovi sa odošle upozornenie, ak sa kurzor implicitne skonvertuje z jedného požadovaného typu na iný.
select_statement
Štandardný príkaz SELECT, ktorý špecifikuje množinu výsledkov kurzora. Kľúčové slová COMPUTE, COMPUTE BY, FOR BROWSE a INTO nie sú povolené select_statement deklarácia kurzora.
SQL Server implicitne konvertuje kurzor na iný typ, ak sú klauzuly v select_statement konflikt s kurzorom požadovaného typu. Ďalšie informácie nájdete v časti Implicitné prevody kurzora.
NA AKTUALIZÁCIU]
Definuje stĺpce v kurzore, ktoré sa majú aktualizovať. Ak OF názov_stĺpca [,
... n], zmeny umožňujú iba uvedené stĺpce. Ak sa príkaz UPDATE použije bez zoznamu stĺpcov, aktualizácia je možná pre všetky stĺpce, pokiaľ nebola zadaná možnosť súbežnosti READ_ONLY.
Príkaz DECLARE CURSOR definuje atribúty kurzora servera Transact-SQL, ako sú vlastnosti zobrazenia a dotaz používaný na zostavenie sady výsledkov, na ktorej kurzor pracuje. Príkaz OPEN vyplní množinu výsledkov a príkaz FETCH z nej vráti riadok. Príkaz CLOSE vymaže aktuálnu sadu výsledkov spojenú s kurzorom. Príkaz DEALLOCATE uvoľňuje prostriedky používané kurzorom.
Prvá forma príkazu DECLARE CURSOR používa syntax ISO na špecifikáciu parametrov kurzora. Druhá forma príkazu DECLARE CURSOR používa rozšírenia jazyka Transact-SQL, ktoré vám umožňujú definovať kurzory pomocou rovnakých typov, aké sa používajú v kurzorových funkciách databázových rozhraní API, ako sú ODBC a ADO.
Tieto dve formy nemožno miešať. Ak zadáte SCROLL alebo vynecháte kľúčové slová pred kľúčovým slovom CURSOR, nemôžete použiť kľúčové slová medzi CURSOR a tiež pre select_statement Kľúčové slová. Pri zadávaní kľúčových slov medzi KURZOR, ako aj pre select_statement kľúčové slová, nemôžete zadať SCROLL alebo INSENSITIVE pred kľúčové slovo CURSOR.
Ak pre príkaz DECLARE CURSOR použijete syntax Transact-SQL a nezadáte voľby READ_ONLY, OPTIMISTIC alebo SCROLL_LOCKS, predpokladá sa nasledujúca predvolená hodnota.
Ak príkaz SELECT nepodporuje aktualizácie (alebo má nedostatočné povolenia, alebo pristupuje k vzdialeným tabuľkám, ktoré nepodporujú aktualizácie atď.), kurzor je nastavený na READ_ONLY.
STATIC a FAST_FORWARD kurzory predvolene na READ_ONLY.
Kurzory DYNAMIC a KEYSET predvolene sú OPTIMISTICKÉ.
Na kurzory môžu odkazovať iba iné príkazy Transact-SQL. Funkcie databázového API nemôžu odkazovať na kurzory. Napríklad, keď je kurzor deklarovaný, funkcie a metódy OLE DB, ODBC alebo ADO nemôžu odkazovať na jeho názov. Riadky kurzora nie je možné vybrať pomocou zodpovedajúcich funkcií a metód API; Na tento účel musíte použiť príkazy Transact-SQL FETCH.
Nasledujúce uložené procedúry možno použiť na definovanie vlastností kurzora po jeho deklarovaní.
Premenné môžu byť použité ako súčasť select_statement, v ktorom je deklarovaný kurzor. Hodnoty kurzorových premenných sa po jeho deklarovaní nemenia.
V predvolenom nastavení sú povolenia DECLARE CURSOR udelené všetkým používateľom, ktorí majú povolenie SELECT pre zobrazenia, tabuľky a stĺpce používané kurzorom.
V tabuľke s klastrovaným indexom columnstore nemôžete použiť kurzory alebo spúšťače. Toto obmedzenie sa nevzťahuje na indexy bez klastrov; Kurzory a spúšťače môžete použiť v tabuľke s neklastrovaným indexom columnstore.
A. Použitie jednoduchého kurzora a syntaxe
Sada výsledkov vytvorená pri otvorení tohto kurzora obsahuje všetky riadky a stĺpce tabuľky. Tento kurzor je možné aktualizovať, všetky aktualizácie a vymazania sú zastúpené vo výbere pre tento kurzor. FETCH``NEXT je načítanie len preto, že parameter SCROLL nebol zadaný.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. Použitie vnorených kurzorov na zobrazenie zostavy
Nasledujúci príklad používa vnorené kurzory na zobrazenie komplexnej zostavy. Pre každého poskytovateľa je deklarovaný interný kurzor.
SET NOCOUNT ON ;
DECLARE @vendor_id int , @vendor_name nvarchar (50), @message varchar (80), @product nvarchar (50); TLAČ" -------- Správa o produktoch dodávateľa --------";
DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor DO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 ZAČAŤ TLAČ " " VYBERTE @správu = "----- Produkty od dodávateľa: "+ @vendor_name VYTLAČIŤ @správu -- Deklarovať vnútorný kurzor založený
-- na vendor_id z vonkajšieho kurzora. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Hodnota premennej z vonkajšieho kurzora OPEN product_cursor FETCH NEXT FROM product_cursor DO @product IF @@FETCH_STATUS<>0 TLAČ "<
Kurzor je odkaz na oblasť kontextovej pamäte. V niektorých implementáciách programovacieho jazyka SQL (Oracle, Microsoft SQL Server) - množina výsledkov získaná pri vykonávaní dotazu as ním spojený ukazovateľ aktuálneho záznamu. Povedal by som, že kurzor je virtuálna tabuľka, ktorá predstavuje alternatívne úložisko dát. V tomto prípade vám kurzor umožňuje prístup k jeho údajom, ako keby to boli údaje bežného poľa.
V uložených procedúrach sa používajú kurzory. Dosť bolo teórie, pozrime sa na príklad:
Máme databázu (databáza je trochu nekvalitná, toto je jedna z mojich laboratórne práce, ale náš učiteľ databázy trval na takejto štruktúre)
/*bankové informácie*/
VYTVORIŤ TABUĽKU „banka“ (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
PRIMÁRNY KĽÚČ (`BankId`))ENGINE=InnoDB
ZNAKOVÁ SADA "utf8" COLLATE "utf8_bin" ;
/*údaje o vkladoch */
CREATE TABLE `banková distribúcia` (
`BankId` INTEGER (11) NOT NULL ,
"Persent" INTEGER (11) PREDVOLENÁ NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMÁRNY KĽÚČ(`BankId`, `ClientId`),
KĽÚČ `BankId` (`BankId`),
KĽÚČ `ClientId` (`ClientId`),
OBMEDZENIE `bankdistribution_fk` CUDZÍ KĽÚČ (`BankId`) REFERENCIE `bank` (`BankId`),
OBMEDZENIE `bankdistribution_fk1` CUDZÍ KĽÚČ (`ClientId`) REFERENCIE `client` (`ClientId`)
)ENGINE=InnoDB
/*údaje o investoroch*/
VYTVORIŤ TABUĽKU „klient“ (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Priezvisko` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Názov` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
"Telefón" VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Adresa` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMÁRNY KĽÚČ(`ClientId`, `CreditCardId`),
KĽÚČ `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 SADA ZNAKOV "utf8" COLLATE "utf8_bin"
Povedzme, že potrebujeme postupne prijať každú banku a vykonať s ňou nejaké akcie, s tým by nám mohol pomôcť nasledujúci dotaz
Vyberte `banku`.* Z `banky` LIMIT POČET THE_RECORD_WE POTREBUJEME,1. Takže pomocou LIMIT WE NEED_RECORD NUMBER, 1, extrahujeme každý záznam v slučke z bankovej tabuľky a vykonáme s ním akcie, ktoré potrebujeme, pričom zvýšime hodnotu WE NEED_RECORD NUMBER o 1. Teraz urobíme to isté, ale pomocou kurzor
Začať
/* premenné, kde extrahujeme dáta */
Declare vBankId integer ;
Declare vBankName VARCHAR(50);
Deklarovať vAdresu VARCHAR(50);
Vyhlásiť vPhone VARCHAR (50);
/* premenná hadler - a*/
Declare done integer default 0;
/*Vyhlásenie kurzora*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` kde 1;
/*Účel HANDLER, ktorý bude vysvetlený nižšie*/
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1;
/* otvoriť kurzor */
Otvorte BankCursor;
/*získať dáta*/
KEĎ hotovo = 0 DO
podnikneme kroky, ktoré potrebujeme
KONIEC KÝM ;
/*zatvorenie kurzora*/
Zavrieť BankCursor;
KONIEC ;* Tento zdrojový kód bol zvýraznený pomocou Zvýrazňovača zdrojového kódu.
Chyba: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Správa: Žiadne údaje – nebolo načítaných, vybraných ani spracovaných nula riadkov
SQLSTATE: 02000 sa spustí, keď sa dosiahne koniec kurzora, alebo keď výber alebo aktualizácia vráti prázdny reťazec.
V ďalšom riadku sme deklarovali kurzor DECLARE meno_kurzora CURSOR FOR select_statement;
Otvoriť kurzor Otvoriť názov_kurzora;
Potom, kým sa nedostaneme na koniec kurzora (WHILE done = 0 DO), extrahujeme dáta a spracujeme ich.
Pred ukončením uloženej procedúry musíte zavrieť kurzor. Zavrieť názov_kurzora;
Nezdá sa to zložité. Existuje však veľa úskalí spojených s SQLSTATE "02000".
KEĎ hotovo = 0 DO
FETCH Bank Cursor INTO vBankId,vBankName,vAddress,vPhone;
Vyberte (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribúcie kde BankId = vBankId limit 1;
robíme nejaké akcie
KONIEC KÝM ;* Tento zdrojový kód bol zvýraznený pomocou Zvýrazňovača zdrojového kódu.
Z hľadiska syntaxe je všetko v poriadku a správne. Ale z logického hľadiska nie. Môže sa stať, že vkladatelia nemajú otvorené účty v niektorej banke, potom pre Select (ContributeAmount) INTO vContributeAmountSUM FROM banková distribúcia kde BankId = vBankId limit 1; SQLSTATE: 02000 sa spustí, premenná done sa nastaví na 1 a cyklus while sa skončí skôr, ako sme očakávali. Tomu sa dá predísť nasledujúcim postupom
KEĎ hotovo = 0 DO
FETCH Bank Cursor INTO vBankId,vBankName,vAddress,vPhone;
/* výpis pre banku výšku ktoréhokoľvek z jej vkladov */
if (vContributeAmountSUM > 0) then
/* výpis pre banku výšku ktoréhokoľvek z jej vkladov */
koniec Ak ;
robíme nejaké akcie
KONIEC KÝM ;* Tento zdrojový kód bol zvýraznený pomocou Zvýrazňovača zdrojového kódu.
Pri prvej požiadavke sme skontrolovali, či existujú príspevky (ak nie sú, tak vContributeAmountSUM == 0) a iba ak nejaké existujú, údaje načítame.
Teraz povedzme, že potrebujeme odstrániť celkovú sumu na účtoch v rôznych bankách pre každého klienta
Deklarovať ClientSummCursor Cursor pre Select sum
Deklarujte kurzor ClientSummCursor pre výber sumy (`banková distribúcia`.`ContributeAmount`), `banková distribúcia`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`), kde 1 skupina podľa `bankovej distribúcie`. `ClientId`;Otvorte ClientSummCursor;
KEĎ hotovo = 0 DO
FETCH Bank Cursor INTO vBankId,vBankName,vAddress,vPhone;
/* výpis pre banku výšku ktoréhokoľvek z jej vkladov */
Vyberte Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribúcie kde BankId = vBankId limit 1;
/* skontrolujte, či sú v tejto banke skutočne vklady */
if (vContributeAmountSUM > 0) then
/* výpis pre banku výšku ktoréhokoľvek z jej vkladov */
Vyberte ContributeAmount INTO vContributeAmountSUM FROM bankdistribution kde BankId = vBankId limit 1;
koniec Ak ;
robíme nejaké akcie.
KONIEC KÝM ;* Tento zdrojový kód bol zvýraznený pomocou Zvýrazňovača zdrojového kódu.
Rovnaká situácia môže nastať, keď údaje v kurzore ClientSummCursor skončia skôr ako údaje v BankCursor, spustí sa SQLSTATE: 02000, premenná done je nastavená na 1 a cyklus while sa skončí skôr, ako sme očakávali. Tomu sa dá predísť nasledujúcim postupom
Otvorte ClientSummCursor;
KEĎ hotovo = 0 DO
FETCH Bank Cursor INTO vBankId,vBankName,vAddress,vPhone;
/* výpis pre banku výšku ktoréhokoľvek z jej vkladov */
Vyberte Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribúcie kde BankId = vBankId limit 1;
/* skontrolujte, či sú v tejto banke skutočne vklady */
if (vContributeAmountSUM > 0) then
/* výpis pre banku výšku ktoréhokoľvek z jej vkladov */
Vyberte ContributeAmount INTO vContributeAmountSUM FROM bankdistribution kde BankId = vBankId limit 1;
koniec Ak ;
/* pred extrahovaním údajov z druhého kurzora si zapamätajte stav sqlstate */
SET old_status = hotovo;
/* extrahovať údaje, ktoré potrebujeme */
FETCH ClientSummCursor INTO vSum,vClientId;
/* skontrolujte, či boli údaje načítané a či zlyhal sqlstate 0200 */
if (hotovo = 0) potom
robíme nejaké akcie.
koniec Ak ;
/* pred koncom while obnovte hodnotu premennej done */
set done = starý_stav;
KONIEC KÝM ;* Tento zdrojový kód bol zvýraznený pomocou Zvýrazňovača zdrojového kódu.
Ďakujem všetkým, ktorí to dočítali až sem, dúfam, že to bude pre niekoho užitočné.
Implementácia kurzora v databáze sa podobá triede Java, ktorá má súbor údajov a metód na ich spracovanie. V čom sql kurzor používa údaje ako bežné pole. Kurzory možno použiť v spúšťačoch, uložených procedúrach a funkciách.
V súlade so štandardom SQL sa pri práci s kurzormi vykonávajú tieto základné akcie:
- deklarácia kurzora;
- otvorenie kurzora s čítaním údajov;
- vzorkovanie údajov z kurzora riadok po riadku;
- zmena údajov riadku pomocou kurzora;
- zatvorenie kurzora, po ktorom sa stane neprístupným;
- uvoľnenie kurzora, t.j. odstránenie kurzora z pamäte, pretože jeho zatvorenie nevyhnutne neuvoľní pamäť, ktorá je s ním spojená.
V rôznych implementáciách definícia kurzor môže mať nejaké rozdiely. Niekedy je napríklad potrebné explicitne uvoľniť pamäť pridelenú pre kurzor. Po uvoľnení kurzora sa uvoľní aj s ním spojená pamäť. To umožňuje opätovné použitie názvu kurzora. V iných implementáciách, keď je kurzor zatvorený, pamäť sa uvoľní implicitne.
V niektorých prípadoch sa nezaobídete bez použitia kurzora. Ak je to možné, mali by ste sa však vyhnúť používaniu kurzora a pracovať so štandardnými príkazmi na spracovanie údajov: SELECT, UPDATE, INSERT, DELETE. Je to spôsobené tým, že kurzory neumožňujú modifikačné operácie na celom objeme údajov a rýchlosť vykonávania operácií spracovania údajov pomocou kurzora je výrazne nižšia ako rýchlosť štandardné prostriedky SQL.
Ak program dokáže zmeniť údaje načítané do kurzora, potom sa to nazýva modifikovateľné. Keď hovoríme o kurzoroch, nemali by sme zabudnúť na izoláciu transakcií. Jeden používateľ upravuje záznam pomocou kurzora, zatiaľ čo iný používateľ číta záznam pomocou vlastného kurzora. Okrem toho môže zmeniť rovnaký záznam, čo si vyžaduje zachovanie integrity údajov.
Vyhlásenie kurzora
Pred použitím musia byť kurzory deklarované. Štandard SQL používa na vytvorenie kurzora nasledujúcu syntax:
Deklarovať kurzor názvu kurzora pre príkaz select ])]
Tento výraz deklaruje kurzor deklarovať kurzor s názvom "cursor_name".
NECITLIVÝ vytvorí sa statický kurzor, ktorý neumožňuje vykonávať zmeny. Okrem toho sa nezobrazia zmeny vykonané inými používateľmi. Ak kľúčové slovo INSENSITIVE chýba, vytvorí sa dynamický kurzor.
Pri použití kľúčového slova SCROLL vytvorený kurzor je možné posúvať v ľubovoľnom smere, čo vám umožňuje použiť ľubovoľné príkazy výberu. Ak je tento argument vynechaný, kurzor bude sekvenčný, t.j. jeho prezeranie bude možné len jedným smerom - od začiatku do konca.
Výraz select_statement označuje štruktúru na čítanie informácií, napríklad vybrať ... z ... . Nesmie obsahovať operátor do, keďže kurzor má svoj vlastný operátor aport na vyplnenie premenných údajmi kurzora.
Pri zadávaní argumentu LEN PRE ČÍTANIE vytvorí sa kurzor len na čítanie a nebudú povolené žiadne úpravy údajov. Dynamický kurzor možno deklarovať ako kurzor len na čítanie, čo umožňuje zobrazenie zmien vykonaných iným používateľom.
Vytvorenie kurzora s argumentom NA AKTUALIZÁCIU umožňuje vykonávať zmeny údajov v kurzore buď v určených stĺpcoch, alebo pri absencii argumentu OF názov_stĺpca, vo všetkých stĺpcoch.
V podprograme môžete deklarovať viacero kurzorov. Ale každý kurzor musí mať jedinečný názov. Ak chcete otvoriť kurzor, musíte použiť operátor OTVORENÉ ktorý otvorí predtým deklarovaný kurzor:
Kurzor otvorený
SQL definuje nasledujúcu syntax pre otvorenie kurzora "kurzor otvorený":
Otvorte názov_kurzora;
Načítanie údajov z kurzora, načítanie kurzora
Syntax na čítanie údajov z kurzora do niektorých premenných je nasledovná:
Načítajte názov_kurzora do názvu_varu [, názov_var] ...;
Operátor aport vyberie otvorené údaje kurzora do premenných umiestnených za do a presunie kurzor na ďalšiu pozíciu.
Zatvorte kurzor
Operátor Zavrieť zatvorí kurzor. Ak operátor nie je explicitne špecifikovaný, kurzor sa automaticky zatvorí pri zatvorení príslušného bloku programu.
Zavrieť názov_kurzora;
Po zatvorení sa kurzor stane nedostupným. Pri zatváraní sa uvoľnia všetky zámky nainštalované počas chodu kurzora. Zatvoriť možno iba otvorené kurzory. Zatvorený, ale neuvoľnený kurzor je možné znova otvoriť. Nie je dovolené zatvoriť neotvorený kurzor.
Každý DBMS má svoje vlastné zvláštnosti používania kurzora.
Funkcie používania kurzorov v Oracle
V PL/SQL sú štyri atribúty kurzora % FOUND, %NENÁJDENÉ, %JE OTVORENÉ A % ROWCOUNT. Atribúty kurzora sú deklarované ako operátory %TYPE a %ROWTYPE napravo od názvu kurzora.
Atribút %FOUND
Atribút %NOTFOUND
Atribút %NOTFOUND je presným opakom atribútu %FOUND.
Atribút %ISOPEN
Atribút %ISOPEN iba indikuje, či je kurzor otvorený alebo nie.
%ROWCOUNT atribút
Atribút % ROWCOUNT je číselný atribút, ktorý vracia počet riadkov prečítaných kurzorom v určitom časovom bode.
Príklad SQL kurzora v Oracle DBMS
Deklarovať v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs kurzor pre výber id, name, sum(comm) as comm from managers, kde sa údaje medzi "2014-11-01" a "2014-11-30" zoskupujú podľa id, name; začať otvorené crs; slučka EXIT WHEN crs%NOTFOUND; FETCH crs do v_id, v_name, v_comm; vložiť do bonus(id, meno, comm) hodnoty (crs.id, crs.name, crs.comm); koncová slučka; zaviazať sa; zavrieť crs; koniec;
Funkcie používania kurzorov na serveri SQL
Kurzory používané v MSSQL môžu byť sekvenčné alebo rolovacie. Sekvenčný umožňuje vyberať údaje iba jedným smerom – od začiatku do konca. Posúvateľné kurzory umožňujú pohyb v oboch smeroch a umožňujú vám preskočiť na ľubovoľný riadok v sade výsledkov kurzora.
SQL Server podporuje statické, dynamické, sekvenčné kurzory a kurzory riadené súpravou kľúčov.
V dizajne statického kurzora sú informácie uložené ako snímka v určitom časovom bode. Preto zmeny vykonané v databáze iným používateľom nie sú viditeľné. Počas otvárania kurzora server nastaví zámok na všetky riadky zahrnuté v jeho kompletnej množine výsledkov. Statický kurzor sa po vytvorení nemení a vždy zobrazuje množinu údajov, ktorá existovala v čase, keď bola otvorená. Ak iní používatelia zmenia údaje zahrnuté v kurzore v zdrojovej tabuľke, neovplyvní to statický kurzor. Nie je možné vykonať zmeny v statickom kurzore, takže sa vždy otvára v režime len na čítanie.
Dynamický kurzor vyžaduje dodatočné sieťové a softvérové zdroje. Pri použití dynamických kurzorov sa nevytvorí úplná kópia údajov, ale výbery zo zdrojových tabuliek sa vykonajú len vtedy, keď používateľ pristupuje k určitým údajom. Počas načítania server zamkne riadky a všetky zmeny, ktoré používateľ vykoná v celej množine výsledkov kurzora, budú viditeľné v kurzore. Keď však kurzor načíta údaje, zmeny vykonané iným používateľom sa už v kurzore neprejavia.
Kurzor ovládaný sadou kláves má vlastnosti medzi statickými a dynamickými. Záznamy sa identifikujú v čase odberu vzoriek, a tak sa sledujú zmeny. Tento typ kurzora je užitočný pri implementácii spätného posúvania. V tomto prípade nie sú pridania a vymazania údajov viditeľné, kým sa informácie neaktualizujú a kurzor sa nevyberie Nová verzia záznamy, ak v nich boli vykonané zmeny.
Statické kurzory sa najlepšie používajú pre systémy spracovania informácií, t.j. pre systémy podávania správ alebo na štatistické a analytické účely. Statický kurzor je lepší pri získavaní veľkého množstva údajov. V systémoch pre elektronické nákupy alebo rezervácie objektov (miestenky, vstupenky) je potrebné dynamicky vnímať aktualizované informácie pri zmenách. V takýchto prípadoch sa používa dynamický kurzor. V týchto aplikáciách je množstvo prenesených údajov zvyčajne malé a pristupuje sa k nim na úrovni jednotlivých záznamov.
Sekvenčné kurzory neumožňujú načítanie údajov v opačnom smere, iba od začiatku do konca kurzora. Sekvenčný kurzor neukladá množinu všetkých riadkov údajov. Načítavajú sa z databázy hneď po vykonaní výberu v kurzore, čo umožňuje dynamicky odrážať všetky zmeny vykonané používateľmi v databáze pomocou príkazov INSERT, UPDATE, DELETE. Kurzor číta najnovší stav údajov.
Vyhlásenie kurzora
Deklarovať kurzor názvu kurzora pre príkaz SELECT ]]
Pri použití kľúčového slova MIESTNE Vytvorí sa lokálny kurzor, ktorý je viditeľný iba v rámci bloku, spúšťača, uloženej procedúry alebo používateľom definovanej funkcie. Kľúčové slovo GLOBAL, definuje globálny kurzor, ktorý existuje, kým sa neuzavrie aktuálne pripojenie.
Operátor FORWARD_ONLY definuje sekvenčný kurzor, ktorý umožňuje načítanie údajov iba v smere od prvého riadku k poslednému. Pri použití operátora SCROLL je vytvorený posuvný kurzor, ktorý umožňuje prístup k údajom v akomkoľvek poradí a v akomkoľvek smere.
Typ kurzora je určený operátormi:
- STATIC - vytvorenie statického kurzora;
- DYNAMIC - vytvorenie dynamického kurzora;
- KEYSET - vytvorenie kľúčového kurzora.
Ak pre kurzor IBA NA ČÍTANIEšpecifikovať argument RÝCHLO VPRED, potom bude vytvorený kurzor optimalizovaný pre rýchly prístup k údajom. Tento argument nemožno použiť v spojení s argumentmi FORWARD_ONLY A OPTIMISTICKÝ.
Ak je kurzor vytvorený pomocou operátora OPTIMISTICKÝ, potom je zakázané meniť alebo mazať riadky, ktoré boli zmenené po otvorení kurzora.
Pri zadávaní argumentu TYPE_WARNING server ohlási implicitnú zmenu typu kurzora, ak nie je kompatibilný s dotazom SELECT.
Načítanie údajov z kurzora, načítanie
Ihneď po otvorení kurzora môžete získať jeho obsah pomocou nasledujúceho príkazu:
Pri použití operátora NAJPRV vráti sa prvý riadok sady výsledkov kurzora, ktorý sa stane aktuálnym riadkom. Keď je špecifikované POSLEDNÉ vráti sa posledný riadok kurzora. Stáva sa tiež aktuálnou líniou.
Pri zadávaní operátora ĎALŠIE vráti sa riadok hneď za aktuálnym v sade výsledkov. Tento riadok sa stane aktuálnym riadkom. Predvolený príkaz FETCH používa presne tento spôsob načítania riadkov.
Pri zadávaní operátora PREDCHÁDZAJÚCI riadok pred aktuálnym sa vráti. Tento riadok sa stane aktuálnym riadkom.
Operátor ABSOLÚTNE (číslo_riadku | @číslo_riadku_premenná) vráti riadok podľa jeho absolútneho poradového čísla v kompletnej množine výsledkov kurzora. Číslo riadku je možné zadať pomocou konštanty alebo ako názov premennej, v ktorej je číslo riadku uložené. Premenná musí byť celočíselný dátový typ. Uvádzajú sa kladné aj záporné hodnoty. Pri zadaní kladnej hodnoty sa reťazec počíta od začiatku množiny, zatiaľ čo záporná hodnota sa počíta od konca. Vybraný riadok sa stane aktuálnym riadkom. Ak je zadaná hodnota null, nevráti sa žiadny riadok.
Argument RELATÍVNE (počet riadkov | @premenný počet riadkov) vráti posun riadku zadaný počet riadkov za aktuálny. Ak zadáte záporný počet riadkov, vráti sa riadok, ktorý má zadaný počet riadkov pred aktuálnym. Zadaním hodnoty null sa vráti aktuálny riadok. Vrátený riadok sa stane aktuálnym riadkom.
Ak chcete otvoriť globálny kurzor, musíte pred jeho názvom zadať kľúčové slovo GLOBAL. Názov kurzora možno zadať aj pomocou premennej.
Vo výraze INTO @názov_premennej [,...n] je definovaný zoznam premenných, v ktorom budú uložené zodpovedajúce hodnoty stĺpcov vráteného riadku. Poradie premenných sa musí zhodovať s poradím stĺpcov v kurzore a typ údajov premennej sa musí zhodovať s typom údajov v stĺpci kurzora.
Zmena a vymazanie údajov pomocou kurzora
Ak chcete zmeniť údaje pomocou kurzora, musíte zadať príkaz UPDATE v nasledujúcom formáte:
V jednej operácii je možné zmeniť hodnoty niekoľkých stĺpcov aktuálneho riadku kurzora, ale všetky musia patriť do rovnakej tabuľky.
Ak chcete odstrániť údaje pomocou kurzora, použite príkaz DELETE v nasledujúcom formáte:
V dôsledku toho sa vymaže aktuálny riadok v kurzore.
Uvoľnenie pamäte, uvoľnenie
Ak chcete odstrániť kurzor z pamäte, použite príkaz
Prideliť názov_kurzora;
Atribút @@FETCH_STATUS
Na určenie prítomnosti riadkov v kurzore by ste mali použiť globálnu premennú @@FETCH_STATUS, ktorá má nenulovú hodnotu, ak v kurzore nie sú žiadne ďalšie riadky. Ak množina riadkov ešte nebola vyčerpaná, potom sa @@FETCH_STATUS rovná nule.
Príklad kurzora na serveri SQL
Declare @company varchar(50), @manager varchar(50), @message varchar(256); deklarovať kurzor crs_clients lokálny pre vybranú spoločnosť, manažéra od zákazníkov, kde mesto = "Moskva" objednávka podľa spoločnosti, manažéra; vytlačiť "Zoznam klientov"; otvoriť crs_clients; fetch next from crs_clients do @company, @manager; pričom @@FETCH_STATUS = 0 začať vybrať @message = "Spoločnosť " + @spoločnosť + " manažér " + @manažér; vytlačiť @správu; -- presunúť sa na ďalšie načítanie záznamu z crs_clients do @spoločnosť, @manager; koniec; close crs_clients; dealokovať crs_clients;
Definícia kurzora je uvedená. Poskytuje sa popis jeho typov a správania: statické, dynamické, sekvenčné a kľúčové kurzory. Popísané sú princípy ovládania kurzora: vytvorenie a otvorenie kurzora, čítanie údajov, zatvorenie kurzora. Uvádzame príklady programovania kurzora.Koncept kurzora
Dotaz proti relačnej databáze zvyčajne vracia viacero riadkov (záznamov) údajov, ale aplikácia spracováva iba jeden záznam naraz. Aj keď sa zaoberá viacerými riadkami súčasne (napríklad zobrazuje údaje vo forme tabuliek), ich počet je stále obmedzený. Okrem toho pri úprave, odstraňovaní alebo pridávaní údajov je pracovnou jednotkou séria. V tejto situácii sa dostáva do popredia pojem kurzor a v tomto kontexte je kurzor ukazovateľom na riadok.
Kurzor v SQL je oblasť v pamäti databázy, ktorá je určená na uloženie posledného príkazu SQL. Ak je aktuálny príkaz databázový dotaz, do pamäte sa uloží aj riadok údajov dotazu nazývaný aktuálna hodnota alebo riadok aktuálneho kurzora. Špecifikovaná oblasť v pamäti je pomenovaná a prístupná pre aplikačné programy.
Kurzory sa zvyčajne používajú na výber podmnožiny informácií, ktoré sú v nej uložené. Aplikačný program môže kedykoľvek skontrolovať jeden riadok kurzora. Kurzory sa často používajú v SQL príkazy zabudované do aplikačných programov napísaných v procedurálnych jazykoch. Niektoré z nich sú implicitne vytvorené databázovým serverom, zatiaľ čo iné sú definované programátormi.
V súlade so štandardom SQL je možné pri práci s kurzormi rozlíšiť tieto hlavné akcie:
- tvorba resp deklarácia kurzora;
- otváranie kurzora, t.j. jeho naplnenie údajmi, ktoré sú uložené vo viacúrovňovej pamäti;
- výber z kurzora a pomocou nej meniť riadky údajov;
- zatvorenie kurzora, po ktorom sa stane neprístupným pre používateľské programy;
- uvoľnenie kurzora, t.j. vymazanie kurzora ako objektu, pretože jeho zatvorenie nevyhnutne neuvoľní pamäť, ktorá je s ním spojená.
Definícia kurzora sa môže v jednotlivých implementáciách mierne líšiť. Napríklad niekedy musí vývojár explicitne uvoľniť pamäť pridelenú kurzoru. Po uvoľnite kurzor uvoľní sa aj jeho pridružená pamäť. To umožňuje opätovné použitie jeho mena. V iných implementáciách keď zatvorenie kurzora k uvoľneniu pamäte dochádza implicitne. Ihneď po zotavení je k dispozícii pre ďalšie operácie: otvorenie ďalšieho kurzora atď.
V niektorých prípadoch je použitie kurzora nevyhnutné. Ak je to možné, treba sa tomu vyhnúť a pracovať so štandardnými príkazmi na spracovanie údajov: SELECT, UPDATE, INSERT, DELETE. Okrem toho, že kurzory neumožňujú modifikačné operácie na celom objeme údajov, rýchlosť vykonávania operácií spracovania údajov pomocou kurzora je výrazne nižšia ako pri štandardných nástrojoch SQL.
Implementácia kurzorov v prostredí MS SQL Server
SQL Server podporuje tri typy kurzorov:
- Kurzory SQL sa používajú predovšetkým v rámci spúšťačov, uložených procedúr a skriptov;
- serverové kurzory fungujú na serveri a implementujú aplikačné programové rozhranie pre ODBC, OLE DB, DB_Library;
- Klientské kurzory sú implementované na samotnom klientovi. Načítavajú celú sadu riadkov výsledkov zo servera a ukladajú ju lokálne, čo urýchľuje spracovanie údajov znížením plytvania časom stráveným sieťovými operáciami.
Rôzne typy aplikácií pre viacerých používateľov vyžadujú rôzne typy paralelného prístupu k údajom. Niektoré aplikácie vyžadujú okamžitý prístup k informáciám o zmenách v databáze. To je typické pre systémy rezervácie vstupeniek. V iných prípadoch, ako sú systémy štatistického vykazovania, je stabilita údajov dôležitá, pretože ak sa neustále upravujú, programy nebudú schopné efektívne zobrazovať informácie. Rôzne aplikácie potrebujú rôzne implementácie kurzorov.
V SQL Server sa typy kurzorov líšia v možnostiach, ktoré poskytujú. Typ kurzora je určený vo fáze jeho vytvárania a nemožno ho zmeniť. Niektoré typy kurzorov dokážu zistiť zmeny vykonané inými používateľmi v riadkoch zahrnutých v sade výsledkov. SQL Server však sleduje zmeny v takýchto riadkoch iba počas prístupu k riadku a neumožňuje zmenu zmien, keď už bol riadok prečítaný.
Kurzory sú rozdelené do dvoch kategórií: sekvenčné a posúvateľné. Postupne umožňujú výber údajov iba jedným smerom - od začiatku do konca. Posúvateľné kurzory poskytujú väčšiu voľnosť konania - je možné sa pohybovať oboma smermi a skočiť na ľubovoľný riadok výsledkovej množiny kurzora Ak je program schopný modifikovať údaje, na ktoré kurzor ukazuje, nazýva sa rolovateľný a modifikovateľný. Keď už hovoríme o kurzoroch, nemali by sme zabudnúť na izoláciu transakcií. Keď jeden používateľ upraví záznam, iný ho prečíta pomocou vlastného kurzora a navyše môže ten istý záznam upraviť, čo si vyžaduje zachovanie integrity údajov.
SQL Server podporuje statické, dynamické, sekvenčné a ovláda sa sadou kláves.
V schéme s statický kurzor informácie sa z databázy načítajú raz a uložia sa ako snímka (od určitého okamihu), takže zmeny vykonané v databáze iným používateľom nie sú viditeľné. Na chvíľu otvorenie kurzora server nastaví zámok na všetky riadky zahrnuté v jeho kompletnej množine výsledkov. Statický kurzor sa po vytvorení nemení a vždy zobrazuje súbor údajov, ktorý existoval v čase jeho otvorenia.
Ak iní používatelia zmenia údaje zahrnuté v kurzore v zdrojovej tabuľke, neovplyvní to statický kurzor.
IN statický kurzor Nie je možné vykonávať zmeny, preto sa vždy otvára v režime iba na čítanie.
Dynamický kurzor udržiava údaje v „živom“ stave, vyžaduje si to však sieťové a softvérové zdroje. Použitím dynamické kurzory nevytvorí sa úplná kópia zdrojových údajov, ale dynamický výber sa vykoná zo zdrojových tabuliek až vtedy, keď používateľ pristupuje k určitým údajom. Počas načítavania server zamkne riadky a všetky zmeny, ktoré používateľ vykoná v celej množine výsledkov kurzora, budú viditeľné v kurzore. Ak však po načítaní údajov kurzorom vykonal zmeny iný používateľ, v kurzore sa tieto zmeny neprejavia.
Kurzor ovládaný sadou kláves, je uprostred medzi týmito extrémami. Záznamy sa identifikujú v čase odberu vzoriek, a tak sa sledujú zmeny. Tento typ kurzora je užitočný pri implementácii posúvania späť – vtedy nie sú pridania a vymazania riadkov viditeľné, kým sa neaktualizujú informácie a vodič vyberie novú verziu záznamu, ak v ňom boli vykonané zmeny.
Sekvenčné kurzory nie je dovolené načítať údaje v opačnom smere. Používateľ môže vybrať iba riadky od začiatku do konca kurzora. Sériový kurzor neukladá množinu všetkých riadkov. Načítavajú sa z databázy hneď, ako sú vybraté v kurzore, čo umožňuje, aby sa všetky zmeny vykonané užívateľmi v databáze dynamicky prejavili pomocou príkazov INSERT, UPDATE, DELETE. Kurzor zobrazuje najnovší stav údajov.
Statické kurzory poskytujú stabilný pohľad na dáta. Sú vhodné pre informačné „skladové“ systémy: aplikácie pre systémy podávania správ alebo na štatistické a analytické účely. okrem toho statický kurzor lepšie ako iné zvláda vzorkovanie veľkého množstva údajov. Naproti tomu systémy elektronického nákupu alebo rezervácie vstupeniek vyžadujú dynamické vnímanie aktualizovaných informácií pri vykonávaní zmien. V takýchto prípadoch sa používa dynamický kurzor. V týchto aplikáciách je množstvo prenesených údajov zvyčajne malé a pristupuje sa k nim na úrovni riadkov (jednotlivých záznamov). Skupinový prístup je veľmi zriedkavý.
Správa kurzorov v prostredí MS SQL Server
Ovládanie kurzora implementovaný vykonaním nasledujúcich príkazov:
- DECLARE - tvorba resp deklarácia kurzora;
- OTVORENÉ - otváranie kurzora, t.j. naplniť ho údajmi;
- FETCH výber z kurzora a zmena riadkov údajov pomocou kurzora;
- ZAVRIEŤ - zatvorenie kurzora;
- DEALOKOVAŤ – uvoľnenie kurzora, t.j. odstránenie kurzora ako objektu.
Vyhlásenie kurzora
Štandard SQL poskytuje nasledujúci príkaz na vytvorenie kurzora:
Pomocou kľúčového slova INSENSITIVE vytvoríte statický kurzor. Zmeny údajov nie sú povolené, navyše sa nezobrazujú zmeny vykonané inými používateľmi. Ak kľúčové slovo INSENSITIVE chýba, a dynamický kurzor.
Keď zadáte kľúčové slovo SCROLL, vytvorený kurzor sa môže posúvať v ľubovoľnom smere, čo vám umožní použiť ľubovoľné príkazy na výber. Ak tento argument vynecháte, kurzor bude konzistentné, t.j. jeho prezeranie bude možné len jedným smerom - od začiatku do konca.
Príkaz SELECT špecifikuje telo požiadavky SELECT, ktorá určuje výslednú množinu riadkov pre kurzor.
Zadanie FOR READ_ONLY vytvorí kurzor len na čítanie a neumožňuje žiadne úpravy údajov. Odlišuje sa od statického, hoci druhý tiež neumožňuje zmenu údajov. Môže byť deklarovaný ako kurzor len na čítanie dynamický kurzor, ktorá umožní zobrazenie zmien vykonaných iným používateľom.
Vytvorenie kurzora s argumentom PRE AKTUALIZÁCIU vám umožňuje vykonávať v kurzore zmena údajov buď v špecifikovaných stĺpcoch, alebo v prípade absencie argumentu OF názov_stĺpca vo všetkých stĺpcoch.
V prostredí MS SQL Server je akceptovaná nasledujúca syntax príkazu na vytvorenie kurzora:
<создание_курсора>::= DECLARE cursor_name CURSOR FOR SELECT_statement ]]
Použitie kľúčového slova LOCAL vytvorí lokálny kurzor, ktorý je viditeľný len v rámci balíka, spúšťača, uloženej procedúry alebo užívateľom definovanej funkcie, ktorá ho vytvorila. Keď sa balík, spúšťač, procedúra alebo funkcia ukončí, kurzor sa implicitne zničí. Ak chcete odovzdať obsah kurzora mimo konštrukcie, ktorá ho vytvorila, musíte jeho parametru priradiť argument OUTPUT.
Ak je zadané kľúčové slovo GLOBAL, vytvorí sa globálny kurzor; existuje, kým sa neuzavrie aktuálne spojenie.
Zadanie FORWARD_ONLY vytvorí sériový kurzor; Údaje je možné vzorkovať iba v smere od prvého riadku po posledný.
Zadaním SCROLL vytvoríte rolovateľný kurzor; K údajom je možné pristupovať v akomkoľvek poradí a v akomkoľvek smere.
Zadanie STATIC vytvára statický kurzor.
Zadaním KEYSET sa vytvorí klávesový kurzor.
Zadanie DYNAMIC vytvára dynamický kurzor.
Ak zadáte argument FAST_FORWARD pre kurzor READ_ONLY, vytvorený kurzor bude optimalizovaný pre rýchly prístup k údajom. Tento argument nemožno použiť v spojení s argumentmi FORWARD_ONLY alebo OPTIMISTIC.
Kurzor vytvorený s argumentom OPTIMISTIC zabraňuje úprave alebo vymazaniu riadkov, ktoré boli upravené potom otvorenie kurzora.
Zadaním argumentu TYPE_WARNING bude server informovať používateľa o implicitnej zmene typu kurzora, ak nie je kompatibilný s dotazom SELECT.
Otvorenie kurzora
Pre otvorenie kurzora a vyplňte ho údajmi z dotazu SELECT zadaného pri vytváraní kurzora, použite nasledujúci príkaz:
Po otvorenie kurzora Vykoná sa príslušný príkaz SELECT, ktorého výstup je uložený vo viacúrovňovej pamäti.
Načítavanie údajov z kurzora
Hneď po otvorenie kurzora jeho obsah (výsledok vykonania príslušného dotazu) môžete vybrať pomocou nasledujúceho príkazu:
Zadaním FIRST sa vráti úplne prvý riadok kompletnej sady výsledkov kurzora, ktorý sa stane aktuálnym riadkom.
Zadaním LAST sa vráti posledný riadok kurzora. Stáva sa tiež aktuálnou líniou.
Zadaním NEXT sa vráti riadok hneď za aktuálnym v celej množine výsledkov. Teraz sa to stáva aktuálnym. Príkaz FETCH štandardne používa túto metódu na načítanie riadkov.
Kľúčové slovo PRIOR vráti riadok pred aktuálnym. Stáva sa aktuálnym.
Argument ABSOLÚTNE (číslo_riadku | @číslo_riadku_premenná) vráti riadok podľa jeho absolútneho poradového čísla v kompletnej množine výsledkov kurzora. Číslo riadku je možné zadať pomocou konštanty alebo ako názov premennej, v ktorej je číslo riadku uložené. Premenná musí byť celočíselný dátový typ. Uvádzajú sa kladné aj záporné hodnoty. Pri zadaní kladnej hodnoty sa reťazec počíta od začiatku množiny, zatiaľ čo záporná hodnota sa počíta od konca. Vybraný riadok sa stane aktuálnym riadkom. Ak je zadaná hodnota null, nevráti sa žiadny riadok.
Argument RELATÍVNE (počet riadkov | @premenný počet riadkov) vráti riadok, ktorý je zadaným počtom riadkov za aktuálnym. Ak zadáte záporný počet riadkov, vráti sa riadok, ktorý má zadaný počet riadkov pred aktuálnym. Zadaním hodnoty null sa vráti aktuálny riadok. Vrátený riadok sa stane aktuálnym riadkom.
Komu otvoriť globálny kurzor, pred jeho názvom musíte zadať kľúčové slovo GLOBAL. Názov kurzora možno zadať aj pomocou premennej.
V dizajne INTO @názov_premennej [,...n] je zadaný zoznam premenných, v ktorom budú uložené zodpovedajúce hodnoty stĺpcov vráteného riadku. Poradie zadávania premenných sa musí zhodovať s poradím stĺpcov v kurzore a typ údajov premennej sa musí zhodovať s typom údajov v stĺpci kurzora. Ak nie je zadaný konštrukt INTO, potom sa správanie príkazu FETCH bude podobať správaniu príkazu SELECT – údaje sa zobrazia na obrazovke.
Zmena a vymazanie údajov
Ak chcete vykonať zmeny pomocou kurzora, musíte zadať príkaz UPDATE v nasledujúcom formáte:
V jednej operácii je možné zmeniť niekoľko stĺpcov aktuálneho riadku kurzora, ale všetky musia patriť do rovnakej tabuľky.
Ak chcete odstrániť údaje pomocou kurzora, použite príkaz DELETE v nasledujúcom formáte:
V dôsledku toho sa riadok nastavený v kurzore vymaže.
Zatvorenie kurzora
Po zatvorení sa kurzor pre používateľov programu stane nedostupným. Po zatvorení sú všetky zámky nainštalované počas jeho prevádzky odstránené. Uzavretie je možné použiť iba na otvorené kurzory. Zatvorené, ale nie uvoľnený kurzor môžu byť znovu otvorené. Nie je dovolené zatvoriť neotvorený kurzor.
Uvoľnite kurzor
Zatvorenie kurzora nemusí nutne uvoľniť pamäť s tým spojenú. Niektoré implementácie ho musia explicitne uvoľniť pomocou príkazu DEALLOCATE. Po uvoľnite kurzor Uvoľní sa aj pamäť, čo umožňuje opätovné použitie názvu kurzora.
Na kontrolu, či sa dosiahol koniec kurzora, sa odporúča použiť funkciu: @@FETCH_STATUS
Funkcia @@FETCH_STATUS vracia:
0, ak bolo načítanie úspešné;
1, ak načítanie zlyhalo kvôli pokusu o načítanie riadku mimo kurzora;
2, ak načítanie zlyhalo v dôsledku pokusu o prístup k odstránenému alebo upravenému riadku.
DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @name VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Nákupný zoznam" DECLARE klient_cursor MIESTNY KURZOR PRE VÝBER Kód klienta, Spoločnosť, Priezvisko OD klienta KDE Mesto="Moskva" ORDER BY Spoločnosť, Priezvisko OTVORIŤ klient_kurzor FETCH NEXT FROM klient_cursor DO @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 ZAČAŤ VYBRAŤ @message="Klient "+@fam+ "Spoločnosť "+ @firm PRINT @správa SELECT @message="Názov produktu Dátum nákupu Cena" TLAČIŤ @správa DECLARE tovar_cursor KURZOR PRE VÝBER Názov produktu, Dátum transakcie, Produkt .Cena* Transakcia.Množstvo AKO NÁKLADY OD PRODUKTOV VNÚTORNÁ PRIPOJENIE Transakcie S produktom. Kód produktu=Transaction.Product Code WHERE Transaction.Customer Code=@id_kl OPEN tovar_cursor NAČÍTAJTE ĎALEJ Z tovar_cursor DO @nam, @d, @p AK @@FETCH_STATUS<>0 PRINT "Žiadne nákupy" WHILE @@FETCH_STATUS=0 ZAČAŤ VYBRAŤ @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) TLAČIŤ @správa NASTAVIŤ @s=@s+@p NAČÍTAŤ ĎALEJ Z tovar_kurzor DO @nam, @d, @p KONIEC ZAVRIEŤ tovar_kurzor DEALLOCATE tovar_cursor SELECT @message="Celková cena "+ ODOSIELAŤ(@s AS CHAR(6)) TLAČIŤ @správu -- prejsť na ďalšieho klienta -- NAČÍTAŤ ĎALŠIE Z klient_kurzor DO @id_kl, @firm, @fam KONIEC ZATVORIŤ klient_kurzor DEALLOCATE klient_cursor Príklad 13.6. Kurzor pre zobrazenie zoznamu tovarov zakúpených klientmi z Moskvy a ich celkových nákladov.
Príklad 13.7. Vytvorte rolovateľný kurzor pre klientov z Moskvy. Ak telefónne číslo začína 1, vymažte klienta s týmto číslom a v prvom zadaní kurzora nahraďte prvú číslicu v telefónnom čísle 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @správa VARCHAR(80) TLAČIŤ "Zoznam klientov" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEY SET FOR SELECT Firma, Priezvisko, Telefón OD klienta KDE Mesto ="Moskva" OBJEDNAŤ PODĽA Spoločnosti, Priezviska PRE AKTUALIZÁCIU OTVORIŤ klient_kurzor NAČÍTAJ ĎALEJ OD klient_kurzor DO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 ZAČAŤ VYBRAŤ @message="Klient "+@fam+ " Spoločnosť "+ @firm " Phone "+ @tel VYTLAČIŤ @správa -- ak telefónne číslo začína 1, -- vymazať klienta s týmto číslom AK @tel LIKE '1%' VYMAZAŤ klienta, KDE JE AKTUÁLNY klient_kurzor INAK -- prejsť na ďalší klient NAČÍTAJ ĎALŠIE OD klient_kurzor DO @firm, @fam, @tel KONIEC NAČÍTANIE ABSOLÚTNE 1 OD klient_kurzor DO @firma, @fam, @tel -- v prvom zázname nahraďte prvú číslicu v telefónnom čísle 4 AKTUALIZOVAŤ NASTAVENIE klienta Telefón ='4' + RIGHT(@ tel,LEN(@tel)-1)) KDE AKTUÁLNY AKTUÁL klient_kurzor SELECT @message="Klient "+@fam+" Firma "+ @firm "Telefón "+ @tel VYTLAČIŤ @správu ZATVORIŤ klient_kurzor DEALLOCATE klient_kurzor Príklad 13.7. Rolovateľný kurzor pre klientov z Moskvy.
Príklad 13.8. Použitie kurzor ako výstupný parameter procedúry. Procedúra vráti množinu údajov – zoznam produktov.
Vyvolanie procedúry a tlač údajov z výstupného kurzora sa vykonáva takto:
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur VÝSTUP NAČÍTANIE NEXT Z @my_cur DO @n SELECT @n WHILE (@@FETCH_STATUS=0) ZAČAŤ NAČÍTANIE ĎALEJ Z @my_cur DO @n SELECT @n KONIEC ZAVRIEŤ @my_cur DEALLOCATE @my_cur
