Користење на курсори и јамки во Transact-SQL. Курсори во MySQL складирани процедури Курсори во sql сервер
Командата DECLARE CURSOR ви овозможува да ги вратите записите од табелата ред по ред за манипулација. Ова овозможува обработка ред-по-ред наместо традиционалната обработка на податоци што ја прави SQL.
Како прво приближување, при работа со курсорот се користат следните чекори.
Покажувачот се креира со командата DECLARE. Покажувачот се отвора со командата OPEN.
Операциите со курсорот се вршат со помош на командата FETCH. Покажувачот се затвора со командата CLOSE.
Командата DECLARE CURSOR одредува изјава SELECT. Секој ред вратен со изјавата SELECT може да се превземе и обработи поединечно. Следниот пример на Oracle декларира курсор во блок за декларација заедно со неколку други променливи. По ова, во наредниот блок BEGIN...END, курсорот се отвора, од него се избира и курсорот се затвора.
CURSOR title_price_cursor IS SELECT наслов, цена ОД наслови
КАДЕ што цената НЕ Е НИШТА; title_price_val title_price_cursor ROWTYPE; нова_цена БРОЈ (10,2);
ОТВОРИ наслов_цена_курсор;
ВНИМАВАЈ наслов_цена_кур-сор ВО наслов_цена_вал;
new_price:= "title_price_val.price" * 1.25 Вметнете ВО new_title_price VALUES
(наслов_цена_вал.наслов, нова_цена) ЗАТВОРИ наслов_цена_курсор; КРАЈ;
Бидејќи овој пример користи PL/SQL, нема да објасниме многу од кодот во оваа книга. Сепак, блокот DECLARE јасно ја покажува декларацијата на курсорот. Во извршниот блок PL/SQL, курсорот се иницијализира со командата OPEN, вредностите се преземаат со командата FETCH и на крајот курсорот се затвора со командата CLOSE.
Изјавата SELECT е основата на курсорот, па затоа е добра практика да се тестира темелно пред да се вклучи во изјавата DECLARE CURSOR. Изјавата SELECT може да работи на основната табела или приказ. Затоа, курсорите само за читање можат да работат со прикази кои не се ажурираат. Изјавата SELECT може да содржи клаузули како ORDER BY, GROUP BY и HAVING се додека овие клаузули не ја ажурираат изворната табела. Ако курсорот е дефиниран како ЗА АЖУРИРАЊЕ, тогаш се препорачува да се отстранат таквите клаузули од изјавата SELECT.
Локалните курсори често се користат како излезни параметри на складираните процедури. Затоа, можете да го дефинирате и наполните курсорот во зачувана процедура и да го пренесете на сериската работа или зачуваната процедура.
Во следниот едноставен пример DB2, ќе објавиме курсор кој ги бара броевите на одделите, имињата на одделите и броевите на менаџерите во администраторската група „XO1“.
ИЗЈАВАЈ ДЕТ_курсор КУРСОР
ЗА ИЗБЕРИ dept_nbr, dept_name, mgr_nbr
WHERE admin_group="X01"
НАРАЧАЈ ПО d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;
Следниот пример на Microsoft SQL Server декларира и отвора курсор за табелата на издавачи. Покажувачот го избира првиот запис што одговара на изјавата SELECT од табелата на издавачи и го вметнува во друга табела. Потоа преминува на следниот запис, па на следниот, додека не се обработат сите записи. Конечно, курсорот се затвора и меморијата се ослободува (командата DEALLOCATE се користи само во Microsoft SQL Server).
ИЗЈАВИ @publisher_name VARCHAR(20)
ИЗБЕРЕТЕ pub_cursor КУРСОР ЗА ИЗБЕРЕТЕ pub_name ОД издавачи WHERE земја „САД“
ДОНЕСИ ГО СЛЕДНОТО ОД pub_cursor ВО Името на издавачот
ДОДЕКА @s>FETCH_STATUS=0
INSERT INTO Foreign_publishers VALUES("j>издавач_име)
ЗАТВОРИ pub_cursor DEALLOCATE pub_cursor
Во овој пример, можете да го видите курсорот како се движи низ збир на записи. (Овој пример има за цел само да ја демонстрира идејата, бидејќи во реалноста постои Најдобриот начинрешение за овој проблем, имено инструкцијата INSERT, SELECT.)
ТОА ВАЖИ НА: SQL Server (од 2008 година) База SQL податоци Azure SQL Data Warehouse Паралелно складиште на податоци
Ги дефинира атрибутите на курсорот на серверот Transact-SQL, како што се својствата на погледот и барањето што се користи за да се изгради множеството резултати на кои работи курсорот. Изјавата DECLARE CURSOR поддржува и ISO стандард синтакса и синтакса што го користи множеството наставки на јазици Transact-SQL.
ISO синтакса ПРИЈАВИ ИМЕ на курсорот [ НЕСЕНЗИТИВЕН ] [ СКРОЛАЈ ] КУРСОР ЗА select_statement [ ЗА (САМО ЗА ЧИТАЊЕ | АЖУРИРАЊЕ [ НА ИМЕ_КОЛОНА [ ,...n ] ] ] ] [;] Проширена синтакса на Transact-SQL ПРИЈАВИ ИМЕ на курсорот КУРСОР [ ЛОКАЛНИ | ГЛОБАЛНО ] [ САМО НАПРЕД | ДВИЖЕЊЕ ] [ СТАТИЧКИ | КЛУЧОК | ДИНАМИЧНО | FAST_FORWARD ] [ САМО ЗА РЕАД | SCROLL_LOCKS | ОПТИМИСТИЧКИ ] [ ТИП_ПРЕДУПРЕДУВАЊЕ ] ЗА изберете_изјава [ ЗА АЖУРИРАЊЕ [ НА ИМЕ_КОЛОНА [ ,...n ] ] ] [;]
име на курсорот
име на курсорот
НЕСЕНЗИТИВЕН
tempdb; така, промените во основните табели не се рефлектираат во податоците вратени од селекциите на овој курсор и овој курсор не е променлив. Кога користите ISO синтакса, освен ако не е наведена опцијата НЕСЕНЗИТИВНО, извршените ажурирања и бришења направени во основните табели се појавуваат во следните селекции.
СКРОЛАЈ
Укажува дека се достапни сите опции за земање примероци (ПРВА, ПОСЛЕДНА, ПРЕТХОДНА, СЛЕДНА, РЕЛАТИВНА, АПСОЛУТНА). Ако изјавата ISO DECLARE CURSOR не наведе опција SCROLL, поддржана е само опцијата NEXT fetch. Опцијата SCROLL не може да се назначи со опцијата FAST_FORWARD.
изберете_изјава
Стандардна изјава SELECT која го одредува резултатот сет на курсорот. Не се дозволени клучните зборови ЗА ПРЕЛИЗИРАЈ и ВО изберете_изјавадекларација на курсорот.
изберете_изјаваконфликт со курсорот од бараниот тип.
САМО ЧИТАЈ
Ажурирање ]
колона_име [,
.. .н] е наведено, само наведените колони дозволуваат промени. Ако изјавата UPDATE се користи без список на колони, тогаш ажурирањето е можно за сите колони.
име на курсорот
Името Transact-SQL на специфичниот курсор на серверот. име на курсоротмора да ги следи правилата за идентификатори.
ЛОКАЛНИ
Покажува дека курсорот е локален на пакетот, зачуваната процедура или активирањето во кое е креиран. Името на курсорот важи само во оваа област. Покажувачот може да се референцира со локални променливи на пакети, складирани процедури, предизвикувачи или излезниот параметар на складирана процедура. Параметарот OUTPUT се користи за пренесување на локален курсор до повикувачкиот пакет, складирана процедура или активирач, кој потоа може да го додели параметарот на променливата на курсорот за последователен пристап до курсорот откако ќе заврши зачуваната процедура. Покажувачот имплицитно се ослободува кога серијата, складираната процедура или активирањето ќе го завршат извршувањето, освен ако курсорот не е предаден на параметарот OUTPUT. Ако курсорот е предаден на параметарот OUTPUT, курсорот се ослободува кога ќе се ослободат сите променливи што упатуваат на него или кога ќе се излезе од опсегот.
ГЛОБАЛЕН
Покажува дека курсорот е глобален за врската. Името на курсорот може да се користи од која било зачувана процедура или пакет што работи на врската. Покажувачот имплицитно се ослободува само ако врската е прекината.
НАПРЕД_САМО
Назначува дека курсорот може да се гледа само од првата до последната линија. Поддржана е само опцијата за преземање FETCH NEXT. Ако FORWARD_ONLY е наведен без клучни зборови STATIC, KEYSET или DYNAMIC, курсорот се однесува како DYNAMIC курсор. Ако не е наведен ниту аргументот FORWARD_ONLY ниту аргументот SCROLL, стандардниот е аргументот FORWARD_ONLY освен ако не се присутни клучните зборови STATIC, KEYSET или DYNAMIC. Покажувачите STATIC, KEYSET и DYNAMIC имаат стандардна вредност SCROLL. За разлика од API-те на базата на податоци како што се ODBC и ADO, режимот FORWARD_ONLY е поддржан од следните курсори Transact-SQL: STATIC, KEYSET и DYNAMIC.
СТАТИЧНИ
Дефинира курсор кој создава привремена копија на податоци за употреба од курсорот. Сите барања до курсорот пристапуваат до наведената привремена табела во tempdb; така, промените во основните табели не се рефлектираат во податоците вратени од селекциите на овој курсор и овој курсор не е променлив.
КЛУЧОК
Укажува дека членството или редоследот на редовите во курсорот е непроменет кога ќе се отвори. Збир на клучеви кои уникатно ги идентификуваат редовите се вградени во табелата tempdbповикани клучеви.
Промените на вредностите што не се клучни во основните табели направени од сопственикот на курсорот или извршени од други корисници се прикажуваат кога сопственикот на курсорот ќе го види. Промените направени од други корисници не се рефлектираат (промените не можат да се направат со помош на курсорот на серверот Transact-SQL). Ако некој ред е избришан, обидот за преземање редови враќа @@FETCH_STATUS -2. Ажурирањата на клучните вредности преку границите на курсорот се слични на бришење стар ред и потоа вметнување нов ред. Редот со новите вредности не е видлив и се обидува да го преземе редот со старите вредности, враќа @@FETCH_STATUS -2. Ажурирањата се видливи веднаш ако се направени преку курсорот користејќи ја клаузулата WHERE CURRENT OF.
ДИНАМИЧНО
Дефинира курсор кој ги прикажува сите промени на податоците направени во редовите во комплетот со резултати додека го гледате овој курсор. Вредностите на податоците, редоследот и членството во редови во секој избор може да се разликуваат. Опцијата АПСОЛУТЕН избор не е поддржана од динамички курсори.
БРЗО_НАПРЕД
Покажува курсор FORWARD_ONLY, READ_ONLY кој има овозможена оптимизација на перформансите. Опцијата FAST_FORWARD не може да се одреди со опциите SCROLL или FOR_UPDATE.
САМО ЧИТАЈ
Спречува промени направени преку овој курсор. Клаузулата WHERE CURRENT OF не може да упатува на курсорот во изјавата АЖУРИРАЈ или БРИШИ. Оваа опција има приоритет над стандардната функција за освежување на курсорот.
SCROLL_LOCKS
Покажува дека позиционираните ажурирања или бришења направени со помош на курсорот гарантирано ќе успеат. SQL Server ги заклучува редовите додека се читаат во курсорот за да се осигура дека тие редови се достапни за последователни промени. Опцијата SCROLL_LOCKS не може да се одреди со опцијата FAST_FORWARD или STATIC.
ОПТИМИСТИЧНИ
Одредува дека позиционираните ажурирања или бришења направени со помош на курсорот нема да успеат ако редот е ажуриран откако е прочитан во курсорот. SQL Server не ги заклучува редовите додека се читаат во курсорот. Наместо тоа, се користат споредби временски печатвредности на колони или контролни суми, ако не во табелата временски печатколона за да се утврди дали редот е променет откако е прочитан во курсорот. Ако редот е изменет, тогаш обидите за позиционирано ажурирање или бришење нема да успеат. Опцијата OPTIMISTIC не може да се назначи со опцијата FAST_FORWARD.
TYPE_WARNING
Назначува дека ќе биде испратено предупредување до клиентот доколку курсорот имплицитно се конвертира од еден баран тип во друг.
изберете_изјава
Стандардна изјава SELECT која го одредува резултатот сет на курсорот. Клучните зборови COMPUTE, COMPUTE BY, FOR Browse и INTO не се дозволени во изберете_изјавадекларација на курсорот.
SQL Server имплицитно го конвертира курсорот во друг тип ако клаузулите се вклучени изберете_изјаваконфликт со курсорот од бараниот тип. За повеќе информации, видете Имплицитни конверзии на курсорот.
ЗА АЖУРИРАЊЕ]
Ги дефинира колоните во курсорот што треба да се ажурираат. Ако НА колона_име [,
... n] е обезбедено, само наведените колони дозволуваат промени. Ако изјавата UPDATE се користи без список со колони, тогаш ажурирањето е можно за сите колони, освен ако не е наведена опцијата за истовременост READ_ONLY.
Изјавата DECLARE CURSOR ги дефинира атрибутите на курсорот на серверот Transact-SQL, како што се својствата на погледот и барањето што се користи за да се изгради множеството резултати на кои работи курсорот. Изјавата OPEN го пополнува множеството резултати, а изјавата FETCH враќа ред од него. Изјавата CLOSE го брише тековниот сет на резултати поврзан со курсорот. Изјавата DEALLOCATE ги ослободува ресурсите што ги користи курсорот.
Првата форма на изјавата DECLARE CURSOR користи ISO синтакса за да ги специфицира параметрите на курсорот. Втората форма на изјавата DECLARE CURSOR користи екстензии на јазикот Transact-SQL кои ви дозволуваат да ги дефинирате курсорите користејќи ги истите типови како оние што се користат во функциите на курсорот на API-ите на базата на податоци како што се ODBC и ADO.
Овие две форми не можат да се мешаат. Ако наведете SCROLL или испуштате клучни зборови пред клучниот збор CURSOR, не можете да користите клучни зборови помеѓу CURSOR, а исто така и за изберете_изјаваклучни зборови. При одредување на клучни зборови помеѓу КУРСОР, како и за изберете_изјаваклучни зборови, не можете да наведете SCROLL или INSENSITIVE пред клучниот збор CURSOR.
Ако ја користите синтаксата Transact-SQL за изјавата DECLARE CURSOR и не ги наведете опциите READ_ONLY, OPTIMISTIC или SCROLL_LOCKS, се претпоставува следната стандардна вредност.
Ако изјавата SELECT не поддржува ажурирања (или има недоволни дозволи или пристапува до оддалечени табели што не поддржуваат ажурирања итн.), курсорот е поставен на READ_ONLY.
Покажувачите STATIC и FAST_FORWARD стандардно се READ_ONLY.
Покажувачите DYNAMIC и KEYSET стандардно се OPTIMISTIC.
Покажувачите може да се референцираат само со други извештаи на Transact-SQL. Функциите на API на базата на податоци не можат да упатуваат на курсори. На пример, откако ќе се декларира курсорот, функциите и методите на OLE DB, ODBC или ADO не можат да се однесуваат на неговото име. Редовите на курсорот не можат да се изберат со користење на соодветните функции и методи на API; За таа цел, мора да користите Transact-SQL FETCH изјави.
Следниве складирани процедури може да се користат за дефинирање на својствата на курсорот откако ќе се декларира.
Променливите може да се користат како дел изберете_изјава, во која е деклариран курсорот. Вредностите на променливите на курсорот не се менуваат откако ќе се декларира.
Стандардно, дозволите за DECLARE CURSOR им се доделуваат на сите корисници кои имаат дозвола SELECT за прегледите, табелите и колоните што ги користи курсорот.
Не можете да користите курсори или предизвикувачи на табела со индекс на кластерирана продавница за колони. Ова ограничување не се однесува на некластерирани индекси; Можете да користите курсори и активирања на табела со негрупен индекс на продавница за колони.
A. Користење на едноставен курсор и синтакса
Збирот на резултати создаден кога ќе го отворите курсорот ги вклучува сите редови и колони од табелата. Овој курсор може да се ажурира, сите ажурирања и бришења се претставени во изборот за овој курсор. FETCH``NEXT се зема само затоа што параметарот SCROLL не беше наведен.
ОБЈАВИ vend_курсор КУРСОР ЗА ИЗБЕР * ОД Купување.Продавач ОТВОРЕТЕ Vend_cursor ВНИМАЈ СЛЕДНО ОД vend_cursor;
Б. Користење на вгнездени курсори за прикажување извештај
Следниот пример користи вгнездени курсори за прикажување сложен извештај. За секој провајдер се декларира внатрешен курсор.
ПОСТАВЕТЕ ВКЛУЧЕНО NO COUNT ;
ИЗЈАВИ @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 );ПЕЧЕТИ" -------- Извештај за производи од продавач --------";
ИЗЈАВИ vendor_cursor КУРСОР ЗА ИЗБЕРЕН ИД на добавувач, Име од купување. Добавувач КАДЕ PreferredVendorStatus = 1 НАРАЧКА ПО ИД на продавач;ОТВОРИ го vendor_cursor ДОНЕСИ СЛЕДНО ОД vendor_cursor ВО @vendor_id, @vendor_name ДОДЕКА @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Производи од продавачот:"+ @vendor_name ПЕЧЕТИ @порака -- Декларирајте внатрешен курсор базиран
-- на vendor_id од надворешниот курсор.ИЗБЕРЕТЕ производ_курсор КУРСОР ЗА ИЗБОР В.Име ОД Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID И pv.VendorID = @vendor_id -- Променлива вредност од надворешниот курсорОТВОРИ го производ_курсорот ВЛЕДИ СЛЕДНО ОД производ_курсор ВО @product IF @@FETCH_STATUS<>0 ПЕЧАТЕЊЕ"<
Курсорот е врска до областа за контекстуална меморија. Во некои имплементации на програмскиот јазик SQL (Oracle, Microsoft SQL Server) - множеството резултати добиени при извршување на барањето и покажувачот на тековниот рекорд поврзан со него. Би рекол дека курсорот е виртуелна табела што претставува алтернативно складирање на податоци. Во овој случај, курсорот ви овозможува да пристапите до неговите податоци како да се податоци од обична низа.
Покажувачите се користат во складирани процедури. Доста теорија, ајде да погледнеме на пример:
Имаме база на податоци (базата на податоци е малку не добра, ова е една од моите лабораториска работа, но нашиот наставник за базата на податоци инсистираше на таква структура)
/*банкарски информации*/
КРЕИРАЈ ТАБЕЛА „банка“ (
`Име на банка` VARCHAR (50) СОСТАВУВА utf8_bin НЕ НУЛЛО ПОСТАНДНО "" ,
ПРИМАРЕН КЛУЧ (`BankId`))ENGINE=InnoDB
МОТ КАРАКТЕРИ "utf8" КОЛАТИРАЈ "utf8_bin" ;
/*податоци за депозитите */
КРЕИРАЈ ТАБЕЛА `банкарска дистрибуција` (
„BankId“ ЦЕЛ БЕГ (11) НЕ NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` ДЕЦИМАЛНИ (10,0) НЕ НУЛЛО ,
`ClientId` ЦЕЛ БЕГ (11) НЕ NULL ,
ПРИМАРЕН КЛУЧ (`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
ОГРАНИЧУВАЊЕ `bankdistribution_fk` СТРАНСКИ КЛУЧ (`BankId`) РЕФЕРЕНЦИ „bank“ (`BankId`),
ОГРАНИЧУВАЊЕ `bankdistribution_fk1` СТРАНСКИ КЛУЧ (`ClientId`) РЕФЕРЕНЦИ „клиент“ (`ClientId`)
)ENGINE=InnoDB
/*податоци за инвеститори*/
КРЕИРАЈ ТАБЕЛА „клиент“ (
`ClientId` INTEGER (3) НЕ NULL AUTO_INCREMENT,
`Credit CardId` BIGINT(10) НЕ NULL,
`Презиме` VARCHAR (50) СОСТАВУВА utf8_bin НЕ НУЛЛО ПОСТАВЕНО "" ,
`Име` VARCHAR (50) СОСТАВУВА utf8_bin НЕ НУЛЛО ПОСТАВЕНО "" ,
`FirstName` VARCHAR (50) СОБИРАЈТЕ utf8_bin НЕ НУЛЛО ПОСТАВЕНО "" ,
`Телефон` VARCHAR (50) СОСТАВУВА utf8_bin НЕ НУЛЛ ПОСТАНДЕН "" ,
`Адреса` VARCHAR (50) КОЛАТИРАЈ utf8_bin НЕ НУЛЛ ПОСТАНДЕН "" ,
`SafeId` ЦЕЛ БЕГ (5) НЕ NULL ,
ПРИМАРЕН КЛУЧ (`КлиентИд`, `Ид на кредитна картичка`),
KEY `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 КОМПЛЕТ НА КАРАКТЕРИ „utf8“ КОЛАТИРАЈ „utf8_bin“
Да речеме дека треба да ја примиме секоја банка по ред и да извршиме некои активности со неа, следното барање може да ни помогне со ова
Изберете `банка`.* ОД `банка` ГРАНИЧЕН БРОЈ НА КОИ НИ Е ПОТРЕБЕН_РЕКОРД,1. Така, користејќи LIMIT WE NEED_RECORD NUMBER, 1, го извлекуваме секој запис во јамка од табелата на банката и ги извршуваме дејствата што ни се потребни со неа, додека ја зголемуваме вредноста на WE NEED_RECORD NUMBER за 1. Сега ќе го направиме истото, но со користење на курсорот
Започнете
/* променливи каде што извлекуваме податоци */
Декларирајте vBankId цел број ;
Изјави vBankName VARCHAR(50);
Декларирајте vAddress VARCHAR(50);
Изјави vPhone VARCHAR (50);
/* hadler променлива - a*/
Прогласи завршено цел број стандардно 0;
/*Декларација на курсорот*/
Декларирајте го курсорот на банкарскиот курсор за Изберете `банка`.`Име на банка`,`банка`.`Име на банка`,`банка`.`Адреса`,`банка`.`Телефон, ОД `банка` каде 1;
/*Целта на HANDLER, која ќе биде објаснета подолу*/
ИЗЈАВИ ПРОДОЛЖУВАЊЕ РАКУВАЧ ЗА SQLSTATE "02000" SET done=1;
/* отвори курсор */
Отворете го курсорот на банката;
/*поврати податоци*/
ДОДЕКА е готово = 0 НАПРАВИ
ги преземаме активностите што ни се потребни
КРАЈ ДОДЕКА ;
/*затворање на курсорот*/
Затвори банкарски курсор;
КРАЈ ;* Овој изворен код беше означен со Истакнувачот на изворниот код.
Грешка: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Порака: Нема податоци - нула редови преземени, избрани или обработени
SQLSTATE: 02000 се вклучува кога ќе се достигне крајот на курсорот или кога изберете или ажурирање враќа празна низа.
Следниот ред го прогласивме курсорот ИЗЈАВИ ИМЕ на курсорот КУРСОР ЗА select_statement;
Отворете го курсорот Отворете го името на курсорот;
Потоа, додека не стигнеме до крајот на курсорот (ДОДЕКА е готово = 0 DO), ги извлекуваме податоците и ги обработуваме.
Мора да го затворите курсорот пред да излезете од зачуваната процедура. Затвори го името на курсорот;
Не изгледа комплицирано. Но, има многу стапици поврзани со SQLSTATE "02000".
ДОДЕКА е готово = 0 НАПРАВИ
ДОНЕСИ ГО БАНККУРСОРОТ ВО vBankId, vBankName, vAdress, vPhone;
Изберете (ContributeAmount) INTO vContributeAmountSUM ОД банкарска дистрибуција каде BankId = vBankId лимит 1;
правиме некои активности
КРАЈ ДОДЕКА ;* Овој изворен код беше означен со Истакнувачот на изворниот код.
Сè е во ред и правилно од синтаксичка гледна точка. Но, од логична гледна точка, не. Може да се случи штедачите да не отвориле сметки во некоја банка, потоа за Изберете (ContributeAmount) INTO vContributeAmountSUM ОД банкарска дистрибуција каде BankId = vBankId лимит 1; SQLSTATE: 02000 ќе се активира, променливата done ќе биде поставена на 1, а јамката while ќе заврши порано отколку што очекувавме. Ова може да се избегне ако го направите следново
ДОДЕКА е готово = 0 НАПРАВИ
ДОНЕСИ ГО БАНККУРСОРОТ ВО vBankId, vBankName, vAdress, vPhone;
/* извлече за банката износот на кој било од нејзините депозити */
ако (vContributeAmountSUM > 0) тогаш
/* извлече за банката износот на кој било од нејзините депозити */
крај ако ;
правиме некои активности
КРАЈ ДОДЕКА ;* Овој изворен код беше означен со Истакнувачот на изворниот код.
Со првото барање проверивме дали има придонеси (ако нема, тогаш vContributeAmountSUM == 0) и само доколку ги има, ги вадиме податоците.
Сега да речеме дека треба да го отстраниме вкупниот износ на сметките во различни банки за секој клиент
Декларирајте го курсорот ClientSummCursor за Изберете сума
Декларирајте го курсорот ClientSummCursor за Select sum (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join клиент на (client.ClientId = дистрибуција на банка.`ClientId`) каде што 1 група по ``kbankdistribution`. `ClientId`;Отвори ClientSummCursor;
ДОДЕКА е готово = 0 НАПРАВИ
ДОНЕСИ ГО БАНККУРСОРОТ ВО vBankId, vBankName, vAdress, vPhone;
/* извлече за банката износот на кој било од нејзините депозити */
Изберете Count(ContributeAmount) INTO vContributeAmountSUM ОД банкарска дистрибуција каде BankId = vBankId лимит 1;
/* проверете дали навистина има депозити во оваа банка */
ако (vContributeAmountSUM > 0) тогаш
/* извлече за банката износот на кој било од нејзините депозити */
Изберете ContributeAmount INTO vContributeAmountSUM ОД банкарска дистрибуција каде BankId = vBankId лимит 1;
крај ако ;
правиме некои активности.
КРАЈ ДОДЕКА ;* Овој изворен код беше означен со Истакнувачот на изворниот код.
Истата ситуација може да се појави кога податоците во курсорот ClientSummCursor завршуваат порано од податоците во BankCursor, SQLSTATE: 02000 се активира, променливата done е поставена на 1, а циклусот while завршува порано отколку што очекувавме. Ова може да се избегне ако го направите следново
Отвори ClientSummCursor;
ДОДЕКА е готово = 0 НАПРАВИ
ДОНЕСИ ГО БАНККУРСОРОТ ВО vBankId, vBankName, vAdress, vPhone;
/* извлече за банката износот на кој било од нејзините депозити */
Изберете Count(ContributeAmount) INTO vContributeAmountSUM ОД банкарска дистрибуција каде BankId = vBankId лимит 1;
/* проверете дали навистина има депозити во оваа банка */
ако (vContributeAmountSUM > 0) тогаш
/* извлече за банката износот на кој било од нејзините депозити */
Изберете ContributeAmount INTO vContributeAmountSUM ОД банкарска дистрибуција каде BankId = vBankId лимит 1;
крај ако ;
/* пред да извлечете податоци од вториот курсор, запомнете ја состојбата sqlstate */
ПОСТАВЕТЕ стар_статус = завршено;
/* извлечете ги податоците што ни се потребни */
ДОНЕСИ СУМ-Курсор на клиентот ВО vSum,vClientId;
/* проверете дали податоците се преземени и дали sqlstate 0200 не успеа */
ако (готово = 0) тогаш
правиме некои активности.
крај ако ;
/* пред крајот на времето, вратете ја вредноста на завршената променлива */
поставено е завршено = стар_статус;
КРАЈ ДОДЕКА ;* Овој изворен код беше означен со Истакнувачот на изворниот код.
Ви благодарам на сите што прочитавте досега, се надевам дека ова ќе биде корисно за некого.
Имплементацијата на курсорот во базата на податоци наликува на Java класа која има збир на податоци и методи за нивна обработка. При што SQL курсоротги користи податоците како редовна низа. Покажувачите може да се користат во предизвикувачи, складирани процедури и функции.
Во согласност со стандардот SQL, при работа со курсори, се вршат следниве основни дејства:
- декларација на курсорот;
- отворање курсор со податоци за читање;
- линија-по-линија земање примероци на податоци од курсорот;
- менување на податоците за ред со помош на курсорот;
- затворање на курсорот, по што станува недостапен;
- ослободување на курсорот, т.е. отстранување на курсорот од меморијата бидејќи неговото затворање не мора да ја ослободи меморијата поврзана со него.
Во различни имплементации дефиницијата курсоротможе да има некои разлики. На пример, понекогаш е неопходно експлицитно да се ослободи меморијата доделена за курсорот. Откако ќе се ослободи курсорот, се ослободува и меморијата поврзана со него. Ова овозможува повторно користење на името на курсорот. Во други имплементации, кога курсорот е затворен, меморијата се ослободува имплицитно.
Во некои случаи, не можете без користење на курсорот. Меѓутоа, доколку е можно, треба да избегнувате користење на курсорот и да работите со стандардни команди за обработка на податоци: ИЗБИРА, АЖУРИРАЈ, ВНЕСИ, ИЗБРИШИ. Ова се должи на фактот што курсорите не дозволуваат операции за модификација на целиот волумен на податоци и брзината на извршување на операциите за обработка на податоци со помош на курсорот е значително помала од онаа на стандардни средства SQL.
Ако некоја програма може да ги промени податоците вчитани во курсорот, тогаш таа се нарекува модифицирана. Кога зборуваме за курсори, не треба да заборавиме на изолацијата на трансакциите. Еден корисник менува запис со помош на курсорот, додека друг корисник го чита тој запис користејќи свој курсор. Покрај тоа, тој може да го промени истиот запис, што го прави неопходно да се одржи интегритетот на податоците.
Декларирање на курсорот
Покажувачите мора да бидат декларирани пред да можат да се користат. Стандардот SQL ја користи следнава синтакса за да креира курсор:
Декларирај го курсорот cursor_name за select_statement ])]
Овој израз декларира курсор прогласи курсорсо името „курсор_име“.
НЕСЕНЗИТИВЕНсе создава статичен курсор кој не дозволува да се прават промени. Дополнително, промените направени од други корисници не се прикажуваат. Ако недостасува клучниот збор НЕСЕНЗИТИВ, се креира динамичен курсор.
Кога користите клучен збор СКРОЛАЈкреираниот курсор може да се движи во која било насока, што ви овозможува да примените какви било команди за избор. Ако овој аргумент е испуштен, тогаш курсорот ќе биде секвенцијален, т.е. неговото гледање ќе биде можно само во една насока - од почеток до крај.
Изразување изберете_изјаваозначува структура за читање информации како изберете ... од ... . Не смее да го содржи операторот во, бидејќи курсорот има свој оператор донесиза пополнување на променливите со податоци за курсорот.
При одредување аргумент САМО ЗА ЧИТАЊЕќе се креира курсор само за читање и нема да бидат дозволени измени на податоците. Динамичниот курсор може да се декларира како покажувач само за читање, овозможувајќи да се прикажат промените направени од друг корисник.
Создавање курсор со аргумент ЗА АЖУРИРАЊЕви овозможува да направите промени на податоците во курсорот или во наведените колони или, во отсуство на аргумент ОД име на колона, во сите колони.
Може да декларирате повеќе курсори во потпрограма. Но, секој курсор мора да има уникатно име. За да отворите курсор, мора да го користите операторот отвореништо го отвора претходно декларираниот курсор:
Курсорот отворен
SQL ја дефинира следнава синтакса за отворање на курсорот „курсорот отворен“:
Отворете го името на курсорот;
Преземање податоци од курсорот, преземање на курсорот
Синтаксата за читање податоци од курсорот во некои променливи е како што следува:
Преземи име на курсорот во име на var [, var_name] ...;
Оператор донесиизбира отворени податоци на курсорот во променливи лоцирани после вои го поместува курсорот на следната позиција.
Затворете го курсорот
Оператор затвориго затвора курсорот. Ако операторот не е експлицитно наведен, курсорот автоматски се затвора кога соодветниот програмски блок е затворен.
Затвори го името на курсорот;
По затворањето, курсорот станува недостапен. Кога се затвора, се ослободуваат сите брави инсталирани додека курсорот работи. Може да се затворат само отворените курсори. Затворен, но не ослободен курсор може повторно да се отвори. Не е дозволено затворање на неотворен курсор.
Секој DBMS има свои особености за користење на курсорот.
Карактеристики на користење курсори во Oracle
Постојат четири атрибути на курсорот во PL/SQL % НАЈДЕНО, %НЕ Е НАЈДЕНО, %ISOPENИ %ROWCOUNT. Атрибутите на курсорот се декларирани како операторите %TYPE и %ROWTYPE, десно од името на курсорот.
%FOUND атрибут
%NOTFOUND атрибут
Атрибутот %NOTFOUND е токму спротивното од %FOUND.
%ISOPEN атрибут
Атрибутот %ISOPEN само покажува дали курсорот е отворен или не.
%ROWCOUNT атрибут
Атрибут %ROWCOUNTе нумерички атрибут кој го враќа бројот на редови прочитани од курсорот во одреден момент во времето.
Пример за SQL курсор во Oracle DBMS
Декларирајте v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm менаџери.comm%TYPE; crs курсорот за изберете id, име, сума(comm) како comm од менаџери каде податоците помеѓу „2014-11-01“ и „2014-11-30“ се групираат по ID, име; започнете со отворен Crs; јамка ИЗЛЕЗ КОГА crs% НЕ СЕ НАЈДЕ; ДОНЕСИ ги crs во v_id, v_name, v_comm; вметнете во вредностите на бонус (ид, име, заговор) (crs.id, crs.name, crs.comm); крајна јамка; посветат; затвори crs; крај;
Карактеристики на користење курсори во SQL сервер
Покажувачите што се користат во MSSQL можат да бидат секвенцијални или скролливи. Секвенцијалот ви овозможува да изберете податоци само во една насока - од почеток до крај. Покажувачите со можност за лизгање овозможуваат движење во двете насоки и ви дозволуваат да скокнете до произволен ред во комплетот резултати на курсорот.
SQL Server поддржува статични, динамични, секвенцијални и курсори управувани од клучеви.
Во статичниот дизајн на курсорот, информациите се зачувуваат како слика во одреден момент во времето. Затоа, промените направени во базата на податоци од друг корисник не се видливи. Додека курсорот се отвора, серверот поставува заклучување на сите редови вклучени во целосниот сет на резултати. Статичкиот курсор не се менува по креирањето и секогаш го прикажува збирот на податоци што постоел во моментот кога бил отворен. Ако другите корисници ги променат податоците вклучени во курсорот во изворната табела, тоа нема да влијае на статичниот курсор. Невозможно е да се направат промени на статичниот курсор, така што тој секогаш се отвора во режим само за читање.
Динамичниот курсор бара дополнителни мрежни надземни и софтверски ресурси. При користење на динамички курсори, не се креира целосна копија од податоците, туку селекциите од изворните табели се вршат само кога корисникот пристапува до одредени податоци. За време на преземањето, серверот ги заклучува редовите и сите промени што корисникот ги прави во целосниот сет на резултати на курсорот ќе бидат видливи во курсорот. Меѓутоа, штом курсорот ќе преземе податоци, промените направени од друг корисник веќе нема да се рефлектираат во курсорот.
Курсорот контролиран од множество копчиња има својства помеѓу статички и динамички. Записите се идентификуваат во моментот на земање примероци и на тој начин се следат промените. Овој тип на курсор е корисен кога се спроведува лизгање наназад. Во овој случај, додавањата и бришењата на податоците не се видливи додека информациите не се ажурираат и курсорот не избере нова верзијаевидентира доколку се направени промени кај нив.
Статичните курсори најдобро се користат за системи за обработка на информации, т.е. за системи за известување или за статистички и аналитички цели. Статичниот курсор е подобар во преземањето големи количини на податоци. Во системите за електронски набавки или резервации на објекти (седишта, билети), неопходно е динамично да се согледаат ажурираните информации додека се прават промени. Во такви случаи, се користи динамичен курсор. Во овие апликации, количината на пренесени податоци е типично мала и до која се пристапува на ниво на индивидуална евиденција.
Секвенцијалните покажувачи не дозволуваат преземање на податоци во обратна насока, само од почетокот до крајот на курсорот. Секвенцијален курсор не складира множество од сите редови со податоци. Тие се читаат од базата на податоци веднаш штом ќе се направи избор во курсорот, што овозможува динамично прикажување на сите промени направени од корисниците во базата со помош на командите INSERT, UPDATE, DELETE. Покажувачот ја чита најновата состојба на податоци.
Декларација на курсорот
Декларирај го курсорот cursor_name за SELECT_statement ]]
Кога користите клучен збор ЛОКАЛНИЌе се создаде локален курсор кој е видлив само во блокот, активирањето, складираната процедура или функцијата дефинирана од корисникот. Клучен збор ГЛОБАЛЕН, дефинира глобален курсор кој постои додека не се затвори моменталната врска.
Оператор НАПРЕД_САМОдефинира секвенцијален курсор кој овозможува преземање на податоците само во насока од првиот ред до последниот. При користење на операторот СКРОЛАЈсе креира курсор за лизгање кој овозможува пристап до податоците во кој било редослед и во која било насока.
Типот на курсорот го одредуваат операторите:
- STATIC - создавање статичен курсор;
- ДИНАМИЧНО - создавање динамичен курсор;
- KEYSET - креирање на курсорот на клучот.
Ако за курсорот САМО ЧИТАЈнаведете аргумент БРЗО_НАПРЕД, тогаш креираниот курсор ќе биде оптимизиран за брз пристапна податоците. Овој аргумент не може да се користи заедно со аргументи НАПРЕД_САМОИ ОПТИМИСТИЧНИ.
Ако курсорот е креиран со операторот ОПТИМИСТИЧНИ, тогаш е забрането да се менуваат или бришат редовите што биле променети по отворањето на курсорот.
При одредување аргумент TYPE_WARNINGсерверот ќе пријави имплицитна промена на типот на курсорот ако е некомпатибилен со барањето SELECT.
Враќање податоци од курсорот, земете
Веднаш по отворањето на курсорот, можете да ја добиете неговата содржина користејќи ја следнава команда:
При користење на операторот ПРВОќе се врати првиот ред од множеството резултати на курсорот, кој станува тековен ред. Кога е наведено ПОСЛЕДНОќе се врати последната линија на курсорот. Таа, исто така станува актуелна линија.
При одредување на оператор СЛЕДНОќе се врати редот веднаш по тековниот во резултатот. Оваа линија станува тековната линија. Стандардна команда ДОНЕСИго користи токму овој метод за преземање редови.
При одредување на оператор ПРЕТХОДНОлинијата пред сегашната ќе се врати. Оваа линија станува тековната линија.
Оператор АПСОЛУТНО (линија_број | @line_number_variable)враќа ред според неговиот апсолутен реден број во комплетното множество резултати на курсорот. Бројот на линијата може да се наведе со помош на константа или како име на променлива во која е зачуван бројот на линијата. Променливата мора да биде цел број податочен тип. Наведени се и позитивни и негативни вредности. При одредување на позитивна вредност, низата се брои од почетокот на множеството, додека негативната вредност се брои од крајот. Избраната линија станува тековната линија. Ако е наведена нула вредност, ниеден ред не се враќа.
Аргумент РЕЛАТИВНО (број на редови | @променлива број на редови)ја враќа линијата поместување на наведениот број на линии по тековната. Ако наведете негативен број на редови, ќе се врати редот што е наведениот број на редови пред тековниот. Наведувањето нулта вредност ќе го врати тековниот ред. Вратениот ред станува тековен ред.
За да отворите глобален курсор, мора да наведете клучен збор пред неговото име ГЛОБАЛЕН. Името на курсорот може да се одреди и со помош на променлива.
Во изразувањето ВО @variable_name [,...n]дефинирана е листа на променливи во која ќе се зачуваат соодветните вредности на колоните од вратениот ред. Редоследот на променливите мора да одговара на редоследот на колоните во курсорот, а типот на податоци на променливата мора да одговара на типот на податоци во колоната на курсорот.
Промена и бришење податоци со помош на курсорот
За да ги промените податоците со помош на курсорот, мора да издадете команда UPDATE во следниот формат:
Во една операција, вредностите на неколку колони од тековниот ред на курсорот може да се променат, но сите тие мора да припаѓаат на истата табела.
За да избришете податоци со помош на курсорот, користете ја командата DELETE во следниот формат:
Како резултат на тоа, тековната линија во курсорот ќе биде избришана.
Ослободете ја меморијата, распоредете
За да отстраните курсор од меморијата, користете ја командата
Распоредете го името на курсорот;
@@FETCH_STATUS атрибут
За да го одредите присуството на редови во курсорот, треба да користите глобална променлива @@FETCH_STATUS, кој зема вредност не-нулта ако нема повеќе редови во курсорот. Ако множеството редови сè уште не е исцрпено, тогаш @@FETCH_STATUS е еднакво на нула.
Пример за курсор во SQL сервер
Изјави @company varchar(50), @manager varchar(50), @message varchar(256); прогласи го курсорот crs_clients за локален за избрана компанија, менаџер од клиенти каде град = „Москва“ нарачка по компанија, менаџер; печати „Список на клиенти“; отвори crs_clients; преземете следно од crs_clients во @company, @manager; додека @@FETCH_STATUS = 0 започне изберете @message = „Компанија“ + @company + „менаџер“ + @manager; печати @порака; -- префрлете се на следното преземање записи од crs_clients во @company, @manager; крај; затвори crs_clients; распредели crs_clients;
Дадена е дефиницијата на курсорот. Даден е опис на неговите типови и однесување: статични, динамични, секвенцијални и клучни курсори. Опишани се принципите на контрола на курсорот: креирање и отворање на курсорот, читање податоци, затворање на курсорот. Дадени се примери за програмирање на курсорот.Концепт на курсорот
Барањето против релациска база на податоци обично враќа повеќе редови (записи) на податоци, но апликацијата обработува само еден запис во исто време. Дури и ако се занимава со неколку редови во исто време (на пример, прикажување податоци во форма на табеларни пресметки), нивниот број е сè уште ограничен. Дополнително, кога менувате, бришете или додавате податоци, работната единица е серијата. Во оваа ситуација, концептот на курсорот доаѓа до израз, и во овој контекст, курсорот е покажувач на ред.
Курсорот во SQL е област во меморијата на базата на податоци што е дизајнирана да ја задржи последната изјава на SQL. Ако тековната изјава е барање на базата на податоци, во меморијата се зачувува и ред со податоци за пребарување наречен тековна вредност или тековна линија на курсорот. Наведената област во меморијата е именувана и достапна за апликативните програми.
Вообичаено, курсорите се користат за избирање од базата на податоци подмножество на информации складирани во неа. Во секое време, една линија на курсорот може да се провери од апликациската програма. Покажувачите често се користат во SQL изјави, вградени во апликативни програми напишани на процедурални јазици. Некои од нив се имплицитно креирани од серверот на базата на податоци, додека други се дефинирани од програмери.
Во согласност со стандардот SQL, при работа со курсори, може да се разликуваат следниве главни дејства:
- создавање или декларација на курсорот;
- отворање на курсорот, т.е. пополнување со податоци што се зачувани во меморија на повеќе нивоа;
- избор од курсороти менување на редовите на податоци со него;
- затворање на курсорот, по што станува недостапен за корисничките програми;
- ослободување на курсорот, т.е. бришење на курсорот како објект бидејќи неговото затворање не мора да ја ослободи меморијата поврзана со него.
Дефиницијата на курсорот може малку да се разликува кај имплементациите. На пример, понекогаш развивачот мора експлицитно да ја ослободи меморијата доделена за курсорот. По ослободете го курсоротнејзината поврзана меморија е исто така ослободена. Ова овозможува повторно користење на неговото име. Во други имплементации кога затворање на курсоротослободувањето на меморијата се случува имплицитно. Веднаш по закрепнувањето, станува достапен за други операции: отворање на друг курсоритн.
Во некои случаи, користењето на курсорот е неизбежно. Меѓутоа, ако е можно, ова треба да се избегне и да работи со стандардни команди за обработка на податоци: SELECT, UPDATE, INSERT, DELETE. Покрај тоа што курсорите не дозволуваат операции за модификација на целиот волумен на податоци, брзината на извршување на операциите за обработка на податоци со помош на курсор е значително помала од онаа на стандардните SQL алатки.
Имплементација на курсори во MS SQL Server околина
SQL Server поддржува три типа курсори:
- Курсорите на SQL се користат првенствено во тригери, складирани процедури и скрипти;
- курсорите на серверот работат на серверот и го имплементираат апликацискиот програмски интерфејс за ODBC, OLE DB, DB_Library;
- Курсорите на клиентот се имплементирани на самиот клиент. Тие го преземаат целиот резултатски сет на редови од серверот и го складираат локално, што ја забрзува обработката на податоците со намалување на потрошеното време потрошено на мрежните операции.
Различни типови на повеќекориснички апликации бараат различни типови на паралелен пристап до податоците. Некои апликации бараат непосреден пристап до информации за промените во базата на податоци. Ова е типично за системите за резервација на билети. Во други случаи, како што се системите за статистичко известување, стабилноста на податоците е важна затоа што ако постојано се менуваат, програмите нема да можат ефективно да прикажуваат информации. На различни апликации им се потребни различни имплементации на курсори.
Во SQL Server, типовите на курсори се разликуваат во можностите што ги обезбедуваат. Типот на курсорот се одредува во фазата на неговото создавање и не може да се промени. Некои типови курсори може да детектираат промени направени од други корисници во редовите вклучени во комплетот резултати. Меѓутоа, SQL Server ги следи промените во таквите редови само додека се пристапува до редот и не дозволува промените да се менуваат откако редот веќе е прочитан.
Курсорите се поделени во две категории: секвенцијалени може да се прелистува. Последователниви дозволуваат да изберете податоци само во една насока - од почеток до крај. Курсори за лизгањеобезбедете поголема слобода на дејствување - можно е да се движите во двете насоки и да скокнете до произволен ред од множеството резултати на курсорот.Ако програмата може да ги измени податоците на кои покажува курсорот, тоа се нарекува скролливо и модифицирано. Зборувајќи за курсори, не треба да заборавиме на изолацијата на трансакциите. Кога еден корисник менува запис, друг го чита со помош на сопствен курсор, а згора на тоа, тој може да го измени истиот запис, што го прави неопходно да се одржи интегритетот на податоците.
SQL Server поддржува статички, динамички, секвенцијалени контролиран од збир на клучеви.
Во шемата со статичен курсоринформациите се читаат од базата на податоци еднаш и се складираат како снимка (од одреден момент во времето), така што промените направени во базата на податоци од друг корисник не се видливи. За некое време отворање на курсоротсерверот поставува заклучување на сите редови вклучени во целосниот сет на резултати. Статички курсорне се менува по креирањето и секогаш го прикажува множеството податоци што постоело во моментот на неговото отворање.
Ако другите корисници ги променат податоците вклучени во курсорот во изворната табела, тоа нема да влијае на статичен курсор.
ВО статичен курсорНе е можно да се прават промени, затоа секогаш се отвора во режим само за читање.
Динамичен курсорги одржува податоците во „жива“ состојба, но за тоа се потребни мрежни и софтверски ресурси. Користење на динамични курсорине се креира целосна копија од изворните податоци, туку се врши динамична селекција од изворните табели само кога корисникот пристапува до одредени податоци. За време на преземањето, серверот ги заклучува редовите и сите промени што корисникот ги прави на целосниот сет на резултати на курсорот ќе бидат видливи во курсорот. Меѓутоа, ако друг корисник направил промени откако курсорот ќе ги превземе податоците, тие нема да се рефлектираат во курсорот.
Курсорот контролиран од збир на копчиња, е во средината помеѓу овие крајности. Записите се идентификуваат во моментот на земање примероци и на тој начин се следат промените. Овој тип на курсор е корисен при спроведување на лизгање назад - тогаш додавањата и бришењата на редовите не се видливи додека информациите не се ажурираат, а возачот избира нова верзија на записот доколку се направени промени во него.
Секвенцијални курсорине им е дозволено да преземаат податоци во обратна насока. Корисникот може да избира само редови од почетокот до крајот на курсорот. Сериски курсорне складира множество од сите редови. Тие се читаат од базата веднаш штом ќе бидат избрани во курсорот, што овозможува сите промени направени од корисниците во базата да бидат динамички рефлектирани со помош на наредбите INSERT, UPDATE, DELETE. Покажувачот ја прикажува најновата состојба на податоците.
Статични курсориобезбедуваат стабилен приказ на податоците. Тие се добри за системи за „складирање“ на информации: апликации за системи за известување или за статистички и аналитички цели. Освен тоа, статичен курсорсе справува подобро од другите со земање примероци од големи количини на податоци. Спротивно на тоа, системите за електронско купување или резервација на билети бараат динамична перцепција на ажурираните информации додека се прават промени. Во такви случаи се користи динамичен курсор. Во овие апликации, количината на пренесени податоци е типично мала и до која се пристапува на ниво на ред (индивидуален запис). Групниот пристап е многу редок.
Управување со курсорот во околината на MS SQL Server
Контрола на курсоротимплементирано со извршување на следните команди:
- ИЗЈАВА - создавање или декларација на курсорот;
- ОТВОРЕНО - отворање на курсорот, т.е. пополнување со податоци;
- ДОНЕСИ избор од курсороти менување на податочните редови со помош на курсорот;
- ЗАТВОРИ - затворање на курсорот;
- ДЕАЛОКИРАЈ - ослободување на курсорот, т.е. бришење на курсорот како објект.
Декларација на курсорот
Стандардот SQL ја обезбедува следнава команда за создавање курсор:
Користењето на клучниот збор НЕСЕНЗИТИВНО ќе создаде статичен курсор. Промените на податоцитене се дозволени, освен тоа, промените направени од други корисници не се прикажуваат. Ако недостасува клучниот збор НЕСЕНЗИТИВ, a динамичен курсор.
Кога ќе го наведете клучниот збор SCROLL, креираниот курсор може да се движи во која било насока, што ќе ви овозможи да користите какви било команди за избор. Ако овој аргумент е испуштен, курсорот ќе биде конзистентна, т.е. неговото гледање ќе биде можно само во една насока - од почеток до крај.
Изјавата SELECT го одредува телото на барањето SELECT, кое го одредува добиениот сет на редови за курсорот.
Наведувањето FOR READ_ONLY создава курсор само за читање и не дозволува никакви измени на податоците. Се разликува од статичниот, иако вториот исто така не дозволува промена на податоците. Може да се декларира како курсор само за читање динамичен курсор, што ќе овозможи да се прикажат промените направени од друг корисник.
Создавањето курсор со аргумент ЗА АЖУРИРАЊЕ ви овозможува да извршите во курсорот промена на податоцитеили во наведените колони или, во отсуство на аргументот OF column_name, во сите колони.
Во околината MS SQL Server, следнава синтакса за командата за создавање курсор е прифатена:
<создание_курсора>::= ИЗЈАВИ ИМЕ на курсорот КУРСОР ЗА ИЗБОРНА_изјава ]]
Користењето на клучниот збор LOCAL ќе создаде локален курсор кој е видлив само во опсегот на пакетот, активирањето, складираната процедура или функцијата дефинирана од корисникот што го создала. Кога пакетот, активирањето, процедурата или функцијата завршува, курсорот имплицитно се уништува. За да ја пренесете содржината на курсорот надвор од конструктот што го создал, мора да доделите аргумент OUTPUT на неговиот параметар.
Ако е наведен клучниот збор GLOBAL, се креира глобален курсор; постои додека не се затвори моменталната врска.
Наведувањето FORWARD_ONLY создава сериски курсор; Податоците може да се земаат само во насока од првиот до последниот ред.
Со одредување SCROLL се создава курсорот со можност за лизгање; До податоците може да се пристапи во кој било редослед и во која било насока.
Наведувањето STATIC создава статичен курсор.
Со одредување на KEYSET се создава курсорот на клучот.
Одредувањето DYNAMIC создава динамичен курсор.
Ако го наведете аргументот FAST_FORWARD за курсорот READ_ONLY, креираниот курсор ќе биде оптимизиран за брз пристап до податоци. Овој аргумент не може да се користи заедно со аргументите FORWARD_ONLY или OPTIMISTIC.
Курсорот создаден со аргументот ОПТИМИСТИЧКИ спречува модификација или бришење на редови кои биле изменети по отворање на курсорот.
Со одредување на аргументот TYPE_WARNING, серверот ќе го информира корисникот за имплицитна промена на типот на курсорот доколку е некомпатибилна со барањето SELECT.
Отворање на курсорот
За отворање на курсороти пополнувајќи го со податоци од барањето SELECT наведено при креирањето на курсорот, користете ја следнава команда:
По отворање на курсоротСе извршува поврзаната изјава SELECT, чиј излез е зачуван во меморија на повеќе нивоа.
Враќање податоци од курсорот
Веднаш по отворање на курсоротможете да ја изберете неговата содржина (резултат од извршувањето на соодветното барање) користејќи ја следнава команда:
Наведувањето FIRST ќе го врати првиот ред од комплетниот сет на резултати на курсорот, кој станува тековен ред.
Со одредување LAST се враќа најновиот ред од курсорот. Таа, исто така станува актуелна линија.
Со одредување на NEXT се враќа редот веднаш по тековниот во целосниот сет на резултати. Сега станува актуелно. Стандардно, командата FETCH го користи овој метод за преземање редови.
Клучниот збор PRIOR го враќа редот пред тековниот. Станува актуелно.
Аргумент АПСОЛУТНО (линија_број | @line_number_variable)враќа ред според неговиот апсолутен реден број во комплетното множество резултати на курсорот. Бројот на линијата може да се наведе со помош на константа или како име на променлива во која е зачуван бројот на линијата. Променливата мора да биде цел број податочен тип. Наведени се и позитивни и негативни вредности. При одредување на позитивна вредност, низата се брои од почетокот на множеството, додека негативната вредност се брои од крајот. Избраната линија станува тековната линија. Ако е наведена нула вредност, ниеден ред не се враќа.
Аргумент РЕЛАТИВНО (број на редови | @променлива број на редови)ја враќа линијата што е наведениот број на линии по тековната. Ако наведете негативен број на редови, ќе се врати редот што е наведениот број на редови пред тековниот. Наведувањето нулта вредност ќе го врати тековниот ред. Вратениот ред станува тековен ред.
До отворете го глобалниот курсор, мора да го наведете клучниот збор ГЛОБАЛ пред неговото име. Името на курсорот може да се одреди и со помош на променлива.
Во дизајнот ВО @variable_name [,...n]е наведена листа на променливи во која ќе се зачуваат соодветните вредности на колоните на вратениот ред. Редоследот на одредување на променливите мора да одговара на редоследот на колоните во курсорот, а типот на податоци на променливата мора да одговара на типот на податоци во колоната на курсорот. Ако конструкцијата INTO не е наведена, тогаш однесувањето на командата FETCH ќе личи на однесувањето на командата SELECT - податоците се прикажуваат на екранот.
Промена и бришење податоци
За да направите промени со помош на курсорот, мора да издадете команда UPDATE во следниот формат:
Неколку колони од тековниот ред на курсорот може да се променат во една операција, но сите тие мора да припаѓаат на истата табела.
За да избришете податоци со помош на курсорот, користете ја командата DELETE во следниот формат:
Како резултат на тоа, линијата поставена струја во курсорот ќе биде избришана.
Затворање на курсорот
По затворањето, курсорот станува недостапен за корисниците на програмата. Кога е затворено, сите брави инсталирани за време на неговото работење се отстранети. Затворањето може да се примени само на отворени курсори. Затворено, но не ослободен курсорможе повторно да се отвори. Не е дозволено затворање на неотворен курсор.
Ослободете го курсорот
Затворање на курсоротне мора да ја ослободи меморијата поврзана со неа. Некои имплементации мора експлицитно да го распределат користејќи ја изјавата DEALLOCATE. По ослободете го курсоротМеморијата е исто така ослободена, што овозможува повторно користење на името на курсорот.
За да контролирате дали е достигнат крајот на курсорот, се препорачува да се користи функцијата: @@FETCH_STATUS
Функцијата @@FETCH_STATUS се враќа:
0 ако преземањето беше успешно;
1 ако превземањето не успеа поради обид да се донесе линија надвор од курсорот;
2 ако преземањето не успеа поради обид за пристап до избришан или изменет ред.
ИЗЈАВИ @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @порака VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Shopping list" ИЗЈАВИ клиент_курсор КУРСОР ЛОКАЛЕН ЗА ИЗБЕРЕН Шифра на клиентот, компанија, презиме ОД клиент WHERE City="Moscow" НАРАЧАЈ ПО КОМПЈА, Презиме ОТВОРИ ОТВОРЕН klient_cursor ВЕНИ СЛЕДНО ОД klient_cursor INTO @id_kl, @STALE, @USFETCH_Fam =0 BEGIN SELECT @message="Client "+@fam+ "Company "+ @firm PRINT @message SELECT @message="Име на производот Датум на купување Цена" PRINT @порака ИЗЈАВИ tovar_cursor CURSOR FOR SELECT производ.Име, трансакција.Датум, производ .Цена* Трансакција.Количина КАКО трошок ОД Производ ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ Трансакција на производот. Код на производ=Transaction.Код на производ КАДЕ Трансакција.Код на клиент=@id_kl ОТВОРИ tovar_cursor ВЛЕДИ СЛЕДНО ОД tovar_cursor ВО @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "No buys" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p КРАЈ ЗАТВОРИ tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Вкупна цена "+ CAST(@s AS CHAR(6)) PRINT @message -- преместете се на следниот клиент-- ДОНЕСИ ГО СЛЕДНОТО ОД klient_cursor ВО @id_kl, @firm, @fam КРАЈ ЗАТВОРЕТЕ klient_cursor DEALLOCATE klient_cursor Пример 13.6. Курсор за прикажување на список на стоки купени од клиенти од Москва и нивната вкупна цена.
Пример 13.7.Развијте курсор за лизгање за клиенти од Москва. Ако телефонскиот број започнува со 1, избришете го клиентот со тој број и во првиот запис на курсорот заменете ја првата цифра од телефонскиот број со 4.
ИЗЈАВИ @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @порака VARCHAR(80) ПЕЧАТЕЊЕ „Листа на клиенти“ ИЗЈАВИ klient_cursor ГЛОБАЛЕН КЛУЧОК ЗА ДОДЕЛУВАЊЕ КУРСОР ЗА ИЗБЕРЕН Фирма, Презиме, Телефон ФРОМ WHERE City ="Moscow" НАРАЧАЈ ПО КОМПАНИЈА, Презиме ЗА АЖУРИРАЊЕ ОТВОРЕНО klient_cursor ПРЕЗЕМИ СЛЕДНО ОД klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN ИЗБЕРЕ @message="Клиент "+@fam+ " Компанија "+ @firm " Телефон "+ @tel PRINT @message -- ако телефонскиот број започнува со 1, -- избришете го клиентот со тој број АКО @tel LIKE '1%' ИЗБРИШИ Клиент КАДЕ ТЕКОВНА НА klient_cursor ДРУГО -- преминете на следното клиент ПРЕЗЕМИ СЛЕДНО ОД klient_cursor ВО @firm, @fam, @tel КРАЈ ДОБИЕ АПСОЛУТЕН 1 ОД klient_cursor ВО @firm, @fam, @tel -- во првиот запис, заменете ја првата цифра од телефонскиот број со 4 АЖУРИРАЈТЕ ПРЕТСТАВУВАЊЕ НА Клиентот ='4' + RIGHT(@ tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm "Phone"+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Пример 13.7. Курсор со можност за лизгање за клиенти од Москва.
Пример 13.8.Употреба курсорот како излезен параметар на постапката. Постапката враќа збир на податоци - листа на производи.
Повикувањето на постапката и печатењето на податоците од излезниот курсор се врши на следниов начин:
ОБЈАВИ @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur ИЗЛЕЗОТ ПРЕЗЕМЕ СЛЕДНО ОД @my_cur ВО @n ИЗБЕРИ @n ДОДЕКА (@@FETCH_STATUS=0) ЗАПОЧНЕТЕ ДА ГО ПРЕЗЕМЕ СЛЕДНОТО ИЗБЕРЕТЕ ОД @_n КРАЈ ЗАТВОРИ @my_cur DEALLOCATE @my_cur
