Създаване на sql съхранена процедура. Какво представляват съхранените процедури в T-SQL? По-малък шанс за SQL инжектиране

Концепцията за съхранена процедура

Съхранени процедуриса групи от взаимосвързани SQL изрази, чието използване прави работата на програмиста по-лесна и гъвкава, тъй като съхранена процедурачесто е много по-проста от последователност от отделни SQL изрази. Съхранените процедури са набор от команди, състоящи се от един или повече SQL изрази или функции и съхранени в компилиран вид в база данни. Изпълнение в базата данни съхранени процедуриВместо отделни SQL изрази, потребителят има следните предимства:

• необходимите оператори вече се съдържат в базата данни;
• всички минаха сцената разбори са в изпълним формат; преди изпълнение на съхранена процедура SQL сървъргенерира план за изпълнение за него, извършва неговата оптимизация и компилация;
• съхранени процедуриподдържа модулно програмиране, тъй като ви позволяват да разделяте големи задачи на независими, по-малки и по-лесни за управление части;
• съхранени процедуриможе да причини други съхранени процедурии функции;
• съхранени процедуриможе да се извиква от други видове приложни програми;
• обикновено, съхранени процедуриизпълняват по-бързо от поредица от отделни изрази;
• съхранени процедурипо-лесни за използване: те могат да се състоят от десетки или стотици команди, но за да ги изпълните, просто трябва да посочите името на желаната съхранена процедура. Това ви позволява да намалите размера на заявката, изпратена от клиента към сървъра, и следователно натоварването на мрежата.

Съхраняването на процедури на същото място, където се изпълняват, намалява количеството данни, прехвърляни по мрежата, и подобрява цялостната производителност на системата. Приложение съхранени процедуриопростява поддръжката софтуерни системии извършване на промени в тях. Обикновено всички ограничения на целостта под формата на правила и алгоритми за обработка на данни се изпълняват на сървъра на базата данни и са достъпни за крайното приложение като набор съхранени процедури, които представляват интерфейса за обработка на данни. За да се гарантира целостта на данните, както и за целите на сигурността, приложението обикновено не получава директен достъп до данните - цялата работа с него се извършва чрез извикване на определени съхранени процедури.

Този подход прави много лесно модифицирането на алгоритмите за обработка на данни, които незабавно стават достъпни за всички мрежови потребители, и предоставя възможност за разширяване на системата, без да правите промени в самото приложение: просто променете съхранена процедурана сървъра на базата данни. Разработчикът не трябва да прекомпилира приложението, да създава негови копия или да инструктира потребителите да работят с новата версия. Потребителите може дори да не знаят, че са направени промени в системата.

Съхранени процедурисъществуват независимо от таблици или други обекти на база данни. Те се извикват от клиентската програма, др съхранена процедураили задействане. Програмистът може да управлява правата за достъп до съхранена процедура, разрешаване или забрана на неговото изпълнение. Промяна на кода съхранена процедураразрешено само от неговия собственик или член на фиксирана роля на база данни. Ако е необходимо, можете да прехвърлите собствеността върху него от един потребител на друг.

Съхранени процедури в среда на MS SQL Server

Когато работят с SQL Server, потребителите могат да създават свои собствени процедури, които изпълняват определени действия. Съхранени процедуриса пълноценни обекти на база данни и следователно всеки от тях се съхранява в конкретна база данни. Директно обаждане съхранена процедурае възможно само ако се извършва в контекста на базата данни, където се намира процедурата.

Видове съхранени процедури

SQL Server има няколко типа съхранени процедури.

• Система съхранени процедурипредназначени за извършване на различни административни действия. С тяхна помощ се извършват почти всички дейности по администриране на сървъра. Можем да кажем, че системно съхранени процедуриса интерфейс, който осигурява работа със системни таблици, която в крайна сметка се свежда до промяна, добавяне, изтриване и извличане на данни от системни таблици на потребителски и системни бази данни. Система съхранени процедуриимат префикс sp_, съхраняват се в системната база данни и могат да бъдат извикани в контекста на всяка друга база данни.
• Персонализиран съхранени процедуриизпълнява определени действия. Съхранени процедури– пълноценен обект на база данни. В резултат на това всеки съхранена процедурасе намира в конкретна база данни, където се изпълнява.
• Временно съхранени процедурисъществуват само известно време, след което се унищожават автоматично от сървъра. Те се делят на локални и глобални. Местен временен съхранени процедуримогат да бъдат извикани само от връзката, в която са създадени. Когато създавате такава процедура, трябва да й дадете име, което започва с един знак #. Като всички временни обекти, съхранени процедуриот този тип се изтриват автоматично, когато потребителят прекъсне връзката или сървърът се рестартира или спре. Глобален временен съхранени процедуриса налични за всякакви връзки от сървър, който има същата процедура. За да го дефинирате, просто му дайте име, започващо със знаците ##. Тези процедури се изтриват, когато сървърът се рестартира или спре, или когато връзката в контекста, в който са създадени, се затвори.

