Съхранени процедури
Темата на тази глава е един от най-мощните инструменти, предлагани на разработчиците на приложения за бази данни. InterBase данниза внедряване на бизнес логика Процедурите на Stoied ви позволяват да внедрите значителна част от логиката на приложението на ниво база данни и по този начин да увеличите производителността на цялото приложение, да централизирате обработката на данни и да намалите количеството код, необходим за изпълнение на задачите Почти всичко е достатъчно сложно приложениебазите данни не са пълни без използването на съхранени процедури.
В допълнение към тези добре познати предимства на използването на съхранени процедури, които са общи за повечето релационни СУБД, съхранените процедури InterBase могат да действат като почти актуални набори от данни, което им позволява да използват резултатите, които връщат в редовни SQL заявки.
Често начинаещите разработчици мислят за съхранените процедури просто като набор от специфични SQL заявки, които правят нещо вътре в базата данни, и има мнение, че работата със съхранени процедури е много по-трудна от имплементирането на същата функционалност в клиентско приложение на езика високо ниво
И така, какво представляват съхранените процедури в InterBase?
Съхранената процедура (SP) е част от метаданните на базата данни, която е подпрограма, компилирана във вътрешното представяне на InterBase, написана на специален език, чийто компилатор е вграден в ядрото на сървъра InteiBase
Съхранената процедура може да бъде извикана от клиентски приложения, от тригери и други съхранени процедури. Съхранената процедура се изпълнява вътре в сървърния процес и може да манипулира данни в базата данни, както и да връща резултатите от нейното изпълнение на клиента, който я е извикал (т.е. тригер, CP, приложение)
Основата на мощните възможности, присъщи на SP, е процедурен език за програмиране, който включва както модифицирани изрази на обикновен SQL, като INSERT, UPDATE и SELECT, така и инструменти за разклоняване и цикъл (IF, WHILE), както и обработка на грешки инструменти и изключителни ситуации Езикът на съхранените процедури ви позволява да прилагате сложни алгоритми за работа с данни и поради фокуса върху работата с релационни данни, HP са много по-компактни от подобни процедури в традиционните езици.
Трябва да се отбележи, че същият език за програмиране се използва за тригери, с изключение на редица функции и ограничения. Разликите между подмножеството на езика, използван в тригерите, и езика на XPS са разгледани подробно в главата "Тригери" (Част 1).
Пример за проста съхранена процедура
Сега е моментът да създадете първата съхранена процедура и да я използвате, за да научите процеса на създаване на съхранени процедури. Но първо трябва да кажем няколко думи за това как се работи със съхранените процедури Факт е, че изключително лошите стандартни инструменти за разработване и отстраняване на грешки в съхранени процедури дължат своята слава на неясния и неудобен CP инструмент. В документацията на InterBase се препоръчва да се създават процедури с помощта на SQL скриптови файлове, съдържащи текста на SP, които се подават на входа на isql интерпретатора и по този начин създават и модифицират SP. Ако в този SQL скрипт на етапа на компилиране на текста на процедурата в BLR Ако възникне грешка, 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 ДВОЙНА ТОЧНОСТ,
second_arg ДВОЙНА ТОЧНОСТ)
ВРЪЩАНЕ (резултат ДВОЙНА ТОЧНОСТ)
КАТО
ЗАПОЧНЕТЕ
Резултат = първи_арг + втори_арг;
ПРЕКРАЩАНЕ;
КРАЙ
Както можете да видите, всичко е просто: след командата CREATE PROCEDURE се посочва името на новосъздадената процедура (която трябва да е уникална в базата данни) - в в такъв случай SP_Add, тогава входните параметри на SP - first_arg и second_arg - са изброени в скоби, разделени със запетаи, с посочване на техните типове.
Списъкът с входни параметри е по избор. CREATE изявлениеПРОЦЕДУРА - има случаи, когато процедурата получава всички данни за работата си чрез заявки към таблиците в тялото на процедурата.
Всички скаларни типове данни на InteiBase се използват в съхранените процедури Не могат да се използват масиви и потребителски дефинирани типове - домейни
Следва ключовата дума RETURNS, след която върнатите параметри са изброени в скоби, като се посочват техните типове - в случая само един - Резултат.
Ако процедурата не трябва да връща параметри, тогава думата RETURNS и списъкът с параметри за връщане липсват.
RETURNSQ е последвано от ключовата дума AS. Преди ключова думаКАТО върви заглавие,а след него - techoпроцедури.
Тялото на съхранената процедура е списък с описания на нейните вътрешни (локални) променливи (ако има такива, ще ги обсъдим по-подробно по-долу), разделени с точка и запетая (;) и блок от изрази, затворен в операторни скоби BEGIN КРАЙ. В този случай тялото на SP е много просто - ние искаме да добавим два входни аргумента и да присвоим техния резултат на изхода, а след това да извикаме командата SUSPEND. Малко по-късно ще обясним същността на работата на тази команда, но засега ще отбележим само, че е необходимо върнатите параметри да се предадат до мястото, откъдето е извикана съхранената процедура.
Сепаратори в съхранени процедури
Обърнете внимание, че израз в рамките на процедура завършва с точка и запетая (;). Както знаете, точката и запетаята е стандартният разделител на команди в SQL - това е сигнал към SQL интерпретатора, че текстът на командата е въведен изцяло и е необходимо да се започне обработката му. Възможно ли е, когато открие точка и запетая в средата на SP, SQL интерпретаторът ще приеме, че командата е въведена изцяло и ще се опита да изпълни част от съхранената процедура? Това предположение има смисъл. Всъщност, ако създадете файл, в който да напишете горния пример, добавите команда за свързване от базата данни и се опитате да изпълните този SQL скрипт с помощта на isql интерпретатора, ще бъде върната грешка, свързана с неочакваното, според интерпретатора, края на командата за създаване на съхранена процедура. Ако създавате съхранени процедури с помощта на SQL скриптови файлове, без да използвате специализирани инструменти за разработчици на InterBase, тогава преди всяка команда за създаване на CP (същото важи и за тригерите), променете разделителя на командите на скрипта на друг знак, различен от точка и запетая, а след текста на HP на възстанови го обратно. Разделител за промяна на командите Isql SQL изрази, изглежда така:
ЗАДАДЕТЕ СРОК
За типичен случайсъздаване на съхранена процедура изглежда така:
ЗАДАДЕТЕ СРОК ^;
CREATE PROCEDURE някаква_процедура
... . .
КРАЙ
^
ЗАДАДЕТЕ СРОК; ^
Извикване на съхранена процедура
Но да се върнем към нашата съхранена процедура. Сега, когато е създаден, трябва да го извикате по някакъв начин, да му предадете параметри и да получите върнатите резултати. Много е лесно да направите това - просто трябва да напишете SQL заявка в следната форма:
ИЗБЕРЕТЕ *
ОТ Sp_add (181.35, 23.09)
Тази заявка ще ни върне един ред, съдържащ само едно поле за резултат, което ще съдържа сумата от числата 181.35 и 23.09, т.е. 204.44.
По този начин нашата процедура може да се използва в конвенционалните SQL заявкиизпълнено като в клиентски програми, и в други HP или тригери. Това използване на нашата процедура стана възможно чрез използването на командата SUSPEND в края на съхранената процедура.
Факт е, че в InterBase (и във всичките му клонинги) има два вида съхранени процедури: избираеми процедури и изпълними процедури. Разликата в работата на тези два типа SP е, че процедурите за извличане обикновено връщат много набори от изходни параметри, групирани ред по ред, които са под формата на набор от данни, а изпълнените процедури може или да не връщат параметри изобщо, или връща само един набор от изходни параметри, изброени в Връщания, където един ред от параметри. Процедурите за избор се извикват в заявки SELECT, а изпълними процедури се извикват чрез EXECUTE командиПРОЦЕДУРА.
И двата типа съхранени процедури имат един и същ синтаксис за създаване и не са формално различни, така че всяка изпълнима процедура може да бъде извикана в заявка SELECT и всяка процедура за избор може да бъде извикана с помощта на EXECUTE PROCEDURE. Въпросът е как ще се държи HP кога различни видовеповикване. С други думи, разликата е в дизайна на процедурата за от определен типповикване. Тоест процедура за избор е специално създадена, за да бъде извикана от заявка SELECT, а изпълнима процедура е специално създадена, за да бъде извикана с помощта на EXECUTE PROCEDURE. Нека да разгледаме какви са разликите в дизайна на тези два вида HP.
За да разберете как работи процедурата за вземане на проби, трябва да отидете малко по-дълбоко в теорията. Нека си представим типична SQL заявка като SELECT ID, NAME FROM Table_example. В резултат на неговото изпълнение получаваме на изхода таблица, състояща се от две колони (ID и NAME) и определен брой редове (равен на броя на редовете в таблицата Table_example). Таблицата, върната в резултат на тази заявка, също се нарича набор SQL данниНека помислим как се формира наборът от данни по време на изпълнението на тази заявка.Сървърът, след като е получил заявката, определя към кои таблици принадлежи, след което установява кое подмножество от записи от тези таблици трябва да бъде включено в резултата от заявката. След това сървърът чете всеки запис, който удовлетворява резултатите от заявката, избира необходимите полета от него (в нашия случай това са ID и NAME) и ги изпраща на клиента. След това процесът се повтаря отново - и така нататък за всеки избран запис.
Цялото това отклонение е необходимо, за да може скъпият читател да разбере, че всички SQL набори от данни се формират ред по ред, включително в съхранени процедури! И основната разлика между процедурите за извличане и изпълними процедури е, че първите са проектирани да връщат много редове, докато вторите са само за един. Следователно те се използват по различни начини: процедура за избор се извиква с помощта на командата SELECT, която "изисква" процедурата да даде всички записи, които може да върне. Изпълнимата процедура се извиква чрез EXECUTE PROCEDURE, която "изважда" само един ред от CP и игнорира останалите (дори и да има такива!).
Нека разгледаме пример за процедура за извличане, за да стане по-ясно. За> прошка, нека създадем съхранена процедура, която работи точно както SELECT заявка ID, NAME FROM Table_Example, тоест просто избира полетата ID и NAME от цялата таблица. Ето този пример:
СЪЗДАВАНЕ НА ПРОЦЕДУРА Simple_Select_SP
СЕ ЗАВРЪЩА (
procID INTEGER,
procNAME VARCHAR (80))
КАТО
ЗАПОЧНЕТЕ
ЗА
ИЗБЕРЕТЕ ИД, ИМЕ ОТ таблица_пример
INTO: procID,: procNAME
НАПРАВЕТЕ
ЗАПОЧНЕТЕ
ПРЕКРАЩАНЕ;
КРАЙ
КРАЙ
Нека преминем през стъпките на тази рутина, наречена Simple_Select_SP. Както можете да видите, той няма входни параметри и има два изходни параметъра - ID и NAME. Най-интересното, разбира се, се крие в тялото на процедурата. Тук се използва конструкцията FOR SELECT:
ЗА
ИЗБЕРЕТЕ ИД, ИМЕ ОТ таблица_пример
INTO: procID,: procNAME
НАПРАВЕТЕ
ЗАПОЧНЕТЕ
/ * направете нещо с променливите procID и procName * /
КРАЙ
Това парче код означава следното: за всеки ред, избран от Table_example, поставете избраните стойности в променливите procID и procName и след това извършете някакво действие с тези променливи.
Може да направите изненадана физиономия и да попитате: "Променливи? Какви други променливи? 9" В тази глава е някаква изненада, че можем да използваме променливи в съхранените процедури. В езика XPS можете да декларирате свои собствени локални променливи в рамките на процедура или да използвате входни и изходни параметри като променливи.
За да декларирате локална променлива в съхранена процедура, трябва да поставите нейното описание след ключовата дума AS и преди първата дума BEGIN. Описанието на локална променлива изглежда така:
ДЕКЛАРИРАНЕ НА ПРОМЕНАВА
Например, за да декларирате целочислена локална променлива Mylnt, вмъкнете следното описание между AS и BEGIN
ДЕКЛАРИРАНЕ НА ПРОМЕНАВА Mylnt ЦЯЛО ЧИСЛО;
Променливите в нашия пример започват с двоеточие. Това се прави, защото те са достъпни в SQL- ЗА команди SELECT, следователно, за да правите разлика между полета в таблици, които се използват в SELECT, и променливи, трябва да предхождате последното двоеточие. В крайна сметка променливите могат да имат точно същото име като полетата в таблиците!
Но двоеточието пред името на променливата трябва да се използва само в SQL заявки. Извън текстовете достъпът до променлива се извършва без двоеточие, например:
procName = "Някое име";
Но да се върнем към тялото на нашата процедура. Клаузата FOR SELECT връща данни не под формата на таблица - набор от данни, а един ред в даден момент. Всяко върнато поле трябва да бъде поставено в собствена променлива: ID => procID, NAME => procName. В DO частта тези променливи се изпращат до клиента, който извика) procedure> p> с помощта на командата SUSPEND
По този начин командата FOR SELECT ... DO преминава през записите, избрани в частта SELECT на командата. В тялото на цикъла, образуван от частта DO, следващият генериран запис се прехвърля на клиента с помощта на командата SUSPEND.
Така че процедурата за извличане е предназначена да върне един или повече редове, за които е организиран цикъл вътре в тялото на SP, който запълва получените променливи параметри. И в края на тялото на този цикъл винаги има команда SUSPEND, която ще върне следващия ред данни на клиента.
Циклови и разклоняващи оператори
В допълнение към командата FOR SELECT ... DO, която организира цикъл върху записите на селекция, има друг тип цикъл - WHILE ... DO, който ви позволява да организирате цикъл на базата на проверка на всякакви условия. Ето пример за CP, използващ цикъл WHILE .. DO. Тази процедура връща квадратите от цели числа между 0 и 99:
СЪЗДАЙТЕ PROCEDJRE QUAD
ВРЪЩАНЕ (КВАДРАТНО ЦЯЛО ЧИСЛО)
КАТО
ДЕКЛАРИРАНЕ НА ПРОМЯНА I ЦЯЛО ЧИСЛО;
ЗАПОЧНЕТЕ
I = 1;
Докато аз<100) DO
ЗАПОЧНЕТЕ
КВАДРАТ = I * I;
I = I + 1;
ПРЕКРАЩАНЕ;
КРАЙ
КРАЙ
В резултат на изпълнението на заявката SELECT FROM QUAD ще получим таблица, съдържаща една QUADRAT колона, в която ще има квадрати от цели числа от 1 до 99
В допълнение към итерацията на резултатите от SQL селекция и класически цикъл, езикът на съхранените процедури използва оператора IF ... THEN..ELSE, който ви позволява да организирате разклоняване в зависимост от изпълнението на някои думи \. Неговият синтаксис е подобно на повечето оператори за разклоняване в езици за програмиране от високо ниво, като Pascal и C.
Нека да разгледаме по-сложен пример за съхранена процедура, която прави следното.
- Изчислява средната цена в таблицата Table_example (вижте глава "Първични ключове и генератори на таблици")
- След това за всеки запис в таблицата прави следната проверка дали текуща цена(PRICE) е по-голяма от средната цена, след което задава цената, равна на стойността на средната цена, плюс определен фиксиран процент
- Ако съществуващата цена е по-малка или равна на средната цена, тогава задава цената равна на предишната цена, плюс половината от разликата между предишната и средна цена.
- Връща всички променени редове в таблицата.
Първо, нека дефинираме името на SP, както и входните и изходните параметри Всичко това е написано в заглавката на съхранената процедура.
СЪЗДАВАНЕ НА ПРОЦЕДУРА Увеличаване на цените (
Процент2 Увеличаване ДВОЙНА ТОЧНОСТ)
ВРЪЩАНЕ (ID INTEGER, ИМЕ VARCHAR (SO), new_price DOUBLE
ТОЧНОСТ) КАТО
Процедурата ще бъде наречена IncreasePrices, има един входен параметър Peiceni21nciease от тип DOUBLE PRECISION и 3 изходни параметъра - ID, NAME и new_pnce. Обърнете внимание, че първите два изходни параметъра имат същите имена като полетата в Table_example, с които ще работим. Това е позволено от правилата на езика на съхранените процедури.
Сега трябва да декларираме локална променлива, която ще се използва за съхраняване на средната стойност. Тази декларация ще изглежда така:
ДЕКЛАРИРАНЕ НА ПРОМЯНА avg_price ДВОЙНА ТОЧНОСТ;
Сега да преминем към тялото на съхранената процедура Отворете тялото на HP
ключовата дума BEGIN.
Първо, трябва да изпълним първата стъпка от нашия алгоритъм - да изчислим средната цена. За да направим това, ще използваме заявка от следната форма:
ИЗБЕРЕТЕ СР. (Цена_л)
ОТ таблица_пример
INTO: avg_price, -
Тази заявка използва агрегатна функция AVG, който връща средната стойност на полето PRICE_1 сред избраните редове на заявка - в нашия случай средната стойност на PRICE_1 за цялата таблица Table_example. Стойността, върната от заявката, се поставя в променливата avg_price. Имайте предвид, че променливата avg_pnce се предшества от двоеточие - за да се разграничи от полетата, използвани в заявката.
Отличителен белег на това исканее, че винаги връща точно един запис. Такива заявки се наричат единични заявки и само такива селекции могат да се използват в съхранените процедури. Ако заявката върне повече от един ред, тогава тя трябва да бъде форматирана под формата на структура FOR SELECT ... DO, която организира цикъл за обработка на всеки върнат ред
И така, получихме средната стойност на цената. Сега трябва да преминете през цялата таблица, да сравните стойността на цената във всеки запис със средната цена и да вземете подходящо действие
От самото начало ние организираме итерацията върху всеки запис от таблицата Table_example.
ЗА
ИЗБЕРЕТЕ ИД, NAME, PRICE_1
ОТ таблица_пример
INTO: ID,: NAME,: new_price
НАПРАВЕТЕ
ЗАПОЧНЕТЕ
/ * _ вкостеняване на всеки запис тук * /
КРАЙ
Когато тази конструкция се изпълни, данните ще бъдат извадени ред по ред от Table_example и стойностите на полетата във всеки ред ще бъдат присвоени на променливите ID, NAME и new_pnce. Разбира се, ще запомните, че тези променливи са декларирани като изходящи параметри, но не трябва да се притеснявате, че избраните данни ще бъдат върнати като резултати: фактът, че нещо е присвоено на изходящите параметри, не означава, че клиентът извиква HP веднага ще получи тези стойности. ! Параметрите се предават само когато се изпълни командата SUSPEND, а преди това можем да използваме изходните параметри като обикновени променливи - в нашия пример правим точно това с параметъра new_price.
И така, вътре в тялото на цикъла BEGIN .. .END можем да обработим стойностите на всеки ред. Както си спомняте, трябва да разберем как текущата цена е в сравнение със средната и да предприемем съответните действия. Ще приложим тази процедура за сравнение с помощта на оператора IF:
IF (new_price> avg_price) THEN / * ако съществуващата цена е по-висока от средната цена * /
ЗАПОЧНЕТЕ
/ * след това инсталирайте нова ценаравна на средната цена плюс фиксиран процент * /
нова_цена = (ср.цена + средна_цена * (Процент2Увеличение / 100));
АКТУАЛИЗИРАНЕ Таблица_пример
ЗАДАЙТЕ ЦЕНА_1 =: нова_цена
WHERE ID =: ID;
КРАЙ
ДРУГО
ЗАПОЧНЕТЕ
/ * Ако съществуващата цена е по-малка или равна на средната цена, тогава задайте цената равна на предишната цена, плюс половината от разликата между предишната и средната цена * /
нова_цена = (ново_pnce + ((ср._пнс нова_цена) / 2));
АКТУАЛИЗИРАНЕ Таблица_пример
ЗАДАЙТЕ ЦЕНА_1 =: нова_цена
WHERE ID = .ID;
КРАЙ
Както можете да видите, резултатът е доста голяма IF конструкция, която би била трудна за разбиране, ако не бяха коментарите, затворени в символите / ** /.
За да променим цената според изчислената разлика, ще използваме оператора UPDATE, който ни позволява да променим съществуващи записи- едно или повече. За да посочим недвусмислено в кой запис трябва да се промени цената, използваме полето в условието WHERE първичен ключсравнявайки го със стойността на променливата, която съхранява стойността на идентификатора за текущ рекорд: ID =: ID. Имайте предвид, че идентификационният номер на променливата се предхожда от двоеточие.
След изпълнението на конструкцията IF ... THEN ... ELSE, променливите ID, NAME и new_price съдържат данни, които трябва да върнем на клиента \ на извикващия на процедурата. За да направите това, след IF, трябва да вмъкнете командата SUSPEND, която ще изпрати данните до мястото, от което е извикан SP. По време на прехвърлянето процедурата ще бъде спряна, а когато SP се изисква нов вход, след това ще бъде продължено отново - и това ще продължи, докато FOR SELECT ... DO повтори всички записи на своята заявка.
Трябва да се отбележи, че в допълнение към командата SUSPEND, която само спира съхранената процедура, има команда EXIT, която прекратява съхранената процедура след предаване на низа. Въпреки това, командата EXIT се използва рядко, тъй като е необходима основно за прекъсване на цикъла, когато се достигне условие.
В този случай, в случай, когато процедурата е извикана от оператора SELECT и завършена от EXIT, последният извлечен ред няма да бъде върнат. Тоест, ако трябва да прекъснете процедурата и все пак> получите този низ, трябва да използвате последователността
ПРЕКРАЩАНЕ;
ИЗХОД;
Основната цел на EXIT е да получи единични набори от данни, връщащи параметри, чрез извикване чрез EXECUTE PROCEDURE. В този случай стойностите на изходните параметри се задават, но наборът от SQL данни не се формира от тях и процедурата приключва.
Нека напишем целия текст на нашата съхранена процедура, за да можем да уловим нейната логика с един поглед:
СЪЗДАВАНЕ НА ПРОЦЕДУРА Увеличаване на цените (
Процент2 Увеличете ДВОЙНА ТОЧНОСТ)
ВРЪЩАНЕ (ИД ЦЕЛО ЧИСЛО, ИМЕ VARCHAR (80),
new_price ДВОЙНА ТОЧНОСТ) КАТО
ДЕКЛАРИРАНЕ НА ПРОМЯНА avg_price ДВОЙНА ТОЧНОСТ;
ЗАПОЧНЕТЕ
ИЗБЕРЕТЕ СР. (Цена_л)
ОТ таблица_пример
INTO: средна_цена;
ЗА
ИЗБЕРЕТЕ ИД, NAME, PRICE_1
ОТ таблица_пример
INTO: ID,: NAME,: new_price
НАПРАВЕТЕ
ЗАПОЧНЕТЕ
/ * обработвайте всеки запис тук * /
IF (new_pnce> avg_price) THEN / * ако съществуващата цена е по-висока от средната цена * /
ЗАПОЧНЕТЕ
/ * задайте нова цена, равна на средната цена плюс фиксиран процент * /
нова_цена = (ср.цена + средна_цена * (Процент2lncrease / 100));
АКТУАЛИЗИРАНЕ Таблица_пример
ЗАДАЙТЕ ЦЕНА_1 =: нова_цена
WHERE ID =: ID;
КРАЙ
ДРУГО
ЗАПОЧНЕТЕ
/ * Ако съществуващата цена е по-малка или равна на средната цена, тогава задава цената равна на предишната цена, плюс половината от разликата между предишната и средната цена * /
нова_цена = (нова_цена + ((ср. цена - нова_цена) / 2));
АКТУАЛИЗИРАНЕ Таблица_пример
ЗАДАЙТЕ ЦЕНА_1 =: нова_цена
WHERE ID =: ID;
КРАЙ
ПРЕКРАЩАНЕ;
КРАЙ
КРАЙ
Тази примерна съхранена процедура илюстрира използването на основни съхранени процедури и конструкции на езика за задействане. След това ще разгледаме как да използваме съхранените процедури за решаване на някои често срещани проблеми.
Рекурсивни съхранени процедури
InterBase съхранените процедури могат да бъдат рекурсивни. Това означава, че можете да извикате себе си от съхранена процедура. Разрешени са до 1000 нива на вмъкване на съхранени процедури, но не забравяйте, че свободните ресурси на сървъра може да изтекат, преди да се достигне максималното влагане на HP.
Една от често срещаните употреби на съхранените процедури е да се манипулират дървовидни структури, съхранявани в база данни. Дърветата често се използват в инвентаризацията, складирането, човешките ресурси и други общи приложения.
Нека разгледаме пример за съхранена процедура, която избира всички продукти от конкретен тип, започвайки от определено ниво на влагане.
Да предположим, че имаме следната формулировка на проблема: имаме каталог на стоки с йерархична структураот този вид:
Стоки
- Уреди
- Хладилници
- Трикамерна
- Двукамерна
- Еднокамерна
-
Перални машини
- Вертикална
- Фронтален
- Класически
- Тесен
- Компютърна технология
....
Тази структура на директорията на продуктовите категории може да има клонове с различна дълбочина. и също се натрупват с течение на времето. Нашата задача е да гарантираме, че всички елементи на листа са избрани от директорията с "разширяване на пълното име", като се започне от всеки възел. Например, ако изберем възела "Перални машини", тогава трябва да получим следните категории:
Перални - Вертикални
Перални - Front Classic
Перални машини - Предна тясна
Нека дефинираме структурата на таблиците за съхранение на информация в каталога на стоките. Използваме опростена схема за организиране на дървото в една таблица:
СЪЗДАВАНЕ НА ТАБЛИЦА GoodsTree
(ID_GOOD INTEGER НЕ NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR (80),
ограничение pkGooci първичен ключ (ID_GOOD));
Създаваме една таблица GoodsTree, в която има само 3 полета: ID_GOOD - идентификатор на интелигентна категория, ID_PARENT_GOOD - идентификатор на родителския град за тази категория и GOOD_NAME - име на категорията. За да гарантираме целостта на данните в тази таблица, ние налагаме ограничение за външен ключ върху тази таблица:
ПРОМЕНИ ТАБЛИЦА GoodsTree
ДОБАВЯНЕ НА ОГРАНИЧЕНИЕ FK_goodstree
ЧУЖЕН КЛЮЧ (ID_PARENT_GOOD)
РЕФЕРЕНЦИИ GOODSTPEE (ID__GOOD)
Таблицата се отнася за себе си и даденият външен ключ следи това. така че в таблицата да няма препратки към несъществуващи родители, а също така предотвратява опитите за изтриване на категории продукти, които имат наследници.
Нека поставим следните данни в нашата таблица:
| ID_GOOD 1
|
ID_PARENT_GOOD 0
|
ХУБАВО ИМЕ СТОКИ |
Сега, когато имаме къде да съхраняваме данните, можем да започнем да създаваме съхранена процедура, която показва всички „крайни“ продуктови категории в „разширен“ вид – например за категорията „Трикамерна“. пълно имекатегорията ще изглежда като "Домакински уреди Хладилници с три отделения".
Съхранените процедури, които обработват дървовидни структури, имат своя собствена терминология. Всеки елемент от дървото се нарича възел; и връзката между възлите, които се позовават един на друг, се нарича връзка родител-дете. Възлите, които са в самия край на дървото и нямат деца, се наричат "листа".
Имайте съхранена процедура входен параметърще има идентификатор на категория, от който ще трябва да започнем да разгръщаме. Съхранената процедура ще изглежда така:
СЪЗДАВАНЕ НА ПРОЦЕДУРА GETPULNAME (ID_GOOD2SHOW INTEGER)
ВРЪЩАНЕ (FULL_GOODS_NAME VARCHAR (1000),
ID_CHILD_GOOD INTEGER)
КАТО
ДЕКЛАРИРАНЕ НА ПРОМЯННА CURR_CHILD_NAME VARCHAR (80);
ЗАПОЧНЕТЕ
/ * 0 организира външно FOR цикълИЗБЕРЕТЕ на непосредствени наследници на продукта с ID_GOOD = ID_GOOD2SHOW * /
ЗА ИЗБОР gtl.id_good, gtl.good_name
ОТ GoodsTree gtl
КЪДЕ gtl.id_parent_good =: ID_good2show
INTO: ID_CHILD_GOOD,: пълно_име на стоката
НАПРАВЕТЕ
ЗАПОЧНЕТЕ
/ "Проверка с функцията EXISTS, която връща TRUE, ако заявката в скоби връща поне един ред. Ако намереният възел с ID_PARENT_GOOD = ID_CHILD_GOOD няма наследници, тогава това е" лист "от дървото и влиза в резултатите * /
АКО (НЕ СЪЩЕСТВУВА (
ИЗБЕРЕТЕ * ОТ GoodsTree
КЪДЕ GoodsTree.id_parent_good =: id_child_good))
ТОГАВА
ЗАПОЧНЕТЕ
/ * Предайте "листото" на дървото към резултатите * /
ПРЕКРАЩАНЕ;
КРАЙ
ДРУГО
/ * За възли, които имат деца * /
ЗАПОЧНЕТЕ
/ * съхранява името на родителския възел във временна променлива * /
CURR_CHILD_NAME = пълно_име_на стока;
/ * стартирайте тази рутина рекурсивно * /
ЗА
SELECT ID_CHILD_GOOD, пълно_име на стоката
ОТ GETfulLNAME (: ID_CHILD_GOOD)
INTO: ID_CHILD_GOOD,: пълно_име на стоката
ЗАПОЧНЕТЕ
/ * добавете името на родителския възел към намерения., името на детето, използвайки операцията за конкатенация на низове || * /
пълно_име_на_стока = CURR_CHILD_NAME | "" | пълно_име_на_стока, -
ПРЕКРАЩАНЕ; / * върнете пълното име на продукта * /
КРАЙ
КРАЙ
КРАЙ
КРАЙ
Ако изпълним тази процедурас входния параметър ID_GOOD2SHOW = 1, тогава получаваме следното:
Както можете да видите, използвайки рекурсивна съхранена процедура, ние преминахме през цялото дърво на категориите и показахме пълното име на категориите "лист", които са в самите върхове на клоновете.
Заключение
Това завършва прегледа на основните характеристики на езика на съхранените процедури. Очевидно е невъзможно да се овладее напълно разработването на съхранени процедури в една глава, но тук се опитахме да представим и обясним основните понятия, свързани със съхранените процедури. Описаните CP конструкции и техники за проектиране могат да бъдат приложени в повечето приложения за бази данни.
Част важни въпросисвързани с разработването на съхранени процедури ще бъдат разкрити в следващата глава - "Разширени функции на езика за съхранявани процедури InterBase", която е посветена на обработката на изключения, обработката на грешки в съхранените процедури и работата с масиви.
Разглежда се ситуация, при която съхранените процедури могат да влошат производителността на заявката.
При компилиране на съхранени процедури в MS SQL Server 2000 съхранени процедури се поставят в процедурен кеш, което може да подобри производителността при изпълнението им, като елиминира необходимостта от синтактичен анализ, оптимизиране и компилиране на код на запаметена процедура.
От друга страна, в съхранението на компилирания код на съхранената процедура лежи подводни скаликоето може да има обратен ефект.
Факт е, че при компилиране на съхранена процедура се компилира планът за изпълнение на тези оператори, които съставляват кода на процедурата, съответно ако компилираната съхранена процедура е кеширана, тогава нейният план за изпълнение се кешира и следователно съхранената процедура няма да да бъдат оптимизирани за конкретна ситуация и параметри на заявката.
Ще направя малък експеримент, за да демонстрира това.
ЕТАП 1... Създаване на база данни.
Нека създадем отделна база данни за експеримента.
СЪЗДАВАНЕ НА БАЗА ДАННИ test_sp_perf
ВКЛЮЧЕНО (ИМЕ = "тест_данни", ИМЕ НА ФАЙЛА = "c: \ temp \ test_data", РАЗМЕР = 1, МАКСИМАЛЕН РАЗМЕР = 10, РАЗМЕР НА ФАЙЛА = 1 Mb)
Влезте в системата (ИМЕ = "test_log", FILENAME = "c: \ temp \ test_log", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
СТЪПКА 2.Създаване на таблица.
CREATE TABLE sp_perf_test (колона 1 int, колона 2 символ (5000))
СТЪПКА 3.Попълване на таблицата с тестови линии. Дублиращи се редове се добавят умишлено към таблицата. 10 000 реда са номерирани от 1 до 10 000, а 10 000 реда са с номер 50 000.
ДЕКЛАРИРАНЕ @i int
SET @ i = 1
Докато аз<10000)
ЗАПОЧНЕТЕ
INSERT INTO sp_perf_test (колона1, колона2) СТОИ (@i, "Тестов низ #" + CAST (@i като char (8)))
INSERT INTO sp_perf_test (колона1, колона2) СТОИ (50000, "Тестов низ #" + CAST (@i като char (8)))
SET @ i = @ i + 1
КРАЙ
ИЗБЕРЕТЕ БРОЙ (*) ОТ sp_perf_test
Отивам
СТЪПКА 4.Създайте неклъстериран индекс. Тъй като планът за изпълнение се кешира с процедурата, индексът ще се използва еднакъв за всички повиквания.
СЪЗДАВАНЕ НА НЕКЛУСТРИРАН ИНДЕКС CL_perf_test ON sp_perf_test (колона 1)
Отивам
СТЪПКА 5.Създаване на съхранена процедура. Процедурата просто изпълнява оператор SELECT с условие.
CREATE PROC proc1 (@param int)
КАТО
ИЗБЕРЕТЕ колона 1, колона 2 ОТ sp_perf_test WHERE [защитен с имейл]
Отивам
СТЪПКА 6.Изпълнение на съхранена процедура. При стартиране на уязвима процедура се използва специално селективен параметър. В резултат на процедурата получаваме 1 ред. Планът за изпълнение посочва използването на неклъстериран индекс, тъй като заявката е селективна и е най-добрият начин за извличане на ред. Процедура, оптимизирана за извличане на един ред, се съхранява в процедурния кеш.
EXEC proc1 1234
Отивам
СТЪПКА 7.Изпълнение на съхранена процедура с неселективен параметър. Като параметър се използва стойността 50 000. Редове с такава стойност на първата колона съответно около 10 000, използвайки неклъстериран индекс и операцията за търсене на отметка е неефективна, но тъй като компилираният код с плана за изпълнение се съхранява в процедурен кеш, той ще бъде използван. Планът за изпълнение показва това, както и факта, че операцията за търсене на отметка е извършена за 9999 реда.
EXEC proc1 50 000
Отивам
СТЪПКА 8.Извличане на редове с първото поле равно на 50 000. Изпълнението на отделна заявка ще оптимизира и компилира заявката с конкретната стойност на първата колона. В резултат на това оптимизаторът на заявки открива, че полето се дублира много пъти и решава да използва операцията за сканиране на таблицата, която в този случай е много по-ефективна от използването на неклъстериран индекс.
ИЗБЕРЕТЕ колона 1, колона 2 ОТ sp_perf_test WHERE колона 1 = 50000
Отивам
По този начин можем да заключим, че използването на съхранени процедури не винаги може да подобри производителността на заявката. Трябва да сте много внимателни относно съхранените процедури, които оперират с резултати с променлив брой редове и използват различни планове за изпълнение.
Можете да използвате скрипта, за да повторите експеримента на нов MS SQL сървър.
Съхранена процедура - обект на база данни, който е набор от SQL изрази, който се компилира веднъж и се съхранява на сървъра. Съхранените процедури са много подобни на обикновените процедури в езици от високо ниво, те могат да имат входни и изходни параметри и локални променливи, могат да извършват числени изчисления и операции върху символни данни, резултатите от които могат да бъдат присвоени на променливи и параметри. Съхранените процедури могат да изпълняват стандартни операции с база данни (както DDL, така и DML). Освен това в съхранените процедури са възможни цикли и разклонения, тоест те могат да използват инструкции за контрол на процеса на изпълнение.
Съхранените процедури са подобни на дефинираните от потребителя функции (UDF). Основната разлика е, че UDF могат да се използват като всеки друг израз в SQL заявка, докато съхранените процедури трябва да се извикват с помощта на функцията CALL:
Процедура ОБАЖДАНЕ (...)
ИЗПЪЛНЯВАНЕ на процедура (...)
Съхранените процедури могат да връщат множество резултати, тоест резултати от заявка SELECT. Такива набори от резултати могат да бъдат обработени с помощта на курсори, други съхранени процедури, връщащи указател за набор от резултати, или чрез приложения. Съхранените процедури могат също да съдържат декларирани променливи за обработка на данни и курсори, които ви позволяват да преглеждате няколко реда в таблица. SQL стандартът предоставя оператори IF, LOOP, REPEAT, CASE и много други за работа. Съхранените процедури могат да приемат променливи, да връщат резултати или да променят и връщат променливи, в зависимост от това къде е декларирана променливата.
Реализацията на съхранените процедури варира от една СУБД до друга. Повечето от големите доставчици на бази данни ги поддържат под една или друга форма. В зависимост от СУБД, съхранените процедури могат да бъдат реализирани на различни езици за програмиране като SQL, Java, C или C++. Съхранените процедури, които не са написани на SQL, могат или не могат да изпълняват SQL заявки сами.
Пер
Споделяне на логика с други приложения. Съхранените процедури капсулират функционалност; това гарантира последователност в достъпа до данни и управлението между различните приложения.
Изолиране на потребители от таблици на базата данни. Това ви позволява да дадете достъп до съхранените процедури, но не и до действителните данни на таблицата.
Осигурява защитен механизъм. Съгласно предишната точка, ако имате достъп до данните само чрез съхранени процедури, никой друг не може да изтрие данните ви чрез командата SQL DELETE.
Подобрена производителност в резултат на намаления мрежов трафик. Със съхранените процедури могат да се комбинират множество заявки.
Срещу
Повишено натоварване на сървъра на базата данни поради факта, че по-голямата част от работата се извършва от страна на сървъра и по-малко от страна на клиента.
Има много за учене. Ще трябва да научите синтаксиса на MySQL изразите, за да напишете вашите съхранени процедури.
Вие дублирате логиката на приложението си на две места: сървърен код и код за съхранени процедури, като по този начин усложнявате процеса на манипулиране на данни.
Миграцията от една СУБД към друга (DB2, SQL Server и др.) може да доведе до проблеми.
Цел и предимства на съхранените процедури
Съхранените процедури подобряват производителността, подобряват програмируемостта и поддържат функции за защита на данните.
Вместо да съхраняват често използвана заявка, клиентите могат да се обърнат към съответната съхранена процедура. Когато се извика съхранена процедура, нейното съдържание незабавно се обработва от сървъра.
В допълнение към действителното изпълнение на заявка, съхранените процедури също позволяват извършване на изчисления и манипулиране на данни - промяна, изтриване, изпълнение на DDL оператори (не във всички СУБД!) И извикване на други съхранени процедури, изпълняващи сложна транзакционна логика. Един оператор ви позволява да извикате сложен скрипт, който се съдържа в съхранена процедура, което избягва изпращането на стотици команди в мрежата и по-специално необходимостта от прехвърляне на големи количества данни от клиента към сървъра.
В повечето СУБД, първият път, когато стартирате съхранена процедура, тя се компилира (разбира и генерира план за достъп до данни). В бъдеще обработката му се извършва по-бързо. Oracle DBMS интерпретира съхранения процедурен код, съхранен в речника на данните. Започвайки от версия Oracle 10g, се поддържа така наречената нативна компилация на съхранения процедурен код в C и след това в машинния код на целевата машина, след което, когато се извика съхранената процедура, нейният компилиран обектен код се изпълнява директно.
Възможности за програмиране
Веднъж създадена, съхранената процедура може да бъде извикана по всяко време, което осигурява модулност и насърчава повторното използване на кода. Последното улеснява поддържането на базата данни, тъй като тя става изолирана от променящите се бизнес правила. Можете да промените съхранената процедура по всяко време в съответствие с новите правила. След това всички приложения, които го използват, автоматично ще отговарят на новите бизнес правила без директна промяна.
Сигурност
Използването на съхранени процедури ви позволява да ограничите или напълно да изключите директния потребителски достъп до таблиците на базата данни, оставяйки на потребителите само разрешения за изпълнение на съхранени процедури, които осигуряват непряк и строго регулиран достъп до данни. В допълнение, някои СУБД поддържат криптиране на текст (обвиване) на съхранената процедура.
Тези защитни функции ви позволяват да изолирате структурата на базата данни от потребителя, което гарантира целостта и надеждността на базата данни.
Вероятността от действия като "SQL инжектиране" е намалена, тъй като добре написаните съхранени процедури допълнително валидират входните параметри, преди да подадат заявка към СУБД.
Реализация на съхранената процедура
Съхранените процедури обикновено се създават с помощта на езика SQL или конкретна негова реализация в избраната СУБД. Например, за тези цели в СУБД на Microsoft SQL Server има език 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 DBMS от своя страна позволява писане на съхранени процедури на езика Java. В IBM DB2 писането на съхранени процедури и функции на общи езици за програмиране е традиционният начин, поддържан от самото начало, а процедурното SQL разширение беше добавено към тази СУБД само в доста късни версии, след включването му в стандарта ANSI. Също така, Java и C процедурите се поддържат от Informix.
В Oracle DBMS съхранените процедури могат да бъдат комбинирани в така наречените пакети. Пакетът се състои от две части - спецификация на пакета, която определя дефиницията на съхранена процедура, и тяло на пакета, където се намира неговата реализация. По този начин Oracle ви позволява да отделите програмния интерфейс от реализацията.
В IBM DB2 съхранените процедури могат да бъдат комбинирани в модули.
Синтаксис
СЪЗДАВАНЕ НА ПРОЦЕДУРА `p2` ()
SQL ДЕФИНИР НА ЗАЩИТА
КОМЕНТАР "Процедура"
ИЗБЕРЕТЕ "Здравей свят!";
Първата част от код създава съхранена процедура. Следващият съдържа незадължителни параметри. След това идва името и накрая, тялото на самата процедура.
4 характеристики на съхранената процедура:
Език: За целите на преносимост SQL е посочен по подразбиране.
Детерминистично: ако процедурата връща един и същ резултат през цялото време и приема едни и същи входни параметри. Това е за процеса на репликация и регистрация. По подразбиране НЕ Е ДЕТЕРМИНИСТИЧЕН.
SQL сигурност: По време на разговора се проверяват правата на потребителя. INVOKER е потребителят, който извиква съхранената процедура. DEFINER е „създателят“ на процедурата. По подразбиране е DEFINER.
Коментар: за целите на документацията по подразбиране е ""
Извикване на съхранена процедура
ИЗВИКАТЕ име на_съхранената_процедура (параметр1, параметър2, ....)
CALL процедура1 (10, "параметър на низ", @parameter_var);
Промяна на съхранена процедура
MySQL предоставя израз ALTER PROCEDURE за модифициране на процедури, но е подходящ за промяна само на няколко характеристики. Ако трябва да промените параметрите или тялото на процедурата, трябва да я изтриете и създадете отново.
Изтриванесъхраненипроцедури
ПРОЦЕДУРА ОТПУСКАНЕ, АКО СЪЩЕСТВУВА p2;
Това е проста команда. Изразът IF EXISTS улавя грешка, ако такава процедура не съществува.
Настроики
CREATE PROCEDURE proc1 (): празен списък с параметри
СЪЗДАВАНЕ НА ПРОЦЕДУРА proc1 (IN varname DATA-TYPE): един входен параметър. Думата IN не е задължителна, тъй като параметрите по подразбиране са IN (входящи).
СЪЗДАВАНЕ НА ПРОЦЕДУРА proc1 (ВЪН ТИП ДАННИ ИЗВЪН име на вар.): един параметър за връщане.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): един параметър, както за въвеждане, така и за връщане.
Синтаксисът за деклариране на променлива изглежда така:
DECLARE varname DATA-TYPE DEFAULT стойност по подразбиране;
Включете в процедурите си реда - SET NOCOUNT ON:
С всеки DML оператор SQL сървърът внимателно ни връща съобщение, съдържащо броя на обработените записи. Тази информация може да ни бъде полезна при отстраняване на грешки в кода, но след това ще бъде напълно безполезна. Предписвайки SET NOCOUNT ON, ние деактивираме тази функция. За съхранени процедури, съдържащи няколко израза или цикли \ и, това действие може да даде значително увеличение на производителността, тъй като количеството трафик ще бъде значително намалено.
Transact-SQL
Използвайте името на схемата с името на обекта:
Е, тук мисля, че е ясно. Тази операция казва на сървъра къде да търси обекти и вместо произволно да рови из кошчетата си, той веднага ще знае къде трябва да отиде и какво да вземе. С голям брой бази данни, таблици и съхранени процедури може значително да ни спести време и нерви.
Transact-SQL
SELECT * FROM dbo.MyTable --Добре е да направите това - Вместо SELECT * FROM MyTable - И е лошо да направите това --Извикване на EXEC процедура dbo.MyProc - Отново добре --Вместо EXEC MyProc --Лошо!
Не използвайте префикса "sp_" в името на вашите съхранени процедури:
Ако името на нашата процедура започва с "sp_", SQL Server ще търси първо в главната си база данни. Въпросът е, че този префикс се използва за лични вътрешни сървърни съхранени процедури. Следователно използването му може да доведе до допълнителни разходи и дори до неправилен резултат, ако в базата й се намери процедура със същото име като вашето.
Използвайте АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ 1) вместо АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ *):
За да проверим за запис в друга таблица, използваме оператора IF EXISTS. Този израз връща true, ако поне една стойност е върната от вътрешния израз, няма значение "1", всички колони или таблица. Върнатите данни по принцип не се използват по никакъв начин. По този начин, за да компресирате трафика по време на предаване на данни, е по-логично да използвате "1", както е показано по-долу.
