Transact-SQL жүйесінде курсорлар мен циклдарды пайдалану. MySQL сақталатын процедуралардағы курсорлар Sql серверіндегі курсорлар
КУРСОРДЫ ЖАРИЯЛАУ пәрмені өңдеу үшін кесте жолынан жазбаларды алуға мүмкіндік береді. Бұл SQL жасайтын дәстүрлі деректер жиынын өңдеудің орнына жолдар бойынша өңдеуге мүмкіндік береді.
Ең бірінші жуықтау ретінде курсормен жұмыс істеу кезінде келесі қадамдар қолданылады.
Курсор DECLARE командасымен жасалады. Курсор OPEN командасымен ашылады.
Курсормен операциялар FETCH командасы арқылы орындалады. Курсор CLOSE командасымен жабылады.
DECLARE CURSOR командасы SELECT операторын көрсетеді. SELECT мәлімдемесі арқылы қайтарылған әрбір жолды жеке алуға және өңдеуге болады. Келесі Oracle мысалы курсорды бірнеше басқа айнымалылармен бірге декларация блогында жариялайды. Осыдан кейін келесі BEGIN...END блогында курсор ашылады, одан таңдау жасалады және курсор жабылады.
КУРСОР тақырып_бағасы_курсоры IS ТАҢДАУ тақырыбы, тақырыптардан бағасы
БАҒА НҰЛ БОЛМАҒАН ЖЕРДЕ; title_price_val title_price_cursor ROWTYPE; жаңа_баға NUMBER(10.2);
АШЫҚ тақырып_бағасы_курсоры;
ТАҚЫРЫПТЫҢ_бағасы_курсы-немесе тақырып_бағасының_валын INTO;
new_price:= "title_price_val.price" * 1,25 new_price_price VALUES INTO INSERT
(тақырып_бағасы.атауы, жаңа_баға) ЖАБУ тақырып_бағасы_курсоры; СОҢЫ;
Бұл мысалда PL/SQL қолданылғандықтан, біз бұл кітаптағы кодтың көп бөлігін түсіндірмейміз. Дегенмен, DECLARE блогы курсордың мәлімдемесін анық көрсетеді. Орындалатын PL/SQL блогында курсор OPEN пәрменімен инициализацияланады, мәндер FETCH пәрменімен шығарылады, соңында курсор CLOSE командасымен жабылады.
SELECT операторы курсордың негізі болып табылады, сондықтан оны DECLARE CURSOR операторына қоспас бұрын оны мұқият тексерген дұрыс. SELECT операторы негізгі кестеде немесе көріністе жұмыс істей алады. Сондықтан тек оқуға арналған курсорлар жаңартылмайтын көріністермен жұмыс істей алады. SELECT операторында ORDER BY, GROUP BY және HAVING сияқты сөйлемдер болуы мүмкін, егер бұл тармақтар бастапқы кестені жаңартпаса. Егер курсор ЖАҢАРТУ ҮШІН деп анықталса, онда SELECT операторынан мұндай сөйлемдерді алып тастау ұсынылады.
Жергілікті курсорлар сақталатын процедуралардың шығыс параметрлері ретінде жиі пайдаланылады. Сондықтан, сіз сақталатын процедурада курсорды анықтап, толтыра аласыз және оны шақыратын пакеттік тапсырмаға немесе сақталған процедураға бере аласыз.
Келесі қарапайым DB2 мысалында біз "XO1" admin_тобындағы бөлім нөмірлерін, бөлім атауларын және басқарушы нөмірлерін іздейтін курсорды жариялаймыз.
ДЕКЛАРАҢЫЗ курсордың курсоры
ТАҢДАУ ҮШІН 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 серверінде қолданылады).
@publisher_name VARCHAR(20) ЖАРИЯЛАУ
"АҚШ" елі ҚАЙДА БАСЫМДАРДАН pub_name ТАҢДАУ ҮШІН pub_cursor КУРСОРДЫ ЖАРИЯЛАУ
pub_cursor FROM INTO publisher_name КЕЛЕСІН АЛУ
WHILE @s>FETCH_STATUS=0
INSERT INTO Foreign_publishers VALUES("j>publisher_name)
pub_курсорды ЖАБУ pub_курсорды АЙЫРУ
Бұл мысалда курсорды жазбалар жиыны арқылы жылжытуды көруге болады. (Бұл мысал тек идеяны көрсетуге арналған, өйткені шын мәнінде бар Ең жақсы жолбұл мәселенің шешімі, атап айтқанда INSERT, SELECT нұсқауы.)
ОЛ ҚОЛДАНЫЛАДЫ: SQL Server (2008 жылдан бастап) База SQL деректері Azure SQL деректер қоймасы Параллельді деректер қоймасы
Көрініс сипаттары және курсор жұмыс істейтін нәтижелер жинағын құру үшін пайдаланылатын сұрау сияқты Transact-SQL сервер курсорының атрибуттарын анықтайды. DECLARE CURSOR операторы Transact-SQL тіл кеңейтім жинағын пайдаланатын ISO стандартты синтаксисін де, синтаксисін де қолдайды.
ISO синтаксисі меңзер_атын ЖАРИЯЛАУ [ НЕГІЗГІСІЗ ] [ АЙНАЛДЫРУ ] таңдау_мәліметіне арналған МЕҢГЕРІШ [ ҮШІН ( ТЕК ОҚУ | ЖАҢАРТУ [ БАҒАН_АТЫНЫН [ ,...n ] ] ) ] [;] Transact-SQL кеңейтілген синтаксисі ЖЕРГІЛІКТІ | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | КҮЙЕУ | ДИНАМИКАЛЫҚ | FAST_FORWARD ] [ ОҚУ_ТЕК | SCROLL_LOCKS | ОПТИМИСТІК ] [ ТҮР_ЕСКЕРТУ ] таңдау_мәлімдемесі үшін [ ЖАҢАРТУ ҮШІН [ баған_атауы [ ,...n ] ] ] [;]
курсордың_атауы
курсордың_атауы
Сезімсіз
tempdb; осылайша, негізгі кестелердегі өзгерістер осы курсордың таңдауларымен қайтарылған деректерде көрсетілмейді және бұл курсор өзгермейді. ISO синтаксисін пайдаланған кезде, СЕЗІМСІЗ опциясы көрсетілмесе, негізгі кестелерге жасалған бекітілген жаңартулар мен жоюлар кейінгі таңдауларда көрсетіледі.
АЙНАЛДЫРУ
Барлық іріктеу опцияларының қол жетімді екенін көрсетеді (БІРІНШІ, СОҢҒЫ, АЛДЫНДАҒЫ, КЕЛЕСІ, АТТЫСЫ, АБСОЛЮТ). ISO DECLARE CURSOR операторы SCROLL опциясын көрсетпесе, тек КЕЛЕСІ алу опциясына қолдау көрсетіледі. SCROLL опциясын FAST_FORWARD опциясымен көрсету мүмкін емес.
таңдау_мәліметі
Меңзердің нәтижелер жиынын көрсететін стандартты SELECT операторы. FOR BROWSE және INTO кілт сөздеріне рұқсат етілмейді таңдау_мәліметікурсордың мәлімдемесі.
таңдау_мәліметісұралған түрдегі курсормен қайшылық.
ТЕК ОҚУ
Жаңарту]
баған_атауы [,
.. .n] көрсетілген, тек тізімдегі бағандар өзгерістерге рұқсат береді. UPDATE мәлімдемесі бағандар тізімінсіз пайдаланылса, жаңарту барлық бағандар үшін мүмкін болады.
курсордың_атауы
Арнайы сервер курсорының Transact-SQL атауы. курсордың_атауыидентификаторларға қатысты ережелерді сақтау керек.
ЖЕРГІЛІКТІ
Меңзер бумаға, сақталған процедураға немесе ол жасалған триггерге жергілікті екенін көрсетеді. Курсор атауы тек осы аймақта жарамды. Курсорға жергілікті пакет айнымалылары, сақталатын процедуралар, триггерлер немесе сақталатын процедураның шығыс параметрі арқылы сілтеме жасауға болады. OUTPUT параметрі жергілікті курсорды шақырушы бумаға, сақталған процедураға немесе триггерге беру үшін пайдаланылады, содан кейін сақталған процедура аяқталғаннан кейін курсорға келесі қатынасу үшін параметрді курсор айнымалысына тағайындай алады. Меңзер OUTPUT параметріне берілмесе, бума, сақталған процедура немесе триггер орындауды аяқтаған кезде курсор жасырын түрде шығарылады. Егер курсор OUTPUT параметріне берілсе, оған сілтеме жасайтын барлық айнымалылар босатылған кезде немесе аумақтан шыққанда курсор босатылады.
ГЛОБАЛДЫҚ
Курсор қосылым үшін ғаламдық екенін көрсетеді. Меңзер атын қосылымда жұмыс істейтін кез келген сақталған процедура немесе бума пайдалана алады. Егер байланыс үзілген болса ғана курсор жасырын түрде шығарылады.
FORWARD_ONLY
Курсорды тек бірінші жолдан соңғы жолға дейін көруге болатынын көрсетеді. Тек АЛУ КЕЛЕСІ алу опциясына қолдау көрсетіледі. Егер FORWARD_ONLY STATIC, KEYSET немесе DYNAMIC кілт сөздерінсіз көрсетілсе, курсор DYNAMIC курсор ретінде әрекет етеді. FORWARD_ONLY аргументі де, SCROLL аргументі де көрсетілмесе, STATIC, KEYSET немесе DYNAMIC кілт сөздері болмаса, әдепкі мән FORWARD_ONLY аргумент болып табылады. STATIC, KEYSET және DYNAMIC курсорларында SCROLL әдепкі мәні бар. ODBC және ADO сияқты дерекқор API интерфейстерінен айырмашылығы, FORWARD_ONLY режиміне келесі Transact-SQL курсорлары қолдау көрсетеді: STATIC, KEYSET және DYNAMIC.
STATIC
Курсор пайдалану үшін деректердің уақытша көшірмесін жасайтын курсорды анықтайды. Курсорға барлық сұраулар көрсетілген уақытша кестеге қатынасады tempdb; осылайша, негізгі кестелердегі өзгерістер осы курсордың таңдауларымен қайтарылған деректерде көрсетілмейді және бұл курсор өзгермейді.
КҮЙГІЗУ
Меңзердегі жолдардың мүшелігі немесе реті ашылған кезде өзгермейтінін көрсетеді. Жолдарды бірегей түрде анықтайтын кілттер жинағы кестеге енгізілген tempdbшақырды пернелер.
Курсор иесі жасаған немесе басқа пайдаланушылар жасаған негізгі кестелердегі негізгі емес мәндерге өзгертулер курсор иесі оны көргенде көрсетіледі. Басқа пайдаланушылар жасаған өзгертулер көрсетілмейді (өзгерістерді Transact-SQL сервер курсоры арқылы енгізу мүмкін емес). Жол жойылса, жолдарды алу әрекеті @@FETCH_STATUS -2 мәнін қайтарады. Меңзер шекаралары арқылы негізгі мәндерді жаңарту ескі жолды жоюға, содан кейін жаңа жолды енгізуге ұқсас. Жаңа мәндері бар жол көрінбейді және ескі мәндері бар жолды алуға әрекет жасайды @@FETCH_STATUS -2 қайтарады. Жаңартулар WHERE CURRENT OF сөйлемі арқылы курсор арқылы жасалса, дереу көрінеді.
ДИНАМИКАЛЫҚ
Осы курсорды қарау кезінде нәтижелер жиынындағы жолдарға жасалған барлық деректер өзгерістерін көрсететін курсорды анықтайды. Әрбір таңдаудағы деректер мәндері, реттілік және жол мүшелігі әртүрлі болуы мүмкін. ABSOLUTE таңдау опциясына динамикалық курсорлар қолдау көрсетпейді.
ЖЫЛДАМ_АЛҒА
Өнімділік оңтайландыруы қосылған FORWARD_ONLY, READ_ONLY курсорын көрсетеді. FAST_FORWARD опциясын SCROLL немесе FOR_UPDATE опцияларымен көрсету мүмкін емес.
ТЕК ОҚУ
Осы курсор арқылы жасалған өзгерістерді болдырмайды. WHERE CURRENT OF сөйлемі UPDATE немесе DELETE мәлімдемесіндегі курсорға сілтеме жасай алмайды. Бұл опция әдепкі курсорды жаңарту мүмкіндігінен басымдылыққа ие.
SCROLL_LOCKS
Меңзерді пайдаланып орындалған орналасқан жаңартулардың немесе жоюлардың сәтті болатынына кепілдік берілгенін көрсетеді. SQL сервері жолдардың келесі өзгертулер үшін қолжетімді болуын қамтамасыз ету үшін курсорда оқылатын жолдарды құлыптайды. SCROLL_LOCKS опциясын FAST_FORWARD немесе STATIC опциясымен көрсету мүмкін емес.
ОПТИМИСТІК
Меңзер арқылы оқылған жол жаңартылған болса, орнатылған жаңартулар немесе жоюлар орындалмайтынын көрсетеді. SQL Server курсорда оқылатын жолдарды құлыптамайды. Оның орнына салыстырулар қолданылады уақыт белгісібаған мәндері немесе бақылау сомалары, кестеде болмаса уақыт белгісібаған курсорға оқылғаннан бері жолдың өзгергенін анықтау үшін. Жол өзгертілген болса, онда орналасқан жаңарту немесе жою әрекеттері сәтсіз болады. OPTIMISTIC опциясын FAST_FORWARD опциясымен көрсету мүмкін емес.
TYPE_WARNING
Курсор бір сұралған түрден екіншісіне жасырын түрлендірілсе, клиентке ескерту жіберілетінін көрсетеді.
таңдау_мәліметі
Меңзердің нәтижелер жиынын көрсететін стандартты SELECT операторы. COMPUTE, COMPUTE BY, FOR BROWSE және INTO кілт сөздеріне рұқсат етілмейді. таңдау_мәліметікурсордың мәлімдемесі.
SQL Server тармақтары болса курсорды басқа түрге жасырын түрлендіреді таңдау_мәліметісұралған түрдегі курсормен қайшылық. Қосымша ақпаратты жасырын курсорды түрлендіру бөлімін қараңыз.
ЖАҢАРТУ ҮШІН]
Жаңартылатын курсордағы бағандарды анықтайды. Егер OF баған_атауы [,
... n] берілген, тек тізімдегі бағандар өзгертуге рұқсат береді. UPDATE мәлімдемесі бағандар тізімінсіз пайдаланылса, READ_ONLY параллельдік опциясы көрсетілмесе, жаңарту барлық бағандар үшін мүмкін болады.
DECLARE CURSOR операторы Transact-SQL сервер курсорының атрибуттарын анықтайды, мысалы, көрініс сипаттары және курсор жұмыс істейтін нәтижелер жиынын құру үшін пайдаланылатын сұрау. OPEN операторы нәтижелер жиынын толтырады, ал FETCH операторы одан жолды қайтарады. CLOSE операторы курсормен байланысты ағымдағы нәтижелер жиынын тазартады. DEALLOCATE операторы курсор пайдаланатын ресурстарды шығарады.
DECLARE CURSOR операторының бірінші пішіні курсор параметрлерін көрсету үшін ISO синтаксисін пайдаланады. DECLARE CURSOR операторының екінші пішімі Transact-SQL тілінің кеңейтімдерін пайдаланады, олар ODBC және ADO сияқты дерекқор API интерфейстерінің курсор функцияларында қолданылатындармен бірдей типтерді пайдаланып курсорларды анықтауға мүмкіндік береді.
Бұл екі пішінді араластыруға болмайды. CURSOR кілт сөзінің алдында SCROLL немесе кілт сөздерді өткізіп жіберсеңіз, CURSOR арасында кілт сөздерді пайдалана алмайсыз. таңдау_мәліметітүйінді сөздер. КУРСОР арасында кілт сөздерді көрсету кезінде, сондай-ақ үшін таңдау_мәліметікілт сөздерді пайдалансаңыз, CURSOR кілт сөзінің алдында SCROLL немесе INSESITIVE параметрін көрсете алмайсыз.
Егер сіз DECLARE CURSOR мәлімдемесі үшін Transact-SQL синтаксисін пайдалансаңыз және READ_ONLY, OPTIMISTIC немесе SCROLL_LOCKS опцияларын көрсетпесеңіз, келесі әдепкі мән қабылданады.
SELECT мәлімдемесі жаңартуларға қолдау көрсетпесе (немесе рұқсаттары жеткіліксіз болса немесе жаңартуларға қолдау көрсетпейтін қашықтағы кестелерге қатынасса және т.б.), курсор READ_ONLY күйіне орнатылады.
STATIC және FAST_FORWARD курсорлары әдепкі бойынша READ_ONLY.
DYNAMIC және KEYSET курсорлары әдепкі бойынша ОПТИМИСТІК мәніне арналған.
Меңзерлерге тек басқа Transact-SQL операторлары сілтеме жасай алады. Дерекқор API функциялары курсорларға сілтеме жасай алмайды. Мысалы, курсор жарияланғаннан кейін OLE DB, ODBC немесе ADO функциялары мен әдістері оның атына сілтеме жасай алмайды. Сәйкес API функциялары мен әдістерін пайдаланып курсор жолдарын таңдау мүмкін емес; Осы мақсат үшін Transact-SQL FETCH операторларын пайдалану керек.
Курсор жарияланғаннан кейін оның сипаттарын анықтау үшін келесі сақталған процедураларды пайдалануға болады.
Айнымалыларды бөлік ретінде пайдалануға болады таңдау_мәліметі, онда курсор жарияланады. Курсордың айнымалы мәндері жарияланғаннан кейін өзгермейді.
Әдепкі бойынша, МЕҢГЕРІШІ ЖАРИЯЛАУ рұқсаттары курсор пайдаланатын көріністерде, кестелерде және бағандарда ТАҢДАУ рұқсаты бар барлық пайдаланушыларға беріледі.
Кластерленген бағандар қоймасының индексі бар кестеде курсорларды немесе триггерлерді пайдалана алмайсыз. Бұл шектеу кластерленбеген индекстерге қолданылмайды; Кластерленбеген бағандар қоймасының индексі бар кестеде курсорлар мен триггерлерді пайдалануға болады.
A. Қарапайым курсор мен синтаксисті қолдану
Осы курсорды ашқанда жасалған нәтижелер жиыны кестенің барлық жолдары мен бағандарын қамтиды. Бұл курсорды жаңартуға болады, барлық жаңартулар мен жоюлар осы курсордың таңдауында көрсетіледі. FETCH``NEXT алу тек SCROLL параметрі көрсетілмегендіктен ғана алынады.
ТАҢДАУ ҮШІН vend_курсорды ЖАРИЯЛАУ * Purchasing FROM.Vendor АШЫҚ vend_cursor КЕЛЕСІ vend_cursor FETCH FROM;
B. Есепті көрсету үшін кірістірілген курсорларды пайдалану
Келесі мысал күрделі есепті көрсету үшін кірістірілген курсорларды пайдаланады. Әрбір провайдер үшін ішкі курсор жарияланады.
NOCOUNT ҚОСУ ;
DECLARE @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 );БАСЫП АЛУ» -------- Жеткізуші өнімдерінің есебі --------»;
вендор_курсорын ТАҢДАУ ЕНДЕРІ ИДЕНТІ ҮШІН МЕҢГЕРІШ ЖАРИЯЛАУ, Purchasing.Vendor ҚАЙДАҒЫ аты PreferredVendorStatus = 1 VendorID ТАПСЫРЫСЫ; vendor_cursor АШЫҚ @vendor_id, @vendor_name INTO vendor_cursor-тен КЕЛЕСІ АЛУ @@FETCH_STATUS = 0 BAGIN PRINT " " ТАҢДАУ @message = "----- Сатушы өнімдері:"+ @vendor_name PRINT @message -- Негізделген ішкі курсорды жариялау
-- сыртқы курсордан vendor_id бойынша. Purchasing.ProductVendor pv, Production.Product v ҚАЙДА pv.ProductID = v.ProductID ЖӘНЕ pv.VendorID = @vendor_id ТАҢДАУ ҮШІН өнім_курсорын ЖАРИЯЛАУ v.Атау -- Сыртқы курсордан айнымалы мәнөнім_курсорын АШУ ЕГЕР @@FETCH_STATUS болса, өнім_курсорынан КЕЛЕСІ АЛУ @product INTO<>0 БАСПА "<
Курсор контекстік жады аймағына сілтеме болып табылады. SQL программалау тілінің кейбір іске асыруларында (Oracle, Microsoft SQL Server) – сұранысты орындау кезінде алынған нәтижелер жиыны және онымен байланысты ағымдағы жазба көрсеткіші. Мен курсорды балама деректерді сақтау орнын көрсететін виртуалды кесте деп айтар едім. Бұл жағдайда курсор оның деректеріне кәдімгі массивтің деректері сияқты қол жеткізуге мүмкіндік береді.
Курсорлар сақталатын процедураларда қолданылады. Теория жеткілікті, мысалды қарастырайық:
Бізде дерекқор бар (деректер базасы жақсы емес, бұл менің зертханалық жұмыс, бірақ біздің деректер базасының мұғалімі осындай құрылымды талап етті)
/*банк ақпараты*/
«банк» КЕСТЕСІН ЖАСАУ (
`BankName` VARCHAR (50) COLLATE utf8_bin ЕМЕС NULL ӘДІПТІ "" ,
НЕГІЗГІ КІЛТ ('БанкИд'))ENGINE=InnoDB
таңбалар жиыны "utf8" COLLATE "utf8_bin" ;
/*депозиттер туралы деректер*/
'банктік бөлу' КЕСТЕСІН ЖАСАУ (
`BankId` БҮТІН (11) NULL ЕМЕС,
`Тұрақты` БҮТІН (11) Әдепкі NULL ,
`Үлес сомасы` ОНДЫҚ (10,0) NULL ЕМЕС,
`ClientId` БҮТІН (11) NULL ЕМЕС,
НЕГІЗГІ КҮЙСЕ(`БанкИдентификаторы', 'КлиентИдентификаторы'),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
ШЕКТЕУ `bankdistribution_fk` ШЕТЕЛ КІЛТІ (`BankId`) СІЛТЕМЕЛЕР `bank` (`BankId`),
ШЕКТЕУ `bankdistribution_fk1` ШЕТЕЛДІК КІЛТ (`ClientId`) СІЛТЕМЕЛЕР `клиент` (`ClientId`)
)ENGINE=InnoDB
/*инвесторлар туралы деректер*/
«клиент» КЕСТЕСІН ЖАСАУ (
`ClientId` INTEGER (3) NULL ЕМЕС AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NULL ЕМЕС ,
`Тегі` VARCHAR (50) COLLATE utf8_bin NO NULL DEFAULT "" ,
`Аты` VARCHAR (50) COLLATE utf8_bin ЕМЕС NULL Әдепкі "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin ЕМЕС НҰЛ ӘДЕПКІ "" ,
`Телефон` VARCHAR (50) COLLATE utf8_bin ЕМЕС NULL Әдепкі "" ,
`Мекенжай` VARCHAR (50) COLLATE utf8_bin NO NULL DEFAULT "" ,
`SafeId` БҮТІН (5) NULL ЕМЕС,
НЕГІЗГІ КҮЙСЕ(`ClientId`, `CreditCardId`),
КЕРІ `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 таңбалар жиыны "utf8" "utf8_bin" ЖИНАУ
Әрбір банкті кезекпен алып, онымен кейбір әрекеттерді орындау керек делік, келесі сұрау бізге бұл мәселеде көмектесе алады.
«банкті» таңдаңыз.* БІЗГЕ КЕРЕК_ЖАЗЫЛҒАН_ШЕКТЕГІ 'банктен',1. Осылайша, WE NEED_RECORD NUMBER, 1 LIMIT көмегімен біз банк кестесінен циклдегі әрбір жазбаны шығарып аламыз және онымен қажетті әрекеттерді орындаймыз, сонымен бірге WE NEED_RECORD NUMBER мәнін 1-ге арттырамыз. Енді біз дәл солай істейміз, бірақ курсор
БАСТА
/* деректерді шығаратын айнымалылар */
vBankId бүтін санын жариялау;
vBankName жариялау VARCHAR(50);
vАдресті жариялау VARCHAR(50);
vPhone VARCHAR (50) деп жариялау;
/* Хэдлер айнымалысы - a*/
Орындалған бүтін әдепкі 0 деп жариялау;
/*Меңзердің мәлімдемесі*/
`банк`.`BankId`,`банк`.`BankName`,`банк`.`Мекенжай`,`банк`.`Телефон`, FROM `банк` үшін Банк курсорын жариялау, мұнда 1;
/*HANDLER мақсаты, ол төменде түсіндіріледі*/
SQLSTATE "02000" СЕТІ ҮШІН ЖАЛҒАСТЫРУ ӨҢДЕУШІ ЖАРИЯЛАУ орындалды=1;
/* курсорды ашу */
Банк курсорын ашу;
/*деректерді шығарып алу*/
Дайын болған кезде = 0 ЖАСАУ
қажетті әрекеттерді жасаймыз
АЯҚТАУ WHILE ;
/*курсорды жабу*/
Банк курсорын жабу;
СОҢЫ ;* Бұл бастапқы код Source Code Highlighter көмегімен бөлектелді.
Қате: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Хабар: Деректер жоқ - нөлдік жолдар алынған, таңдалған немесе өңделген
SQLSTATE: 02000 курсордың соңына жеткенде немесе таңдау немесе жаңарту бос жолды қайтарғанда іске қосылады.
Келесі жолда курсорды DECLARE cursor_name CURSOR FOR select_statement жариялады;
Курсорды ашу Курсор_атын ашу;
Содан кейін курсордың соңына жеткенше (WHILE done = 0 DO) деректерді шығарып, оны өңдейміз.
Сақталған процедурадан шықпас бұрын курсорды жабу керек. курсордың_атын жабу;
Бұл күрделі емес сияқты. Бірақ SQLSTATE «02000» байланысты көптеген тұзақтар бар.
Дайын болған кезде = 0 ЖАСАУ
vBankId, vBankName, vAddress, vPhone INTO Банк курсорын АЛУ;
BankId = vBankId шегі 1 болатын банктік таратудан vContributeAmountSUM INTO (ContributeAmount) таңдаңыз;
кейбір әрекеттерді жасаймыз
АЯҚТАУ WHILE ;* Бұл бастапқы код Source Code Highlighter көмегімен бөлектелді.
Синтаксис тұрғысынан бәрі жақсы және дұрыс. Бірақ логикалық тұрғыдан алғанда, жоқ. Салымшылардың кейбір банкте шот ашпауы мүмкін, содан кейін Банк коды = vBankId шегі 1 болатын банктік таратудан vContributeAmountSUM INTO (Үлес сомасы) таңдау үшін; SQLSTATE: 02000 іске қосылады, орындалған айнымалы мән 1-ге орнатылады және while циклі біз күткеннен ерте аяқталады. Мұны келесі әрекеттерді орындау арқылы болдырмауға болады
Дайын болған кезде = 0 ЖАСАУ
vBankId, vBankName, vAddress, vPhone INTO Банк курсорын АЛУ;
/* банкке оның кез келген салымының сомасын үзінді көшірме */
егер (vContributeAmountSUM > 0) болса
/* банкке оның кез келген салымының сомасын үзінді көшірме */
егер аяқталса;
кейбір әрекеттерді жасаймыз
АЯҚТАУ WHILE ;* Бұл бастапқы код Source Code Highlighter көмегімен бөлектелді.
Бірінші сұрау арқылы біз үлестердің бар-жоғын тексердік (егер жоқ болса, онда vContributeAmountSUM == 0) және бар болса ғана деректерді шығарып аламыз.
Енді әр клиент үшін әртүрлі банктердегі шоттардағы жалпы соманы алып тастау керек делік
Қосындыны таңдау үшін ClientSummCursor курсорын жариялаңыз
Таңдалған сома (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join клиентінде (client.ClientId = bankdistribution.`ClientId`) ClientSummCursor курсорын жариялаңыз, мұнда 1 топ `bankdistribution` бойынша. `ClientId`;Open ClientSummCursor;
Дайын болған кезде = 0 ЖАСАУ
vBankId, vBankName, vAddress, vPhone INTO Банк курсорын АЛУ;
/* банкке оның кез келген салымының сомасын үзінді көшірме */
BankId = vBankId шегі 1;
/* осы банкте шынымен депозит бар-жоғын тексеріңіз */
егер (vContributeAmountSUM > 0) болса
/* банкке оның кез келген салымының сомасын үзінді көшірме */
BankId = vBankId шегі 1 болатын банктік таратудан vContributeAmountSUM INTO ContributeAmount таңдаңыз;
егер аяқталса;
кейбір әрекеттерді жасаймыз.
АЯҚТАУ WHILE ;* Бұл бастапқы код Source Code Highlighter көмегімен бөлектелді.
Дәл осындай жағдай ClientSummCursor курсорындағы деректер BankCursor ішіндегі деректерден ерте аяқталса, SQLSTATE: 02000 іске қосылғанда, орындалған айнымалы 1 мәніне орнатылғанда және while циклі біз күткеннен ертерек аяқталғанда туындауы мүмкін. Мұны келесі әрекеттерді орындау арқылы болдырмауға болады
Open ClientSummCursor;
Дайын болған кезде = 0 ЖАСАУ
vBankId, vBankName, vAddress, vPhone INTO Банк курсорын АЛУ;
/* банкке оның кез келген салымының сомасын үзінді көшірме */
BankId = vBankId шегі 1;
/* осы банкте шынымен депозит бар-жоғын тексеріңіз */
егер (vContributeAmountSUM > 0) болса
/* банкке оның кез келген салымының сомасын үзінді көшірме */
BankId = vBankId шегі 1 болатын банктік таратудан vContributeAmountSUM INTO ContributeAmount таңдаңыз;
егер аяқталса;
/* екінші курсордан деректерді шығармас бұрын, sqlstate күйін есте сақтаңыз */
SET old_status = орындалды;
/* бізге қажет деректерді шығарып алыңыз */
vSum, vClientId INTO ClientSummCursor FETCH;
/* деректердің алынғанын және sqlstate 0200 орындалмағанын тексеріңіз */
егер (орындалды = 0) болса
кейбір әрекеттерді жасаймыз.
егер аяқталса;
/* уақыттың соңына дейін орындалған айнымалының мәнін қалпына келтіріңіз */
жиын орындалды = ескі_күй;
АЯҚТАУ WHILE ;* Бұл бастапқы код Source Code Highlighter көмегімен бөлектелді.
Осы уақытқа дейін оқығандардың барлығына рахмет, бұл біреуге пайдалы болады деп үміттенемін.
Деректер базасында курсорды іске асыру деректер мен оны өңдеу әдістерінің жиынтығы бар Java класына ұқсайды. Бола тұра sql курсорыдеректерді кәдімгі массив ретінде пайдаланады. Курсорларды триггерлерде, сақталатын процедураларда және функцияларда пайдалануға болады.
SQL стандартына сәйкес курсорлармен жұмыс істеу кезінде келесі негізгі әрекеттер орындалады:
- курсордың мәлімдемесі;
- мәліметтерді оқумен курсорды ашу;
- курсордан мәліметтерді жол бойынша іріктеу;
- курсордың көмегімен жол деректерін өзгерту;
- курсорды жабу, содан кейін оған қол жетімсіз болады;
- курсорды босату, яғни. курсорды жадтан алып тастау, себебі оны жабу онымен байланысты жадты міндетті түрде босатпайды.
Әртүрлі іске асыруда анықтама курсоркейбір айырмашылықтары болуы мүмкін. Мысалы, кейде курсорға бөлінген жадты нақты босату қажет. Курсор босатылғаннан кейін онымен байланысты жады да босатылады. Бұл курсор атауын қайта пайдалануға мүмкіндік береді. Басқа іске асыруларда, курсор жабылған кезде, жад жасырын түрде босатылады.
Кейбір жағдайларда курсорды қолданбай жасай алмайсыз. Дегенмен, мүмкін болса, курсорды пайдаланудан аулақ болу керек және деректерді өңдеудің стандартты пәрмендерімен жұмыс істеу керек: ТАҢДАУ, ЖАҢАРТУ, INSERT, DELETE. Бұл курсорлар деректердің бүкіл көлемін өзгерту операцияларына мүмкіндік бермейді және курсорды пайдаланып деректерді өңдеу операцияларын орындау жылдамдығы айтарлықтай төмен екеніне байланысты. стандартты құралдар SQL.
Егер бағдарлама курсорға жүктелген мәліметтерді өзгерте алса, онда ол өзгертілетін деп аталады. Курсорлар туралы айтқанда, транзакцияны оқшаулау туралы ұмытпау керек. Бір пайдаланушы курсорды пайдаланып жазбаны өзгертеді, ал басқа пайдаланушы бұл жазбаны өзінің курсоры арқылы оқиды. Сонымен қатар, ол сол жазбаны өзгерте алады, бұл деректердің тұтастығын сақтауды қажет етеді.
Курсорды жариялау
Меңзерлерді қолданар алдында жариялау керек. SQL стандарты курсорды жасау үшін келесі синтаксисті пайдаланады:
Select_statement ])] үшін курсор_атауы курсорын жариялау
Бұл өрнек курсорды жариялайды курсорды жариялау"курсор_атауы" атымен.
Сезімсізөзгертулер енгізуге мүмкіндік бермейтін статикалық курсор жасалады. Сонымен қатар, басқа пайдаланушылар жасаған өзгертулер көрсетілмейді. INSESITIVE кілт сөзі жоқ болса, динамикалық курсор жасалады.
Кілт сөзді пайдаланған кезде АЙНАЛДЫРУжасалған курсорды кез келген бағытта жылжытуға болады, бұл кез келген таңдау пәрмендерін қолдануға мүмкіндік береді. Егер бұл аргумент алынып тасталса, курсор дәйекті болады, яғни. оны қарау тек бір бағытта – басынан аяғына дейін мүмкін болады.
Өрнек таңдау_мәліметітаңдау ... ішінен ... сияқты ақпаратты оқу құрылымын көрсетеді. Онда оператор болмауы керек ішіне, өйткені курсордың өз операторы бар әкелуайнымалыларды курсор деректерімен толтыру үшін.
Аргументті көрсету кезінде ТЕК ОҚУ ҮШІНтек оқуға арналған курсор жасалады және деректерге ешқандай өзгертулер енгізуге рұқсат етілмейді. Динамикалық курсорды тек оқуға арналған курсор ретінде жариялауға болады, бұл басқа пайдаланушы жасаған өзгерістерді көрсетуге мүмкіндік береді.
Аргументпен курсорды құру ЖАҢАРТУ ҮШІНкурсордағы деректерге көрсетілген бағандарда немесе аргумент болмаған жағдайда өзгертулер енгізуге мүмкіндік береді OF баған_атауы, барлық бағандарда.
Ішкі бағдарламада бірнеше курсорды жариялауға болады. Бірақ әрбір курсордың бірегей атауы болуы керек. Курсорды ашу үшін операторды пайдалану керек ашықол бұрын жарияланған курсорды ашады:
Курсор ашық
SQL «курсор ашық» курсорын ашу үшін келесі синтаксисті анықтайды:
курсордың_атын ашу;
Курсордан деректерді алу, курсорды алу
Курсордан деректерді кейбір айнымалыларға оқу синтаксисі келесідей:
курсор_атын var_name ішіне алу [, var_name] ...;
Оператор әкелукейін орналасқан айнымалыларға ашық курсор деректерін таңдайды ішінежәне курсорды келесі орынға жылжытады.
Курсор жабылады
Оператор жабықкурсорды жабады. Егер оператор нақты көрсетілмесе, сәйкес бағдарлама блогы жабылған кезде курсор автоматты түрде жабылады.
курсордың_атын жабу;
Жабылғаннан кейін курсор қол жетімсіз болады. Жабу кезінде курсор жұмыс істеп тұрған кезде орнатылған барлық құлыптар босатылады. Тек ашық курсорларды жабуға болады. Жабылған, бірақ босатылмаған курсорды қайта ашуға болады. Ашылмаған курсорды жабуға рұқсат етілмейді.
Әрбір ДҚБЖ курсорды қолданудың өзіндік ерекшеліктеріне ие.
Oracle бағдарламасында курсорларды қолдану мүмкіндіктері
PL/SQL тілінде курсордың төрт атрибуттары бар % ТАБЫЛДЫ, %ТАБЫЛМАДЫ, %ISOPENЖәне %ROWCOUNT. Меңзер атрибуттары курсор атауының оң жағында %TYPE және %ROWTYPE операторлары сияқты жарияланады.
%FOUND төлсипаты
%NOTFOUND атрибуты
%NOTFOUND атрибуты %FOUND атрибуты болып табылады.
%ISOPEN атрибуты
%ISOPEN атрибуты тек курсордың ашық немесе ашық емес екенін көрсетеді.
%ROWCOUNT төлсипаты
Атрибут %ROWCOUNTбелгілі бір уақытта курсор оқитын жолдар санын қайтаратын сандық атрибут болып табылады.
Oracle ДҚБЖ-дағы SQL курсорының мысалы
v_id managers.id %TYPE жариялау; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs курсоры таңдау идентификаторы, аты, қосындысы (comm) басқарушылардан comm ретінде, мұнда "2014-11-01" және "2014-11-30" аралығындағы деректер идентификаторы, аты бойынша топтастырылады; ашық crs бастау; цикл EXIT WHEN crs%NOTFOUND; v_id, v_name, v_comm ішіне crs FETCH; бонустық мәндерге (id, name, comm) кірістіру (crs.id, crs.name, crs.comm); соңғы цикл; міндеттеу; жабу crs; Соңы;
SQL серверінде курсорларды қолдану мүмкіндіктері
MSSQL-де қолданылатын курсорлар ретті немесе айналдыруға болады. Тізбектілік деректерді тек бір бағытта – басынан аяғына дейін таңдауға мүмкіндік береді. Жылжытылатын курсорлар екі бағытта да қозғалуға мүмкіндік береді және курсордың нәтижелер жинағындағы ерікті жолға өтуге мүмкіндік береді.
SQL Server статикалық, динамикалық, дәйекті және пернелер жиынымен басқарылатын курсорларды қолдайды.
Статикалық курсор дизайнында ақпарат белгілі бір уақытта сурет ретінде сақталады. Сондықтан басқа пайдаланушының дерекқорға жасаған өзгертулері көрінбейді. Курсор ашылып жатқанда, сервер оның толық нәтижелер жинағына кіретін барлық жолдарға құлыпты орнатады. Статикалық курсор жасалғаннан кейін өзгермейді және әрқашан ашылған кезде болған деректер жиынын көрсетеді. Егер басқа пайдаланушылар бастапқы кестедегі курсорға енгізілген деректерді өзгертсе, бұл статикалық курсорға әсер етпейді. Статикалық курсорға өзгертулер енгізу мүмкін емес, сондықтан ол әрқашан тек оқуға арналған режимде ашылады.
Динамикалық курсор қосымша желілік шығындар мен бағдарламалық қамтамасыз ету ресурстарын қажет етеді. Динамикалық курсорларды пайдалану кезінде деректердің толық көшірмесі жасалмайды, бірақ бастапқы кестелерден таңдаулар пайдаланушы белгілі бір деректерге қол жеткізген кезде ғана орындалады. Алу кезінде сервер жолдарды құлыптайды және пайдаланушы курсордың толық нәтижелер жиынына енгізген кез келген өзгерістер курсорда көрінеді. Дегенмен, курсор деректерді алғаннан кейін, басқа пайдаланушы жасаған өзгертулер енді курсорда көрсетілмейді.
Пернелер жиынымен басқарылатын курсордың статикалық және динамикалық қасиеттері бар. Жазбалар іріктеу кезінде анықталады және осылайша өзгерістер бақыланады. Курсордың бұл түрі артқа айналдыруды жүзеге асыру кезінде пайдалы. Бұл жағдайда деректер жаңартылмайынша және курсор таңдалғанша деректерді қосу және жою көрінбейді жаңа нұсқасыегер оларға өзгерістер енгізілген болса, жазбалар.
Статикалық курсорлар ақпаратты өңдеу жүйелері үшін жақсы қолданылады, яғни. есеп беру жүйелері немесе статистикалық және аналитикалық мақсаттар үшін. Статикалық курсор деректердің үлкен көлемін алуда жақсырақ. Объектілерді (орындар, билеттер) электронды сатып алу немесе брондау жүйелерінде өзгерістер енгізілген сайын жаңартылған ақпаратты динамикалық түрде қабылдау қажет. Мұндай жағдайларда динамикалық курсор қолданылады. Бұл қолданбаларда тасымалданатын деректер көлемі әдетте шағын және жеке жазба деңгейінде қол жеткізіледі.
Тізбекті курсорлар деректерді кері бағытта алуға мүмкіндік бермейді, тек курсордың басынан аяғына дейін. Тізбекті курсор барлық деректер жолдарының жиынын сақтамайды. Курсорда таңдау жасалғаннан кейін олар дерекқордан оқылады, бұл INSERT, UPDATE, DELETE командалары арқылы деректер қорына пайдаланушылар жасаған барлық өзгерістерді динамикалық түрде көрсетуге мүмкіндік береді. Курсор ең соңғы деректер күйін оқиды.
Курсор декларациясы
SELECT_statement ]] үшін курсор_атауы курсорын жариялау
Кілт сөзді пайдаланған кезде ЖЕРГІЛІКТІБлокта, триггерде, сақталатын процедурада немесе пайдаланушы анықтайтын функцияда ғана көрінетін жергілікті курсор жасалады. Негізгі сөз ГЛОБАЛДЫҚ, ағымдағы қосылым жабылғанға дейін бар жаһандық курсорды анықтайды.
Оператор FORWARD_ONLYдеректерді бірінші жолдан соңғы бағытта ғана алуға мүмкіндік беретін дәйекті курсорды анықтайды. Операторды пайдаланған кезде АЙНАЛДЫРУдеректерге кез келген ретпен және кез келген бағытта қол жеткізуге мүмкіндік беретін айналдырылатын курсор жасалады.
Курсор түрін операторлар анықтайды:
- STATIC – статикалық курсорды құру;
- DYNAMIC – динамикалық курсорды құру;
- KEYSET – перне курсорын құру.
Егер курсор үшін ТЕК ОҚУаргументті көрсетіңіз ЖЫЛДАМ_АЛҒА, содан кейін жасалған курсор үшін оңтайландырылған болады жылдам қол жеткізудеректерге. Бұл аргумент дәлелдермен бірге қолданыла алмайды FORWARD_ONLYЖәне ОПТИМИСТІК.
Егер курсор оператормен жасалса ОПТИМИСТІК, онда курсор ашылғаннан кейін өзгертілген жолдарды өзгертуге немесе жоюға тыйым салынады.
Аргументті көрсету кезінде TYPE_WARNINGсервер ТАҢДАУ сұрауымен үйлеспейтін болса, курсор түрін жасырын өзгерту туралы хабарлайды.
Курсордан деректерді алу, алу
Курсорды ашқаннан кейін оның мазмұнын келесі пәрмен арқылы алуға болады:
Операторды пайдаланған кезде БІРІНШІкурсордың нәтижелер жиынының бірінші жолы қайтарылады, ол ағымдағы жолға айналады. Белгіленген кезде СОҢҒЫкурсордың соңғы жолы қайтарылады. Ол сондай-ақ ағымдағы сызыққа айналады.
Операторды көрсету кезінде КЕЛЕСІнәтижелер жиынындағы ағымдағы жолдан кейінгі жол бірден қайтарылады. Бұл жол ағымдағы жолға айналады. Әдепкі пәрмен АЛУжолдарды алудың дәл осы әдісін қолданады.
Операторды көрсету кезінде АЛДЫНДАағымдағы жолдың алдындағы жол қайтарылады. Бұл жол ағымдағы жолға айналады.
Оператор ABSOLUTE (жол_нөмірі | @line_number_variable)курсордың толық нәтижелер жиынындағы абсолютті реттік саны бойынша жолды қайтарады. Жол нөмірін тұрақты мәнді пайдаланып немесе жол нөмірі сақталатын айнымалының аты ретінде көрсетуге болады. Айнымалы бүтін деректер түрі болуы керек. Оң және теріс мәндер көрсетілген. Оң мәнді көрсеткенде, жол жиынның басынан бастап есептеледі, ал теріс мән соңынан есептеледі. Таңдалған жол ағымдағы жолға айналады. Нөл мән көрсетілсе, жол қайтарылмайды.
Аргумент RELATIVE (жолдар саны | @variable жолдар саны)ағымдағы жолдан кейінгі көрсетілген жолдар санының ығысуын қайтарады. Жолдардың теріс санын көрсетсеңіз, ағымдағыға дейінгі жолдардың көрсетілген саны болып табылатын жол қайтарылады. Нөл мәнді көрсету ағымдағы жолды қайтарады. Қайтарылған жол ағымдағы жолға айналады.
Жаһандық курсорды ашу үшін оның атының алдында кілт сөзді көрсету керек ГЛОБАЛДЫҚ. Курсор атауын айнымалы мәнді пайдаланып көрсетуге де болады.
Өрнекте INTO @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 = "Компания" + @компания + "менеджер" + @manager таңдаңыз; @message басып шығару; -- crs_clients ішінен @company, @manager ішінен келесі жазбаны алуға көшу; Соңы; crs_clients жабу; crs_clients бөлу;
Курсордың анықтамасы берілген. Оның түрлері мен әрекетінің сипаттамасы берілген: статикалық, динамикалық, тізбекті және кілттік курсорлар. Курсорды басқару принциптері сипатталған: курсорды құру және ашу, мәліметтерді оқу, курсорды жабу. Курсорды программалау мысалдары келтірілген.Курсор түсінігі
Реляциялық дерекқорға қарсы сұрау әдетте деректердің бірнеше жолын (жазбаларын) қайтарады, бірақ қолданба бір уақытта тек бір жазбаны өңдейді. Ол бір уақытта бірнеше жолдармен жұмыс істесе де (мысалы, мәліметтерді электрондық кестелер түрінде көрсету), олардың саны әлі де шектеулі. Сонымен қатар, деректерді өзгерту, жою немесе қосу кезінде жұмыс бірлігі серия болып табылады. Бұл жағдайда курсор түсінігі бірінші орынға шығады және бұл контексте курсор жолдың көрсеткіші болып табылады.
SQL-дегі курсор - соңғы SQL операторын ұстауға арналған дерекқор жадындағы аймақ. Ағымдағы мәлімдеме дерекқор сұрауы болса, ағымдағы мән немесе ағымдағы курсор жолы деп аталатын сұрау деректерінің жолы да жадта сақталады. Жадтағы көрсетілген аймақ аталды және қолданбалы бағдарламаларға қол жетімді.
Әдетте, курсорлар дерекқордан онда сақталған ақпараттың ішкі жиынын таңдау үшін пайдаланылады. Кез келген уақытта курсордың бір жолын қолданбалы бағдарлама арқылы тексеруге болады. Курсорлар жиі қолданылады SQL мәлімдемелері, процедуралық тілдерде жазылған қолданбалы бағдарламаларға енгізілген. Олардың кейбіреулері дерекқор серверімен жасырын түрде жасалады, ал басқалары бағдарламашылармен анықталады.
SQL стандартына сәйкес курсорлармен жұмыс істеу кезінде келесі негізгі әрекеттерді бөліп көрсетуге болады:
- құру немесе курсордың мәлімдемесі;
- курсорды ашу, яғни. оны көп деңгейлі жадта сақталатын мәліметтермен толтыру;
- курсордан таңдаужәне онымен деректер қатарын өзгерту;
- курсорды жабу, содан кейін ол пайдаланушы бағдарламалары үшін қолжетімсіз болады;
- курсорды босату, яғни. курсорды нысан ретінде жою, себебі оны жабу онымен байланысты жадты міндетті түрде босатпайды.
Жүгіргінің анықтамасы іске асыруда аздап өзгеруі мүмкін. Мысалы, кейде әзірлеуші курсор үшін бөлінген жадты анық босатуы керек. Кейін курсорды босатыңызоның байланысты жады да босатылады. Бұл оның атын қайта пайдалануға мүмкіндік береді. Басқа іске асыруларда қашан курсорды жабужадты босату жасырын түрде жүреді. Қалпына келтіруден кейін ол басқа операциялар үшін қол жетімді болады: басқа курсорды ашужәне т.б.
Кейбір жағдайларда курсорды пайдалану мүмкін емес. Дегенмен, мүмкін болса, бұған жол бермеу керек және деректерді өңдеудің стандартты пәрмендерімен жұмыс істеу керек: ТАҢДАУ, ЖАҢАРТУ, INSERT, DELETE. Курсорлар деректердің бүкіл көлемін өзгерту операцияларына мүмкіндік бермейтіндігінен басқа, курсорды пайдаланып деректерді өңдеу операцияларын орындау жылдамдығы стандартты SQL құралдарымен салыстырғанда айтарлықтай төмен.
MS SQL Server ортасында курсорларды іске асыру
SQL Server курсорлардың үш түрін қолдайды:
- SQL курсорлары негізінен триггерлерде, сақталатын процедураларда және сценарийлерде қолданылады;
- сервер курсорлары серверде жұмыс істейді және ODBC, OLE DB, DB_Library үшін қолданбалы бағдарламалау интерфейсін жүзеге асырады;
- Клиент курсорлары клиенттің өзінде жүзеге асырылады. Олар серверден жолдардың барлық нәтижелер жинағын алады және оны жергілікті түрде сақтайды, бұл желілік операцияларға жұмсалған бос уақытты азайту арқылы деректерді өңдеуді жылдамдатады.
Көп пайдаланушы қолданбаларының әртүрлі түрлері деректерге параллель қол жеткізудің әртүрлі түрлерін талап етеді. Кейбір қолданбалар дерекқордағы өзгерістер туралы ақпаратқа дереу қол жеткізуді талап етеді. Бұл билеттерді брондау жүйелеріне тән. Басқа жағдайларда, мысалы, статистикалық есеп беру жүйелері, деректердің тұрақтылығы маңызды, себебі ол үнемі өзгертіліп отырса, бағдарламалар ақпаратты тиімді көрсете алмайды. Әртүрлі қолданбалар курсорлардың әртүрлі орындалуын қажет етеді.
SQL серверінде курсор түрлері олар беретін мүмкіндіктерге қарай өзгереді. Курсор түрі оның жасалу кезеңінде анықталады және оны өзгерту мүмкін емес. Курсорлардың кейбір түрлері басқа пайдаланушылар нәтижелер жиынына енгізілген жолдарға жасаған өзгерістерді анықтай алады. Дегенмен, SQL сервері жолға қатынасу кезінде осындай жолдардағы өзгерістерді ғана қадағалайды және жол оқылып болғаннан кейін өзгертулерді өзгертуге рұқсат бермейді.
Курсорлар екі санатқа бөлінеді: дәйектіжәне айналдыруға болады. Дәйектідеректерді тек бір бағытта – басынан аяғына дейін таңдауға мүмкіндік береді. Жылжытылатын курсорларәрекеттің үлкен еркіндігін қамтамасыз етеді – екі бағытта да қозғалуға және курсордың нәтижелер жиынының ерікті жолына өтуге болады.Егер бағдарлама курсор көрсететін деректерді өзгертуге қабілетті болса, оны айналдыру және өзгерту мүмкін деп атайды. Курсорлар туралы айтатын болсақ, транзакцияны оқшаулау туралы ұмытпау керек. Бір пайдаланушы жазбаны өзгерткенде, екіншісі оны өзінің курсоры арқылы оқиды, сонымен қатар ол сол жазбаны өзгерте алады, бұл деректердің тұтастығын сақтауды қажет етеді.
SQL сервері статикалық, динамикалық, дәйектіжәне кілттер жиынтығымен басқарылады.
Схемада статикалық курсорақпарат дерекқордан бір рет оқылады және сурет ретінде сақталады (белгілі бір уақытта), сондықтан басқа пайдаланушы дерекқорға енгізген өзгерістер көрінбейді. Уақытша курсорды ашусервер оның толық нәтижелер жинағына кіретін барлық жолдарға құлыпты орнатады. Статикалық курсоржасалғаннан кейін өзгермейді және әрқашан оны ашу кезінде болған деректер жинағын көрсетеді.
Егер басқа пайдаланушылар бастапқы кестедегі курсорға енгізілген деректерді өзгертсе, бұл әсер етпейді статикалық курсор.
IN статикалық курсорӨзгерістер енгізу мүмкін емес, сондықтан ол әрқашан тек оқуға арналған режимде ашылады.
Динамикалық курсордеректерді «тірі» күйде сақтайды, бірақ бұл үшін желі мен бағдарламалық құрал ресурстары қажет. Қолдану динамикалық курсорларбастапқы деректердің толық көшірмесі жасалмайды, бірақ пайдаланушы белгілі бір деректерге қол жеткізген кезде ғана бастапқы кестелерден динамикалық таңдау орындалады. Алу кезінде сервер жолдарды құлыптайды және пайдаланушы курсордың толық нәтижелер жиынына енгізген кез келген өзгерістер курсорда көрінеді. Дегенмен, курсор деректерді алғаннан кейін басқа пайдаланушы өзгертулер енгізсе, олар курсорда көрсетілмейді.
Курсор пернелер жиынтығымен басқарылады, осы шектен шыққандардың ортасында орналасқан. Жазбалар іріктеу кезінде анықталады, осылайша өзгерістер бақыланады. Меңзердің бұл түрі артқа айналдыруды жүзеге асыру кезінде пайдалы - содан кейін жолдардың толықтырулары мен жойылуы ақпарат жаңартылмайынша көрінбейді, ал драйвер оған өзгертулер енгізілген болса, жазбаның жаңа нұсқасын таңдайды.
Тізбекті курсорларкері бағытта деректерді алуға рұқсат етілмейді. Пайдаланушы курсордың басынан аяғына дейінгі жолдарды ғана таңдай алады. Сериялық курсорбарлық жолдардың жиынын сақтамайды. Олар курсорда таңдалған бойда деректер қорынан оқылады, бұл пайдаланушылар деректер базасына енгізген барлық өзгерістерді INSERT, UPDATE, DELETE командалары арқылы динамикалық түрде көрсетуге мүмкіндік береді. Курсор деректердің ең соңғы күйін көрсетеді.
Статикалық курсорлардеректердің тұрақты көрінісін қамтамасыз етеді. Олар ақпараттық «қойма» жүйелері үшін жақсы: есеп беру жүйелеріне арналған қосымшалар немесе статистикалық және аналитикалық мақсаттар үшін. Сонымен қатар, статикалық курсордеректердің үлкен көлемін іріктеумен басқаларға қарағанда жақсы жұмыс істейді. Керісінше, электронды сатып алу немесе билеттерді брондау жүйелері өзгерістер енгізілген кезде жаңартылған ақпаратты динамикалық қабылдауды талап етеді. Мұндай жағдайларда ол қолданылады динамикалық курсор. Бұл қолданбаларда тасымалданатын деректер көлемі әдетте шағын және жол (жеке жазба) деңгейінде қол жеткізіледі. Топтық қатынас өте сирек кездеседі.
MS SQL Server ортасында курсорды басқару
Курсорды басқарукелесі командаларды орындау арқылы жүзеге асырылады:
- DECLARE - құру немесе курсордың мәлімдемесі;
- АШЫҚ – курсорды ашу, яғни. оны мәліметтермен толтыру;
- АЛУ курсордан таңдаужәне курсордың көмегімен деректер жолын өзгерту;
- ЖАБЫҚ - курсорды жабу;
- АЛУ – курсорды босату, яғни. курсорды нысан ретінде жою.
Курсор декларациясы
SQL стандарты курсорды жасау үшін келесі пәрменді береді:
INSESITIVE кілт сөзін пайдалану жасалады статикалық курсор. Деректер өзгерістерірұқсат етілмейді, сонымен қатар басқа пайдаланушылар енгізген өзгерістер көрсетілмейді. INSESITIVE кілт сөзі жоқ болса, a динамикалық курсор.
SCROLL кілт сөзін көрсеткенде, жасалған курсорды кез келген бағытта жылжытуға болады, бұл кез келген таңдау пәрмендерін пайдалануға мүмкіндік береді. Бұл аргумент алынып тасталса, курсор болады дәйекті, яғни. оны қарау тек бір бағытта – басынан аяғына дейін мүмкін болады.
SELECT операторы ТАҢДАУ сұрауының негізгі бөлігін көрсетеді, ол курсор үшін жолдардың нәтиже жиынын анықтайды.
FOR READ_ONLY параметрін көрсету тек оқуға арналған курсорды жасайды және деректерге ешқандай өзгертулер енгізуге рұқсат бермейді. Ол статикалықтан ерекшеленеді, бірақ соңғысы деректерді өзгертуге мүмкіндік бермейді. Тек оқуға арналған курсор ретінде жариялауға болады динамикалық курсор, ол басқа пайдаланушы жасаған өзгерістерді көрсетуге мүмкіндік береді.
FOR UPDATE аргументі бар курсорды жасау курсорда орындауға мүмкіндік береді деректердің өзгеруікөрсетілген бағандарда немесе OF бағанының_атауы аргументі болмаған жағдайда, барлық бағандарда.
MS SQL Server ортасында курсорды жасау пәрмені үшін келесі синтаксис қабылданады:
<создание_курсора>::= SELECT_statement ҮШІН курсор_атын ЖАРИЯЛАУ КУРСОР ]]
LOCAL кілт сөзін пайдалану тек буманың, триггердің, сақталатын процедураның немесе оны жасаған пайдаланушы анықтайтын функцияның ауқымында көрінетін жергілікті курсорды жасайды. Бума, триггер, процедура немесе функция аяқталғанда, курсор жасырын түрде жойылады. Курсордың мазмұнын оны жасаған құрылымнан тыс өткізу үшін оның параметріне OUTPUT аргументін тағайындау керек.
Егер GLOBAL кілт сөзі көрсетілсе, жаһандық курсор жасалады; ол ағымдағы қосылым жабылғанға дейін бар.
FORWARD_ONLY белгілеу жасайды сериялық курсор; Деректерді тек бірінші жолдан соңғы жолға дейінгі бағытта таңдауға болады.
SCROLL белгілеу жасайды айналдырылатын курсор; Деректерге кез келген ретпен және кез келген бағытта қол жеткізуге болады.
STATIC жасауды көрсету статикалық курсор.
KEYSET параметрін көрсету перне курсорын жасайды.
DYNAMIC жасауды көрсету динамикалық курсор.
READ_ONLY курсоры үшін FAST_FORWARD аргументін көрсетсеңіз, жасалған курсор деректерге жылдам қол жеткізу үшін оңтайландырылған болады. Бұл аргументті FORWARD_ONLY немесе OPTIMISTIC аргументтерімен бірге пайдалану мүмкін емес.
OPTIMISTIC аргументі арқылы жасалған курсор кейін өзгертілген жолдарды өзгертуге немесе жоюға жол бермейді. курсорды ашу.
TYPE_WARNING аргументін көрсету арқылы сервер ТАҢДАУ сұрауымен үйлеспейтін болса, пайдаланушыға курсор түріне жасырын өзгерту туралы хабарлайды.
Курсорды ашу
Үшін курсорды ашужәне оны курсорды жасау кезінде көрсетілген ТАҢДАУ сұрауының деректерімен толтыру үшін келесі пәрменді пайдаланыңыз:
Кейін курсорды ашуБайланысты SELECT операторы орындалады, оның шығысы көп деңгейлі жадта сақталады.
Курсордан деректерді шығарып алу
Содан кейін курсорды ашуоның мазмұнын (тиісті сұрауды орындау нәтижесі) келесі пәрмен арқылы таңдауға болады:
FIRST параметрін көрсету курсордың ағымдағы жолға айналатын толық нәтижелер жиынының ең бірінші жолын қайтарады.
LAST мәнін көрсету курсордың ең соңғы жолын қайтарады. Ол сондай-ақ ағымдағы сызыққа айналады.
NEXT параметрін көрсету толық нәтиже жиынындағы ағымдағы жолдан кейінгі жолды бірден қайтарады. Енді ол ағымдағы болады. Әдепкі бойынша, FETCH пәрмені жолдарды алу үшін осы әдісті пайдаланады.
PRIOR кілт сөзі ағымдағы жолдың алдындағы жолды қайтарады. Ол токқа айналады.
Аргумент ABSOLUTE (жол_нөмірі | @line_number_variable)курсордың толық нәтижелер жиынындағы абсолютті реттік саны бойынша жолды қайтарады. Жол нөмірін тұрақты мәнді пайдаланып немесе жол нөмірі сақталатын айнымалының аты ретінде көрсетуге болады. Айнымалы бүтін деректер түрі болуы керек. Оң және теріс мәндер көрсетілген. Оң мәнді көрсеткенде, жол жиынның басынан бастап есептеледі, ал теріс мән соңынан есептеледі. Таңдалған жол ағымдағы жолға айналады. Нөл мән көрсетілсе, жол қайтарылмайды.
Аргумент RELATIVE (жолдар саны | @variable жолдар саны)ағымдағы жолдан кейінгі жолдардың көрсетілген саны болатын жолды қайтарады. Жолдардың теріс санын көрсетсеңіз, ағымдағыға дейінгі жолдардың көрсетілген саны болып табылатын жол қайтарылады. Нөл мәнді көрсету ағымдағы жолды қайтарады. Қайтарылған жол ағымдағы жолға айналады.
Кімге жаһандық курсорды ашыңыз, оның атының алдында GLOBAL кілт сөзін көрсету керек. Курсор атауын айнымалы мәнді пайдаланып көрсетуге де болады.
Дизайнда INTO @variable_name [,...n]қайтарылған жолдың сәйкес баған мәндері сақталатын айнымалылар тізімі көрсетіледі. Айнымалы мәндерді көрсету реті курсордағы бағандардың ретімен, ал айнымалының деректер түрі курсор бағанындағы деректер түріне сәйкес болуы керек. Егер INTO конструкциясы көрсетілмесе, онда FETCH командасының әрекеті SELECT командасының әрекетіне ұқсайды - деректер экранда көрсетіледі.
Деректерді өзгерту және жою
Курсорды пайдаланып өзгертулер енгізу үшін келесі пішімде UPDATE пәрменін шығару керек:
Ағымдағы курсор жолының бірнеше бағандарын бір операцияда өзгертуге болады, бірақ олардың барлығы бір кестеге тиесілі болуы керек.
Курсорды пайдаланып деректерді жою үшін келесі пішімде DELETE пәрменін пайдаланыңыз:
Нәтижесінде курсордағы жол жиыны ағымы жойылады.
Курсорды жабу
Жабылғаннан кейін курсор бағдарлама пайдаланушылары үшін қолжетімсіз болады. Жабу кезінде оның жұмысы кезінде орнатылған барлық құлыптар жойылады. Жабуды тек ашық курсорларға қолдануға болады. Жабық, бірақ жоқ бос курсорқайта ашылуы мүмкін. Ашылмаған курсорды жабуға рұқсат етілмейді.
Курсорды босатыңыз
Курсорды жабуонымен байланысты жадты міндетті түрде босатпайды. Кейбір іске асырулар оны DEALLOCATE мәлімдемесі арқылы нақты бөлуі керек. Кейін курсорды босатыңызСондай-ақ жад босатылады, бұл курсор атауын қайта пайдалануға мүмкіндік береді.
Курсордың соңына жеткен-жетпегенін бақылау үшін мына функцияны пайдалану ұсынылады: @@FETCH_STATUS
@@FETCH_STATUS функциясы қайтарады:
0 егер алу сәтті болса;
1 курсордан тыс жолды алу әрекетіне байланысты алу сәтсіз болса;
2 жойылған немесе өзгертілген жолға кіру әрекетіне байланысты алу сәтсіз аяқталса.
DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Сатып алу тізімі" Клиент_курсорын ТАҢДАУ ҮШІН ЖЕРГІЛІКТІ ЖАРИЯЛАУ Клиент коды, компания, тегі Клиенттен ТАПСЫРЫС ҚАЙДА Қала="Мәскеу" КОМПАНИЯ ТАПСЫРЫСЫ, Тегі АШЫҚ klient_cursor КЕЛЕСІ клиent_курсордан @id_kl, @firm, @ATST_ INTO АЛУ =0 БАСТАУ ТАҢДАУ @message="Клиент "+@fam+ "Компания"+ @firm PRINT @message SELECT @message="Өнім атауы Сатып алу күні құны" PRINT @message ТАҢДАУ ҮШІН tovar_cursor МЕҢГЕРІ.Аты,Транзакция.Күні, Өнім .Бағасы* Транзакция.Саны өнімнен алынатын құн ретінде өнімге ІШКІ қосылу транзакциясы. Өнім коды=Транзакция.Өнім коды ҚАЙДА Transaction.Customer коды=@id_kl АШЫҚ tovar_cursor КЕЛЕСІ tovar_cursor ДЕН АЛУ @nam, @d, @p ЕГЕР @@FETCH_STATUS<>0 @@FETCH_STATUS=0 БАСТАУ КЕЗІНДЕ "Сатып алулар жоқ" БАСЫП АЛУ @message=""+@nam+" "+ ТАҢДАУ(@d CHAR(12))+" "+ CAST(@p CA CHAR(6)) БАСЫП АЛУ @message ОРНАТУ @s=@s+@p tovar_cursor КЕЛЕСІНЕН АЛУ @nam, @d, @p END CLOSE tovar_cursor БӨЛУ tovar_cursor SELECT @message="Жалпы құн "+ ШЫҒАРУ(@s CHAR(6)) PRINT @message -- келесі клиентке өту-- klient_cursor INTO @id_kl, @firm, @fam КЕЛЕСІН АЛУ СОҢҒЫ ЖАБУ klient_cursor Клиент_курсорын АЙЫРУ 13.6-мысал. Мәскеуден клиенттер сатып алған тауарлардың тізімін және олардың жалпы құнын көрсетуге арналған курсор.
13.7-мысал.Мәскеуден келген клиенттер үшін айналдырылатын курсорды жасаңыз. Егер телефон нөмірі 1-ден басталса, сол нөмірі бар клиентті жойыңыз және бірінші курсор жазбасында телефон нөміріндегі бірінші санды 4-ке ауыстырыңыз.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) "Тұтынушылар тізімін" БАСЫП АЛУ klient_cursor МЕРЗІМІН ЖАҢАЛЫҚ АЙНАЛДЫРУ ПЕРНЕМЕЛЕРІН ТАҢДАУ ҮШІН Фирманы, тегі, телефоны F. ҚАЙДА Қала ="Мәскеу" КОМПАНИЯНЫҢ ТАПСЫРЫСЫ, Тегі ЖАҢАРТУ ҮШІН АШЫҚ klient_cursor klient_cursor КЕЛЕСІНЕН АЛУ @firm, @fam, @tel WHILE WHILE @@FETCH_STATUS=0 ТАҢДАУ @message="Клиент "+@fam+ " Компания "+ @firm " Phone "+ @tel PRINT @message -- телефон нөмірі 1-ден басталса, -- сол нөмірлі клиентті жою ЕГЕР @tel '1%' ҰНАСА Клиентті ЖОЮ Клиент_курсорының АҒЫМЫ БАСҚА ЖЕРДЕ -- келесіге өту клиент КЛИент_курсордан @firm, @fam, @tel КЕЛЕСІН АЛУ АЯҚТАЛУ Клиент_курсорынан @firm, @fam, @tel КІШІНЕ АБСОЛЮТ 1 АЛУ -- бірінші жазбада телефон нөміріндегі бірінші санды 4 ЖАҢАЛЫҚ Клиент параметрлері телефонына ауыстырыңыз. ='4' + RIGHT(@ tel,LEN(@tel)-1)) Клиент_курсорының АҒЫМЫ ҚАЙДА ТАҢДАУ @message="Клиент "+@fam+" Фирма "+ @firm "Телефон"+ @tel PRINT @message CLOSE klient_cursor Клиент_курсорын БӨУ 13.7-мысал. Мәскеуден келген клиенттер үшін айналдырылатын курсор.
13.8-мысал.Қолданылуы курсор процедураның шығыс параметрі ретінде. Процедура деректер жиынын – өнімдер тізімін қайтарады.
Процедураны шақыру және шығыс курсордан деректерді басып шығару келесі түрде жүзеге асырылады:
ЖАРИЯЛАУ @my_cur МЕҢГЕРІҢІЗ @n VARCHAR (20) ОРЫНДАУ my_proc @cur=@my_cur ШЫҒЫСТЫ АЛЫП АЛУ @my_cur FROM INTO INTO INTO (@@FETCH_STATUS=0) ЖАҒДАЙДА ТАҢДАҢЫЗ. АЯҚТАЛУ @my_cur ЖАБУ @my_cur