Създаване, модифициране и изтриване на съхранени процедури

Създаване съхранена процедуравключва решаване на следните проблеми:

• определяне на вида на създадените съхранена процедура: временно или персонализирано. Освен това можете да създадете своя собствена система съхранена процедура, давайки му име с префикс sp_ и го поставя в системната база данни. Тази процедура ще бъде достъпна в контекста на всяка локална сървърна база данни;
• права за достъп при планиране. Докато създавате съхранена процедуратрябва да се има предвид, че той ще има същите права за достъп до обектите на базата данни като потребителя, който го е създал;
• определение параметри на съхранена процедура. Подобно на процедурите, включени в повечето езици за програмиране, съхранени процедуриможе да има входни и изходни параметри;
• разработка на код съхранена процедура. Кодът на процедурата може да съдържа поредица от всякакви SQL команди, включително извиквания към други съхранени процедури.

Създаване на нов и промяна на съществуващ съхранена процедуранаправено с помощта на следната команда:

<определение_процедуры>::= (CREATE | ALTER ) PROC име_на_процедура [;номер] [(@параметър_име тип_данни) [=по подразбиране] ][,...n] AS sql_operator [...n]

Нека да разгледаме параметрите на тази команда.

Използвайки префиксите sp_ ​​, #, ##, създадената процедура може да бъде определена като системна или временна. Както можете да видите от синтаксиса на командата, не е позволено да се указва името на собственика, който ще притежава създадената процедура, както и името на базата данни, където тя трябва да бъде разположена. Така, за да се постави създаденото съхранена процедурав конкретна база данни, трябва да подадете командата CREATE PROCEDURE в контекста на тази база данни. При обръщане от тялото съхранена процедурасъкратените имена могат да се използват за обекти от същата база данни, т.е. без да се посочва името на базата данни. Когато имате нужда от достъп до обекти, намиращи се в други бази данни, указването на името на базата данни е задължително.

Числото в името е идентификационен номер съхранена процедура, което уникално го идентифицира в група от процедури. За по-лесно управление процедурите са логически от един и същи тип съхранени процедуримогат да бъдат групирани, като им се даде едно и също име, но различни идентификационни номера.

За прехвърляне на входни и изходни данни в създадените съхранена процедурамогат да се използват параметри, имената на които, подобно на имената на локалните променливи, трябва да започват със символа @. един съхранена процедураМожете да посочите няколко параметъра, разделени със запетаи. Тялото на процедурата не трябва да използва локални променливи, чиито имена съвпадат с имената на параметрите на тази процедура.

За да определите типа данни, които съответстват параметър на съхранена процедура, всеки тип е подходящ SQL данни, включително дефинирани от потребителя. Типът данни CURSOR обаче може да се използва само като изходен параметър съхранена процедура, т.е. като посочите ключовата дума OUTPUT.

Наличието на ключовата дума OUTPUT означава, че съответният параметър е предназначен да върне данни от съхранена процедура. Това обаче не означава, че параметърът не е подходящ за предаване на стойности към съхранена процедура. Указването на ключовата дума OUTPUT инструктира сървъра да излезе съхранена процедураприсвоете текущата стойност на параметъра на локалната променлива, която е зададена при извикване на процедурата като стойност на параметъра. Обърнете внимание, че когато зададете ключовата дума OUTPUT, стойността на съответния параметър при извикване на процедурата може да бъде зададена само с помощта на локална променлива. Всички изрази или константи, които са разрешени за регулярни параметри, не са разрешени.

Ключовата дума VARYING се използва във връзка с параметъра OUTPUT, който е от тип CURSOR. Това определя изходен параметърще има набор от резултати.

