Какво представляват съхранените процедури. CREATE PROCEDURE клауза. Лесно отстраняване на грешки в SQL
Включете във вашите процедури реда - 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 сървърпърво ще погледне в основната си база данни. Факт е, че този префикс се използва за лични вътрешни съхранени процедури на сървъра. Поради това използването му може да доведе до допълнителни разходи и дори неправилни резултати, ако процедура със същото име като вашето бъде открита в неговата база данни.
Използвайте АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ 1) вместо АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ *):
За да проверим дали даден запис съществува в друга таблица, използваме израза IF EXISTS. Този изразвръща true, ако поне една стойност е върната от вътрешния израз, няма значение „1“, всички колони или таблица. Върнатите данни по принцип не се използват по никакъв начин. По този начин, за да компресирате трафика по време на предаване на данни, е по-логично да използвате "1", както е показано по-долу.
Съхранена процедурасъхранена процедура) е именуван програмен обект на база данни. SQL Server има няколко вида запаметени процедури.
Системни съхранени процедурисистемна съхранена процедура) се предоставят от разработчиците на СУБД и се използват за извършване на действия върху системния каталог или получаване на системна информация. Имената им обикновено започват с префикса "sp_". Съхранените процедури от всички типове се изпълняват с командата EXECUTE, която може да бъде съкратена до EXEC. Например съхранената процедура sp_helplogins, изпълнявана без параметри, генерира два отчета за имена на акаунти (Английски)влизания) и съответните им потребители във всяка база данни (Английски)потребители).
EXEC sp_helplogins;
За да дадете представа за действията, извършени с помощта на системни съхранени процедури, в табл. 10.6 показва някои примери. Общо има повече от хиляда системни съхранени процедури в SQL Server.
Таблица 10.6
Примери за системни съхранени процедури на SQL Server
Потребителят може да създава съхранени процедури в потребителски бази данни и в база данни за временни обекти. В последния случай съхранената процедура ще бъде времеви.Както при временните таблици, името на временна съхранена процедура трябва да започва с префикс "#", ако е локална временно съхранена процедура, или "##", ако е глобална. Локална временна процедура може да се използва само във връзката, в която е създадена, глобална може да се използва и в други връзки.
Програмируемите обекти на SQL Server могат да бъдат създадени или с помощта на инструменти на Transact-SQL, или с помощта на модули (Английски)асемблиране) в средата на CRL (Common Language Runtime) на Microsoft .Net Framework. В този урок ще бъде разгледан само първият метод.
За да създадете съхранени процедури, използвайте командата CREATE PROCEDURE (може да бъде съкратена от PROC), чийто формат е даден по-долу:
CREATE (PROC I ПРОЦЕДУРА) proc_name [ ; номер]
[(gparameter data_type )
[„по подразбиране] |
[СЪС
[ ЗА РЕПЛИКАЦИЯ ]
AS ([ НАЧАЛО ] sql_statement [;] [ ...n ] [ КРАЙ ] )
Ако съхранена процедура (или тригер, функция, изглед) е създадена с опцията ENCRYPTION, нейният код се преобразува по такъв начин, че текстът да стане нечетлив. В същото време, както е отбелязано в , използваният алгоритъм е пренесен от по-ранни версии на SQL Server и не може да се счита за надежден алгоритъм за защита - има помощни програми, които ви позволяват бързо да извършите обратното преобразуване.
Опцията RECOMPILE указва, че при всяко извикване на процедурата системата ще компилира отново текста. В обичайния случай процедурата, компилирана при първото изпълнение, се съхранява в кеша, което ви позволява да увеличите производителността.
EXECUTE AS дефинира контекста на сигурността, в който трябва да се изпълни процедурата. След това една от стойностите f CALLER | СЕБЕ | СОБСТВЕНИК | "потребителско_име"). CALLER е стойността по подразбиране и означава, че кодът ще бъде изпълнен в контекста на сигурността на потребителя, който извиква този модул. Съответно, потребителят трябва да има разрешения не само за самия програмируем обект, но и за други обекти на база данни, засегнати от него. EXECUTE AS SELF означава използване на контекста на потребител, създаващ или модифициращ програмируем обект. OWNER указва, че кодът ще бъде изпълнен в контекста на текущия собственик на процедурата. Ако за него не е дефиниран собственик, тогава се приема собственикът на схемата, към която принадлежи. EXECUTE AS "user_name" ви позволява изрично да посочите потребителското име (в единични кавички).
Параметрите могат да бъдат зададени за процедура. Това са локални променливи, използвани за предаване на стойности към процедура. Ако даден параметър е деклариран с ключовата дума OUTPUT (или накратко OUT), това е изход: стойността, дадена му в процедурата, може да се използва от програмата, която е извикала процедурата, след като приключи. Ключовата дума READONLY означава, че стойността на параметъра не може да се променя вътре в съхранената процедура.
На параметрите могат да бъдат присвоени стойности по подразбиране, които ще се използват, ако стойността на параметъра не е изрично посочена при извикване на процедурата. Помислете за пример:
CREATE PROC surma (@a int, @b int=0,
©result int OUTPUT) AS
SET @result=0a+0b
Създадохме процедура с три параметъра, където параметърът @b има стойност по подразбиране 0, а параметърът @result е изходен параметър: чрез него стойността се връща на извикващата програма. Извършваните действия са съвсем прости - изходният параметър получава стойността на сумата от двата входа.
Когато работите в SQL Server Management Studio, създадената съхранена процедура може да бъде намерена в раздела за програмируеми обекти на база данни (Английски)Програмируемост) в раздела за запомнени процедури (Фигура 10.2).
Когато извиквате процедура, както променливи, така и константи могат да се използват като входни параметри. Нека разгледаме два примера. В първия, входните параметри на процедурата са изрично зададени от константи, ключовата дума OUTPUT е посочена за изходния параметър в извикването. Във втория вариант стойността на променливата се използва като първи входен параметър, а за втория параметър се използва ключова дума DEFAULT указва, че трябва да се използва стойността по подразбиране:
Ориз. 10.2.
DECLARE @with int;
EXEC сума 10,5,@c ИЗХОД;
PRINT0c; - Ще бъдат показани 15
ДЕКЛАРИРАНЕ Gi int = 5;
- при повикване използвайте стойността по подразбиране
EXEC сума Gi, ПО ПОДРАЗБИРАНЕ, 0s ИЗХОД;
PRINT0c; - Ще се покаже 5
Помислете сега за пример с анализ на кода за връщане, с който процедурата завършва. Нека е необходимо да се изчисли колко книги в таблицата Bookl са публикувани в даден диапазон от години. В този случай, ако началната година е по-голяма от крайната, процедурата връща "1" и не се брои, в противен случай броим броя на книгите и връщаме 0:
CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) AS
АКО 0FirsYear>0LastYear RETURN 1
SET @result= (SELECT COUNT(*) FROM dbo.Bookl
КЪДЕ МЕЖДУ 0FirsYear И 0LastYear);
Помислете за вариант на извикване на тази процедура, при който кодът за връщане се съхранява в целочислената променлива 0ret, след което се анализира стойността му (в този случай ще бъде 1). използвани в оператора Функция PRINT CAST се използва за преобразуване на стойността на целочислена променлива Gres в тип низ:
ДЕКЛАРИРАНЕ 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret=l PRINT "Началната година е по-голяма от крайната"
PRINT "Брой книги "+ CAST(Gres като varchar(20))
Съхранените процедури могат не само да четат данни от таблица, но и да променят данни и дори да създават таблици и редица други обекти на база данни.
Въпреки това, схеми, функции, тригери, процедури и изгледи не могат да бъдат създадени от съхранена процедура.
Следващият пример илюстрира както тези възможности, така и проблеми, свързани с обхвата на временните обекти. Следната съхранена процедура проверява за съществуването на временна таблица #Tab2; ако тази таблица не съществува, тя я създава. След това стойностите на две колони се въвеждат в таблицата #Tab2 и съдържанието на таблицата се показва от оператора SELECT:
CREATE PROC My_Procl (@id int, @name varchar(30))
АКО OBJECT_ID("tempdb.dbo.#Tab21) Е NULL
INSERT INTO dbo.#Tab2 (id, име) СТОЙНОСТИ (0id,0name)
ИЗБЕРЕТЕ * ОТ dbo. #Tab2 - #1
Преди първото извикване на запомнената процедура, нека създадем временната таблица #Tab2, използвана в нея. Обърнете внимание на оператора EXEC. В предишните примери параметрите бяха предадени на процедурата "по позиция", но в този случай се използва различен формат за подаване на параметри - "по име", името на параметъра и неговата стойност са изрично посочени:
CREATE TABLE dbo.#Tab2 (id int, name varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
ИЗБЕРЕТЕ * ОТ dbo.#Tab2; – №2
В горния пример операторът SELECT ще се изпълни два пъти: първият път - вътре в процедурата, вторият път - от фрагмента на извикващия код (маркиран с коментар "№ 2").
Преди второто извикване на процедура, нека изтрием временната таблица #Tab2. След това временната таблица със същото име ще бъде създадена от съхранената процедура:
ПУСКАНЕ НА ТАБЛИЦА dbo.#Tab2;
EXEC My_Procl 0name="Иван", 0id=2;
ИЗБЕРЕТЕ * ОТ dbo.#Tab2; – №2
В този случай само командата SELECT в процедурата (с коментара „Xa 1“) ще покаже данните. Изпълнението на SELECT "#2" ще доведе до грешка, тъй като временната таблица, създадена в съхранената процедура, вече ще бъде изтрита от базата данни tempdb, когато процедурата се върне.
Можете да премахнете запомнена процедура, като използвате командата DROP PROCEDURE. Неговият формат е показан по-долу. С един оператор можете да изтриете няколко съхранени процедури, като ги посочите разделени със запетаи:
DROP (PROC I ПРОЦЕДУРА) ( процедура ) [
Например, нека премахнем създадената преди това summa процедура:
DROP PROC summa;
Можете да направите промени в съществуваща процедура (всъщност да я замените) с помощта на оператора ALTER PROCEDURE (разрешете
съкращение PROC). С изключение на ключовата дума ALTER, форматът на оператора е почти същият като този на CREATE PROCEDURE. Например, нека променим процедурата dbo. rownum, като го настроите да се изпълнява в контекста на сигурността на собственика:
ALTER PROC dbo.rownum(SFirsYear int,
SLastYear int, Result int OUTPUT)
С ИЗПЪЛНЕНИЕ КАТО Собственик - задайте опция
АКО 0FirsYear>0LastYear RETURN 1 ELSE BEGIN
SET 0result= (SELECT COUNT(*) FROM dbo.Bookl
WHERE BETWEEN SFirsYear AND SLastYear);
В някои случаи може да се наложи динамично генериране на команда и нейното изпълнение на сървъра на базата данни. Тази задача може да бъде решена и с помощта на оператора EXEC. Следният пример избира записи от таблицата Bookl въз основа на условието, че атрибутът Year е равен на стойността, зададена от променливата:
ДЕКЛАРИРАНЕ 0y int = 2000;
EXEC("SELECT * FROM dbo.Bookl WHERE = " [имейл защитен]) ;
Изпълнението на динамично генерирани инструкции създава предпоставки за реализиране на компютърни атаки като "SQL инжектиране" (Английски) SQL инжекция). Същността на атаката е, че нарушителят инжектира свой собствен SQL код в динамично генерирана заявка. Това обикновено се случва, когато вградените параметри се вземат от резултатите, въведени от потребителя.
Нека променим малко предишния пример:
ДЕКЛАРИРАНЕ 0y varchar(100);
SET 0y="2OOO"; - това получихме от потребителя
Ако приемем, че сме получили стойността на низа, присвоена в оператора SET от потребителя (без значение как, например чрез уеб приложение), тогава примерът илюстрира "нормалното" поведение на нашия код.
ДЕКЛАРИРАНЕ 0y varchar(100);
SET 0y="2000; ИЗТРИВАНЕ ОТ dbo.Book2"; – инжекция
EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);
Препоръчително е, когато е възможно, в такива случаи да използвате системната съхранена процедура sp_executcsql, която ви позволява да контролирате типа на параметрите, което е една от бариерите пред SQL инжектирането. Без да разглеждаме подробно формата му, ще анализираме пример, подобен на представения по-рано:
ИЗПЪЛНИТЕ sp_executesql
N"SELECT * FROM dbo.Bookl WHERE =0y",
Това изрично указва типа на параметъра, използван в заявката, и SQL Server ще го контролира по време на изпълнение. Буквата "N" преди кавичките показва, че това е литерална константа на Unicode, както се изисква от процедурата. На параметър може да бъде присвоена не само постоянна стойност, но и стойността на друга променлива.
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.
Първият ми пост, не съдете строго.
Съхранена процедурае специален тип Transact-SQL партида с изявления, създадена с помощта на езика SQL и процедурни разширения. Основната разлика между пакет и съхранена процедура е, че последната се съхранява като обект на база данни. С други думи, съхранените процедури се съхраняват от страната на сървъра, за да се подобри производителността и последователността на повтарящи се задачи.
Database Engine поддържа съхранени процедури и системни процедури. Съхранените процедури се създават по същия начин като всички останали обекти на база данни, т.е. използвайки езика DDL. Системни процедурисе предоставят от Database Engine и могат да се използват за достъп и промяна на информация в системния каталог.
Когато създавате запомнена процедура, можете да дефинирате незадължителен списък с параметри. По този начин процедурата ще приема съответните аргументи при всяко извикване. Съхранените процедури могат да върнат стойност, съдържаща дефинирана от потребителя информация или, в случай на грешка, подходящо съобщение за грешка.
Съхранената процедура се компилира предварително, преди да бъде съхранена като обект в базата данни. Предварително компилираната форма на процедурата се съхранява в базата данни и се използва при всяко нейно извикване. Това свойство на съхранените процедури осигурява важната полза от елиминирането (в почти всички случаи) на повторно компилиране на процедури и получаване на съответно подобрение на производителността. Това свойство на съхранените процедури също има положителен ефект върху количеството данни, обменяни между системата от бази данни и приложенията. По-специално, извикването на съхранена процедура от няколко хиляди байта може да изисква по-малко от 50 байта. Когато множество потребители изпълняват повтарящи се задачи, използвайки съхранени процедури, кумулативният ефект от тези спестявания може да бъде значителен.
Съхранените процедури могат да се използват и за следните цели:
за създаване на регистър с регистрационни файлове за действия с таблици на база данни.
Използването на съхранени процедури осигурява ниво на контрол на сигурността, което надхвърля сигурността, предоставена от използването на изрази GRANT и REVOKE, които предоставят различни привилегии за достъп на потребителите. Това е възможно, тъй като разрешението за изпълнение на запомнена процедура е независимо от разрешението за модифициране на обектите, съдържащи се в запомнената процедура, както е описано в следващия раздел.
Съхранените процедури, които създават регистрационни файлове на операции за запис и/или четене на таблици, предоставят допълнителна възможностсигурност на базата данни. Използвайки такива процедури, администраторът на базата данни може да следи модификациите, направени в базата данни от потребители или приложения.
Създаване и изпълнение на съхранени процедури
Съхранените процедури се създават с помощта на израза СЪЗДАВАНЕ НА ПРОЦЕДУРА, който има следния синтаксис:
CREATE PROC proc_name [((@param1) type1 [ VARYING] [= default1] )] (, …) AS batch | ВЪНШНО ИМЕ име на_метод Синтаксис
Параметърът schema_name указва името на схемата, което е присвоено от собственика на генерираната съхранена процедура. Параметърът proc_name указва името на съхранената процедура. Параметърът @param1 е параметър на процедура (формален аргумент), чийто тип данни е посочен от параметъра type1. Параметрите на процедурата са локални в рамките на процедура, точно както локалните променливи са локални в рамките на пакет. Параметрите на процедурата са стойности, които се предават от повикващия на процедурата за използване в нея. Параметърът default1 указва стойността по подразбиране за съответния параметър на процедурата. (Стойността по подразбиране може също да бъде NULL.)
Опция ИЗХОДуказва, че даден параметър на процедура може да се върне и може да се използва за връщане на стойност от съхранена процедура към извикващата процедура или система.
Както бе споменато по-рано, предварително компилираната форма на процедурата се съхранява в базата данни и се използва при всяко нейно извикване. Ако по някаква причина съхранена процедура трябва да се компилира всеки път, когато се извиква, декларацията на процедурата използва опция С РЕКОМПИЛИРАНЕ. Използването на опцията WITH RECOMPILE отрича едно от най-важните предимства на съхранените процедури: подобряването на производителността, дължащо се на една компилация. Следователно опцията WITH RECOMPILE трябва да се използва само ако има чести промени в обектите на базата данни, използвани от запомнената процедура.
ИЗПЪЛНИТЕ КАТО офертадефинира контекста на сигурността, в който трябва да се изпълни съхранената процедура, след като бъде извикана. Чрез задаване на този контекст, Database Engine може да контролира избора на потребителски акаунти за проверка на разрешения за достъп до обекти, посочени от тази съхранена процедура.
По подразбиране само членовете на фиксираната сървърна роля на sysadmin и db_owner или db_ddladmin фиксираната роля на базата данни могат да използват оператора CREATE PROCEDURE. Въпреки това членовете на тези роли могат да присвоят това право на други потребители, като използват инструкцията ПРОЦЕДУРА ЗА ПРЕДОСТАВЯНЕ НА СЪЗДАВАНЕ.
Примерът по-долу показва как да създадете проста съхранена процедура за работа с таблицата Project:
ИЗПОЛЗВАЙТЕ SampleDb; ПРОЦЕДУРА ЗА СЪЗДАВАНЕ НА УВЕЛИЧАВАНЕ НА БЮДЖЕТ (@percent INT=5) КАТО АКТУАЛИЗИРАНЕ НА ПРОЕКТ БЮДЖЕТ = Бюджет + Бюджет * @процент/100;
Както бе споменато по-рано, за разделяне на два пакета се използва GO инструкция. Операторът CREATE PROCEDURE не може да се комбинира с други оператори на Transact-SQL в същия пакет. Съхранената процедура IncreaseBudget увеличава бюджетите за всички проекти с определен процент, определен от параметъра @percent. Процедурата също дефинира процентна стойност по подразбиране (5), която се прилага, ако този аргумент не присъства по време на изпълнение на процедурата.
Съхранените процедури имат достъп до таблици, които не съществуват. Това свойство ви позволява да дебъгвате кода на процедурата, без първо да създавате подходящите таблици и дори без да се свързвате към целевия сървър.
За разлика от основните съхранени процедури, които винаги се съхраняват в текущата база данни, е възможно да се създадат временни съхранени процедури, които винаги се поставят във временната системна база данни tempdb. Една от причините за създаване на временно съхранени процедури може да бъде избягването на повтарящо се изпълнение на определена група изрази при свързване към база данни. Можете да създадете локални или глобални временни процедури. За да направите това, името на локалната процедура се посочва с един знак # (#proc_name), а името на глобалната процедура се посочва с двоен знак (##proc_name).
Локална временно съхранена процедура може да бъде изпълнена само от потребителя, който я е създал, и само по време на връзката към базата данни, в която е създадена. Глобална временна процедура може да бъде изпълнена от всички потребители, но само докато не бъде прекратена последната връзка, на която се изпълнява (обикновено връзката на създателя на процедурата).
Жизненият цикъл на съхранена процедура се състои от две фази: нейното създаване и нейното изпълнение. Всяка процедура се създава веднъж и се изпълнява много пъти. Съхранената процедура се изпълнява от Изявления EXECUTEпотребител, който притежава процедурата или има EXECUTE право за достъп до процедурата. Операторът EXECUTE има следния синтаксис:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] стойност | [@parameter1=] @variable ] | DEFAULT).. Синтаксисни конвенции
С изключение на параметъра return_status, всички параметри на оператора EXECUTE имат същата булева стойност като параметрите на оператора CREATE PROCEDURE със същото име. Параметърът return_status дефинира целочислена променлива, която съхранява състоянието на връщане на процедурата. Стойност може да бъде присвоена на параметър, като се използва или константа (стойност), или локална променлива (@variable). Редът на наименуваните стойности на параметри не е важен, но ненаименуваните стойности на параметри трябва да бъдат предоставени в реда, в който са дефинирани в израза CREATE PROCEDURE.
Клауза DEFAULTпредоставя стойности по подразбиране за параметър на процедура, който е посочен в дефиницията на процедурата. Когато дадена процедура очаква стойност за параметър, за който не е дефинирана стойност по подразбиране и параметърът липсва или е указана ключовата дума DEFAULT, възниква грешка.
Когато операторът EXECUTE е първият оператор в пакета, ключовата дума EXECUTE може да бъде пропусната. Въпреки това е по-безопасно да включите тази дума във всеки пакет. Използването на оператора EXECUTE е показано в примера по-долу:
ИЗПОЛЗВАЙТЕ SampleDb; ИЗПЪЛНЕНИЕ Увеличаване на бюджета 10;
Изявлението EXECUTE в този пример изпълнява съхранената процедура IncreaseBudget, която увеличава бюджета на всички проекти с 10%.
Следният пример показва как да създадете съхранена процедура за обработка на данни в таблиците Employee и Works_on:
Процедурата ModifyEmpId в примера илюстрира използването на съхранени процедури като част от процеса на референтна цялост (в този случай между таблиците Employee и Works_on). Такава съхранена процедура може да се използва в дефиниция на тригер, което всъщност налага референтна цялост.
Следващият пример показва използването на клаузата OUTPUT в запомнена процедура:
Тази съхранена процедура може да бъде изпълнена с помощта на следните оператори:
DECLARE @quantityDeleteEmployee INT; ИЗПЪЛНЕТЕ DeleteEmployee @empId=18316, @ [имейл защитен]ИЗХОД; PRINT N"Изтрити служители: " + convert(nvarchar(30), @quantityDeleteEmployee);
Тази процедура отчита броя на проектите, върху които работи служител с номер на персонала @empId, и присвоява получената стойност на параметъра ©counter. След изтриване на всички редове за даден персонален номер от таблиците Employee и Works_on, изчислената стойност се присвоява на променливата @quantityDeleteEmployee.
Стойността на параметъра се връща към извикващата процедура само ако е указана опцията OUTPUT. В примера по-горе процедурата DeleteEmployee предава параметъра @counter на извикващата процедура, така че съхранената процедура връща стойността на системата. Следователно параметърът @counter трябва да бъде указан както в опцията OUTPUT, когато декларирате процедурата, така и в оператора EXECUTE, когато я извиквате.
WITH RESULTS SETS клауза на оператор EXECUTE
В SQL Server 2012 се въвежда изразът EXECUTE С клауза НАБОРИ ЗА РЕЗУЛТАТИ A, който при определени условия може да промени формата на набора от резултати на съхранената процедура.
Следващите два примера ще ви помогнат да обясните това изречение. Първият пример е въвеждащ пример, който показва как може да изглежда резултатът, когато клаузата WITH RESULTS SETS е пропусната:
Процедурата EmployeesInDept е проста процедура, която показва персоналните номера и фамилните имена на всички служители, работещи в определен отдел. Номерът на отдела е параметър на процедурата и трябва да се посочи при извикване на процедурата. Изпълнението на тази процедура извежда таблица с две колони, чиито заглавия съвпадат с имената на съответните колони в таблицата на базата данни, т.е. id и фамилия. За да промени заглавките на колоните с резултати (както и техния тип данни), SQL Server 2012 използва новата клауза WITH RESULTS SETS. Приложението на тази клауза е показано в примера по-долу:
ИЗПОЛЗВАЙТЕ SampleDb; EXEC EmployeesInDept "d1" С РЕЗУЛТАТНИ НАБОРИ (( INT NOT NULL, [Last Name] CHAR(20) NOT NULL));
Резултатът от изпълнението на запомнена процедура, извикана по този начин, ще бъде както следва:
Както можете да видите, изпълнението на съхранена процедура с помощта на клаузата WITH RESULT SETS в оператор EXECUTE ви позволява да промените имената и типа на данните на колоните с резултантен набор, произведени от процедурата. По този начин тази нова функционалност осигурява повече гъвкавост при изпълнение на съхранени процедури и поставяне на техните резултати в нова таблица.
Промяна на структурата на съхранените процедури
Database Engine също подкрепя твърдението ПРОМЯНА НА ПРОЦЕДУРАТАза промяна на структурата на съхранените процедури. Изявление ALTER PROCEDURE обикновено се използва за модифициране на Transact-SQL изрази в рамките на процедура. Всички параметри на оператора ALTER PROCEDURE имат същото значение като параметрите на оператора CREATE PROCEDURE със същото име. Основната цел на използването на този оператор е да се избегне замяната на съществуващи разрешения за съхранена процедура.
Database Engine поддържа Тип данни CURSOR. Този тип данни се използва за деклариране на курсори в съхранени процедури. Курсоре програмна конструкция, използвана за съхраняване на резултатите от заявка (обикновено набор от редове) и за позволяване на потребителите да показват този резултат ред по ред.
За да премахнете една или група от съхранени процедури, използвайте Изявление DROP PROCEDURE. Само собственикът на запомнената процедура или членовете на фиксираните роли db_owner и sysadmin могат да изтрият запомнена процедура.
Съхранени процедури и време за изпълнение на общ език
SQL Server поддържа Common Language Runtime (CLR), което ви позволява да разработвате различни обекти на база данни (съхранени процедури, дефинирани от потребителя функции, тригери, дефинирани от потребителя агрегати и дефинирани от потребителя типове данни) с помощта на C# и Visual Basic. Общата езикова среда за изпълнение също позволява тези обекти да бъдат изпълнени с помощта на общата система за изпълнение.
Средата за изпълнение на общ език се активира и деактивира чрез опцията clr_enabledсистемна процедура sp_configure, който се стартира за изпълнение от инструкцията ПРЕКОНФИГУРИРАНЕ. Следващият пример показва как можете да активирате общата езикова среда за изпълнение с помощта на системната процедура sp_configure:
ИЗПОЛЗВАЙТЕ SampleDb; EXEC sp_configure "clr_enabled",1 ПРЕКОНФИГУРИРАНЕ
Създаването, компилирането и записването на процедура с помощта на CLR изисква следната последователност от стъпки в посочения ред:
Създайте съхранена процедура в C# или Visual Basic и след това я компилирайте с помощта на подходящия компилатор.
Инструкция за използване СЪЗДАВАНЕ НА СЪЗДАВАНЕ, създайте подходящия изпълним файл.
Изпълнете процедура с помощта на оператора EXECUTE.
Фигурата по-долу показва графична схемаописаните по-горе стъпки. Следното е още Подробно описаниетози процес.
Първо създайте необходимата програма в някаква среда за разработка, например визуално студио. Компилирайте готовата програма в обектен код с помощта на компилатора C# или Visual Basic. Този код се съхранява във файл с библиотека с динамични връзки (.dll), който служи като източник за оператора CREATE ASSEMBLY, който създава междинен изпълним код. След това издайте оператор CREATE PROCEDURE, за да запазите кода, който се изпълнява като обект на база данни. Накрая изпълнете процедурата, като използвате познатия оператор EXECUTE.
Примерът по-долу показва изходния код на запомнената процедура в C#:
Използване на System.Data.SqlClient; използване на Microsoft.SqlServer.Server; public partial class StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) като "Брой служители" " + "от Служител"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )
Тази процедура изпълнява заявка за преброяване на броя на редовете в таблицата Employee. Използването на директиви в началото на програма указва пространствата от имена, необходими за нейното изпълнение. Използването на тези директиви ви позволява да указвате имена на класове в изходния код, без изрично да указвате съответните пространства от имена. След това се дефинира класът StoredProcedures, за който Атрибут SqlProcedure, което информира компилатора, че този клас е запомнена процедура. В кода на класа е дефиниран методът CountEmployees(). Връзката към системата от бази данни се осъществява чрез екземпляр на класа SqlConnection. За отваряне на връзка се използва методът Open() на този екземпляр. А Метод CreateCommand().позволява достъп до екземпляр на клас SqlCommnd, на който се предава желаната SQL команда.
В следния кодов фрагмент:
Cmd.CommandText = "изберете count(*) като "Брой служители" " + "от Служител";
използва оператор SELECT, за да преброи броя на редовете в таблицата Employee и да покаже резултата. Текстът на командата се указва чрез задаване на свойството CommandText на променливата cmd на екземпляра, върнат от метода CreateCommand(). Следващото се нарича Метод ExecuteScalar().екземпляр на SqlCommand. Този метод връща скаларна стойност, която се преобразува в целочислен тип данни int и се присвоява на променливата rows.
Вече можете да компилирате този код с помощта на Visual Studio. Добавих този клас към проекта с името CLRStoredProcedures, така че Visual Studio ще компилира асемблирането със същото име с разширението *.dll. Примерът по-долу показва следващата стъпка в създаването на съхранена процедура: създаване на кода за изпълнение. Преди да изпълните кода в този пример, трябва да знаете местоположението на компилирания .dll файл (обикновено се намира в папката Debug на проекта).
ИЗПОЛЗВАЙТЕ SampleDb; СЪЗДАВАЙТЕ СЪЗДАВАНЕ НА CLRStoredProcedures ОТ "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" С PERMISSION_SET = SAFE
Операторът CREATE ASSEMBLY приема управляван код като вход и създава подходящ обект, за който можете да създадете съхранени процедури за обща езикова среда за изпълнение (CLR), дефинирани от потребителя функции и тригери. Тази инструкция има следния синтаксис:
CREATE ASSEMBLY име_на_сборка [ AUTHORIZATION име_на_собственик ] FROM (dll_file) Синтаксис
Параметърът assembly_name указва името на сборката. Незадължителната клауза AUTHORIZATION указва името на роля като собственик на този сбор. Клаузата FROM указва пътя, където се намира сборката за зареждане.
WITH PERMISSION_SET клаузае много важна клауза на оператора CREATE ASSEMBLY и винаги трябва да бъде посочена. Той дефинира набора от права за достъп, предоставени на асемблерния код. Комплектът права SAFE е най-ограничителен. Асемблиращият код, който има тези права, няма достъп до външни системни ресурси, като файлове. Наборът права EXTERNAL_ACCESS позволява на асемблиращия код да има достъп до определени външни системни ресурси, докато наборът права UNSAFE осигурява неограничен достъп до ресурси както вътре, така и извън системата на базата данни.
За да съхранява информация за асемблерния код, потребителят трябва да може да издаде оператор CREATE ASSEMBLY. Сглобяването е собственост на потребителя (или ролята), който изпълнява израза. Можете да промените собственика на сборка, като използвате клаузата AUTHORIZATION на оператора CREATE SCHEMA.
Database Engine също така поддържа оператори ALTER ASSEMBLY и DROP ASSEMBLY. Декларация ALTER ASSEMBLYизползвани за актуализиране на компилацията до последна версия. Тази инструкция също добавя или премахва файлове, свързани със съответния сбор. Изявление DROPМОНТАЖпремахва посочения сбор и всички свързани файлове от текущата база данни.
Следният пример показва как да създадете съхранена процедура въз основа на управлявания код, внедрен по-рано:
ИЗПОЛЗВАЙТЕ SampleDb; СЪЗДАВАЙТЕ ПРОЦЕДУРА CountEmployees КАТО ВЪНШНО ИМЕ CLRStoredProcedures.StoredProcedures.CountEmployees
Операторът CREATE PROCEDURE в примера се различава от същия оператор в по-ранните примери по това, че съдържа Параметър ВЪНШНО ИМЕ. Тази опция указва, че кодът се генерира от CLR. Името в това изречение се състои от три части:
име_на_сглобяване.име_на_клас.име_на_метод
assembly_name - определя името на сборката;
class_name - указва името на генералния клас;
method_name - незадължителна част, указва името на метода, който е зададен вътре в класа.
Изпълнението на процедурата CountEmployees е показано в примера по-долу:
ИЗПОЛЗВАЙТЕ SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Връща 7
Операторът PRINT връща текущия брой редове в таблицата Employee.
съхранена процедурае възможно само ако се изпълнява в контекста на базата данни, където се намира процедурата.Видове съхранени процедури
В SQL Server има няколко типа съхранени процедури.
- Системен съхранени процедурипредназначени за извършване на различни административни действия. С тяхна помощ се извършват почти всички действия по администриране на сървъра. Можем да кажем, че системата съхранени процедуриса интерфейс, който осигурява работа със системни таблици, която в крайна сметка се свежда до промяна, добавяне, изтриване и извличане на данни от системни таблици на потребителски и системни бази данни. Системен съхранени процедуриса с префикс sp_, съхраняват се в системната база данни и могат да бъдат извикани в контекста на всяка друга база данни.
- Персонализиран съхранени процедуриизпълнява определени действия. Съхранени процедури- пълен обект на база данни. В резултат на това всеки съхранена процедурасе намира в определена база данни, където се изпълнява.
- Временно съхранени процедурисъществуват само за кратко време, след което автоматично се унищожават от сървъра. Те се делят на локални и глобални. Местен временен съхранени процедуримогат да бъдат извикани само от връзката, в която са създадени. Когато създавате такава процедура, тя трябва да получи име, което започва с един знак #. Като всички временни обекти, съхранени процедуриот този тип се изтриват автоматично, когато потребителят прекъсне връзката, рестартира или спре сървъра. Глобален временен съхранени процедуриналичен за всички сървърни връзки, които имат същата процедура. За да го дефинирате, е достатъчно да му дадете име, което започва със знаците ##. Тези процедури се изтриват, когато сървърът се рестартира или спре, или когато връзката, в чийто контекст са създадени, се затвори.
Създаване, модифициране и изтриване на съхранени процедури
Създаване съхранена процедуравключва решаване на следните задачи:
- определяне на вида на съхранена процедура: временно или персонализирано. Освен това можете да създадете своя собствена система съхранена процедура, като му дадете име с префикса sp_ и го поставите в системната база данни. Такава процедура ще бъде достъпна в контекста на всяка база данни на локалния сървър;
- планиране на достъпа. Докато създавате съхранена процедураимайте предвид, че ще има същите права за достъп до обектите на базата данни като потребителя, който го е създал;
- определение параметри на съхранена процедура. Подобно на процедурите, включени в повечето езици за програмиране, съхранени процедуриможе да има входни и изходни параметри;
- разработка на код съхранена процедура. Кодът на процедурата може да съдържа поредица от всякакви SQL команди, включително извикване на други. съхранени процедури.
Създаване на нов и модифициране на съществуващ съхранена процедурастава със следната команда:
<определение_процедуры>::= (CREATE | ALTER ) име_на_процедура [;номер] [(@параметър_име тип данни) [=по подразбиране] ][,...n] AS sql_statement [...n]
Помислете за параметрите на тази команда.
Използвайки префиксите sp_ , #, ##, създадената процедура може да бъде дефинирана като системна или временна процедура. Както можете да видите от синтаксиса на командата, не е позволено да се указва името на собственика, на когото ще принадлежи създадената процедура, както и името на базата данни, където тя трябва да бъде поставена. Така, за да се побере създаденото съхранена процедурав конкретна база данни, трябва да изпълните командата CREATE PROCEDURE в контекста на тази база данни. При манипулиране от тялото съхранена процедураСъкратените имена могат да се използват за обекти в същата база данни, т.е. без да се посочва името на базата данни. Когато искате да се обърнете към обекти, намиращи се в други бази данни, е необходимо да посочите името на базата данни.
Числото в името е идентификационният номер съхранена процедура, което еднозначно го дефинира в група от процедури. За удобство на управлението на процедурите, логически от същия тип съхранени процедуримогат да бъдат групирани, като им се даде едно и също име, но различни идентификационни номера.
За предаване на входни и изходни данни в създадените съхранена процедурамогат да се използват параметри, чиито имена, както имената на локални променливи, трябва да започват със символа @. един съхранена процедураМожете да посочите няколко опции, разделени със запетаи. Тялото на процедурата не трябва да използва локални променливи, чиито имена съвпадат с имената на параметрите на процедурата.
За да определите типа данни, които съответстват параметър на съхранена процедура, всеки тип е подходящ SQL данни, включително дефинирани от потребителя. Типът данни CURSOR обаче може да се използва само като изходен параметър съхранена процедура, т.е. с ключовата дума ИЗХОД.
Наличието на ключовата дума OUTPUT означава, че съответният параметър е предназначен да върне данни от съхранена процедура. Това обаче изобщо не означава, че параметърът не е подходящ за предаване на стойности съхранена процедура. Указването на ключовата дума OUTPUT инструктира сървъра да излезе от съхранена процедураприсвоете текущата стойност на параметъра на локалната променлива, която е била указана при извикването на процедурата като стойност на параметъра. Обърнете внимание, че когато зададете ключовата дума OUTPUT, стойността на съответния параметър при извикване на процедурата може да бъде зададена само с помощта на локална променлива. Всички изрази или константи, разрешени за нормални параметри, не са разрешени.
Ключовата дума VARYING се използва във връзка с