Kursoru un cilpu izmantošana programmā Transact-SQL. Kursori MySQL saglabātās procedūras Kursori SQL serverī
Komanda DECLARE CURSOR ļauj izgūt ierakstus no tabulas rindas pēc rindas manipulācijām. Tas ļauj veikt apstrādi pa rindiņām, nevis tradicionālo datu kopu apstrādi, ko veic SQL.
Strādājot ar kursoru, vispirms tiek izmantotas šādas darbības.
Kursors tiek izveidots ar komandu DECLARE. Kursors tiek atvērts ar komandu OPEN.
Darbības ar kursoru tiek veiktas, izmantojot komandu FETCH. Kursors tiek aizvērts ar komandu CLOSE.
Komanda DECLARE CURSOR norāda SELECT priekšrakstu. Katru SELECT priekšraksta atgriezto rindu var izgūt un apstrādāt atsevišķi. Šis Oracle piemērs deklarē kursoru deklarācijas blokā kopā ar vairākiem citiem mainīgajiem. Pēc tam nākamajā blokā BEGIN...END tiek atvērts kursors, no tā tiek veikta atlase un kursors tiek aizvērts.
CURSOR title_price_cursor IS SELECT virsraksts, cena NO nosaukumiem
KAD cena NAV NULL; virsraksta_cenas_valits virsraksta_cenas_kursors ROWTYPE; jaunā_cena NUMURS(10.2);
OPEN virsraksts_cena_kursors;
IEŅEMT virsraksta_cenas_kursa vai INTO nosaukuma_cenas_val.;
new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VĒRTĪBAS
(title_price_val.title, new_price) CLOSE title_price_cursor; BEIGAS;
Tā kā šajā piemērā tiek izmantots PL/SQL, mēs šajā grāmatā nepaskaidrosim daudz koda. Tomēr DECLARE bloks skaidri parāda kursora deklarāciju. Izpildāmā PL/SQL blokā kursors tiek inicializēts ar komandu OPEN, vērtības tiek izgūtas ar komandu FETCH, un visbeidzot kursors tiek aizvērts ar komandu CLOSE.
SELECT priekšraksts ir kursora pamatā, tāpēc ir laba prakse to rūpīgi pārbaudīt, pirms iekļaujat to priekšrakstā DECLARE CURSOR. SELECT priekšraksts var darboties pamatā esošajā tabulā vai skatā. Tāpēc tikai lasāmi kursori var darboties ar neatjaunināmiem skatiem. Paziņojumā SELECT var būt ietvertas tādas klauzulas kā ORDER BY, GROUP BY un HAVING, ja vien šīs klauzulas neatjaunina avota tabulu. Ja kursors ir definēts kā FOR UPDATE, tad ieteicams noņemt šādas klauzulas no priekšraksta SELECT.
Vietējie kursori bieži tiek izmantoti kā saglabāto procedūru izvades parametri. Tāpēc varat definēt un aizpildīt kursoru saglabātajā procedūrā un nodot to pakešu darbam vai saglabātajai procedūrai, kas to izsauca.
Šajā vienkāršajā DB2 piemērā mēs deklarēsim kursoru, kas meklē nodaļu numurus, nodaļu nosaukumus un vadītāju numurus admin_group "XO1".
DEKLARĒT 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;
Šis Microsoft SQL Server piemērs deklarē un atver izdevēju tabulas kursoru. Kursors atlasa pirmo ierakstu, kas atbilst SELECT priekšrakstam no izdevēju tabulas, un ievieto to citā tabulā. Pēc tam tas pāriet uz nākamo ierakstu, pēc tam uz nākamo, līdz visi ieraksti ir apstrādāti. Visbeidzot, kursors tiek aizvērts un atmiņa tiek atbrīvota (komanda DEALLOCATE tiek izmantota tikai Microsoft SQL Server).
DEKLARĀT @izdevēja_nosaukums VARCHAR(20)
DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM izdevējiem WHERE valsts "ASV"
IEŅEMT TĀLĀK NO pub_cursor UZ izdevēja_nosaukumu
WHILE @s>FETCH_STATUS=0
INSERT INTO ārzemju_izdevēji VALUES("j>izdevēja_nosaukums)
AIZVĒRT pub_kursoru AIZVĒRT pub_kursoru
Šajā piemērā var redzēt, kā kursors pārvietojas pa ierakstu kopu. (Šis piemērs ir paredzēts tikai idejas demonstrēšanai, jo patiesībā tāda ir Labākais veidsšīs problēmas risinājums, proti, instrukcija INSERT, SELECT.)
TAS ATTIECAS UZ: SQL Server (kopš 2008)Base SQL dati Azure SQL Data WarehouseParallel Data Warehouse
Definē Transact-SQL servera kursora atribūtus, piemēram, skata rekvizītus un vaicājumu, ko izmanto, lai izveidotu rezultātu kopu, uz kuras darbojas kursors. Paziņojums DECLARE CURSOR atbalsta gan ISO standarta sintaksi, gan sintaksi, kas izmanto Transact-SQL valodas paplašinājumu kopu.
ISO sintakse DEKLARĒT kursora_nosaukumu [ NEJŪTĪGS ] [ RITINĀT ] KURSORS Select_statement [ FOR ( TIKAI LASĪT | ATJAUNINĀJUMS [ OF kolonnas_nosaukums [ ,...n ] ] ) ] [;] Transact-SQL paplašinātā sintakse DEKLARĒT kursora_nosaukums KURSORS [ LOCAL | GLOBĀLS ] [ FORWARD_ONLY | RITINĀT ] [ STATISKA | KEYSET | DINAMISKAIS | FAST_FORWARD ] [ TIKAI LASĪT | SCROLL_LOCKS | OPTIMISTISKA ] [ TIPA_BRĪDINĀJUMS ] FOR select_statement [ ATJAUNINĀŠANAI [ OF kolonnas_nosaukums [ ,...n ] ] ] [;]
kursora_nosaukums
kursora_nosaukums
NEJŪTĪGS
tempdb; tādējādi izmaiņas pamatā esošajās tabulās netiek atspoguļotas datos, ko atgriež šī kursora atlase, un šis kursors nav maināms. Izmantojot ISO sintaksi, ja vien nav norādīta opcija NEJŪTĪGA, pamata tabulās veiktie atjauninājumi un dzējumi tiek parādīti turpmākajās atlasēs.
RITINĀT
Norāda, ka ir pieejamas visas izlases iespējas (PIRMAIS, PĒDĒJAIS, PRIOR, NEXT, RELATIVE, ABSOLUTE). Ja priekšrakstā ISO DECLARE CURSOR nav norādīta opcija SCROLL, tiek atbalstīta tikai opcija NEXT fetch. Opciju SCROLL nevar norādīt ar opciju FAST_FORWARD.
atlasīt_paziņojumu
Standarta SELECT priekšraksts, kas norāda kursora rezultātu kopu. Atslēgvārdi FOR BROWSE un INTO nav atļauti atlasīt_paziņojumu kursora deklarācija.
atlasīt_paziņojumu konflikts ar pieprasītā veida kursoru.
TIKAI LASĪT
Atjaunināt ]
kolonnas_nosaukums [,
.. .n] ir norādīts, izmaiņas pieļauj tikai uzskaitītajās kolonnās. Ja UPDATE priekšraksts tiek izmantots bez kolonnu saraksta, tad atjauninājums ir iespējams visām kolonnām.
kursora_nosaukums
Konkrētā servera kursora Transact-SQL nosaukums. kursora_nosaukums jāievēro identifikatoru noteikumi.
VIETĒJS
Norāda, ka kursors ir lokāls pakotnei, saglabātajai procedūrai vai aktivizētājam, kurā tas tika izveidots. Kursora nosaukums ir derīgs tikai šajā apgabalā. Uz kursoru var atsaukties, izmantojot pakotnes lokālos mainīgos, saglabātās procedūras, trigerus vai saglabātās procedūras izvades parametru. Parametrs OUTPUT tiek izmantots, lai nodotu lokālo kursoru izsaucējai pakotnei, saglabātajai procedūrai vai aktivizētājam, kas pēc tam var piešķirt parametru kursora mainīgajam, lai pēc tam, kad saglabātā procedūra ir pabeigta, piekļūtu kursoram. Kursors tiek netieši atbrīvots, kad partija, saglabātā procedūra vai trigeris pabeidz izpildi, ja vien kursors nav nodots parametram OUTPUT. Ja kursors tika nodots parametram OUTPUT, kursors tiek atbrīvots, kad tiek atbrīvoti visi mainīgie, kas atsaucas uz to, vai kad tiek iziets no tvēruma.
GLOBĀLI
Norāda, ka savienojumam kursors ir globāls. Kursora nosaukumu var izmantot jebkura saglabātā procedūra vai pakotne, kas darbojas savienojumā. Kursors tiek netieši atbrīvots tikai tad, ja savienojums ir bojāts.
FORWARD_ONLY
Norāda, ka kursoru var skatīt tikai no pirmās rindas līdz pēdējai. Tiek atbalstīta tikai ieneses opcija FETCH NEXT. Ja FORWARD_ONLY ir norādīts bez atslēgvārdiem STATIC, KEYSET vai DYNAMIC, kursors darbojas kā DINAMISKAIS kursors. Ja nav norādīts ne FORWARD_ONLY, ne SCROLL arguments, pēc noklusējuma ir arguments FORWARD_ONLY, ja vien nav atslēgvārdu STATIC, KEYSET vai DYNAMIC. Kursoriem STATIC, KEYSET un DYNAMIC ir noklusējuma vērtība SCROLL. Atšķirībā no datu bāzes API, piemēram, ODBC un ADO, režīmu FORWARD_ONLY atbalsta šādi Transact-SQL kursori: STATIC, KEYSET un DYNAMIC.
STATISKA
Definē kursoru, kas izveido pagaidu datu kopiju, ko izmantot kursors. Visi kursora vaicājumi piekļūst norādītajai pagaidu tabulai tempdb; tādējādi izmaiņas pamatā esošajās tabulās netiek atspoguļotas datos, ko atgriež šī kursora atlase, un šis kursors nav maināms.
KEYSET
Norāda, ka kursora rindu dalība vai secība paliek nemainīga, kad tas tiek atvērts. Tabulā ir iebūvēts atslēgu komplekts, kas unikāli identificē rindas tempdb sauca atslēgas.
Kursora īpašnieka veiktās vai citu lietotāju veiktās bezatslēgas vērtību izmaiņas pamata tabulās tiek parādītas, kad kursora īpašnieks to apskata. Citu lietotāju veiktās izmaiņas netiek atspoguļotas (izmaiņas nevar veikt, izmantojot Transact-SQL servera kursoru). Ja rinda tiek dzēsta, mēģinot ienest rindas, tiek atgriezta @@FETCH_STATUS -2. Galveno vērtību atjauninājumi pāri kursora robežām ir līdzīgi vecas rindas dzēšanai un jaunas rindas ievietošanai. Rinda ar jaunajām vērtībām nav redzama, un mēģinājumi ienest rindu ar vecajām vērtībām atgriež @@FETCH_STATUS -2. Atjauninājumi ir redzami nekavējoties, ja tie tiek veikti, izmantojot kursoru, izmantojot klauzulu WHERE CURRENT OF.
DINAMISKI
Definē kursoru, kas parāda visas datu izmaiņas, kas veiktas rezultātu kopas rindās, skatot šo kursoru. Datu vērtības, secība un rindas piederība katrā atlasē var atšķirties. Atlases opciju ABSOLUTE neatbalsta dinamiskie kursori.
ĀTRI UZ PRIEKŠU
Norāda FORWARD_ONLY, READ_ONLY kursoru, kuram ir iespējota veiktspējas optimizācija. Opciju FAST_FORWARD nevar norādīt ar opcijām SCROLL vai FOR_UPDATE.
TIKAI LASĪT
Novērš izmaiņas, kas veiktas, izmantojot šo kursoru. Klauzula WHERE CURRENT OF nevar atsaukties uz kursoru priekšrakstā UPDATE vai DELETE. Šai opcijai ir prioritāte pār noklusējuma kursora atsvaidzināšanas līdzekli.
SCROLL_LOCKS
Norāda, ka pozicionētie atjauninājumi vai dzējumi, kas veikti, izmantojot kursoru, ir garantēti veiksmīgi. SQL Server bloķē rindas, kad tās tiek nolasītas kursorā, lai nodrošinātu, ka šīs rindas ir pieejamas turpmākām izmaiņām. Opciju SCROLL_LOCKS nevar norādīt ar opciju FAST_FORWARD vai STATIC.
OPTIMISTISKA
Norāda, ka pozicionētie atjauninājumi vai dzēšana, kas veikta, izmantojot kursoru, neizdosies, ja rinda ir atjaunināta kopš tās nolasīšanas kursorā. SQL Server nebloķē rindas, kad tās tiek nolasītas kursorā. Tā vietā tiek izmantoti salīdzinājumi laika zīmogs kolonnu vērtības vai kontrolsummas, ja nav tabulā laika zīmogs kolonnu, lai noteiktu, vai rinda ir mainījusies kopš tās nolasīšanas kursorā. Ja rinda ir mainīta, pozicionētās atjaunināšanas vai dzēšanas mēģinājumi neizdosies. Opciju OPTIMISTIC nevar norādīt ar opciju FAST_FORWARD.
TYPE_WARNING
Norāda, ka klientam tiks nosūtīts brīdinājums, ja kursors tiek netieši pārveidots no viena pieprasītā veida uz citu.
atlasīt_paziņojumu
Standarta SELECT priekšraksts, kas norāda kursora rezultātu kopu. Atslēgvārdi COMPUTE, COMPUTE BY, FOR BROWSE un INTO nav atļauti atlasīt_paziņojumu kursora deklarācija.
SQL Server netieši pārvērš kursoru uz citu tipu, ja klauzulas ir iekļautas atlasīt_paziņojumu konflikts ar pieprasītā veida kursoru. Papildinformāciju skatiet sadaļā Netiešā kursora reklāmguvumi.
ATJAUNINĀŠANAI ]
Definē atjaunināmās kursora kolonnas. Ja OF kolonnas_nosaukums [,
... n] ir nodrošināts, izmaiņas pieļauj tikai uzskaitītajās kolonnās. Ja UPDATE priekšraksts tiek izmantots bez kolonnu saraksta, tad atjauninājums ir iespējams visām kolonnām, ja vien nav norādīta vienlaicības opcija READ_ONLY.
Paziņojums DECLARE CURSOR definē Transact-SQL servera kursora atribūtus, piemēram, skata rekvizītus un vaicājumu, ko izmanto, lai izveidotu rezultātu kopu, kurā darbojas kursors. Priekšraksts OPEN aizpilda rezultātu kopu, un FETCH priekšraksts atgriež no tās rindu. Paziņojums CLOSE notīra pašreizējo rezultātu kopu, kas saistīta ar kursoru. Paziņojums DEALLOCATE atbrīvo kursora izmantotos resursus.
Pirmā priekšraksta DECLARE CURSOR forma izmanto ISO sintaksi, lai norādītu kursora parametrus. Otrajā priekšraksta DECLARE CURSOR formā tiek izmantoti Transact-SQL valodas paplašinājumi, kas ļauj definēt kursorus, izmantojot tos pašus veidus, kas tiek izmantoti datu bāzes API kursora funkcijās, piemēram, ODBC un ADO.
Šīs divas formas nevar sajaukt. Ja pirms atslēgvārda CURSOR norādāt SCROLL vai izlaižat atslēgvārdus, jūs nevarat izmantot atslēgvārdus starp CURSOR un arī atlasīt_paziņojumu atslēgvārdi. Norādot atslēgvārdus starp KURSORU, kā arī priekš atlasīt_paziņojumu atslēgvārdus, jūs nevarat norādīt SCROLL vai INSENSITIVE pirms CURSOR atslēgvārda.
Ja priekšrakstam DECLARE CURSOR izmantojat Transact-SQL sintaksi un nenorāda opcijas READ_ONLY, OPTIMISTIC vai SCROLL_LOCKS, tiek pieņemta šāda noklusējuma vērtība.
Ja SELECT priekšraksts neatbalsta atjauninājumus (vai tam nav pietiekamas atļaujas, vai tiek piekļūts attālām tabulām, kas neatbalsta atjauninājumus utt.), kursors ir iestatīts uz READ_ONLY.
STATIC un FAST_FORWARD kursori pēc noklusējuma ir READ_ONLY.
DYNAMIC un KEYSET kursori pēc noklusējuma ir OPTIMISTIC.
Uz kursoriem var atsaukties tikai citi Transact-SQL priekšraksti. Datu bāzes API funkcijas nevar atsaukties uz kursoriem. Piemēram, kad kursors ir deklarēts, OLE DB, ODBC vai ADO funkcijas un metodes nevar atsaukties uz tā nosaukumu. Kursora rindas nevar atlasīt, izmantojot atbilstošās API funkcijas un metodes; Šim nolūkam ir jāizmanto Transact-SQL FETCH priekšraksti.
Tālāk norādītās saglabātās procedūras var izmantot, lai definētu kursora īpašības pēc tā deklarēšanas.
Mainīgos var izmantot kā daļu atlasīt_paziņojumu, kurā ir deklarēts kursors. Kursora mainīgo vērtības pēc tā deklarēšanas nemainās.
Pēc noklusējuma DECLARE CURSOR atļaujas tiek piešķirtas visiem lietotājiem, kuriem ir SELECT atļauja kursora izmantotajos skatos, tabulās un kolonnās.
Tabulā ar kopu kolonnu krātuves indeksu nevar izmantot kursorus vai aktivizētājus. Šis ierobežojums neattiecas uz negrupētiem indeksiem; Varat izmantot kursorus un aktivizētājus tabulā ar negrupētu kolonnu krātuves indeksu.
A. Izmantojot vienkāršu kursoru un sintaksi
Rezultātu kopa, kas izveidota, atverot šo kursoru, ietver visas tabulas rindas un kolonnas. Šo kursoru var atjaunināt, visi atjauninājumi un dzējumi ir attēloti šī kursora atlasē. FETCH``NEXT ir ienest tikai tāpēc, ka netika norādīts parametrs SCROLL.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. Ligzdoto kursoru izmantošana, lai parādītu atskaiti
Nākamajā piemērā tiek izmantoti ligzdoti kursori, lai parādītu sarežģītu pārskatu. Katram pakalpojumu sniedzējam tiek deklarēts iekšējais kursors.
IESTATĪT NOCOUNT ON ;
DECLARE @vendor_id int, @vendor_name nvarchar (50), @message varchar (80), @product nvarchar (50); DRUKĀT" -------- Pārdevēja produktu pārskats --------";
DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor IEŅEMT TĀLĀK NO vendor_cursor UZ @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 SĀKT DRUKĀT " " ATLASĪT @ziņojums = "----- Produkti no pārdevēja: "+ @vendor_name PRINT @ message -- Pasludināt, pamatojoties uz iekšējo kursoru
-- uz vendor_id no ārējā kursora. DEKLARĒT product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID UN pv.VendorID = @vendor_id -- Mainīga vērtība no ārējā kursora ATVĒRT produkta_kursoru IEŅEMT TĀLĀK NO produkta_kursora UZ @product IF @@FETCH_STATUS<>0 PRINT "<
Kursors ir saite uz kontekstuālās atmiņas apgabalu. Dažās SQL programmēšanas valodas implementācijās (Oracle, Microsoft SQL Server) - rezultātu kopa, kas iegūta, izpildot vaicājumu, un ar to saistītais pašreizējā ieraksta rādītājs. Es teiktu, ka kursors ir virtuāla tabula, kas attēlo alternatīvu datu krātuvi. Šajā gadījumā kursors ļauj piekļūt tā datiem tā, it kā tie būtu parasta masīva dati.
Kursori tiek izmantoti saglabātajās procedūrās. Pietiekami daudz teorijas, apskatīsim piemēru:
Mums ir datu bāze (datubāze ir nedaudz slikta, šī ir viena no manām laboratorijas darbi, bet mūsu datu bāzes skolotājs uzstāja uz šādu struktūru)
/*bankas informācija*/
IZVEIDOT TABULU "banka" (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL NOKLUSĒJUMS "" ,
PRIMĀRĀ ATSLĒGA ("Bankas ID"))ENGINE=InnoDB
Rakstzīmju kopa "utf8" SĀKOT "utf8_bin" ;
/*dati par noguldījumiem */
IZVEIDOT TABULU "bankas izplatīšana" (
`BankId' INTEGER (11) NOT NULL ,
"Persent" INTEGER (11) NOKLUSĒJUMS NULL ,
ContributeAmount DECIMAL (10,0) NOT NULL ,
"ClientId" INTEGER (11) NOT NULL ,
PRIMARY KEY ("Bankas ID", "ClientId"),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
IEROBEŽOJUMI `bankdistribution_fk` ĀRĒJĀ ATSLĒGA (`BankId`) ATSAUCES `bank` (`BankId`),
IEROBEŽOJUMS `bankdistribution_fk1` ĀRĒJĀ ATSLĒGA (`ClientId`) ATSAUCES `klients` (`ClientId`)
)ENGINE=InnoDB
/*dati par investoriem*/
IZVEIDOT TABULU "klients" (
"ClientId" INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Uzvārds` VARCHAR (50) COLATE utf8_bin NOT NULL NOKLUSĒJUMS "" ,
`Nosaukums` VARCHAR (50) COLLATE utf8_bin NOT NULL NOKLUSĒJUMS "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL NOKLUSĒJUMS "" ,
Tālrunis VARCHAR (50) COLLATE utf8_bin NOT NULL NOKLUSĒJUMS "" ,
`Adrese` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
"SafeId" INTEGER (5) NOT NULL ,
PRIMĀRĀ ATSLĒGA (klienta ID, kredītkartes ID),
KEY `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" SĀKOT "utf8_bin"
Pieņemsim, ka mums ir jāsaņem katra banka pēc kārtas un jāveic ar to dažas darbības. Tālāk sniegtais vaicājums varētu mums palīdzēt
Atlasiet "banku".* NO "bankas" IEROBEŽOTS NUMURS NO THE_RECORD_WEED,1. Tādējādi, izmantojot LIMIT WE NEED_RECORD NUMBER, 1, mēs izvelkam katru ierakstu ciklā no bankas tabulas un veicam ar to vajadzīgās darbības, vienlaikus palielinot WE NEED_RECORD NUMBER vērtību par 1. Tagad mēs darīsim to pašu, bet izmantosim kursors
Sāciet
/* mainīgie, no kuriem mēs iegūstam datus */
Deklarēt vBankId veselu skaitli ;
Deklarēt vBankas nosaukumu VARCHAR(50);
Deklarēt vAdresi VARCHAR(50);
Paziņot vPhone VARCHAR (50);
/* hadlera mainīgais — a*/
Pasludināt pabeigtu veselu skaitļu noklusējuma vērtību 0;
/*Kursora deklarācija*/
Declare BankCursor kursors Select `bank`.`BankId`, `bank`.`BankName`, `bank`.`Address`, `bank`.`Tālrunis, NO `bankas` kur 1;
/*APSTRĀDĀTĀJA mērķis, kas tiks paskaidrots tālāk*/
DEKLARĒT TURPINĀTĀJU SQLSTATE "02000" SET done=1;
/* atvērt kursoru */
Atvērt BankCursor;
/*izgūt datus*/
KAmēr darīts = 0 DO
mēs veicam nepieciešamās darbības
BEIGAS ;
/*aizverot kursoru*/
Aizvērt BankCursor;
BEIGAS ;* Šis avota kods tika izcelts, izmantojot avota koda izcelšanas rīku.
Kļūda: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Ziņojums: nav datu — nav ielādēta, atlasīta vai apstrādāta nulle rindu
SQLSTATE: 02000 tiek aktivizēts, kad ir sasniegts kursora beigas vai kad atlase vai atjaunināšana atgriež tukšu virkni.
Nākamajā rindā mēs deklarējām kursoru DECLARE cursor_name CURSOR FOR select_statement;
Atvērt kursoru Atvērt kursora_nosaukums;
Pēc tam, līdz sasniedzam kursora beigas (WHILE done = 0 DO), mēs iegūstam datus un apstrādājam tos.
Pirms iziet no saglabātās procedūras, kursors ir jāaizver. Aizvērt kursora_nosaukums;
Tas nešķiet sarežģīti. Taču ar SQLSTATE "02000" ir saistītas daudzas nepilnības.
KAmēr darīts = 0 DO
IEŅEMT BankCursor INTO vBankId,vBankName,vAddress,vPhone;
Atlasiet (ContributeAmount) INTO vContributeAmountSUM NO bankdistribution, kur BankId = vBankId limits 1;
mēs veicam dažas darbības
BEIGAS, KAMĒR ;* Šis avota kods tika izcelts, izmantojot avota koda izcelšanas rīku.
Viss ir labi un pareizi no sintakses viedokļa. Bet no loģiskā viedokļa nē. Var gadīties, ka noguldītāji nav atvēruši kontus kādā bankā, tad Select (ContributeAmount) INTO vContributeAmountSUM FROM bankas izplatīšanai kur BankId = vBankId limits 1; SQLSTATE: tiks aktivizēts 02000, pabeigtais mainīgais tiks iestatīts uz 1, un cilpa while beigsies agrāk, nekā mēs gaidījām. No tā var izvairīties, rīkojoties šādi
KAmēr darīts = 0 DO
IEŅEMT BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* izraksts bankai jebkura tās noguldījuma summas */
if (vContributeAmountSUM > 0), tad
/* izraksts bankai jebkura tās noguldījuma summas */
beigas, ja ;
mēs veicam dažas darbības
BEIGAS, KAMĒR ;* Šis avota kods tika izcelts, izmantojot avota koda izcelšanas rīku.
Ar pirmo pieprasījumu mēs pārbaudījām, vai ir iemaksas (ja tādas nav, tad vContributeAmountSUM == 0) un tikai tad, ja tādas ir, mēs izgūstam datus.
Tagad pieņemsim, ka katram klientam ir jānoņem kopējā summa kontos dažādās bankās
Deklarējiet ClientSummCursor Kursoru, lai atlasītu summu
Deklarējiet ClientSummCursor kursoru, lai atlasītu summu (bankdistribution. ContributeAmount), bankdistribution. ClientId NO 'bankdistribution' iekšējā savienojuma klienta vietā (client.ClientId = bankdistribution.`ClientId`), kur 1 grupē pēc `bankdistribution. `ClientId';Atvērt ClientSummCursor;
KAmēr darīts = 0 DO
IEŅEMT BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* izraksts bankai jebkura tās noguldījuma summas */
Atlasiet Count(ContributeAmount) INTO vContributeAmountSUM NO bankdistribution, kur BankId = vBankId limits 1;
/* pārbaudiet, vai šajā bankā tiešām ir noguldījumi */
if (vContributeAmountSUM > 0), tad
/* izraksts bankai jebkura tās noguldījuma summas */
Atlasiet ContributeAmount INTO vContributeAmountSUM NO bankdistribution, kur BankId = vBankId limits 1;
beigas, ja ;
mēs veicam dažas darbības.
BEIGAS, KAMĒR ;* Šis avota kods tika izcelts, izmantojot avota koda izcelšanas rīku.
Tāda pati situācija var rasties, ja dati ClientSummCursor kursorā beidzas agrāk nekā dati BankCursor, tiek aktivizēts SQLSTATE: 02000, pabeigtais mainīgais ir iestatīts uz 1 un while cilpa beidzas agrāk, nekā mēs gaidījām. No tā var izvairīties, rīkojoties šādi
Atvērt ClientSummCursor;
KAmēr darīts = 0 DO
IEŅEMT BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* izraksts bankai jebkura tās noguldījuma summas */
Atlasiet Count(ContributeAmount) INTO vContributeAmountSUM NO bankdistribution, kur BankId = vBankId limits 1;
/* pārbaudiet, vai šajā bankā tiešām ir noguldījumi */
if (vContributeAmountSUM > 0), tad
/* izraksts bankai jebkura tās noguldījuma summas */
Atlasiet ContributeAmount INTO vContributeAmountSUM NO bankdistribution, kur BankId = vBankId limits 1;
beigas, ja ;
/* pirms datu izvilkšanas no otrā kursora atcerieties sqlstate stāvokli */
SET old_status = pabeigts;
/* izvelciet mums nepieciešamos datus */
IEŅEMT ClientSummCursor INTO vSum,vClientId;
/* pārbaudiet, vai dati tika izgūti un vai sqlstate 0200 neizdevās */
ja (izdarīts = 0), tad
mēs veicam dažas darbības.
beigas, ja ;
/* pirms brīža beigām atjauno pabeigtā mainīgā vērtību */
iestatīts darīts = vecais_statuss;
BEIGAS, KAMĒR ;* Šis avota kods tika izcelts, izmantojot avota koda izcelšanas rīku.
Paldies visiem, kas līdz šim ir lasījuši, ceru, ka kādam noderēs.
Kursora ieviešana datu bāzē atgādina Java klasi, kurai ir datu kopums un metodes tā apstrādei. Kurā sql kursors izmanto datus kā regulāru masīvu. Kursorus var izmantot trigeros, saglabātajās procedūrās un funkcijās.
Saskaņā ar SQL standartu, strādājot ar kursoriem, tiek veiktas šādas pamata darbības:
- kursora deklarācija;
- kursora atvēršana ar nolasīšanas datiem;
- rindu pa rindiņai datu paraugu ņemšana no kursora;
- rindas datu maiņa, izmantojot kursoru;
- kursora aizvēršana, pēc kuras tas kļūst nepieejams;
- atlaižot kursoru, t.i. kursora noņemšana no atmiņas, jo tā aizvēršana ne vienmēr atbrīvo ar to saistīto atmiņu.
Dažādās realizācijās definīcija kursors var būt dažas atšķirības. Piemēram, dažreiz ir nepieciešams skaidri atbrīvot kursoram atvēlēto atmiņu. Kad kursors ir atbrīvots, tiek atbrīvota arī ar to saistītā atmiņa. Tas ļauj atkārtoti izmantot kursora nosaukumu. Citās implementācijās, kad kursors ir aizvērts, atmiņa tiek atbrīvota netieši.
Dažos gadījumos jūs nevarat iztikt bez kursora izmantošanas. Taču, ja iespējams, jāizvairās no kursora lietošanas un jāstrādā ar standarta datu apstrādes komandām: SELECT, UPDATE, INSERT, DELETE. Tas ir saistīts ar to, ka kursori neļauj veikt modifikācijas operācijas visam datu apjomam un datu apstrādes operāciju veikšanas ātrums, izmantojot kursoru, ir ievērojami mazāks nekā standarta līdzekļi SQL.
Ja programma var mainīt kursorā ielādētos datus, tad to sauc par modificējamiem. Runājot par kursoriem, nevajadzētu aizmirst par darījumu izolāciju. Viens lietotājs maina ierakstu, izmantojot kursoru, bet cits lietotājs lasa šo ierakstu, izmantojot savu kursoru. Turklāt viņš var mainīt to pašu ierakstu, kas rada nepieciešamību saglabāt datu integritāti.
Kursora deklarēšana
Kursori ir jādeklarē, pirms tos var izmantot. SQL standarts kursora izveidei izmanto šādu sintaksi:
Kursora_nosaukuma kursora deklarēšana atlases_paziņojumam ])]
Šī izteiksme deklarē kursoru deklarēt kursoru ar nosaukumu "kursora_nosaukums".
NEJŪTĪGS tiek izveidots statisks kursors, kas neļauj veikt izmaiņas. Turklāt netiek rādītas citu lietotāju veiktās izmaiņas. Ja trūkst atslēgvārda INSENSITIVE, tiek izveidots dinamisks kursors.
Lietojot atslēgvārdu RITINĀT izveidoto kursoru var ritināt jebkurā virzienā, ļaujot lietot jebkuras atlases komandas. Ja šis arguments tiek izlaists, kursors būs secīgs, t.i. tā apskate būs iespējama tikai vienā virzienā – no sākuma līdz beigām.
Izteiksme atlasīt_paziņojumu norāda struktūru informācijas lasīšanai, piemēram, atlasiet ... no ... . Tajā nedrīkst būt operators iekšā, jo kursoram ir savs operators atnest lai aizpildītu mainīgos ar kursora datiem.
Norādot argumentu TIKAI LASĪŠANAI tiks izveidots tikai lasāms kursors, un datu izmaiņas netiks atļautas. Dinamisku kursoru var deklarēt kā tikai lasāmu kursoru, ļaujot parādīt cita lietotāja veiktās izmaiņas.
Kursora izveide ar argumentu ATJAUNINĀŠANAIļauj veikt izmaiņas datos kursorā norādītajās kolonnās vai, ja nav argumentu OF kolonnas_nosaukums, visās kolonnās.
Jūs varat deklarēt vairākus kursorus apakšprogrammā. Bet katram kursoram ir jābūt unikālam nosaukumam. Lai atvērtu kursoru, jāizmanto operators atvērts kas atver iepriekš deklarēto kursoru:
Kursors atvērts
SQL definē šādu sintaksi kursora atvēršanai "kursors atvērts":
Atvērt kursora_nosaukums;
Datu ienešana no kursora, kursora ielāde
Sintakse datu nolasīšanai no kursora dažos mainīgajos ir šāda:
Ienest kursora_nosaukums var_nosaukums [, var_nosaukums] ...;
Operators atnest atlasa atvērtos kursora datus mainīgajos, kas atrodas pēc iekšā un pārvieto kursoru uz nākamo pozīciju.
Kursors aizvērt
Operators aizveriet aizver kursoru. Ja operators nav skaidri norādīts, kursors tiek aizvērts automātiski, kad tiek aizvērts attiecīgais programmas bloks.
Aizvērt kursora_nosaukums;
Pēc aizvēršanas kursors kļūst nepieejams. Aizverot, tiek atbrīvotas visas slēdzenes, kas uzstādītas kursora darbības laikā. Var aizvērt tikai atvērtos kursorus. Aizvērtu, bet neatbrīvotu kursoru var atvērt atkārtoti. Nav atļauts aizvērt neatvērtu kursoru.
Katrai DBVS ir savas kursora lietošanas īpatnības.
Kursoru izmantošanas iespējas programmā Oracle
PL/SQL ir četri kursora atribūti %ATRAST, %NAV ATRASTS, %ISOPEN Un %ROWCOUNT. Kursora atribūti tiek deklarēti kā operatori %TYPE un %ROWTYPE, pa labi no kursora nosaukuma.
%FOUND atribūts
%NOTFOUND atribūts
Atribūts %NOTFOUND ir tieši pretējs atribūtam %FOUND.
%ISOPEN atribūts
Atribūts %ISOPEN tikai norāda, vai kursors ir atvērts vai nav.
%ROWCOUNT atribūts
Atribūts %ROWCOUNT ir skaitlisks atribūts, kas atgriež kursora nolasīto rindu skaitu noteiktā laika brīdī.
SQL kursora piemērs Oracle DBVS
Deklarē v_id managers.id % TYPE; v_name vadītāji.nosaukums%TIPA; v_comm managers.comm%TYPE; crs kursors atlasīt id, vārdu, summu (comm) kā komu no pārvaldniekiem, kur dati no "2014-11-01" un "2014-11-30" tiek grupēti pēc id, vārda; sākt atvērt CR; cilpa EXIT WHEN crs%NOTFOUND; IEŅEMT CR uz v_id, v_name, v_comm; ievietojiet bonusa (id, nosaukums, comm) vērtībās (crs.id, crs.name, crs.comm); gala cilpa; apņemties; aizvērt crs; beigas;
Kursoru izmantošanas iespējas SQL serverī
MSSQL izmantotie kursori var būt secīgi vai ritināmi. Sequential ļauj atlasīt datus tikai vienā virzienā – no sākuma līdz beigām. Ritināmie kursori ļauj pārvietoties abos virzienos un ļauj pāriet uz patvaļīgu rindu kursora rezultātu kopā.
SQL Server atbalsta statiskus, dinamiskus, secīgus un atslēgu kopas virzītus kursorus.
Statiskā kursora dizainā informācija tiek saglabāta kā momentuzņēmums kādā brīdī. Līdz ar to cita lietotāja veiktās izmaiņas datu bāzē nav redzamas. Kamēr kursors tiek atvērts, serveris bloķē visas rindas, kas iekļautas tā pilnajā rezultātu kopā. Statiskais kursors pēc izveides nemainās un vienmēr parāda datu kopu, kas pastāvēja tās atvēršanas brīdī. Ja citi lietotāji maina kursorā iekļautos datus avota tabulā, tas neietekmēs statisko kursoru. Statiskajā kursorā nav iespējams veikt izmaiņas, tāpēc tas vienmēr tiek atvērts tikai lasīšanas režīmā.
Dinamiskajam kursoram ir nepieciešami papildu tīkla pieskaitāmie resursi un programmatūras resursi. Izmantojot dinamiskos kursorus, netiek izveidota pilnīga datu kopija, bet atlase no avota tabulām tiek veikta tikai tad, kad lietotājs piekļūst noteiktiem datiem. Ieneses laikā serveris bloķē rindas, un visas izmaiņas, ko lietotājs veic kursora pilnajā rezultātu kopā, būs redzamas kursorā. Tomēr, tiklīdz kursors ir ienesis datus, cita lietotāja veiktās izmaiņas kursorā vairs netiks atspoguļotas.
Kursoram, ko kontrolē taustiņu kopa, ir īpašības starp statisko un dinamisko. Ieraksti tiek identificēti izlases laikā, un tādējādi izmaiņas tiek izsekotas. Šis kursora veids ir noderīgs, ieviešot ritināšanu atpakaļ. Šajā gadījumā datu pievienošana un dzēšana nav redzama, kamēr informācija nav atjaunināta un kursors nav atlasīts jauna versija ierakstus, ja tajos ir veiktas izmaiņas.
Statiskos kursorus vislabāk izmantot informācijas apstrādes sistēmām, t.i. ziņošanas sistēmām vai statistikas un analītiskiem nolūkiem. Statisks kursors ir labāks, lai iegūtu lielu datu apjomu. Objektu (sēdvietu, biļešu) elektroniskās pirkšanas vai rezervēšanas sistēmās ir nepieciešams dinamiski uztvert atjaunināto informāciju, veicot izmaiņas. Šādos gadījumos tiek izmantots dinamiskais kursors. Šajās lietojumprogrammās pārsūtīto datu apjoms parasti ir neliels, un tiem var piekļūt individuālā ieraksta līmenī.
Secīgie kursori neļauj ienest datus pretējā virzienā, tikai no kursora sākuma līdz beigām. Secīgs kursors nesaglabā visu datu rindu kopu. Tie tiek nolasīti no datu bāzes, tiklīdz kursorā ir izdarīta atlase, kas ļauj dinamiski atspoguļot visas lietotāju veiktās izmaiņas datu bāzē, izmantojot komandas INSERT, UPDATE, DELETE. Kursors nolasa jaunāko datu stāvokli.
Kursora deklarācija
Deklarēt kursora_nosaukuma kursoru priekš SELECT_paziņojuma ]]
Lietojot atslēgvārdu VIETĒJS Tiks izveidots lokālais kursors, kas ir redzams tikai blokā, aktivizētājā, saglabātajā procedūrā vai lietotāja definētā funkcijā. Atslēgvārds GLOBĀLI, definē globālo kursoru, kas pastāv, līdz pašreizējais savienojums tiek aizvērts.
Operators FORWARD_ONLY definē secīgu kursoru, kas ļauj izgūt datus tikai virzienā no pirmās rindas uz pēdējo. Lietojot operatoru RITINĀT tiek izveidots ritināms kursors, kas ļauj piekļūt datiem jebkurā secībā un jebkurā virzienā.
Kursora veidu nosaka operatori:
- STATIC - statiskā kursora izveidošana;
- DINAMISKAIS - dinamiska kursora izveidošana;
- KEYSET - atslēgas kursora izveide.
Ja par kursoru TIKAI LASĪT norādīt argumentu ĀTRI UZ PRIEKŠU, tad izveidotais kursors tiks optimizēts ātra piekļuve uz datiem. Šo argumentu nevar izmantot kopā ar argumentiem FORWARD_ONLY Un OPTIMISTISKA.
Ja kursors ir izveidots ar operatoru OPTIMISTISKA, tad ir aizliegts mainīt vai dzēst rindas, kas tika mainītas pēc kursora atvēršanas.
Norādot argumentu TYPE_WARNING serveris ziņos par netiešām kursora veida izmaiņām, ja tās nav saderīgas ar SELECT vaicājumu.
Datu izgūšana no kursora, ielāde
Tūlīt pēc kursora atvēršanas jūs varat iegūt tā saturu, izmantojot šādu komandu:
Lietojot operatoru PIRMAIS tiks atgriezta kursora rezultātu kopas pirmā rinda, kas kļūst par pašreizējo rindu. Kad norādīts PĒDĒJAIS tiks atgriezta kursora pēdējā rinda. Tā arī kļūst par pašreizējo līniju.
Norādot operatoru NĀKAMAIS tiks atgriezta rinda uzreiz aiz pašreizējās rezultātu kopā. Šī rinda kļūst par pašreizējo līniju. Noklusējuma komanda IEŅEMT izmanto tieši šo rindu iegūšanas metodi.
Norādot operatoru PRIOR rinda pirms pašreizējās tiks atgriezta. Šī rinda kļūst par pašreizējo līniju.
Operators ABSOLUTE (rindas_numurs | @line_number_mainīgs) atgriež rindu pēc tās absolūtā kārtas numura visā kursora rezultātu kopā. Rindas numuru var norādīt, izmantojot konstanti vai kā mainīgā lieluma nosaukumu, kurā tiek saglabāts rindas numurs. Mainīgajam ir jābūt vesela skaitļa datu tipam. Ir norādītas gan pozitīvas, gan negatīvas vērtības. Norādot pozitīvu vērtību, virkne tiek skaitīta no kopas sākuma, bet negatīvā vērtība tiek skaitīta no beigām. Atlasītā līnija kļūst par pašreizējo līniju. Ja ir norādīta nulles vērtība, neviena rinda netiek atgriezta.
Arguments RELATIVE (rindu skaits | @mainīgs rindu skaits) atgriež līnijas nobīdi ar norādīto rindu skaitu pēc pašreizējās. Ja norādāt negatīvu rindu skaitu, tiks atgriezta rinda, kas ir norādītais rindu skaits pirms pašreizējās. Norādot nulles vērtību, tiks atgriezta pašreizējā rinda. Atgrieztā rinda kļūst par pašreizējo rindu.
Lai atvērtu globālo kursoru, pirms tā nosaukuma ir jānorāda atslēgvārds GLOBĀLI. Kursora nosaukumu var norādīt arī, izmantojot mainīgo.
Izteiksmē INTO @mainīgā_nosaukums [,...n] ir definēts mainīgo lielumu saraksts, kurā tiks saglabātas atgrieztās rindas atbilstošās kolonnu vērtības. Mainīgo secībai ir jāatbilst kursora kolonnu secībai, un mainīgā datu tipam ir jāatbilst datu veidam kursora kolonnā.
Datu maiņa un dzēšana, izmantojot kursoru
Lai mainītu datus, izmantojot kursoru, jums ir jāizdod komanda UPDATE šādā formātā:
Vienā darbībā var mainīt vairāku pašreizējās kursora rindas kolonnu vērtības, taču tām visām ir jāiekļaujas vienā tabulā.
Lai dzēstu datus, izmantojot kursoru, izmantojiet komandu DELETE šādā formātā:
Rezultātā pašreizējā rinda kursorā tiks dzēsta.
Atbrīvojiet atmiņu, atbrīvojiet vietu
Lai noņemtu kursoru no atmiņas, izmantojiet komandu
Atcelt kursora_nosaukums;
@@FETCH_STATUS atribūts
Lai noteiktu rindu klātbūtni kursorā, jāizmanto globālais mainīgais @@FETCH_STATUS, kas iegūst vērtību, kas nav nulle, ja kursorā nav vairāk rindu. Ja rindu kopa vēl nav izsmelta, @@FETCH_STATUS ir vienāda ar nulli.
Kursora piemērs SQL serverī
Deklarēt @company varchar(50), @manager varchar(50), @message varchar(256); deklarēt crs_clients kursoru par vietējo izvēlētajam uzņēmumam, menedžerim no klientiem, kur pilsēta = "Maskava" pasūtījums pēc uzņēmuma, vadītāja; drukāt "Klientu saraksts"; atvērt crs_clients; ielādēt nākamo no crs_clients uz @company, @manager; while @@FETCH_STATUS = 0 begin select @message = "Uzņēmums " + @uzņēmums + " vadītājs " + @pārvaldnieks; drukāt @ziņa; -- pāriet uz nākamo ierakstu, ienest nākamo no crs_clients uz @company, @manager; beigas; aizvērt crs_clients; atdalīt crs_clients;
Ir dota kursora definīcija. Tiek sniegts tā veidu un uzvedības apraksts: statiskie, dinamiskie, secīgie un taustiņu kursori. Ir aprakstīti kursora vadības principi: kursora izveide un atvēršana, datu nolasīšana, kursora aizvēršana. Ir doti kursora programmēšanas piemēri.Kursora koncepcija
Relāciju datubāzes vaicājums parasti atgriež vairākas datu rindas (ierakstus), bet lietojumprogramma vienlaikus apstrādā tikai vienu ierakstu. Pat tad, ja vienlaikus tiek apstrādātas vairākas rindas (piemēram, datu attēlošana izklājlapu veidā), to skaits joprojām ir ierobežots. Turklāt, pārveidojot, dzēšot vai pievienojot datus, darba vienība ir sērija. Šajā situācijā kursora jēdziens izvirzās priekšplānā, un šajā kontekstā kursors ir rādītājs uz rindu.
Kursors SQL ir apgabals datu bāzes atmiņā, kas paredzēts pēdējā SQL priekšraksta glabāšanai. Ja pašreizējais priekšraksts ir datu bāzes vaicājums, atmiņā tiek saglabāta arī vaicājuma datu rinda, ko sauc par pašreizējo vērtību vai pašreizējo kursora rindiņu. Norādītais apgabals atmiņā ir nosaukts un ir pieejams lietojumprogrammām.
Parasti kursori tiek izmantoti, lai no datu bāzes atlasītu tajā saglabātās informācijas apakškopu. Lietojumprogramma jebkurā laikā var pārbaudīt vienu kursora rindiņu. Kursori bieži tiek izmantoti SQL priekšraksti, iebūvēts lietojumprogrammās, kas rakstītas procesuālajās valodās. Dažus no tiem netieši izveido datu bāzes serveris, bet citus definē programmētāji.
Saskaņā ar SQL standartu, strādājot ar kursoriem, var izdalīt šādas galvenās darbības:
- radīšana vai kursora deklarācija;
- atvēršanas kursors, t.i. aizpildot to ar datiem, kas tiek glabāti daudzlīmeņu atmiņā;
- atlase no kursora un mainot datu rindas ar to;
- aizverot kursoru, pēc kura tas kļūst nepieejams lietotāju programmām;
- kursora atbrīvošana, t.i. kursora kā objekta dzēšana, jo tā aizvēršana ne vienmēr atbrīvo ar to saistīto atmiņu.
Kursora definīcija dažādās implementācijās var nedaudz atšķirties. Piemēram, dažreiz izstrādātājam ir skaidri jāatbrīvo kursoram atvēlētā atmiņa. Pēc atlaidiet kursoru ar to saistītā atmiņa arī tiek atbrīvota. Tas ļauj atkārtoti izmantot viņa vārdu. Citās realizācijās, kad aizverot kursoru atmiņas atbrīvošana notiek netieši. Tūlīt pēc atveseļošanās tas kļūst pieejams citām operācijām: atverot citu kursoru utt.
Dažos gadījumos kursora izmantošana ir neizbēgama. Tomēr, ja iespējams, no tā vajadzētu izvairīties un strādāt ar standarta datu apstrādes komandām: SELECT, UPDATE, INSERT, DELETE. Papildus tam, ka kursori neļauj veikt modifikācijas operācijas uz visu datu apjomu, datu apstrādes operāciju veikšanas ātrums, izmantojot kursoru, ir ievērojami mazāks nekā standarta SQL rīkiem.
Kursoru ieviešana MS SQL Server vidē
SQL Server atbalsta trīs veidu kursorus:
- SQL kursori galvenokārt tiek izmantoti trigeros, saglabātajās procedūrās un skriptos;
- servera kursori darbojas uz servera un ievieš lietojumprogrammu programmēšanas saskarni ODBC, OLE DB, DB_Library;
- Klienta kursori tiek ieviesti uz paša klienta. Viņi ienes visu rezultātu rindu kopu no servera un saglabā to lokāli, kas paātrina datu apstrādi, samazinot izšķērdēto laiku, kas pavadīts tīkla darbībām.
Dažādiem vairāku lietotāju lietojumprogrammu veidiem ir nepieciešama dažāda veida paralēla piekļuve datiem. Dažām lietojumprogrammām nepieciešama tūlītēja piekļuve informācijai par izmaiņām datubāzē. Tas ir raksturīgi biļešu rezervēšanas sistēmām. Citos gadījumos, piemēram, statistikas ziņošanas sistēmās, datu stabilitāte ir svarīga, jo, ja tie tiek pastāvīgi pārveidoti, programmas nevarēs efektīvi attēlot informāciju. Dažādām lietojumprogrammām ir nepieciešamas dažādas kursoru ieviešanas.
Programmā SQL Server kursoru veidi atšķiras atkarībā no to nodrošinātajām iespējām. Kursora veids tiek noteikts tā izveides stadijā, un to nevar mainīt. Daži kursoru veidi var noteikt citu lietotāju veiktās izmaiņas rezultātu kopā iekļautajās rindās. Tomēr SQL Server tikai izseko izmaiņas šādās rindās, kamēr rindai tiek piekļūts, un neļauj mainīt izmaiņas, kad rinda jau ir nolasīta.
Kursori ir sadalīti divās kategorijās: secīgi un ritināms. Pēc kārtasļauj atlasīt datus tikai vienā virzienā – no sākuma līdz beigām. Ritināmi kursori nodrošināt lielāku rīcības brīvību - iespējams pārvietoties abos virzienos un pāriet uz patvaļīgu kursora rezultātu kopas rindu Ja programma spēj modificēt datus, uz kuriem norāda kursors, to sauc par ritināmu un modificējamu. Runājot par kursoriem, nevajadzētu aizmirst par darījumu izolāciju. Kad viens lietotājs modificē ierakstu, cits to nolasa, izmantojot savu kursoru, un turklāt viņš var modificēt to pašu ierakstu, kas rada nepieciešamību saglabāt datu integritāti.
SQL Server atbalsta statisku, dinamisku, secīgi un to kontrolē atslēgu komplekts.
Shēmā ar statiskais kursors informācija tiek nolasīta no datu bāzes vienreiz un saglabāta kā momentuzņēmums (noteiktā brīdī), tāpēc cita lietotāja veiktās izmaiņas datu bāzē nav redzamas. Kādu brīdi atverot kursoru serveris bloķē visas rindas, kas iekļautas tā pilnajā rezultātu kopā. Statiskais kursors nemainās pēc izveides un vienmēr parāda datu kopu, kas pastāvēja tās atvēršanas brīdī.
Ja citi lietotāji mainīs kursorā iekļautos datus avota tabulā, tas neietekmēs statiskais kursors.
IN statiskais kursors Nav iespējams veikt izmaiņas, tāpēc tas vienmēr tiek atvērts tikai lasīšanas režīmā.
Dinamiskais kursors uztur datus “dzīvā” stāvoklī, taču tam ir nepieciešami tīkla un programmatūras resursi. Izmantojot dinamiskie kursori netiek izveidota pilnīga avota datu kopija, bet dinamiska atlase no avota tabulām tiek veikta tikai tad, kad lietotājs piekļūst noteiktiem datiem. Ieneses laikā serveris bloķē rindas, un visas lietotāja veiktās izmaiņas kursora rezultātu komplektā būs redzamas kursorā. Tomēr, ja cits lietotājs ir veicis izmaiņas pēc tam, kad kursors ir ieguvis datus, tās netiks atspoguļotas kursorā.
Kursoru kontrolē taustiņu komplekts, atrodas pa vidu starp šīm galējībām. Ieraksti tiek identificēti izlases laikā, un tādējādi izmaiņas tiek izsekotas. Šāda veida kursors ir noderīgs, ieviešot ritināšanu atpakaļ – tad rindu papildinājumi un dzēšana nav redzami, kamēr informācija nav atjaunināta, un draiveris izvēlas jaunu ieraksta versiju, ja tajā ir veiktas izmaiņas.
Secīgi kursori nav atļauts ienest datus pretējā virzienā. Lietotājs var atlasīt tikai rindas no kursora sākuma līdz beigām. Sērijas kursors nesaglabā visu rindu kopu. Tie tiek nolasīti no datu bāzes, tiklīdz tie ir atlasīti kursorā, kas ļauj dinamiski atspoguļot visas lietotāju veiktās izmaiņas datu bāzē, izmantojot komandas INSERT, UPDATE, DELETE. Kursors parāda jaunāko datu stāvokli.
Statiskie kursori nodrošināt stabilu datu skatījumu. Tie ir piemēroti informācijas "noliktavu" sistēmām: lietojumprogrammām ziņošanas sistēmām vai statistikas un analītiskiem nolūkiem. Turklāt, statiskais kursors labāk nekā citi tiek galā ar liela datu apjoma iztveršanu. Turpretim elektroniskās pirkšanas vai biļešu rezervēšanas sistēmām nepieciešama dinamiska atjauninātās informācijas uztvere, kad tiek veiktas izmaiņas. Šādos gadījumos to izmanto dinamiskais kursors. Šajās lietojumprogrammās pārsūtīto datu apjoms parasti ir neliels, un tiem var piekļūt rindas (atsevišķa ieraksta) līmenī. Grupas piekļuve ir ļoti reta.
Kursora vadība MS SQL Server vidē
Kursora vadība tiek ieviests, izpildot šādas komandas:
- DEKLARĒT - radīšana vai kursora deklarācija;
- ATVĒRTS – atvēršanas kursors, t.i. aizpildot to ar datiem;
- IEŅEMT atlase no kursora un mainot datu rindas, izmantojot kursoru;
- AIZVĒRT - aizverot kursoru;
- ATDALĪT — kursora atbrīvošana, t.i. kursora kā objekta dzēšana.
Kursora deklarācija
SQL standarts nodrošina šādu komandu, lai izveidotu kursoru:
Izmantojot atslēgvārdu INSENSITIVE, tiks izveidots statiskais kursors. Datu izmaiņas nav atļauti, turklāt netiek rādītas citu lietotāju veiktās izmaiņas. Ja trūkst atslēgvārda INSENSITIVE, a dinamiskais kursors.
Norādot atslēgvārdu SCROLL, izveidoto kursoru var ritināt jebkurā virzienā, ļaujot izmantot jebkuras atlases komandas. Ja šis arguments tiek izlaists, kursors būs konsekventi, t.i. tā apskate būs iespējama tikai vienā virzienā – no sākuma līdz beigām.
SELECT priekšraksts norāda SELECT pieprasījuma pamattekstu, kas nosaka iegūto rindu kopu kursoram.
Norādot FOR READ_ONLY, tiek izveidots tikai lasāms kursors un netiek atļautas nekādas izmaiņas datos. Tas atšķiras no statiskā, lai gan pēdējais arī neļauj mainīt datus. Var deklarēt kā tikai lasāmu kursoru dinamiskais kursors, kas ļaus parādīt cita lietotāja veiktās izmaiņas.
Kursora izveide ar argumentu FOR UPDATE ļauj izpildīt kursorā datu maiņa vai nu norādītajās kolonnās, vai, ja nav argumenta OF kolonnas_nosaukums, visās kolonnās.
MS SQL Server vidē kursora izveides komandai tiek pieņemta šāda sintakse:
<создание_курсора>::= DEKLARĒT kursora_nosaukumu CURSOR FOR SELECT_paziņojums ]]
Izmantojot atslēgvārdu LOCAL, tiks izveidots lokālais kursors, kas ir redzams tikai pakotnes, aktivizētāja, saglabātās procedūras vai lietotāja definētas funkcijas ietvaros, kas to izveidoja. Kad pakotne, aktivizētājs, procedūra vai funkcija tiek pārtraukta, kursors tiek netieši iznīcināts. Lai nodotu kursora saturu ārpus konstrukcijas, kas to izveidoja, tā parametram ir jāpiešķir arguments OUTPUT.
Ja ir norādīts GLOBAL atslēgvārds, tiek izveidots globālais kursors; tas pastāv, līdz pašreizējais savienojums tiek aizvērts.
Norādot FORWARD_ONLY izveido sērijas kursors; Datus var ņemt paraugus tikai virzienā no pirmās rindas līdz pēdējai.
Norādot SCROLL, tiek izveidots ritināms kursors; Datiem var piekļūt jebkurā secībā un jebkurā virzienā.
Norādot STATIC, tiek izveidots statiskais kursors.
Norādot KEYSET, tiek izveidots taustiņa kursors.
Norādot DYNAMIC, tiek izveidots dinamiskais kursors.
Ja norādīsiet argumentu FAST_FORWARD kursoram READ_ONLY, izveidotais kursors tiks optimizēts ātrai datu piekļuvei. Šo argumentu nevar izmantot kopā ar FORWARD_ONLY vai OPTIMISTIC argumentiem.
Kursors, kas izveidots ar OPTIMISTIC argumentu, neļauj mainīt vai dzēst rindas, kas tika modificētas pēc atverot kursoru.
Norādot argumentu TYPE_WARNING, serveris informēs lietotāju par netiešām kursora veida izmaiņām, ja tas nav saderīgs ar SELECT vaicājumu.
Kursora atvēršana
Priekš atverot kursoru un aizpildot to ar datiem no SELECT vaicājuma, kas norādīts, veidojot kursoru, izmantojiet šādu komandu:
Pēc atverot kursoru Tiek izpildīts saistītais SELECT priekšraksts, kura izvade tiek saglabāta daudzlīmeņu atmiņā.
Datu izgūšana no kursora
Uzreiz pēc atverot kursoru varat atlasīt tā saturu (atbilstošā vaicājuma izpildes rezultātu), izmantojot šādu komandu:
Norādot FIRST, tiks atgriezta kursora pilnās rezultātu kopas pirmā rinda, kas kļūst par pašreizējo rindu.
Norādot LAST, tiek atgriezta kursora jaunākā rinda. Tā arī kļūst par pašreizējo līniju.
Norādot NEXT, tiek atgriezta rinda uzreiz pēc pašreizējās pilnā rezultātu kopā. Tagad tas kļūst aktuāls. Pēc noklusējuma komanda FETCH izmanto šo metodi rindu ienešanai.
Atslēgvārds PRIOR atgriež rindu pirms pašreizējās. Tas kļūst aktuāls.
Arguments ABSOLUTE (rindas_numurs | @line_number_mainīgs) atgriež rindu pēc tās absolūtā kārtas numura kursora pilnajā rezultātu kopā. Rindas numuru var norādīt, izmantojot konstanti vai kā mainīgā lieluma nosaukumu, kurā tiek saglabāts rindas numurs. Mainīgajam ir jābūt vesela skaitļa datu tipam. Ir norādītas gan pozitīvas, gan negatīvas vērtības. Norādot pozitīvu vērtību, virkne tiek skaitīta no kopas sākuma, bet negatīvā vērtība tiek skaitīta no beigām. Atlasītā līnija kļūst par pašreizējo līniju. Ja ir norādīta nulles vērtība, neviena rinda netiek atgriezta.
Arguments RELATIVE (rindu skaits | @mainīgs rindu skaits) atgriež rindu, kas ir norādītais rindu skaits pēc pašreizējās. Ja norādāt negatīvu rindu skaitu, tiks atgriezta rinda, kas ir norādītais rindu skaits pirms pašreizējās. Norādot nulles vērtību, tiks atgriezta pašreizējā rinda. Atgrieztā rinda kļūst par pašreizējo rindu.
Uz atvērt globālo kursoru, pirms tā nosaukuma ir jānorāda GLOBAL atslēgvārds. Kursora nosaukumu var norādīt arī, izmantojot mainīgo.
Dizainā INTO @mainīgā_nosaukums [,...n] ir norādīts mainīgo lielumu saraksts, kurā tiks saglabātas atgrieztās rindas atbilstošās kolonnas vērtības. Mainīgo norādīšanas secībai ir jāatbilst kursora kolonnu secībai, un mainīgā datu tipam ir jāatbilst datu veidam kursora kolonnā. Ja INTO konstrukcija nav norādīta, tad komandas FETCH darbība līdzinās komandas SELECT uzvedībai - dati tiek parādīti ekrānā.
Datu maiņa un dzēšana
Lai veiktu izmaiņas, izmantojot kursoru, jums ir jāizdod komanda UPDATE šādā formātā:
Ar vienu darbību var mainīt vairākas pašreizējās kursora rindas kolonnas, taču tām visām ir jāatrodas vienā tabulā.
Lai dzēstu datus, izmantojot kursoru, izmantojiet komandu DELETE šādā formātā:
Rezultātā līnijas iestatītā strāva kursorā tiks dzēsta.
Kursora aizvēršana
Pēc aizvēršanas kursors programmas lietotājiem kļūst nepieejams. Kad tas ir aizvērts, tiek noņemtas visas tās darbības laikā uzstādītās slēdzenes. Slēgšanu var piemērot tikai atvērtiem kursoriem. Slēgts, bet ne atbrīvots kursors var tikt atvērts atkārtoti. Nav atļauts aizvērt neatvērtu kursoru.
Atlaidiet kursoru
Kursora aizvēršana ne vienmēr atbrīvo ar to saistīto atmiņu. Dažām implementācijām tas ir skaidri jāatdala, izmantojot priekšrakstu DEALLOCATE. Pēc atlaidiet kursoru Tiek atbrīvota arī atmiņa, kas ļauj atkārtoti izmantot kursora nosaukumu.
Lai kontrolētu, vai ir sasniegts kursora beigas, ieteicams izmantot funkciju: @@FETCH_STATUS
Funkcija @@FETCH_STATUS atgriež:
0, ja ielāde bija veiksmīga;
1, ja ielāde neizdevās, jo tika mēģināts ienest līniju ārpus kursora;
2, ja ielāde neizdevās, jo mēģināts piekļūt dzēstai vai pārveidotai rindai.
DEKLARĒT @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Iepirkumu saraksts" DEKLARĀT klient_cursor CURSOR LOCAL FOR SELECT Klienta kods, uzņēmums, uzvārds FROM Client WHERE City="Maskava" PASŪTĪJUMS PĒC uzņēmuma, uzvārds OPEN klient_cursor IEŅEMT TĀLĀK NO klient_kursora UZ @id_kl, @firm, @fam CH_STATUS@FEM =0 BEGIN SELECT @message="Klients "+@fam+ "Uzņēmums "+ @firm PRINT @message SELECT @message="Produkta nosaukums Pirkuma datums Maksa" DRUKĀT @ziņojums DEKLARĀT tovar_cursor KURSORS ATLASĪTIES Produktam.Nosaukums, darījums.Datums, produkts .Cena* Darījums. Daudzums KĀ izmaksas NO produkta IEKŠĒJS PIEVIENOJIES Darījums PAR produktu. Produkta kods=Transaction.Product Code WHERE Transaction.Customer Code=@id_kl OPEN tovar_cursor FETCH NEXT FROM FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 DRUKĀT "Nav pirkumu", KAmēr @@FETCH_STATUS=0 SĀKT ATLASĪT @ziņa=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) DRUKĀT @message SET @s=@s+@p IEŅEMT TĀLĀK NO tovar_cursor UZ @nam, @d, @p BEIGAS AIZVĒRT tovar_cursor ATTEIKT tovar_cursor SELECT @message="Kopējās izmaksas "+ CAST(@s AS CHAR(6)) PRINT @message -- pāriet uz nākamo klientu -- IEŅEMT TĀLĀK NO klient_cursor UZ @id_kl, @firm, @fam END AIZVĒRT klient_cursor ATTEIKT klient_kursoru Piemērs 13.6. Kursors, lai parādītu Maskavas klientu iegādāto preču sarakstu un to kopējās izmaksas.
Piemērs 13.7. Izstrādājiet ritināmu kursoru klientiem no Maskavas. Ja tālruņa numurs sākas ar 1, izdzēsiet klientu ar šo numuru un pirmajā kursora ierakstā aizstājiet tālruņa numura pirmo ciparu ar 4.
DEKLARĀT @firma VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @ziņa VARCHAR(80) DRUKĀT "Klientu sarakstu" DEKLARĒT klient_cursor KURSORA GLOBĀLĀ RITINĀŠANAS KEYSET IZVĒLĒTIES Firmai, Uzvārds, Tālrunis FROM Client KUR Pilsēta ="Maskava" PASŪTĪJUMS PĒC uzņēmuma, uzvārds ATJAUNINĀŠANAI ATVĒRT klient_cursor IEŅEMT TĀLĀK NO klient_kursors UZ @firm, @fam, @tel WHILE @@FETCH_STATUS=0 SĀKT ATLASĪT @message="Klients "+@fam+ " Uzņēmums "+ @firm " Phone "+ @tel PRINT @message -- ja tālruņa numurs sākas ar 1, -- izdzēsiet klientu ar šo numuru IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- pāriet uz nākamo klients IEŅEMT TĀLĀK NO klient_kursora UZ @firm, @fam, @tel BEIGĀS IEŅEMT ABSOLŪTI 1 NO klient_cursor UZ @firm, @fam, @tel -- pirmajā ierakstā tālruņa numura pirmo ciparu nomainiet ar 4 UPDATE Client SET Phone ='4' + LABAIS(@ tel,LEN(@tel)-1)) KUR PAŠREIZĒJĀS klient_kursors IZVĒLĒTIES @ziņa="Klients "+@fam+" Firma "+ @firma "Tālrunis"+ @tel DRUKĀT @ziņa AIZVĒRT klient_kursoru ATTIECĪT klienta_kursoru Piemērs 13.7. Ritināms kursors klientiem no Maskavas.
Piemērs 13.8. Lietošana kursors kā procedūras izejas parametrs. Procedūra atgriež datu kopu - produktu sarakstu.
Procedūras izsaukšana un datu drukāšana no izvades kursora tiek veikta šādi:
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur IZRĀDE IEŅEMT TĀLĀK NO @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) SĀKT IEŅEMT TĀLĀK NO @my_cur @n BEIGAS AIZVĒRT @my_cur ATDALĪT @my_cur