Ключовата дума DEFAULT представлява стойността, която съответства параметър по подразбиране. По този начин, когато извиквате процедура, не е необходимо изрично да указвате стойността на съответния параметър.

Тъй като сървърът кешира плана за изпълнение на заявката и компилирания код, при следващото извикване на процедурата ще се използват готовите стойности. Въпреки това, в някои случаи все още е необходимо да се прекомпилира кодът на процедурата. Указването на ключовата дума RECOMPILE инструктира системата да създаде план за изпълнение съхранена процедуравсеки път, когато тя се обади.

Параметърът FOR REPLICATION е необходим при репликиране на данни и активиране на създадените съхранена процедуракато статия за публикуване.

Ключовата дума ENCRYPTION инструктира сървъра да шифрова кода съхранена процедура, който може да осигури защита срещу използването на патентовани алгоритми, които изпълняват работата съхранена процедура.

Ключовата дума AS се поставя в началото на самото тяло съхранена процедура, т.е. набор от SQL команди, с помощта на които ще се изпълнява това или онова действие. В тялото на процедурата могат да се използват почти всички SQL команди, могат да се декларират транзакции, да се задават заключвания и да се извикват други. съхранени процедури. Изход от съхранена процедураможе да се направи с помощта на командата RETURN.

Премахване на съхранена процедураизпълнява се от командата:

ОТПУСКАНЕ НА ПРОЦЕДУРА (име_на_процедура) [,...n]

Изпълнение на съхранена процедура

За изпълни съхранена процедураИзползваната команда е:

