Transact-SQL, съхранени процедури
Съхранената процедура - специален вид на Transact-SQL изявления пакет, създаден с помощта на SQL и разширение процесуално език. Основната разлика между пакета и съхранената процедура се състои в това, че то се съхранява в обект на базата данни. С други думи, за запомнени процедури, се съхраняват на сървъра страна, за да се подобри ефективността и последователността на изпълнение на повтарящи се задачи.
Машината на базата данни поддържа съхранени процедури и процедури на системата. Съхранени процедури са създадени по същия начин, както и всички други обекти на база данни, т.е. с DDL език. Системни лечения са на разположение, както и бази данни могат да се използват за достъп до информацията в каталога на системата и нейните модификации.
Когато създавате съхранена процедура, можете да определите по желание списък с параметър. По този начин, процедурата ще предприеме съответните аргументи всеки път, когато той се нарича. Съхранени процедури могат да връщат стойност, която съдържа дефинирани от потребителя информация, или, в случай на грешка, подходящо съобщение за грешка.
Съхранената процедура рано съставен преди той да бъде съхранен като обект в базата данни. Предварително компилирана форма на процедурата се съхранява в базата данни и се използва всеки път, когато той се нарича. Този имот съхранени процедури осигурява важна полза е премахването (в почти всички случаи) прекомпилира процедури и съответните подобрения в производителността. Този имот съхранени процедури също има положителен ефект върху количеството данни, които участват в обмена между системата от база данни и приложения. По-специално, да се обадя на съхранена процедура на няколко хиляди байта може да отнеме по-малко от 50 байта. Когато множество потребители да извършват повтарящи се задачи с използване на съхранени процедури, кумулативния ефект от тези спестявания могат да бъдат доста значителен.
Съхранени процедури могат да се използват за следните цели:
за създаване на дневник дневник на действията на таблиците в базата данни.
Съхранени процедури, които създават трупи от запис и / или таблици за четене, осигуряват допълнителен капацитет, за да се гарантира сигурността на базата данни. Използването на такива процедури, администраторът на базата данни може да проследява модификации на потребителите на база данни или приложения.
Създаване и изпълнение на съхранени процедури
Съхранени процедури са създадени посредством отчета за СЪЗДАВАНЕ НА ПРОЦЕДУРА. който има следния синтаксис:
Schema_name параметър определя името на схемата, която се назначава от собственика, за да се създаде запомнена процедура. Proc_name параметър определя името на съхранената процедура. @ Параметър param1 е параметър на процедурата (формално аргумент), чиито тип данни се определя от TYPE1 параметър. Параметрите са местни процедури в рамките на процедурата, както и локалните променливи са локализирани в рамките на пакета. процедура параметри - стойностите, които се предават на процедурата за обаждащия се да го използвам. параметър Default1 определя стойността по подразбиране за съответния параметър за обработка. (Стойността по подразбиране може да бъде NULL.)
Вариант OUTPUT показва, че параметърът процедура се върна, и той може да се използва, за да се върне стойността на съхранената процедура процедура призвание или система.
Работи според клауза определя контекста на сигурността, в която трябва да бъде изпълнена съхранената процедура след повикването. Питам този контекст, с помощта на Database Engine, можете да контролирате кои сметки на потребителя за валидиране на разрешения за обекти, които са предвидени в настоящата съхранена процедура.
По подразбиране, използвайте инструкциите сътворяват ПРОЦЕДУРА Само членове на сървъра ролята на администратор фиксирани и фиксираната роля db_owner или db_ddladmin база данни. Но членовете на тези роли могат да възложат това право на други потребители, използващи за отпускане на безвъзмездни CREATE изявление ПРОЦЕДУРА.
Следващият пример показва как да се създаде проста съхранени процедури за работа с таблица на проекта:
Както споменах по-рано, за да се отдели на двата набора от инструкции, използвани в движение. Инструкция СЪЗДАВАНЕ НА ПРОЦЕДУРА не може да се комбинира с други Transact-SQL изявления в една и съща партида. Съхранената процедура IncreaseBudget увеличава бюджети за всички проекти на определен брой процента, определен от @percent на параметъра. Процедурата също се определя от стойността по подразбиране на интерес (5), който се използва, ако по време на процедурата, това твърдение отсъства.
Съхранени процедури могат да се отнасят до несъществуваща маса. Тази функция ви позволява да трасира кода процедура без да се създава първите съответните таблици, а дори и без да е свързан с терминален сървър.
За разлика от основните, съхранени процедури, които винаги са съхранени в текущата база данни, можете да създадете временни съхранени процедури, които винаги са поставени в базата данни на временна система tempdb. Една от причините за създаване на временни съхранени процедури може да бъде желание за избягване повторно извършване на определена инструкция група при свързване към база данни. Можете да създавате местни или глобални временни процедури. За да направите това, името на местната процедура е посочена с един знак # (#proc_name), а името на Global Action - двойно (## proc_name).
Местна временно съхранена процедура може да изпълнява само на потребителя, който го е създал, и то само за връзка с базата данни, в която е бил създаден. Global временна процедура за всички потребители, но само толкова дълго, тъй като последната е завършена съединение, в която е извършена (обикновено процедурата за връзка, създател).
Жизненият цикъл на съхранената процедура се състои от два етапа на нейното създаване и нейното изпълнение. Всяка процедура се създава само веднъж и се изпълнява многократно. Съхранена процедура се извършва от потребителя ИЗПЪЛНЯВАТ инструкция, която е собственик на процедурата или има право на достъп до Изпълнение тази процедура. ИЗПЪЛНЯВАТ изявление има следния синтаксис:
Освен return_status параметър, всички параметри изпълни изявление имат една и съща логическа стойност, както и същ параметър CREATE изявление ПРОЦЕДУРА. параметър Return_status указва целочислена променлива, която съхранява състоянието на връщане на процедурата. Параметърът стойност може да бъде определен като се използва или константа (стойност), или локална променлива (@variable). Редът на именувани параметри не е важно, но стойностите на неназовани параметри трябва да бъдат дадени в реда, в който са определени в отчета за СЪЗДАВАНЕ НА ПРОЦЕДУРА.
клауза DEFAULT осигурява стойност по подразбиране за параметъра процедура, която е посочен в дефиницията на процедурата. Когато процедурата чака стойността на параметъра, за които не е определена стойност по подразбиране и няма опция, или е определен ключовата дума DEFAULT, а след това се появи грешка.
Когато ИЗПЪЛНЯВАТ инструкция е първата инструкция пакета, ключовата дума изпълни може да се пропусне. Независимо от това, че ще бъде по-безопасно да се включи тази дума във всеки пакет. Използването ИЗПЪЛНЯВАТ инструкция е показано в примера по-долу:
ИЗПЪЛНЯВАТ изявление в този пример се изпълнява съхранената процедура IncreaseBudget който увеличава бюджета на 10% от проектите.
Следният пример показва как да създадете съхранена процедура за обработване на данните в таблици и служителите Works_on:
процедура ModifyEmpId в пример илюстрира използването на съхранени процедури като част от процеса, за да се гарантира целостта на референтна (в този случай между наети и Works_on таблици). Такава съхранена процедура може да бъде използвана при определяне на спусъка, което всъщност осигурява референтна цялост.
Следният пример илюстрира използването на съхранена процедура предлага изход:
Това съхранена процедура може да се управлява да извърши с помощта на следните указания:
Предложение с резултатите комплекти ИЗПЪЛНЯВАТ изявление
Следващите два примера ще ви помогнат да се обясни предложението. Първият пример е въвеждаща пример, който показва как може да се появи в резултат, когато предложението е пропуснат С комплекти с резултати:
Изходът на съхранената процедура, причинено от такъв метод е както следва:
Както може да се види, бягане запомнена процедура използвате с комплекти с резултати предложения изпълни инструкция позволява да промените името и тип данни колона в комплекта резултат на това, тази процедура се извежда. По този начин, тази нова функционалност осигурява по-голяма гъвкавост при изпълнение на съхранени процедури и въвеждане на резултатите им в нова таблица.
Промени в структурата на съхранени процедури
Компонент Database Engine поддържа ALTER изявление процедура за промяна на структурата на съхранени процедури. ALTER ПРОЦЕДУРА инструкция обикновено се използва за модифициране Transact-SQL отчети на рамките на процедурата. Всички изявление параметри след избор, имат същото значение, както е същият параметър CREATE изявление ПРОЦЕДУРА. Основната цел на настоящото ръководство е да се избегне първостепенни на съществуващите права на съхранената процедура.
За да премахнете един или група от съхранени процедури, използвани по метода на обучение DROP. Изтриване на съхранена процедура може да бъде само на собственика или на членовете на предварително дефинирани роли db_owner и администратор.
Съхранени процедури и CLR
SQL Server поддържа Common Language Runtime CLR (Common Language Runtime), което дава възможност за развитието на различни обекти на база данни (съхранени процедури, дефинирани от потребителя функции, тригери, дефинирани от потребителя обобщаващи функции и потребителски дефинирани типове данни) с помощта на езика C # и Visual Basic. В CLR позволява също така тези обекти, използващи обща време на работа на системата.
В CLR е разрешено и забранено от опции clr_enabled система на sp_configure. който получава изпълнен преконфигурира инструкция. Следният пример показва как системата може да използва процедурата sp_configure позволява използването CLR среда:
За да създадете, съставите и запаметите процедурата с помощта на CLR е необходимо да извършите следните стъпки в този ред:
Създаване на запомнена процедура в C # или Visual Basic, а след това го събират с помощта на подходящ компилатор.
Използването СЪЗДАВАНЕ събрание отчета за. създаване на подходяща изпълнимия файл.
Запазване на процедурата като сървър обект чрез CREATE ПРОЦЕДУРА отчета за.
Следвайте процедурата, използвайки Изпълнение изявление.
Фигурата по-долу показва диаграма на етапите, описани по-рано поток. По-долу е по-подробно описание на този процес.
Първо се създаде желаната програма по какъвто и среда за разработка, като Visual Studio. Събиране на крайния програма в обектен код с помощта на C # компилатор или Visual Basic. Този код се съхранява в динамична връзка библиотека файл (.dll), която служи като източник за инструкция за монтаж Създаване създава междинен изпълним код. На следващо място, изпълнява СЪЗДАВАНЕ НА ПРОЦЕДУРА отчета за да спаси изпълнимия код под формата на обект на базата данни. И накрая, да започне процедурата за изпълнение, като се използва познатата ИЗПЪЛНЯВАТ изявлението.
Следният пример показва изходния код на съхранената процедура в езика C #:
При тази процедура, искане се изпълнява да брои броя на редовете в таблицата за служителите. При използване на директивите в началото на програмата е посочено Пространствата от имена на необходимите за изпълнението му. Използването на тези директиви позволява да посочите имената на изходния код на класа, без изрично да се посочи съответното пространство от имена. Допълнителни StoredProcedures определят клас, за които атрибута прилага SqlProcedure. която информира компилатора, че този клас е запомнена процедура. метод Вътрешни CountEmployees (), определена от класа код. Съединение със системата за база данни е създадена с SqlConnection например клас. За отваряне на връзка, използва Open () метод на модел. метод А CreateCommand () ви дава достъп до клас инстанция SqlCommnd. което излъчва най-SQL-команда.
Следния кодов фрагмент:
SELECT инструкция се използва да брои броя на редовете в таблицата за служителите и показване на резултата. Текстът на командния се определя от възлагане CommandText собственост на променлива ПМС инстанция връща от CreateCommand (). Наричан по-долу ExecuteScalar () метод на пример SqlCommand. Този метод връща скаларна стойност, която се превръща число тип данни Int на, и определя на редове.
Сега можете да компилирате кода с помощта на Visual Studio среда. Добавих този клас да CLRStoredProcedures на проекта с име, така че Visual Studio ще състави монтажа на едно и също име с разширение * .dll. Следният пример илюстрира следващата стъпка в създаването на съхранена процедура: създаване изпълним код. Преди да изпълни код в този пример, е необходимо да се знае местоположението на компилиран DLL-файла (обикновено се намира в папката Debug на проекта).
СЪЗДАВАНЕ НА АСАМБЛЕЯ обучение се като вход успяха код и създава подходяща обекта, за които можете да създадете CLR съхранени процедури, дефинирани от потребителя функции и тригери. Инструкцията има следния синтаксис:
В assembly_name параметър определя името на събранието. В незадължителна клауза за РАЗРЕШЕНИЕ посочва името на ролята като собственик на системата. Най ОТ клауза определя пътя, където се зарежда събранието.
С PERMISSION_SET предложение е много важен предложение CREATE изявление събрание и винаги трябва да се посочи. Тя определя набор от права за достъп, предоставени от код събрание. SAFE набор от права е най-рестриктивния. монтаж код с тези права, не може да влезе външни системни ресурси като файлове. Задайте права EXTERNAL_ACCESS позволява на код събрание за достъп до определени външни системни ресурси, както и набор от права опасни осигурява неограничен достъп до ресурси, така и извън системата на база данни.
За да запишете информация за код събрание, потребителят трябва да бъде в състояние да изпълнява СЪЗДАВАНЕ НА АСАМБЛЕЯ отчета за. монтаж е собственост на потребителя (или роля), извършване на тези инструкции. Собственикът на събранието може да се направи на друг потребител, използвайки Създаване на инструкции SCHEMA предлагат на разрешението.
Машината на базата данни поддържа също потребителя и ALTER СЪБРАНИЕ DROP събрание. изявление ALTER събрание се използва за актуализиране на събранието до последната версия. Това твърдение, добавя или премахва файлове, асоциирани със съответния събрание. Инструкции DROP СЪБРАНИЕ премахва уточнен събрание и всички свързани файлове от текущата база данни.
Следният пример показва как да създадете съхранена процедура, въз основа на управлявания код реализира по-рано:
CREATE изявление процедура в пример различава от същия инструкция в примерите по-горе в това, че съдържа параметър EXTERNAL NAME. Този параметър определя, че кодът, създадена от CLR. Името на това предложение се състои от три части:
assembly_name - показва името на монтажа;
CLASS_NAME - показва общата името клас;
method_name - незадължителна част, уточнява името на метода, която се определя в рамките на класа.
Извършване процедура CountEmployees е показано в примера по-долу:
PRINT инструкция връща текущия брой редове в таблицата на служителите.