Съхранени процедури и тяхното използване. Използвайте специални заявки. Непоносимост към съхранени функции
Съхранени процедури
Предметът на тази глава е един от най-мощните инструменти, предлагани на разработчиците на приложения за бази данни InterBase за внедряване на бизнес логика.Съхранените процедури (на английски, stoied procedues) ви позволяват да внедрите значителна част от логиката на приложението на ниво база данни и по този начин да увеличите производителността на цялото приложение, централизирайте обработката на данни и намалете количеството код, необходим за изпълнение на задачите. Почти всичко е достатъчно комплексно приложениеБазите данни не са пълни без използването на съхранени процедури.
В допълнение към тези добре известни предимства на използването на запомнени процедури, общи за повечето релационни СУБД, съхранените процедури на InterBase могат да действат като почти пълни набори от данни, което позволява резултатите, които връщат, да бъдат използвани в обикновени SQL заявки.
Често начинаещите разработчици си представят съхранените процедури просто като набор от специфични SQL заявки, които правят нещо в базата данни и има мнение, че работата със запомнени процедури е много по-трудна от прилагането на същата функционалност в клиентско приложение на езика високо ниво
И така, какво представляват съхранените процедури в InterBase?
Съхранената процедура (SP) е част от метаданните на базата данни, която е подпрограма, компилирана във вътрешното представяне на InterBase, написана на специален език, чийто компилатор е вграден в ядрото на сървъра InteiBase
Съхранена процедура може да бъде извикана от клиентски приложения, от тригери и от други запаметени процедури. Съхранената процедура се изпълнява вътре в сървърния процес и може да манипулира данни в базата данни, както и да връща резултатите от нейното изпълнение на клиента, който я е извикал (т.е. тригер, HP, приложение)
Основа мощни способности, вграден в HP, е процедурен език за програмиране, който включва както модифицирани редовни SQL изрази, като INSERT, UPDATE и SELECT, така и инструменти за организиране на разклонения и цикли (IF, WHILE), както и средства за обработка на грешки и изключения Езикът на съхранените процедури ви позволява да прилагате сложни алгоритми за работа с данни и поради фокуса си върху работата с релационни данни HP е много по-компактен от подобни процедури в традиционните езици.
Трябва да се отбележи, че един и същ език за програмиране се използва за тригери, с изключение на редица функции и ограничения. Разликите между подмножеството на езика, използван в тригерите, и езика на HP са обсъдени подробно в главата „Тригери“ (част 1).
Пример за проста съхранена процедура
Време е да създадете първата си запомнена процедура и да я използвате като пример, за да научите процеса на създаване на запомнени процедури. Но първо трябва да кажем няколко думи за това как да работим със запомнените процедури Факт е, че HP дължи репутацията си на неясен и неудобен инструмент на изключително лошите стандартни инструменти за разработване и отстраняване на грешки в запомнени процедури. Документацията на InterBase препоръчва създаване на процедури, използващи SQL скриптови файлове, съдържащи HP текст, които се доставят като входни данни за интерпретатора isql, и по този начин създаване и модифициране на HP If в този SQL скрипт, на етапа на компилиране на текста на процедурата в BLR (около BLR, вижте глава „Структура на базата данни InterBase“ (част 4)), ако възникне грешка, isql ще покаже съобщение за това на кой ред от SQL скрипт файла е възникнала тази грешка. Поправете грешката и направете всичко отначало. Изобщо не се говори за отстраняване на грешки в съвременния смисъл на думата, т.е. за проследяване на изпълнението, с възможност за преглед на междинни стойности на променливи. Очевидно този подход не допринася за нарастването на привлекателността на съхранените процедури в очите на разработчика
Въпреки това, в допълнение към стандартния минималистичен подход към разработката на HP<_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Синтаксисът на запомнените процедури е описан по следния начин:
CREATE PROCEDURE име
[ (параметр тип данни [, параметър тип данни ...]) ]
)]
КАТО
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE var тип данни;
НАЧАЛО
< compound_statement>
[< compound_statement> ...]
КРАЙ
< compound_statement> =
(
Изглежда доста обемно и дори може да е тромаво, но всъщност всичко е много просто.За да овладеете постепенно синтаксиса, нека разгледаме постепенно по-сложни примери.
И така, ето пример за много проста съхранена процедура, която приема две числа като вход, събира ги и връща резултата:
CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg ДВОЙНА ТОЧНОСТ)
ВРЪЩАНЕ (Резултат ДВОЙНА ТОЧНОСТ)
КАТО
НАЧАЛО
Резултат=първи_аргумент+втори_аргумент;
СПИРАНЕ;
КРАЙ
Както можете да видите, всичко е просто: след командата CREATE PROCEDURE се посочва името на новосъздадената процедура (която трябва да е уникална в базата данни) - в този случай SP_Add, след това входните параметри на HP - first_arg и second_arg - са изброени в скоби, разделени със запетаи, указващи техните видове.
Списъкът с входни параметри е незадължителна част от оператора CREATE PROCEDURE - има случаи, когато процедура получава всички данни за своята работа чрез заявки към таблици в тялото на процедурата.
Съхранените процедури използват всякакви скаларни типове данни InteiBase Не позволява използването на масиви и дефинирани от потребителя типове - домейни
Следва ключова дума RETURNS, след което в скоби се изписват върнатите параметри, като се посочват техните типове – в случая само един – Резултат.
Ако процедурата не трябва да връща параметри, тогава думата RETURNS и списъкът с върнати параметри липсват.
След RETURNSQ се посочва ключовата дума AS. Преди да отиде ключовата дума AS заглавие,и след това - техопроцедури.
Тялото на съхранена процедура е списък с описания на нейните вътрешни (локални) променливи (ако има такива, ще ги разгледаме по-подробно по-долу), разделени с точка и запетая (;) и блок от изрази, затворени в операторни скоби НАЧАЛО КРАЙ. В този случай тялото на HP е много просто - искаме да добавим два входни аргумента и да присвоим техния резултат на изходния, след което извикваме командата SUSPEND. Малко по-късно ще обясним същността на действието на тази команда, но засега ще отбележим само, че е необходимо да се прехвърлят параметрите за връщане към мястото, откъдето е извикана съхранената процедура.
Разделители в съхранени процедури
Имайте предвид, че израз в рамките на процедура завършва с точка и запетая (;). Както знаете, точката и запетая е стандартен разделител на команди в SQL - това е сигнал за SQL интерпретатора, че текстът на командата е въведен изцяло и трябва да започне да я обработва. Няма ли да се окаже, че ако SQL интерпретатора намери точка и запетая в средата на HP, ще приеме, че командата е въведена изцяло и ще се опита да изпълни част от съхранената процедура? Това предположение не е без основание. Наистина, ако създадете файл, в който да запишете горния пример, добавите команда за свързване от базата данни и се опитате да изпълните този SQL скрипт с помощта на интерпретатора isql, ще бъде върната грешка поради неочакваното, според мнението на интерпретатора, завършване на командата за създаване на запомнена процедура. Ако създавате съхранени процедури с помощта на SQL скрипт файлове, без да използвате специализирани инструменти за разработчици на InterBase, тогава преди всяка команда за създаване на HP (същото важи и за тригерите) трябва да промените разделителя на командите на скрипта на друг знак, различен от точка и запетая, и след текст HP за да го възстановите обратно. Командата isql, която променя разделителя на SQL клауза, изглежда така:
ЗАДАДЕТЕ СРОК
За типичен случай на създаване на съхранена процедура изглежда така:
ЗАДАДЕ СРОК^;
CREATE PROCEDURE някаква_процедура
... . .
КРАЙ
^
ЗАДАДЕТЕ СРОК ;^
Извикване на съхранена процедура
Но нека се върнем към нашата съхранена процедура. Сега, след като е създаден, трябва да го извикате по някакъв начин, да му предадете параметри и да получите върнатите резултати. Това се прави много лесно - просто напишете SQL заявка като тази:
ИЗБЕРЕТЕ *
ОТ Sp_add(181.35, 23.09)
Тази заявка ще ни върне един ред, съдържащ само едно поле за резултат, което ще съдържа сумата от числата 181,35 и 23,09, т.е. 204,44.
По този начин нашата процедура може да се използва в обикновени SQL заявки, изпълнявани както в клиентски програми, така и в други HP или тригери. Това използване на нашата процедура е възможно чрез използване на командата SUSPEND в края на съхранената процедура.
Факт е, че в InterBase (и във всички негови клонинги) има два вида запаметени процедури: избираеми процедури и изпълними процедури. Разликата в работата на тези два типа HP е, че процедурите за вземане на проби обикновено връщат много набори от изходни параметри, групирани ред по ред, които изглеждат като набор от данни, а изпълнимите процедури могат или да не връщат параметри изобщо, или да връщат само един набор от изходни параметри, изброени в Връщания, където един ред параметри. Процедурите за избор се извикват в заявки SELECT, а изпълнимите процедури се извикват с помощта на командата EXECUTE PROCEDURE.
И двата типа запомнени процедури имат един и същ синтаксис на създаване и формално не се различават, така че всяка изпълнима процедура може да бъде извикана в заявка SELECT и всяка процедура за избор може да бъде извикана с помощта на EXECUTE PROCEDURE. Въпросът е как HP ще се държи кога различни видовеобадете се. С други думи, разликата е в проектирането на процедурата за конкретен тип повикване. Това означава, че процедурата за избор е специално създадена, за да бъде извикана от SELECT заявка, а изпълнимата процедура е специално създадена, за да бъде извикана с помощта на EXECUTE PROCEDURE. Нека да разгледаме какви са разликите в дизайна на тези два вида HP.
За да разберете как работи процедурата за вземане на проби, ще трябва да се задълбочите малко в теорията. Нека си представим обикновена SQL заявка като SELECT ID, NAME FROM Table_example. В резултат на неговото изпълнение получаваме таблица, състояща се от две колони (ID и NAME) и определен брой редове (равен на броя редове в таблицата Table_example). Таблицата, върната от тази заявка, също се нарича набор SQL данниНека помислим как се формира наборът от данни по време на изпълнението на тази заявка.Сървърът, след като получи заявката, определя към кои таблици се отнася, след което открива кое подмножество от записи от тези таблици трябва да бъде включено в резултата от заявката. След това сървърът чете всеки запис, който отговаря на резултатите от заявката, и избира от него задължителни полета(в нашия случай това са ID и NAME) и ги изпраща на клиента. След това процесът се повтаря отново - и така нататък за всеки избран запис.
Цялото това отклонение е необходимо, за да може скъпият читател да разбере, че всички SQL набори от данни се генерират ред по ред, включително в съхранени процедури! И основната разлика между процедурите за извличане и изпълнимите процедури е, че първите са проектирани да връщат много редове, докато вторите са проектирани да връщат само един. Ето защо те се използват по различен начин: процедурата select се извиква с помощта на командата SELECT, която "изисква" процедурата да се откаже от всички записи, които може да върне. Изпълнимата процедура се извиква с помощта на EXECUTE PROCEDURE, която „изважда“ само един ред от HP и игнорира останалите (дори и да съществуват!).
Нека да разгледаме примерна процедура за вземане на проби, за да стане по-ясно. За > прошка, нека създадем съхранена процедура, която работи точно като заявка SELECT ID, NAME FROM Table_Example, тоест тя просто избира полетата ID и NAME от цялата таблица. Ето този пример:
СЪЗДАВАНЕ НА ПРОЦЕДУРА Simple_Select_SP
СЕ ЗАВРЪЩА (
procID ЦЯЛО ЧИСЛО,
procNAME VARCHAR(80))
КАТО
НАЧАЛО
ЗА
ИЗБЕРЕТЕ ИД, ИМЕ ОТ table_example
INTO:procID, :procNAME
НАПРАВЕТЕ
НАЧАЛО
СПИРАНЕ;
КРАЙ
КРАЙ
Нека да разгледаме стъпките на тази процедура, наречена Simple_Select_SP. Както можете да видите, той няма входни параметри и има два изходни параметъра - ID и NAME. Най-интересното, разбира се, се крие в тялото на процедурата. Конструкцията FOR SELECT се използва тук:
ЗА
ИЗБЕРЕТЕ ИД, ИМЕ ОТ table_example
INTO:procID, :procNAME
НАПРАВЕТЕ
НАЧАЛО
/*направи нещо с променливите procID и procName*/
КРАЙ
Тази част от кода означава следното: за всеки ред, избран от таблицата Table_example, поставете избраните стойности в променливите procID и procName и след това направете нещо с тези променливи.
Може да направите изненадана физиономия и да попитате: „Променливи? Какви други променливи? 9“ Изненадата в тази глава е, че можем да използваме променливи в запомнени процедури. На езика на HP можете да декларирате както свои собствени локални променливи вътре в процедура, така и да използвате входни и изходни параметри като променливи.
За да декларирате локална променлива в съхранена процедура, трябва да поставите нейното описание след ключовата дума AS и преди първата дума BEGIN Описанието на локална променлива изглежда така:
ДЕКЛАРИРАНЕ НА ПРОМЕНЛИВА
Например, за да декларирате целочислена локална променлива Mylnt, трябва да вмъкнете следната декларация между AS и BEGIN
ДЕКЛАРИРАНЕ НА ПРОМЕНЛИВА Mylnt INTEGER;
Променливите в нашия пример започват с двоеточие. Това се прави, защото те са достъпни в рамките на командата FOR SELECT SQL, така че за да се направи разлика между полетата в таблиците, които се използват в SELECT и променливите, последните трябва да бъдат предхождани от двоеточие. В крайна сметка променливите могат да имат точно същото име като полетата в таблиците!
Но двоеточието преди име на променлива трябва да се използва само в SQL заявки. Извън текстовете променливата се споменава без двоеточие, например:
procName="някакво име";
Но да се върнем към тялото на нашата процедура. Клаузата FOR SELECT връща данни не под формата на таблица - набор от данни, а ред по ред. Всяко върнато поле трябва да бъде поставено в собствена променлива: ID => procID, NAME => procName. В частта DO тези променливи се изпращат на клиента, който е извикал процедурата с помощта на командата SUSPEND
Така командата FOR SELECT...DO преминава през записите, избрани в частта SELECT на командата. В тялото на цикъла, образуван от частта DO, следващият генериран запис се прехвърля към клиента с помощта на командата SUSPEND.
И така, процедурата за избор е предназначена да върне един или повече редове, за които е организиран цикъл в тялото на HP, който запълва получените променливи параметри. И в края на тялото на този цикъл винаги има команда SUSPEND, която ще върне следващия ред от данни на клиента.
Изявления за цикли и разклонения
В допълнение към командата FOR SELECT...DO, която организира цикъл през записите на селекция, има друг тип цикъл - WHILE...DO, който ви позволява да организирате цикъл въз основа на проверка на всякакви условия. Ето пример за HP, използващ WHILE..DO цикъла. Тази процедура връща квадратите на цели числа от 0 до 99:
CREATE PROCEDJRE QUAD
ВРЪЩА (ЦЯЛО ЧИСЛО КВАДРАТ)
КАТО
DECLARE VARIABLE I INTEGER;
НАЧАЛО
I = 1;
Докато аз<100) DO
НАЧАЛО
КВАДРАТ= I*I;
I=I+1;
СПИРАНЕ;
КРАЙ
КРАЙ
В резултат на изпълнение на заявката SELECT FROM QUAD ще получим таблица, съдържаща една колона QUADRAT, която ще съдържа квадратите на цели числа от 1 до 99
В допълнение към итерирането на резултатите от SQL пример и класически цикъл, езикът на съхранената процедура използва оператора IF...THEN..ELSE, който ви позволява да организирате разклоняване в зависимост от изпълнението на всякакви условия. Синтаксисът му е подобен към повечето оператори за разклоняване в езици за програмиране на високо ниво, като Pascal и C.
Нека да разгледаме по-сложен пример на запомнена процедура, която прави следното.
- Изчислява средната цена в таблицата Table_example (вижте глава "Таблици Първични ключове и генератори")
- След това за всеки запис в таблицата прави следната проверка: ако съществуващата цена (PRICE) е по-висока от средната цена, тогава задава цена, равна на средната цена, плюс определен фиксиран процент
- Ако съществуващата цена е по-малка или равна на средната цена, тогава се задава цена, равна на предишната цена, плюс половината от разликата между предишната и средната цена.
- Връща всички променени редове в таблицата.
Първо, нека да определим името на HP, както и входни и изходни параметри.Всичко това е написано в заглавката на съхранената процедура.
СЪЗДАВАНЕ НА ПРОЦЕДУРА Увеличаване на цените (
Percent2lncrease ДВОЙНА ТОЧНОСТ)
ВРЪЩА (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECISION AS
Процедурата ще се нарича IncreasePrices, има един входен параметър Peiceni21nciease от тип DOUBLE PRECISION и 3 изходни параметъра - ID, NAME и new_pnce. Обърнете внимание, че първите два изходни параметъра имат същите имена като полетата в таблицата Table_example, с която ще работим.Това е разрешено от правилата на езика на съхранената процедура.
Сега трябва да декларираме локална променлива, която ще се използва за съхраняване на средната стойност.Декларацията ще изглежда така:
DECLARE VARIABLE avg_price DOUBLE PRECISION;
Сега нека преминем към тялото на съхранената процедура.Отворете тялото на HP
ключова дума BEGIN.
Първо трябва да изпълним първата стъпка от нашия алгоритъм - да изчислим средната цена. За да направим това, ще използваме следния тип заявка:
ИЗБЕРЕТЕ СР.(Цена_l)
ОТ Table_Example
INTO:средна_цена,-
Тази заявка използва агрегатната функция AVG, която връща средната стойност на полето PRICE_1 сред избраните редове на заявката – в нашия случай средната стойност на PRICE_1 в цялата таблица Table_example. Стойността, върната от заявката, се поставя в променливата avg_price. Имайте предвид, че променливата avg_pnce се предшества от двоеточие, за да се разграничи от полетата, използвани в заявката.
Особеност на това исканее, че винаги връща точно един запис. Такива заявки се наричат сингълтън заявки и само такива селекции могат да се използват в запомнени процедури. Ако дадена заявка върне повече от един ред, тогава тя трябва да бъде форматирана като FOR SELECT...DO конструкция, която организира цикъл за обработка на всеки върнат ред
И така, получихме средната цена. Сега трябва да прегледате цялата таблица, да сравните ценовата стойност във всеки запис със средната цена и да предприемете съответните действия
От самото начало организираме търсенето на всеки запис от таблицата Table_example
ЗА
ИЗБЕРЕТЕ ID, ИМЕ, PRICE_1
ОТ Table_Example
INTO:ID, :NAME, :нова_цена
НАПРАВЕТЕ
НАЧАЛО
/*_тук описваме всеки запис*/
КРАЙ
Когато тази конструкция се изпълни, данните ще бъдат извлечени от таблицата Table_example ред по ред и стойностите на полето във всеки ред ще бъдат присвоени на променливите ID, NAME и new_pnce. Вие, разбира се, помните, че тези променливи са декларирани като изходни параметри, но няма нужда да се притеснявате, че избраните данни ще бъдат върнати като резултати: фактът, че на изходните параметри е присвоено нещо, не означава, че клиентът, който извиква HP веднага ще получи тези стойности! Параметрите се предават само когато се изпълни командата SUSPEND, а преди това можем да използваме изходните параметри като обикновени променливи - в нашия пример правим точно това с параметъра new_price.
И така, вътре в тялото на цикъла BEGIN... END можем да обработваме стойностите на всеки ред. Както си спомняте, трябва да разберем как съществуващата цена се сравнява със средната и да предприемем подходящи действия. Ние прилагаме тази процедура за сравнение с помощта на израза IF:
IF (нова_цена > средна_цена) THEN /*ако съществуващата цена е по-висока от средната цена*/
НАЧАЛО
/*тогава ще определим нова цена, равна на средната цена, плюс фиксиран процент */
нова_цена = (ср._цена + ср._цена*(Процент2Увеличение/100));
АКТУАЛИЗАЦИЯ Table_example
ЗАДАДЕ ЦЕНА_1 = :нова_цена
WHERE ID = :ID;
КРАЙ
ДРУГО
НАЧАЛО
/* Ако съществуващата цена е по-малка или равна на средната цена, тогава задайте цена, равна на предишната цена, плюс половината от разликата между предишната и средната цена */
нова_цена = (нов_pnce + ((ср._pnce нова_цена)/2)) ;
АКТУАЛИЗАЦИЯ Table_example
ЗАДАДЕ ЦЕНА_1 = :нова_цена
WHERE ID = .ID;
КРАЙ
Както можете да видите, резултатът е доста голяма IF конструкция, която би била трудна за разбиране, ако не бяха коментарите, затворени в /**/ символите.
За да променим цената според изчислената разлика, ще използваме командата UPDATE, която ни позволява да променим съществуващи записи- едно или повече. За да посочим недвусмислено в кой запис трябва да се промени цената, използваме полето за първичен ключ в условието WHERE, като го сравняваме със стойността на променливата, която съхранява стойността на ID за текущия запис: ID=:ID. Обърнете внимание, че променливата ID се предхожда от двоеточие.
След изпълнение на конструкцията IF...THEN...ELSE, променливите ID, NAME и new_price съдържат данни, които трябва да върнем на клиента, извикал процедурата. За да направите това, след IF трябва да вмъкнете командата SUSPEND, която ще изпрати данните откъдето е извикано HP.По време на трансфера процедурата ще бъде суспендирана, а когато се изисква HP нов вход, след това ще бъде продължено отново - и това ще продължи, докато FOR SELECT...DO премине през всички записи на своята заявка.
Трябва да се отбележи, че в допълнение към командата SUSPEND, която само спира запомнената процедура, има команда EXIT, която прекратява запомнената процедура след предаване на низа. Командата EXIT обаче се използва доста рядко, тъй като е необходима главно за прекъсване на цикъла при достигане на условие
Въпреки това, в случай, че процедурата е извикана с оператор SELECT и завършена с EXIT, последният извлечен ред няма да бъде върнат. Тоест, ако трябва да прекъснете процедурата и все пак да получите този низ, трябва да използвате последователността
СПИРАНЕ;
ИЗХОД;
Основната цел на EXIT е да получава единични набори от данни, върнати параметри чрез извикване на EXECUTE PROCEDURE. В този случай стойностите на изходните параметри са зададени, но наборът от SQL данни не се генерира от тях и изпълнението на процедурата приключва.
Нека запишем целия текст на нашата съхранена процедура, за да можем да уловим нейната логика с един поглед:
СЪЗДАВАНЕ НА ПРОЦЕДУРА Увеличаване на цените (
Процент 2 Увеличете ДВОЙНА ТОЧНОСТ)
ВРЪЩА (ID INTEGER, NAME VARCHAR(80),
new_price ДВОЙНА ТОЧНОСТ) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
НАЧАЛО
ИЗБЕРЕТЕ СР.(Цена_l)
ОТ Table_Example
INTO:средна_цена;
ЗА
ИЗБЕРЕТЕ ID, ИМЕ, PRICE_1
ОТ Table_Example
INTO:ID, :NAME, :нова_цена
НАПРАВЕТЕ
НАЧАЛО
/*обработете всеки запис тук*/
IF (new_pnce > avg_price) THEN /*ако съществуващата цена е по-висока от средната цена*/
НАЧАЛО
/*задаване на нова цена, равна на средната цена плюс фиксиран процент */
нова_цена = (ср._цена + ср._цена*(Процент2lncrease/100));
АКТУАЛИЗАЦИЯ Table_example
ЗАДАДЕ ЦЕНА_1 = :нова_цена
WHERE ID = :ID;
КРАЙ
ДРУГО
НАЧАЛО
/* Ако съществуващата цена е по-малка или равна на средната цена, тогава се задава цена, равна на предишната цена плюс половината от разликата между предишната и средната цена */
нова_цена = (нова_цена + ((ср._цена - нова_цена)/2));
АКТУАЛИЗАЦИЯ Table_example
ЗАДАДЕ ЦЕНА_1 = :нова_цена
WHERE ID = :ID;
КРАЙ
СПИРАНЕ;
КРАЙ
КРАЙ
Този пример за запомнена процедура илюстрира използването на базови езикови конструкции и тригери за запаметена процедура. След това ще разгледаме начини за използване на запомнени процедури за решаване на някои често срещани проблеми.
Рекурсивни съхранени процедури
Съхранените процедури на InterBase могат да бъдат рекурсивни. Това означава, че съхранената процедура може да се самоизвиква. Разрешени са до 1000 нива на влагане на съхранени процедури, но трябва да помним, че свободните ресурси на сървъра може да свършат преди да бъде достигнато максималното влагане на HP.
Една често срещана употреба на запомнените процедури е обработката на дървовидни структури, съхранени в база данни. Дърветата често се използват в състава на продуктите, складовете, персонала и други общи приложения.
Нека да разгледаме пример за съхранена процедура, която избира всички продукти от определен тип, започвайки от определено ниво на влагане.
Нека имаме следната формулировка на проблема: имаме директория със стоки с йерархична структура от следния тип:
Стоки
- уреди
- Хладилници
- Трикамерен
- Двукамерен
- Еднокамерна
-
Перални машини
- Вертикално
- Фронтален
- Класически
- Тесен
- Компютърна технология
....
Тази структура на директорията с продуктови категории може да има разклонения с различна дълбочина. и също се увеличават с времето. Нашата задача е да осигурим избор на всички крайни елементиот директорията с "разгъване на пълното име", започвайки от произволен възел. Например, ако изберем възела „Перални машини“, тогава трябва да получим следните категории:
Перални - Вертикални
Перални - Front Classic
Перални - Предни Тесни
Нека да дефинираме структурата на таблицата за съхраняване на информация от продуктовата директория. Използваме опростена схема за организиране на дървото в една таблица:
СЪЗДАВАНЕ НА ТАБЛИЦА GoodsTree
(ID_GOOD INTEGER NOT NULL,
ID_PARENT_GOOD ЦЯЛО ЧИСЛО,
GOOD_NAME VARCHAR(80),
ограничение pkGooci първичен ключ (ID_GOOD));
Създаваме една таблица GoodsTree, в която има само 3 полета: ID_GOOD - интелигентният идентификатор на категорията, ID_PARENT_GOOD - идентификаторът на компанията майка за тази категория и GOOD_NAME - името на категорията. За да гарантираме целостта на данните в тази таблица, ще наложим ограничение за външен ключ на тази таблица:
ALTER TABLE GoodsTree
ДОБАВЯНЕ НА ОГРАНИЧЕНИЕ FK_goodstree
ВЪНШЕН КЛЮЧ (ID_PARENT_GOOD)
РЕФЕРЕНЦИИ GOODSTPEE (ID__GOOD)
Таблицата препраща към себе си и този външен ключ следи това. така че таблицата да не съдържа препратки към несъществуващи родители и също така предотвратява опитите за изтриване на продуктови категории, които имат деца.
Нека въведем следните данни в нашата таблица:
| ID_GOOD 1
|
ID_PARENT_GOOD 0
|
ХУБАВО ИМЕ СТОКИ |
Сега, когато имаме място за съхраняване на данните, можем да започнем да създаваме съхранена процедура, която ще изведе всички „крайни“ продуктови категории в „разширена“ форма – например за категорията „Трикамерен“, пълната категория име ще бъде "Домакински уреди Хладилници" Трикамерни".
Съхранените процедури, които обработват дървовидни структури, имат своя собствена терминология. Всеки елемент от дървото се нарича възел; и връзката между възлите, които се препращат един към друг, се нарича връзка родител-дете. Възлите, които са в самия край на дървото и нямат деца, се наричат "листа".
За тази съхранена процедура входният параметър ще бъде идентификаторът на категорията, от който ще трябва да започнем разбивката. Съхранената процедура ще изглежда така:
СЪЗДАВАНЕ НА ПРОЦЕДУРА GETFULLNAME (ID_GOOD2SHOW INTEGER)
ВРЪЩАНЕ (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD ЦЯЛО ЧИСЛО)
КАТО
ДЕКЛАРИРАНЕ НА ПРОМЕНЛИВА CURR_CHILD_NAME VARCHAR(80);
НАЧАЛО
/*0 организиране на външен FOR цикълИЗБЕРЕТЕ на непосредствени наследници на продукта с ID_GOOD=ID_GOOD2SHOW */
ЗА ИЗБЕРЕТЕ gtl.id_good, gtl.good_name
ОТ GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :пълно_име_на_стока
НАПРАВЕТЕ
НАЧАЛО
/"Проверете с помощта на функцията EXISTS, която връща TRUE, ако заявката в скоби връща поне един ред. Ако намереният възел с ID_PARENT_GOOD = ID_CHILD_GOOD няма деца, тогава той е „лист“ на дървото и е включен в резултатите */
АКО (НЕ СЪЩЕСТВУВА(
ИЗБЕРЕТЕ * ОТ GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
ТОГАВА
НАЧАЛО
/* Предаване на “листа” на дървото към резултатите */
СПИРАНЕ;
КРАЙ
ДРУГО
/* За възли, които имат деца*/
НАЧАЛО
/*запазване на името на родителския възел във временна променлива */
CURR_CHILD_NAME=пълно_име_на_стока;
/* изпълнява тази процедура рекурсивно */
ЗА
ИЗБЕРЕТЕ ID_CHILD_GOOD, full_goods_name
ОТ GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :пълно_име_на_стока
ЗАПОЧНЕТЕ
/*добавете името на родителския възел към намереното дъщерно име, като използвате операцията за конкатенация на низ || */
full_goods_name=CURR_CHILD_NAME| " " | пълно_име_на_стока,-
СПИРАНЕ; /* връща пълното име на продукта*/
КРАЙ
КРАЙ
КРАЙ
КРАЙ
Ако изпълним тази процедура с входен параметър ID_GOOD2SHOW= 1, ще получим следното:
Както можете да видите, използвайки рекурсивна съхранена процедура, преминахме през цялото дърво на категориите и показахме пълното име на категориите „листа“, които се намират в самите върхове на клоните.
Заключение
Това завършва нашето разглеждане на основните характеристики на езика на съхранената процедура. Очевидно е невъзможно да се овладее напълно разработването на запомнени процедури само в една глава, но тук се опитахме да въведем и обясним основните концепции, свързани със запомнените процедури. Описаните проекти и техники за проектиране на HP могат да бъдат приложени в повечето приложения за бази данни
Някои от важните въпроси, свързани с разработването на запомнени процедури, ще бъдат разгледани в следващата глава - „Разширени възможности на езика за запомнени процедури на InterBase“, която е посветена на обработката на изключения, разрешаването на ситуации на грешка в запомнените процедури и работата с масиви.
Съхранена процедура - обект на база данни, който е набор от SQL инструкции, който се компилира веднъж и се съхранява на сървъра. Съхранените процедури са много подобни на обикновените езикови процедури на високо ниво, те могат да имат входни и изходни параметри и локални променливи, могат да извършват числени изчисления и операции върху символни данни, резултатите от които могат да бъдат присвоени на променливи и параметри. Съхранените процедури могат да изпълняват стандартни операции с бази данни (както DDL, така и DML). В допълнение, съхранените процедури позволяват цикли и разклонения, т.е. те могат да използват инструкции за контрол на процеса на изпълнение.
Съхранените процедури са подобни на дефинираните от потребителя функции (UDF). Основната разлика е, че дефинираните от потребителя функции могат да се използват като всеки друг израз в SQL оператор, докато съхранените процедури трябва да се извикват с помощта на функцията CALL:
Процедура по обаждането (…)
ИЗПЪЛНИТЕ процедура (...)
Съхранените процедури могат да върнат множество резултати, тоест резултатите от заявка SELECT. Такива набори от резултати могат да се обработват с помощта на курсори, други съхранени процедури, които връщат указател на набор от резултати, или приложения. Съхранените процедури могат също да съдържат декларирани променливи за обработка на данни и курсори, които ви позволяват да преминавате през множество редове в таблица. Стандартът SQL предоставя IF, LOOP, REPEAT, CASE и много други за работа. Съхранените процедури могат да приемат променливи, да връщат резултати или да променят променливи и да ги връщат, в зависимост от това къде е декларирана променливата.
Изпълнението на запомнените процедури варира от една СУБД до друга. Повечето големи доставчици на бази данни ги поддържат под една или друга форма. В зависимост от СУБД, запомнените процедури могат да бъдат реализирани на различни езици за програмиране, като SQL, Java, C или C++. Съхранените процедури, които не са написани на SQL, могат или не могат да изпълняват SQL заявки сами.
Отзад
Споделяне на логика с други приложения. Съхранените процедури капсулират функционалност; това осигурява свързаност за достъп до данни и управление в различни приложения.
Изолиране на потребителите от таблиците на базата данни. Това ви позволява да дадете достъп до съхранени процедури, но не и до самите данни в таблицата.
Осигурява защитен механизъм. Съгласно предходната точка, ако имате достъп до данни само чрез съхранени процедури, никой друг не може да изтрие вашите данни чрез командата SQL DELETE.
Подобрено изпълнение в резултат на намален мрежов трафик. С помощта на съхранени процедури могат да се комбинират множество заявки.
Против
Повишено натоварване на сървъра на базата данни поради факта, че по-голямата част от работата се извършва от страната на сървъра и по-малко от страната на клиента.
Ще трябва да научите много. Ще трябва да научите синтаксиса на израза на MySQL, за да напишете своите съхранени процедури.
Вие дублирате логиката на вашето приложение на две места: код на сървъра и код за съхранени процедури, като по този начин усложнявате процеса на манипулиране на данни.
Мигрирането от една СУБД към друга (DB2, SQL Server и др.) може да доведе до проблеми.
Цел и ползи от съхранените процедури
Съхранените процедури подобряват производителността, подобряват възможностите за програмиране и поддържат функции за защита на данните.
Вместо да съхраняват често използвана заявка, клиентите могат да препратят към съответната съхранена процедура. Когато се извика съхранена процедура, нейното съдържание незабавно се обработва от сървъра.
В допълнение към действителното изпълнение на заявката, съхранените процедури също ви позволяват да извършвате изчисления и да манипулирате данни - промяна, изтриване, изпълнение на DDL изрази (не във всички СУБД!) и извикване на други съхранени процедури и извършване на сложна транзакционна логика. Един оператор ви позволява да извикате сложен скрипт, съдържащ се в съхранена процедура, като избягвате изпращането на стотици команди през мрежата и по-специално необходимостта от прехвърляне на големи количества данни от клиента към сървъра.
В повечето СУБД първия път, когато се изпълнява съхранена процедура, тя се компилира (разбира се и се генерира план за достъп до данни). В бъдеще обработката му е по-бърза. СУБД Oracle интерпретира съхранения процедурен код, съхраняван в речника на данните. Започвайки с Oracle 10g, се поддържа така нареченото естествено компилиране на съхранен процедурен код в C и след това в машинния код на целевата машина, след което, когато се извика съхранена процедура, нейният компилиран обектен код се изпълнява директно.
Възможности за програмиране
Създадената съхранена процедура може да бъде извикана по всяко време, което осигурява модулност и насърчава повторното използване на кода. Последното прави базата данни по-лесна за поддръжка, тъй като става изолирана от променящите се бизнес правила. Можете да промените съхранена процедура в съответствие с новите правила по всяко време. След това всички приложения, които го използват, автоматично ще влязат в съответствие с новите бизнес правила без директна промяна.
Безопасност
Използването на съхранени процедури ви позволява да ограничите или напълно елиминирате директния потребителски достъп до таблиците на базата данни, оставяйки на потребителите само разрешения за изпълнение на съхранени процедури, които осигуряват индиректен и строго регулиран достъп до данни. В допълнение, някои СУБД поддържат текстово криптиране (обвиване) на съхранена процедура.
Тези функции за сигурност позволяват структурата на базата данни да бъде изолирана от потребителя, гарантирайки целостта и надеждността на базата данни.
Вероятността от действия като SQL инжектиране е намалена, тъй като добре написаните съхранени процедури допълнително проверяват входните параметри, преди да предадат заявката към СУБД.
Внедряване на съхранени процедури
Съхранените процедури обикновено се създават с помощта на SQL езикили конкретната му реализация в избраната СУБД. Например за тези цели в СУБД Microsoft SQLСървърът има език Transact-SQL, в Oracle - PL/SQL, в InterBase и Firebird - PSQL, в PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, в IBM DB2 - SQL/PL ( инж. ), в Informix - SPL. MySQL следва стандарта SQL:2003 доста близо, езикът му е подобен на SQL/PL.
Някои СУБД позволяват използването на съхранени процедури, написани на всеки език за програмиране, който може да създава независими изпълними файлове, например C++ или Delphi. В терминологията на Microsoft SQL Server такива процедури се наричат разширени съхранени процедури и са просто функции, съдържащи се в Win32 DLL. И например в Interbase и Firebird функциите, извикани от DLL/SO, имат различно име - UDF (User Defined Function). MS SQL 2005 въведе възможността за писане на запомнени процедури на всеки .NET език, а разширените запомнени процедури се планира да бъдат изоставени в бъдеще. СУБД Oracle от своя страна позволява писане на съхранени процедури език Java. В IBM DB2 писането на съхранени процедури и функции на конвенционалните езици за програмиране е традиционен начин, поддържан от самото начало, а процедурното разширение на SQL беше добавено към тази СУБД само в доста късни версии, след включването му в стандарта ANSI. Informix също поддържа процедури в Java и C.
В СУБД Oracle съхранените процедури могат да се комбинират в така наречените пакети. Пакетът се състои от две части - спецификация на пакета, която определя дефиницията на съхранена процедура, и тяло на пакета, което съдържа нейната реализация. Така Oracle ви позволява да отделите интерфейса на програмния код от неговата реализация.
В IBM DB2 СУБД запаметените процедури могат да се комбинират в модули.
Синтаксис
СЪЗДАВАНЕ НА ПРОЦЕДУРА `p2`()
SQL ДЕФИНЕР ЗА СИГУРНОСТ
КОМЕНТАР "Процедура"
ИЗБЕРЕТЕ „Здравей свят!“;
Първата част от кода създава съхранена процедура. Следващият съдържа незадължителни параметри. След това идва името и накрая тялото на самата процедура.
4 характеристики на съхранена процедура:
Език: За целите на преносимостта по подразбиране е SQL.
Детерминиран: ако процедурата винаги връща един и същ резултат и приема едни и същи входни параметри. Това е за процеса на репликация и регистрация. Стойността по подразбиране НЕ е ДЕТЕРМИНИСТИЧНА.
SQL сигурност: потребителските права се проверяват по време на разговора. INVOKER е потребителят, който извиква съхранената процедура. DEFINER е “създателят” на процедурата. Стойността по подразбиране е DEFINER.
Коментар: За целите на документацията стойността по подразбиране е ""
Извикване на съхранена процедура
CALL име на_съхранена_процедура (param1, param2, ....)
CALL procedure1(10, "низов параметър", @parameter_var);
Модифициране на съхранена процедура
MySQL има оператор ALTER PROCEDURE за промяна на процедурите, но той е подходящ само за промяна на определени характеристики. Ако трябва да промените параметрите или тялото на процедура, трябва да я изтриете и да я създадете отново.
Премахванесъхраненипроцедури
ОТПУСКАНЕ НА ПРОЦЕДУРА, АКО СЪЩЕСТВУВА p2;
Това е проста команда. Операторът IF EXISTS улавя грешка, ако такава процедура не съществува.
Настроики
CREATE PROCEDURE proc1(): празен списък с параметри
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): един входен параметър. Думата IN не е задължителна, защото параметрите по подразбиране са IN (in).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): върнат е един параметър.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): един параметър, както вход, така и връщане.
Синтаксисът за деклариране на променлива изглежда така:
DECLARE varname DATA-TYPE DEFAULT стойност по подразбиране;
В предишната статия от тази поредица разгледахме как можете да извличате данни от таблици, да променяте структурата им, да създавате, модифицирате и изтривате бази данни и обектите, които съдържат. В тази статия ще говорим по-подробно за обектите, характерни за сървърните СУБД: изгледи, тригери и съхранени процедури.
В първата статия от тази серия, публикувана в брой 3’2000 на нашето списание, отбелязахме, че повечето съвременни сървърни СУБД поддържат изгледи, тригери и съхранени процедури. Изгледите се поддържат и от много настолни СУБД, например Access, dBase, Clipper.
Трябва да се отбележи, че тригерите и съхранените процедури обикновено са написани на езици за програмиране, които са процедурни разширения на езика SQL. Тези разширения съдържат оператори, които ви позволяват да описвате алгоритми, например do...while, if...then...else, които не присъстват в самия език SQL (ако си спомняте, SQL е непроцедурен език и в него можете да формулирате задача, но не можете да описвате алгоритми за нейното изпълнение). За разлика от съвместимия със стандарта SQL език, неговите процедурни разширения не са стандартизирани по никакъв начин и различните СУБД използват различни синтактични конструкции, за да реализират едни и същи алгоритмични конструкции, но обсъждането на разликите в синтаксиса на SQL разширенията за различни СУБД е извън обхвата на тази статия.
За да илюстрираме как можете да използвате изгледи, тригери и съхранени процедури, ние избрахме Microsoft SQL Server 7.0 и базата данни NorthWind, включена в тази СУБД.
Преди да изпълните примерите, моля, имайте предвид, че внедряването и съхранението на тригери и съхранени процедури в СУБД, която използвате, може да се различава от тези, показани в тази статия. Освен това трябва да имате съответните разрешения, предоставени от администратора на вашата база данни, за да създавате сървърни обекти.
Имайте предвид също, че някои ODBC драйвери не поддържат извикване на съхранени процедури от клиентски приложения, дори ако се поддържат от самата СУБД. В този случай обаче съхранените процедури все още могат да бъдат извикани от тригери.
Нека започнем с изгледи, след това да обсъдим запаметените процедури и да завършим главата с преглед на тригерите.
Представителство
Изгледът е виртуална таблица, обикновено съдържаща набор от колони от една или повече таблици. В действителност изгледът не съдържа данни, а само SQL заявка от типа SELECT, указваща кои данни и от кои таблици трябва да се вземат при достъп до този изглед. От тази гледна точка изгледът е съхранена заявка.
В повечето случаи изгледите се използват за гарантиране на сигурността на данните. Например, някои категории потребители може да имат достъп до изглед, но не и до таблиците, чиито данни го формират; Освен това SQL заявката може да съдържа параметър USER (името, под което потребителят е влязъл), в който случай наличните данни при достъп до изгледа ще зависят от потребителското име.
Следните са основните характеристики на изгледите:
- изгледите се държат като таблици;
- изгледите не съдържат данни;
- изгледите могат да използват данни от повече от една таблица.
Можем да използваме оператора SQL CREATE VIEW, за да създадем изглед, оператора ALTER VIEW, за да го променим, и оператора DROP VIEW, за да го изтрием.
Ще започнем с командата CREATE VIEW, която ни позволява да създадем изглед за текущата база данни.
Клауза CREATE VIEW
Синтаксисът на оператора за създаване на изглед прилича на оператор SQL SELECT с няколко допълнителни ключови думи. По-долу е неговият опростен синтаксис:
CREATE VIEW view_name AS select_statement
Аргументът view_name указва името на изгледа. Ключовата дума, използвана в Microsoft SQL Server, ви позволява да скриете оригиналния текст на израза CREATE VIEW в таблицата syscomments.
Ключовата дума AS указва коя SELECT заявка действително ще бъде изпълнена при достъп до изгледа. Имайте предвид, че тази заявка не може да съдържа ключовите думи ORDER BY, COMPUTE или COMPUTE BY, INTO и не може да препраща към временна таблица.
За да промените предварително създаден изглед, използвайте клаузата ALTER VIEW, описана накратко в следващия раздел.
Оферта за ОТКАЗВАНЕ
Тази клауза се използва за премахване на изглед от базата данни. Имайте предвид, че когато изтриете таблица от базата данни, всички изгледи, които я препращат, също се изтриват. Използвайки тази клауза, трябва да посочим името на изгледа, който да бъде изтрит. След като даден изглед бъде изтрит, цялата информация за него се изтрива от системните таблици.
Друг случай, при който изгледът трябва да бъде изтрит, е ако структурата на таблиците, на които се основава, се е променила след създаването на изгледа. В този случай можете да изтриете изгледа и след това да го създадете отново, като използвате клаузата CREATE VIEW.
Създаване и използване на изгледи
Клаузата CREATE VIEW се използва за създаване на изгледи, които ви позволяват да извличате данни, които отговарят на конкретни изисквания. Изгледът се създава в текущата база данни и се съхранява като отделен обект на база данни.
Най-добрият начин да създадете изглед е да създадете оператор SELECT и след като го тествате, да добавите липсващата част от оператора CREATE VIEW. Нека да разгледаме изходния код за изгледа Products by Category в базата данни NorthWind (списък 1).
Първият ред е маркиран с удебелен, представя как SQL операторът за създаване на изглед се различава от обикновената SELECT заявка, която върши работата по избиране на данни. Клаузата SELECT, съдържаща се в този изглед, избира полета от две таблици - полето CategoryName от таблицата CATEGORIES и полетата ProductName, QuantityPerUnit, UnitsInStock, Disontinued от таблицата PRODUCTS. След това данните от двете таблици се свързват с помощта на полето CategoryID и само тези продукти, които все още са на склад (вижте критерия след ключовата дума WHERE), се включват в резултантния набор от данни. Резултатът от достъпа до този изглед е показан на фиг. 1.
Сега нека създадем изглед, показващ всички територии в източния регион. Този изглед се основава на следната заявка (списък 2).
След като сме сигурни, че клаузата SELECT връща желаните от нас резултати, добавяме оператор CREATE VIEW и именуваме изгледа, който създаваме, EASTTERR (списък 3).
Вместо да създавате текста на презентацията ръчно, можете да използвате визуални инструменти, които обикновено са включени в СУБД. На фиг. Фигура 2 показва как същият изглед може да бъде създаден с помощта на инструмента View Designer, който е интегрална част Enterprise Manager, включен в Microsoft SQL Server.
Горната част на View Designer ви позволява да посочите как таблиците са свързани и кои полета ще се показват в изгледа. По-долу можете да посочите псевдоними за таблици и полета, ограничения за техните стойности и метод на показване. По-долу са изходният текст на изгледа и резултатите от неговото изпълнение.
Преди да приключим кратък прегледидеи, нека поговорим малко за това как да получите Допълнителна информацияза тях. В Microsoft SQL Server 7.0 можем да използваме следните системни съхранени процедури:
- Можете да използвате системната съхранена процедура sp_help, за да получите информация за изглед. Например sp_help EastTerr ще върне информация за новосъздадения изглед;
- Можете да използвате съхранената процедура sp_helptext, за да получите изходния текст на изгледа;
- Можете да използвате системната съхранена процедура sp_depends, за да намерите списъка с таблици, от които зависи даден изглед;
- Можете да използвате системната съхранена процедура sp_rename, за да преименувате изглед.
В този раздел разгледахме как да използваме изгледи за извличане на данни, които отговарят на определени критерии. Нека обаче се върнем към последния пример. В базата данни на NorthWind има четири региона и за да изброим териториите на всички региони, се нуждаем от четири различни изгледа. Тази задача може да бъде улеснена, ако можем да предадем стойността на RegionID като параметър. Това може да стане с помощта на съхранена процедура, за която ще говорим в следващия раздел.
Съхранени процедури
Съхранената процедура е компилиран набор от SQL изрази, съхранени в база данни като именуван обект и изпълнени като единична част от кода. Съхранените процедури могат да приемат и връщат параметри. Когато потребител създаде съхранена процедура, сървърът я компилира и я поставя в споделен кеш, след което компилираният код може да се използва от множество потребители. Когато дадено приложение използва съхранена процедура, то й предава параметри, ако има такива, и сървърът изпълнява процедурата без повторно компилиране.
Съхранените процедури могат да подобрят производителността на приложението. Първо, в сравнение с обикновените SQL заявки, изпратени от клиентско приложение, те изискват по-малко време за подготовка за изпълнение, тъй като вече са компилирани и съхранени. Второ, мрежовият трафик в този случай също е по-малък, отколкото в случай на предаване на SQL заявка, тъй като по-малко данни се предават по мрежата. Ориз. 3 илюстрира извикване на запомнена процедура клиентско приложение.
Съхранените процедури се прекомпилират автоматично, ако бъдат направени промени в обектите, които засягат; с други думи, те винаги са актуални. Както бе споменато по-горе, съхранените процедури могат да приемат параметри, което позволява на различни приложения да използват една и съща процедура, използвайки различни набори от входни данни.
Съхранените процедури обикновено се използват за поддържане на референтната цялост на данните и прилагане на бизнес правила. Последното предоставя допълнителна гъвкавост, защото ако бизнес правилата се променят, може да се промени само текстът на процедурата, без да се променят клиентските приложения.
За създаване, промяна и изтриване на процедури има специални SQL инструкции - CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Ще ги разгледаме в следващия раздел.
CREATE PROCEDURE клауза
Клаузата CREATE PROCEDURE се използва за създаване на запомнена процедура. Той има следния опростен синтаксис:
CREATE PROC proc_name [ (@parameter data_type) [= default] ] [...] AS sql_statements
Аргументът proc_name указва името на съхранената процедура, което трябва да бъде уникално в текущата база данни. Аргументът @parameter указва параметър на процедура. Можете да укажете един или повече параметри в клаузата CREATE PROCEDURE. Ако даден параметър няма стойност по подразбиране, той трябва да бъде предаден от потребителя (или клиентското приложение) при извикване на процедурата. В Microsoft SQL Server 7.0 броят на параметрите на съхранена процедура не трябва да надвишава 1024; по подразбиране те могат да имат NULL стойности.
Имайте предвид обаче, че някои общи механизми за достъп до данни могат да наложат допълнителни ограниченияпо броя на параметрите на съхранените процедури. Например BDE драйверът за Oracle 8 може да работи само с процедури, чийто брой параметри не надвишава 10.
Аргументът data_type указва типа данни за параметъра. Ключовата дума по подразбиране може да се използва за задаване на стойности по подразбиране - това може да бъде константа или NULL. Ако е указана стойност по подразбиране, процедурата може да бъде извикана без да се указва стойност на параметър. Ако дадена процедура използва параметър с ключова дума LIKE, неговата стойност по подразбиране може да съдържа заместващи знаци (%, _ и [^]).
Ключовата дума OUTPUT показва, че това е върнат параметър.
Ключовата дума AS указва действието, което трябва да извърши процедурата, под формата на произволен брой SQL изрази и изрази в специфично за сървъра SQL процедурно разширение.
Процедурата, създадена с помощта на клаузата CREATE PROCEDURE, ще бъде записана в текущата база данни. В Microsoft SQL Server имената на процедурите се съдържат в системната таблица sysobjects, а изходният текст се съдържа в таблицата syscomments.
За да промените предварително създадена съхранена процедура, трябва да използвате клаузата ALTER PROCEDURE, която е описана накратко в следващия раздел.
Оферта ПРОЦЕДУРА ЗА ОТПУСКАНЕ
Тази клауза се използва за премахване на съхранени процедури от базата данни. Клаузата DROP PROCEDURE приема един аргумент, името на процедурата, която трябва да бъде премахната.
Когато изтриете съхранена процедура, информацията за нея се премахва от системните таблици sysobjects и syscomments.
Създаване и използване на съхранени процедури
В раздела за изгледи посочихме, че би било удобно, ако можем да предадем параметър към изгледа, съдържащ стойност на RegionID, за да изберем един от четирите региона в базата данни на NorthWind. Нека да разгледаме още веднъж заявка, която връща списък с територии в даден регион:
SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1
За да изберем различен регион, трябва да променим условието в клаузата WHERE в последния ред на заявката. Следователно, ако използваме променлива (да я наречем RegID), можем да изберем един от четирите региона, без да променяме други части на заявката.
В базата данни на NorthWind има четири региона, номерирани от 1 до 4. Това означава, че променливата RegID трябва да е от тип цяло число. Кодът на съхранената процедура е даден по-долу:
CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID
Забележете, че оставихме почти целия текст на заявката SELECT непокътнат (той е в курсив) и добавихме само клауза CREATE PROCEDURE с името на новосъздадената съхранена процедура (на първия ред), декларация на параметър (на втория ред), и ключовата дума AS, указваща в началото на изреченията, които действително извършват действия.
Резултатът от изпълнението на създадената процедура в SQL Server Query Analyzer за RegID =2 е показан на фиг. 3.
Очевидно можем да използваме съхранени процедури за повече от просто прилагане на разширени версии на изгледи или интелигентни SELECT заявки. Съхранените процедури предоставят механизми, които ви позволяват да автоматизирате много рутинни задачи.
В Microsoft SQL Server 7.0 можем също да използваме системни запомнени процедури, за да работим с обикновени запомнени процедури:
- sp_stored_procedures - показва списък със съхранени процедури;
- sp_helptext - показва изходния текст на съхранената процедура;
- sp_depends - показва информация за зависимостите на съхранените процедури;
- sp_procoption - задава или задава опции за съхранени процедури;
- sp_recompile - прекомпилира процедурата в момента на следващото й извикване;
- sp_rename - променя името на процедурата.
Системни съхранени процедури
Тъй като говорим за Microsoft SQL Server, заслужава да се отбележи огромният брой системни съхранени процедури, внедрени в него. Имената на системните съхранени процедури започват със SP_ или XP_ и се съхраняват в главната база данни. Вече описахме някои от често използваните системни съхранени процедури по-горе.
Моля, обърнете внимание, че тригерите не трябва да връщат данни на потребителя.
Има две специални таблици, които могат да се използват в клаузата CREATE TRIGGER. Например изтритите и вмъкнатите таблици имат същата структура като таблицата, на която е дефиниран тригерът, и съдържат старите и новите стойности на записите, модифицирани от потребителя. Например, можем да използваме следния SQL оператор, за да намерим изтрити записи:
SELECT * FROM изтрит
В табл Фигура 3 показва съдържанието на изтритите и вмъкнати таблици за всички възможни промени в данните.
За да промените съществуващ тригер, използвайте клаузата ALTER TRIGGER. Ще говорим за това в следващия раздел.
Първо, трябва да добавим две нови полета към таблицата, които ще съдържат тази информация. Нека ги наречем UpdatedBy (името на мениджъра, който последно е актуализирал записа) и UpdatedWhen (времето, когато записът е бил променен). След това нека създадем тригер, наречен KeepTrack. Ето неговия код:
CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM вмъкнато, Customers WHERE вмъкнато.CustomerID = Customers.CustomerID
Както можете да видите от изходния код на тригера, той се изпълнява след всяка операция INSERT и UPDATE в таблицата Customers. Този тригер ще съхранява името на мениджъра (потребител на базата данни) в полето Customers.UpdatedBy и датата и часа на промяната в полето Customers.UpdatedWhen. Тези данни се извличат от вмъкнатата временна таблица.
Както можете да видите, този тригер ви позволява да наблюдавате промените и вмъкването на нови записи в таблицата.
Преди да завършим този кратък преглед на тригерите, трябва да ви кажем къде можете да намерите информация за наличните тригери. Таблицата sysobjects съхранява информация за тригерите и техните типове, а таблицата syscomments съдържа изходния им текст.
Заключение
В тази част разгледахме няколко типа обекти на бази данни - съхранени процедури, изгледи и тригери. Научихме следното:
- Изгледът е виртуална таблица, обикновено създадена като подмножество от колони от една или повече таблици. Клаузата CREATE VIEW се използва за създаване на изглед, клаузата ALTER VIEW се използва за модифицирането му, а клаузата DROP VIEW се използва за изтриването му.
- Съхранената процедура е компилиран набор от SQL изрази, съхранени в база данни като именуван обект и изпълнени като единична част от кода. Клаузата CREATE PROCEDURE се използва за създаване на запомнена процедура, ALTER PROCEDURE се използва за нейното модифициране и DROP PROCEDURE се използва за нейното изтриване.
- Тригерът е специален тип съхранена процедура, която се извиква автоматично, когато данните в определена таблица се добавят, изтриват или модифицират с помощта на SQL оператор INSERT, DELETE или UPDATE. Тригерите се създават с помощта на клаузата CREATE TRIGGER. За да промените тригер, използвайте клаузата ALTER TRIGGER, а за да го изтриете, използвайте клаузата DROP TRIGGER.
КомпютърПрес 12"2000г
Съхранени SQL процедуриса изпълним програмен модул, който може да се съхранява под формата на различни обекти. С други думи, това е обект, който съдържа SQL изрази. Тези съхранени процедури могат да бъдат изпълнени в клиента на приложението, за да се получи добра производителност. В допълнение, такива обекти често се извикват от други скриптове или дори от някой друг раздел.
Въведение
Много хора смятат, че те са подобни на различни процедури (съответно, с изключение на MS SQL). Може би това е вярно. Те имат подобни параметри и могат да произвеждат подобни стойности. Освен това в някои случаи те се докосват. Например, те се комбинират с DDL и DML бази данни, както и с потребителски функции (с кодово име UDF).
В действителност SQL съхранените процедури имат широк обхватпредимства, които ги отличават от подобни процеси. Сигурност, гъвкавост на програмирането, производителност - всичко това привлича все повече потребители, работещи с бази данни. Пиковата популярност на процедурите настъпи през 2005-2010 г., когато беше пусната програма от Microsoft, наречена „SQL Server Management Studio“. С негова помощ работата с бази данни стана много по-лесна, практична и удобна. От година на година този набира популярност сред програмистите. Днес това е напълно позната програма, която за потребителите, които „комуникират“ с бази данни, е наравно с Excel.
Когато се извика дадена процедура, тя незабавно се обработва от самия сървър без ненужни процеси или намеса на потребителя. След това можете да извършите всяко изтриване, изпълнение или промяна. За всичко това отговаря DDL операторът, който сам извършва най-сложните действия за обработка на обекти. Освен това всичко това се случва много бързо и сървърът всъщност не се зарежда. Тази скорост и производителност ви позволява много бързо да прехвърляте големи количества информация от потребителя към сървъра и обратно.
За прилагането на тази технология за работа с информация има няколко езика за програмиране. Те включват например PL/SQL от Oracle, PSQL в системите InterBase и Firebird, както и класическия Microsoft Transact-SQL. Всички те са предназначени за създаване и изпълнение на съхранени процедури, което позволява на големите процесори на бази данни да използват свои собствени алгоритми. Това е необходимо и за да може управляващите такава информация да защитят всички обекти от неоторизиран достъп от трети страни и съответно от създаване, промяна или изтриване на определени данни.
Производителност
Тези обекти на база данни могат да бъдат програмирани по различни начини. Това позволява на потребителите да изберат вида на използвания метод, който е най-подходящ, спестявайки усилия и време. В допълнение, процедурата се обработва сама, което избягва огромното време, изразходвано за комуникация между сървъра и потребителя. Също така, модулът може да бъде препрограмиран и сменен в желаната посока по абсолютно всяко време. Особено си струва да се отбележи скоростта, с която се стартира SQL съхранената процедура: този процес се извършва по-бързо от други, подобни на него, което го прави удобен и универсален.
Безопасност
Този тип обработка на информация се различава от подобни процеси по това, че гарантира повишена сигурност. Това се гарантира от факта, че достъпът до процедури от други потребители може да бъде напълно изключен. Това ще позволи на администратора да извършва операции с тях независимо, без страх от прихващане на информация или неоторизиран достъп до базата данни.
Трансфер на данни
Връзката между SQL съхранената процедура и клиентското приложение е използването на параметри и връщани стойности. Последният не трябва да предава данните на съхранената процедура, но тази информация (основно по заявка на потребителя) се обработва за SQL. След като съхранената процедура приключи своята работа, тя изпраща пакети с данни обратно (но, отново, по избор) към приложението, което я е извикало, използвайки различни методи, с помощта на който може да се направи както извикване на съхранена SQL процедура, така и връщане, например:
Прехвърляне на данни с помощта на параметър тип изход;
Предаване на данни чрез оператора return;
Предаване на данни чрез оператора select.
Сега нека разберем как изглежда този процес отвътре.
1. Създайте EXEC съхранена процедура в SQL
Можете да създадете процедура в MS SQL (Managment Studio). След като процедурата бъде създадена, тя ще бъде посочена в програмируемия възел на базата данни, в който процедурата за създаване се изпълнява от оператора. За изпълнение SQL съхранените процедури използват EXEC процес, който съдържа името на самия обект.
Когато създавате процедура, нейното име се появява първо, последвано от един или повече параметри, присвоени към нея. Параметрите може да не са задължителни. След като параметрите, т.е. тялото на процедурата, са написани, трябва да се извършат някои необходими операции.
Въпросът е, че едно тяло може да има локални променливи, разположени в него, и тези променливи също са локални по отношение на процедурите. С други думи, те могат да се разглеждат само в тялото на процедура на Microsoft SQL Server. Съхранените процедури в този случай се считат за локални.
И така, за да създадем процедура, имаме нужда от името на процедурата и поне един параметър като тяло на процедурата. Имайте предвид, че страхотна опция в този случай е да създадете и изпълните процедура с името на схемата в класификатора.
Тялото на процедурата може да бъде от всякакъв вид, като например създаване на таблица, вмъкване на един или повече редове от таблица, установяване на типа и характера на базата данни и т.н. Въпреки това тялото на процедурата ограничава някои операции да бъдат извършвани в него. Някои от важните ограничения са изброени по-долу:
Тялото не трябва да създава друга съхранена процедура;
Тялото не трябва да създава фалшиво впечатление за обекта;
Тялото не трябва да създава никакви задействания.
2. Задаване на променлива в тялото на процедурата
Можете да направите променливи локални към тялото на процедурата и тогава те ще се намират изключително в тялото на процедурата. Добра практика е да създавате променливи в началото на тялото на съхранената процедура. Но можете също да задавате променливи навсякъде в тялото на даден обект.
Понякога може да забележите, че няколко променливи са зададени на един ред и всеки параметър на променлива е разделен със запетая. Също така имайте предвид, че променливата е с префикс @. В тялото на процедурата можете да зададете променливата където пожелаете. Например, променливата @NAME1 може да бъде декларирана близо до края на тялото на процедурата. За да се присвои стойност на декларирана променлива, се използва набор от лични данни. За разлика от ситуацията, при която повече от една променлива е декларирана на един и същ ред, в тази ситуация се използва само един набор от лични данни.
Потребителите често задават въпроса: „Как да присвоите множество стойности в едно изявление в тялото на процедура?“ Добре. Това е интересен въпрос, но е много по-лесно, отколкото си мислите. Отговор: Използване на двойки като „Изберете променлива = стойност“. Можете да използвате тези двойки, като ги разделите със запетая.
В най-много различни примерихората показват създаването на проста съхранена процедура и нейното изпълнение. Процедурата обаче може да приеме параметри, така че процесът, който я извиква, да има стойности, близки до нея (но не винаги). Ако те съвпадат, тогава в тялото започват съответните процеси. Например, ако създадете процедура, която ще приеме град и регион от повикващия и ще върне данни за това колко автори принадлежат към съответния град и регион. Процедурата ще направи заявка към авторските таблици на базата данни, като например Pubs, за да извърши това броене на автори. За да получи тези бази данни, например, Google изтегля SQL скрипта от страницата SQL2005.
В предишния пример процедурата приема два параметъра, които английски езикусловно ще се нарича @State и @City. Типът данни съответства на типа, дефиниран в приложението. Тялото на процедурата има вътрешни променливи @TotalAuthors и тази променлива се използва за показване на броя на авторите. Следва секцията за избор на заявка, която изчислява всичко. Накрая изчислената стойност се отпечатва в изходния прозорец с помощта на израза за печат.
Как да изпълним съхранена процедура в SQL
Има два начина за извършване на процедурата. Първият начин показва, чрез предаване на параметри, как се изпълнява разделен със запетая списък след името на процедурата. Да кажем, че имаме две стойности (както в предишния пример). Тези стойности се събират с помощта на променливите на параметрите на процедурата @State и @City. При този метод на предаване на параметри редът е важен. Този метод се нарича предаване на редни аргументи. При втория метод параметрите вече са директно зададени и в този случай редът не е важен. Този втори метод е известен като предаване на именувани аргументи.
Процедурата може леко да се отклонява от типичната. Всичко е същото като в предишния пример, но само тук параметрите са изместени. Това означава, че параметърът @City се съхранява първи, а @State се съхранява до стойността по подразбиране. Параметърът по подразбиране обикновено се маркира отделно. SQL съхранените процедури се предават само като параметри. В този случай предоставеният параметър "UT" замества стойността по подразбиране "CA". При второто изпълнение се предава само една стойност на аргумент за параметъра @City, а параметърът @State приема стойността по подразбиране на "CA". Опитните програмисти съветват, че всички променливи трябва да бъдат разположени към края на списъка с параметри по подразбиране. В противен случай изпълнението не е възможно и тогава трябва да работите с предаване на именувани аргументи, което е по-дълго и по-сложно.
4. Съхранени процедури на SQL Server: Методи за връщане
Има три важни начина за изпращане на данни в извикана съхранена процедура. Те са изброени по-долу:
Връща стойността на съхранена процедура;
Извеждане на параметри на съхранена процедура;
Избор на една от съхранените процедури.
4.1 Връщане на стойности от SQL съхранени процедури
При тази техника процедура присвоява стойност на локална променлива и я връща. Процедурата може също директно да върне постоянна стойност. В следващия пример създадохме процедура, която връща общия брой автори. Ако сравните тази процедура с предишните, можете да видите, че стойността за печат е обърната.
Сега нека видим как да изпълним процедура и да отпечатаме нейната върната стойност. Изпълнението на процедурата изисква задаване на променлива и отпечатване, което се извършва след целия този процес. Обърнете внимание, че вместо оператор за печат можете да използвате оператор Select, като Select @RetValue, както и OutputValue.
4.2 Извеждане на параметър от SQL съхранена процедура
Стойността на отговора може да се използва за връщане на една променлива, което видяхме в предишния пример. Използването на изходния параметър позволява на процедурата да изпрати една или повече променливи стойности на повикващия. Изходният параметър се обозначава именно с тази ключова дума “Output” при създаване на процедура. Ако даден параметър е даден като изходен параметър, тогава обектът на процедурата трябва да му присвои стойност. SQL съхранените процедури, примери за които могат да се видят по-долу, в този случай се връщат с обобщена информация.
В нашия пример ще има две имена на изход: @TotalAuthors и @TotalNoContract. Те са посочени в списъка с параметри. Тези променливи присвояват стойности в тялото на процедурата. Когато използваме изходни параметри, повикващият може да види зададената стойност в тялото на процедурата.
Освен това в предишния сценарий се декларират две променливи, за да се видят стойностите, които съхранените процедури на MS SQL Server задават в изходния параметър. След това процедурата се изпълнява чрез подаване на нормалната стойност на параметъра “CA”. Следните параметри са изходни параметри и следователно декларираните променливи се предават в указания ред. Имайте предвид, че когато предавате променливи, ключовата дума за изход също се задава тук. След като процедурата приключи успешно, стойностите, върнати от изходните параметри, се показват в прозореца за съобщения.
4.3 Избор на една от SQL съхранените процедури
Тази техника се използва за връщане на набор от стойности като таблица с данни (RecordSet) към извикващата съхранена процедура. В този пример SQL съхранената процедура с параметри @AuthID отправя заявки към таблицата Authors, като филтрира записите, върнати с помощта на този параметър @AuthId. Операторът Select решава какво трябва да се върне на извикващия съхранената процедура. Когато съхранената процедура се изпълни, AuthId се предава обратно. Тази процедура тук винаги връща само един запис или нито един. Но съхранената процедура няма никакви ограничения за връщане на повече от един запис. Не е необичайно да видите примери, при които данните се връщат с помощта на избрани параметри, включващи изчислени променливи чрез предоставяне на множество общи суми.
Накрая
Съхранената процедура е доста сериозен програмен модул, който връща или преминава, а също така задава необходимите променливи благодарение на клиентското приложение. Тъй като съхранената процедура се изпълнява сама на сървъра, може да се избегне огромен обмен на данни между сървъра и клиентското приложение (за някои изчисления). Това ви позволява да намалите натоварването SQL сървър, което, разбира се, е в полза на притежателите им. Един от подвидовете са T SQL съхранените процедури, но тяхното изучаване е необходимо за тези, които създават впечатляващи бази данни. Има и голям, дори огромен брой нюанси, които могат да бъдат полезни при изучаване на съхранени процедури, но това е необходимо повече за тези, които планират да се включат в програмирането, включително професионално.
1. Включете ред във вашите процедури - SET NOCOUNT ON:С всеки DML израз SQL сървърът внимателно ни връща съобщение, съдържащо броя на обработените записи. Тази информацияМоже да ни е полезен, докато дебъгваме кода, но след това ще бъде напълно безполезен. Като напишем SET NOCOUNT ON, деактивираме тази функция. За съхранени процедури, съдържащи няколко израза или/или цикли това действиеможе да даде значително увеличение на производителността, тъй като обемът на трафика ще бъде значително намален.
CREATE PROC dbo.ProcName
КАТО
SET NO COUNT ON;
--Ето кода на процедурата
ИЗБЕРЕТЕ колона1 ОТ dbo.TblTable1
--Превключете SET NOCOUNT в първоначално състояние
SET NO COUNT OFF;
ОТИВАМ
2. Използвайте името на схемата с името на обекта:Е, мисля, че е ясно. Тази операция казва на сървъра къде да търси обекти и вместо произволно да рови в кошчетата си, той веднага ще разбере къде трябва да отиде и какво да вземе. С голям брой бази данни, таблици и съхранени процедури може значително да ни спести време и нерви.
SELECT * FROM dbo.MyTable --Това е добре да направите
-- Вместо
ИЗБЕРЕТЕ * ОТ MyTable --И това е лошо
--Процедурно извикване
EXEC dbo.MyProc --Отново добре
--Вместо
EXEC MyProc --Лошо!
3. Не използвайте префикса “sp_” в името на вашите съхранени процедури:Ако името на нашата процедура започва с "sp_", SQL Server първо ще търси основната си база данни. Факт е, че даден префиксизползвани за частни вътрешни сървърни съхранени процедури. Поради това използването му може да доведе до допълнителни разходи и дори неправилни резултати, ако процедура със същото име като вашето бъде открита в неговата база данни.
4. Използвайте АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ 1) вместо АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ *):За да проверим съществуването на запис в друга таблица, използваме израза IF EXISTS. Този израз връща true, ако поне една стойност е върната от вътрешния израз, няма значение „1“, всички колони или таблица. Върнатите данни по принцип не се използват по никакъв начин. По този начин, за да компресирате трафика по време на предаване на данни, е по-логично да използвате „1“, както е показано по-долу:
АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ 1 ОТ sysobjects
WHERE име = "Моята таблица" И тип = "U")
5. Използвайте TRY-Catch за улавяне на грешки:Преди сървърите от 2005 г. след всяка заявка в процедурата се записваха огромен брой проверки за грешки. Повече код винаги консумира повече ресурси и повече време. С 2005 SQL Server, по-правилен и удобен начинрешения на този проблем:
ЗАПОЧНЕТЕ ОПИТВАЙТЕ
--код
КРАЙ НА ОПИТА
ЗАПОЧНЕТЕ УЛОВА
--грешка при улавяне на код
КРАЙ ЗАХВАТ
Заключение
По принцип това е всичко, което имам за днес. Още веднъж повтарям, че тук са само онези техники, които лично съм използвал в практиката си и мога да гарантирам за тяхната ефективност.
P.S.
Първият ми пост, не съдете прекалено строго.