[[ EXEC [ UTE] име_на_процедура [;номер] [[@parameter_name=](стойност | @име_на_променлива) |][,...n]

Ако обаждането съхранена процедуране е единствената команда в пакета, необходимо е наличието на командата EXECUTE. Освен това тази команда е необходима за извикване на процедура от тялото на друга процедура или тригер.

Използването на ключовата дума OUTPUT при извикване на процедура е разрешено само за параметри, които са били декларирани, когато създаване на процедурас ключовата дума OUTPUT.

Когато ключовата дума DEFAULT е указана за параметър при извикване на процедура, тя ще бъде използвана стойност по подразбиране. Естествено, посочената дума DEFAULT е разрешена само за тези параметри, за които е дефинирана стойност по подразбиране.

Синтаксисът на командата EXECUTE показва, че имената на параметрите могат да бъдат пропуснати при извикване на процедура. В този случай обаче потребителят трябва да посочи стойностите за параметрите в същия ред, в който са били изброени, когато създаване на процедура. Присвояване на параметър стойност по подразбиране, не можете просто да го пропуснете, когато изброявате. Ако искате да пропуснете параметрите, за които е дефиниран стойност по подразбиране, достатъчно е изрично да посочите имената на параметрите при извикване съхранена процедура. Освен това по този начин можете да изброите параметрите и техните стойности в произволен ред.

Имайте предвид, че при извикване на процедура се посочват или имена на параметри със стойности, или само стойности без име на параметър. Комбинирането им не е разрешено.

Пример 12.1. Процедура без параметри. Разработване на процедура за получаване на имената и цената на стоките, закупени от Иванов.

CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE Клиент .Фамилия='Иванов' Пример 12.1. Процедура за получаване на имената и стойностите на стоките, закупени от Иванов.

За достъп до процедуратаможете да използвате командите:

EXEC my_proc1 или my_proc1

Процедурата връща набор от данни.

Пример 12.2. Процедура без параметри. Създайте процедура за намаляване на цената на първокласните стоки с 10%.

За достъп до процедуратаможете да използвате командите:

EXEC my_proc2 или my_proc2

Процедурата не връща никакви данни.

Пример 12.3. Процедура с входен параметър. Създайте процедура за получаване на имената и цените на артикулите, закупени от даден клиент.

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode) ON Customer. CustomerCode =Transaction.ClientCode WHERE Client.LastName=@k Пример 12.3. Процедура за получаване на имената и цените на артикулите, закупени от даден клиент.

За достъп до процедуратаможете да използвате командите:

EXEC my_proc3 "Иванов" или my_proc3 @k="Иванов"

Пример 12.4.. Създайте процедура за намаляване на цената на продукт от даден вид в съответствие с посочения %.

За достъп до процедуратаможете да използвате командите:

EXEC my_proc4 "Вафли",0.05 или EXEC my_proc4 @t="Вафли", @p=0.05

Пример 12.5. Процедура с входни параметрии стойности по подразбиране. Създайте процедура за намаляване на цената на продукт от даден вид в съответствие с посочения %.

CREATE PROC my_proc5 @t VARCHAR(20)=’Candy`, @p FLOAT=0.1 AS UPDATE Product SET Price=Price*(1-@p) WHERE Type=@t Пример 12.5. Процедура с входни параметри и стойности по подразбиране. Създайте процедура за намаляване на цената на продукт от даден вид в съответствие с посочения %.

За достъп до процедуратаможете да използвате командите:

EXEC my_proc5 "Вафли",0.05 или EXEC my_proc5 @t="Вафли", @p=0.05 или EXEC my_proc5 @p=0.05

В този случай цената на бонбоните се намалява (стойността на типа не се посочва при извикване на процедурата и се взема по подразбиране).

В последния случай и двата параметъра (както тип, така и процент) не са посочени при извикване на процедурата; техните стойности се приемат по подразбиране.

Пример 12.6. Процедура с входни и изходни параметри. Създайте процедура за определяне на общата цена на продадените стоки през определен месец.

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Product.Price*Transaction.Quantity) FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Month(Transaction.Date) HAVING Month( Transaction.Date)=@m Пример 12.6. Процедура с входни и изходни параметри. Създайте процедура за определяне на общата цена на продадените стоки през определен месец.

За достъп до процедуратаможете да използвате командите:

DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

Този блок от команди ви позволява да определите цената на продадените стоки през януари ( входен параметърмесец е посочен като 1).

Създайте процедура за определяне на общото количество стоки, закупени от фирмата, в която работи даден служител.

Първо ще разработим процедура за определяне на компанията, в която служителят работи.

Пример 12.7.Използване вложени процедури. Създайте процедура за определяне на общото количество стоки, закупени от фирмата, в която работи даден служител.

След това ще създадем процедура, която изчислява общото количество стоки, закупени от компанията, която ни интересува.

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Transaction.Quantity) FROM Client INNER JOIN Transaction ON Client.ClientCode= Transaction.ClientCode GROUP BY Client.Firm HAVING Client.Company=@firm Пример 12.7. Създайте процедура за определяне на общото количество стоки, закупени от фирмата, в която работи даден служител.

Процедурата се извиква с помощта на командата:

DECLARE @k INT EXEC my_proc8 ‘Иванов’,@k OUTPUT SELECT @k

Съхранена процедура - обект на база данни, който е набор от 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 стойност по подразбиране;

Използването на съхранени функции на СУБД за внедряване на бизнес логика или част от нея винаги е било пречка. От едната страна на барикадата са администраторите на бази данни и програмистите на бази данни, от другата са бекенд разработчиците.
Ще рискувам да си навлека гнева и от двата лагера, но все пак ще обобщя плюсовете и минусите и ще изложа мислите си кога си струва да напиша код в съхранени функции и кога трябва да бъде изнесен навън.

Да започнем с аргументите против:

Омазняване на бизнес логиката

Оскъдността на езика на СУБД

Непоносимост към съхранени функции

Липса на необходимите умения сред екипа и висока „цена“ на съответните специалисти

Сега плюсовете:

Скорост

Скриване на структурата на данните

Гъвкаво управление на правата за достъп

По-малък шанс за SQL инжектиране

Повторно използване на SQL

Лесно отстраняване на грешки в SQL

Кога да напиша бизнес логика в базата данни?

Ако скоростта на обработка на данни е важна

Когато целостта и последователността на данните са важни

Данните имат сложна, но добре установена структура

Кога да пусна кода?

Работа с външни данни

Комплексни алгоритми

Високо натоварване

Изводът е, че няма ясен алгоритъм. Всеки път решението остава на архитектите и мениджъра и определя дали проектът ще затъне в проблеми с условията на състезание и несъответствие на NoSQL данни, проблеми с производителността и отстраняването на грешки на ORM заявки или ще се сблъска с проблеми с мащабирането на СУБД когато използвате запаметени функции. Затова взимайте правилните решения :)

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.
Първият ми пост, не съдете прекалено строго.

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 съхранените процедури, но тяхното изучаване е необходимо за тези, които създават впечатляващи бази данни. Има и голям, дори огромен брой нюанси, които могат да бъдат полезни при изучаване на съхранени процедури, но това е необходимо повече за тези, които планират да се включат в програмирането, включително професионално.