Студопедия

КАТЕГОРИИ:


Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748)

Методи створення збережених процедур




SQL Server надає простий механізм для створення збережених процедур. Для цього необхідно скористатися або вікном Object Explorer або вікном Template Explorer. В обох випадках система запропонує користувачеві зразок коду створення збереженої процедури, який необхідно доробити у відповідності до вимог створення процедури.

Оператор CREATE PROCEDURE

Оператор CREATE PROCEDURE (або його скорочена версія CREATE PROC) призначений для створення збережених процедур за допомогою редактору запитів або утиліт командного рядка, наприклад osql. CREATE PROC дозволяє виконувати наступні завдання:

· визначати згруповані збережені процедури;

· визначати вхідні й вихідні параметри, їхні типи даних і значення за замовчуванням. Визначення параметра завжди починається зі знака «at» (@), після якого треба вказати ім'я параметра й опис його типу даних. В описі вихідних параметрів повинне бути присутнім ключове слово OUTPUT, що дозволяє відрізнити їх від вхідних параметрів;

· виводити відомості про успішне або невдале завершення завдання;

· управляти хешуванням плану виконання процедури;

· шифрувати вміст збереженої процедури для забезпечення її безпеки;

· контролювати режим виконання процедури для передплатника реплікації;

· задавати дії, які виконує процедура під час виконання.

Передача контексту збереженій процедурі

Всі збережені процедури, крім тимчасових, завжди створюються в поточній базі даних. Тому перед створенням процедури варто вибирати поточну базу даних за допомогою оператора USE ім'я_бази_даних, після якого зазначається пакетна команда GO. Для вибору поточної бази даних також використовують список, що розкривається на панелі інструментів.

Перший пакет показаного далі сценарію робить базу даних Pubs поточної й створює процедуру ListAuthorNames, власником якої є dbo:

USE Pubs

GO

CREATE PROCEDURE [dbo].[ListAuthorNames]

AS

SELECT [aiLfnanie], [au_lnarne] FROM [pubs], [dbo]. [authors]

 

Зверніть увагу, що тут використовується повне ім'я процедури, Повне ім'я збереженої процедури складається з імені власника процедури (у цьому випадку dbo) і імені самої процедури - ListAuthorNames. Якщо процедуру варто виконувати незалежно від власника таблиці або бази даних, власником процедури повинен бути dbo. При використанні оператора CREATE PROCEDURE ім'я бази даних не входить у повне ім'я збереженої процедури.

Виконання збереженої процедури

Як уже говорилося раніше, збережену процедуру можна виконати в редакторі запитів, просто набравши її ім'я з необхідними параметрами. Наприклад, для перегляду вмісту збереженої процедури ви набирали sp_helptext і ім'я потрібної процедури, що у цьому випадку є параметром.

Якщо збережена процедура не є першим оператором пакета, то для запуску збереженої процедури необхідно випередити її ім'я ключовим словом EXECUTE (або його скороченою версією - EXEC).

Виклик збереженої процедури

Для виклику збереженої процедури досить указати її повне ім'я, наприклад [ім'я__6ази_даних].[власник]. [ім'я_процедури]. Якщо ви зробите базу даних, у якій утримується збережена процедура, поточною (USE ім'я_бази_даних ), то для виклику процедури досить указати частину імені: [власник].[ім'я_процедури], а якщо ім'я процедури унікально в активній базі даних, то можна використати просто [ім'я_процедури].

При виконанні системних збережених процедур із префіксом sp_, а також локальних і глобальних процедур не обов'язково використовувати повні імена. SQL Server буде шукати в базі даних Master будь-яку збережену процедуру із префіксом sp_, власником якої є dbo. Якщо ви все-таки вирішили привласнити локальній і системній процедурам однакові імена, обов'язково вкажіть у першому випадку ім'я власника, відмінне від dbo. SQL Server не виконує пошук розширених збережених процедур у базі даних Master автоматично. Тому треба або вказувати повне ім'я розширеної збереженої процедури, або зробити активної базу даних, у якій вона перебуває.

Визначення параметрів і їхніх значень

Якщо збереженій процедурі потрібні параметри, при виконанні процедури необхідно задати їхні значення. Визначення вхідних і вихідних параметрів починаються зі знака @, після якого треба вказати ім'я параметра і опис його типу даних. При виклику функції необхідно задати значення параметра і (не обов'язково) його ім'я. У наступних двох прикладах збережена процедура au_info з бази даних Pubs запускається із двома параметрами - @lastname і @firstname:

Викликати збережену процедуру з параметрами

USE Pubs

GO

EXECUTE au_info Green, Marjorie

 

Викликати збережену процедуру із вказівкою імен і значень параметрів

USE Pubs

GO

EXECUTE au_info @lastname = 'Green', @firstname = 'Marjorie'

 

У першому прикладі задані значення параметрів, а їхні імена опущені. Якщо значення параметрів задані без вказівки імен, то їхній порядок повинен збігатися з порядком, у якому були задані параметри при створенні процедури. У другому прикладі значення задані разом із вказівкою імен параметрів, у цьому випадку їхній порядок може бути довільним. Якщо під час створення процедури для її параметрів визначені значення за замовчуванням, то таку процедуру можна успішно виконати, не вказуючи значення параметрів. Нижче приводиться список деяких варіантів синтаксису при виконанні збережених процедур.

· Збережена процедура повертає значення у цілочисленій змінній, певній для зберігання кодів повернення. У збереженій процедурі повинне бути присутнім ключове слово RETURN із цілочисленим значенням (значеннями).

· У збереженій процедурі визначені змінні для зберігання параметрів. Перед командою EXECUTE визначаються змінні за допомогою ключового слова DECLARE.

Модифікація збереженої процедури

Оператор ALTER PROCEDURE (або його скорочена версія ALTER PROC) дозволяє модифікувати вміст користувальницької збереженої процедури за допомогою редактору запитів. Синтаксис оператора ALTER PROCEDURE майже повністю ідентичний синтаксису CREATE PROCEDURE. Цінність застосування оператора ALTER PROCEDURE замість видалення й створення процедури «з нуля» полягає в тім, що ALTER PROCEDURE зберігає більшість властивостей процедури (наприклад, її ідентифікатор об'єкта, набір прав доступу і прапори).

Збережена процедура sp_rename дозволяє перейменовувати користувальницькі процедури. Наступний оператор перейменовує збережену процедуру ByRoyalty в RoyaltyByAuthor1:

USE PUBS

GO

EXECUTE sp_rename

@objname = 'byroyalty', @newname = 'RoyaltyByAuthorl',

@objtype = 'object'

 

В SQL Server Management Studio можна перейменувати користувальницьку збережену процедуру, клацнувши її правою кнопкою й вибравши команду Rename.

При перейменуванні збережених процедур і інших об'єктів (наприклад, таблиць) слід дотримуватися обережності. Збережені процедури можуть бути вкладеними, і при виклику об'єкта вихідній збереженій процедурі не вдасться знайти його, якщо він перейменований.

Видалення збереженої процедури

Для видалення однієї користувальницької збереженої процедури, декількох процедур одночасно або набору згрупованих процедур використовують оператор DROP PROCEDURE або його скорочену версію DROP PROC. Видалимо з бази даних Pubs дві процедури з іменами Procedure01 і Procedure02:

USE pubs

GO

DROP PROCEDURE procedure01, procedure02

 

Зверніть увагу, що база даних Pubs - поточна. При видаленні процедури не можна вказувати ім'я бази даних, повне ім'я процедури виглядає так: [власник].[ім'я_процедури].

Перш ніж видаляти збережену процедуру, варто перевірити, чи не залежать від неї будь-які інші об'єкти бази даних. Для перевірки наявності залежних об'єктів служить системна збережена процедура sp_depends. Джерелом проблем можуть стати тільки ті об'єкти, які залежать від процедури, що видаляється.

Приклади роботи зі збереженими процедурами

У цій вправі ми створимо збережену процедуру в базі даних Northwind і переконаємося в тім, що вона дійсно створена. Далі ми виконаємо, змінимо і видалимо цю процедуру.

Створення збереженої процедури в базі даних Northwind

USE northwind

GO

CREATE PROCEDURE dbo.CustOrderHistRep

@Customer_ID char(5)

AS

SELECT ContactName, ContactTitle

FROM Customers WHERE CustomerID = @CustomerID

 

SELECT ProductName, Total=SUM(Quantity)

FROM Products P, [Order Details] OD, Orders O, Customers C WHERE

C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID

AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

GROUP BY ProductName

GO

 

При виконанні першого пакета база Northwind стає поточною. Далі створюється процедура CustOrderHistRep і визначається єдиний вхідний параметр - CustomerID.

Вхідний параметр може розташовуватися в одному рядку з оператором CREATE PROCEDURE, але тут для ясності він перенесений в окремий рядок. Аналогічний підхід використаний далі при розбивці на рядки коду операторів SELECT. Зверніть увагу, що для параметра @CustomerID заданий тип даних char(5). Якщо виконати запит до таблиці [northwind].[dbo].[customers], то видно, що довжина всіх ідентифікаторів покупців дорівнює п'яти символам. Рядок, у якій перебуває єдине ключове слово AS, є розділовою лінією між створенням процедури в таблиці SysObjects і текстом процедури, що зберігають у таблиці SysComments.

Переглянете оператори SELECT, які розташовані нижче ключового слова AS, але не витрачайте на це занадто багато часу. При виконанні запиту у відповідь на уведення ідентифікатора покупця перший оператор SELECT виводить ім'я контактної особи й заголовок контактної інформації. Другий оператор SELECT виводить назви і загальну кількість (SUM) кожного товару, що придбав покупець. Результуючий набір повертає дані, згруповані за назвою товару. Можна помітити, що кілька з'єднань реалізовано в конструкції WHERE, а не FROM. Під час модифікації процедури ми перемістимо вираз JOIN у конструкцію FROM.

Виконаємо створений код у вікні редактору запитів.

Далі виконаємо наступну команду:

sp_depends custorderhistrep

 

Збережена системна процедура sp_depends перевіряє процедуру CustOrderHistRep, щоб визначити її залежності. Зверніть увагу на наявність у поле Name повторюваних елементів. Однак всі записи з повторюваними елементами відрізняються значеннями інших полів.

Виконання збереженої процедури

Уведемо і виконаємо наступну команду:

EXEC [northwind].[dbo],[custorderhistrep]

@CustomerID = 'thecr'

 

Для запуску збереженої процедури CustOrderHistRep використана скорочена версія ключового слова EXECUTE EXEC. Зверніть увагу на використання повного імені. Це не обов'язково, але в цьому випадку такий прийом дозволяє запустити процедуру, не роблячи активної базу даних Northwind.

Повертаються два результуючі набори. Перший з них (ім'я контактної особи й заголовок контактної інформації) виводиться у верхній частині панелі Results. Другий результуючий набір (назва й кількість товару) відображається в нижній частині панелі Results.

Модифікація збереженої процедури

USE Northwind

GO

ALTER PROCEDURE dbo.CustOrderHistRep

@CustoirerID char(5)

AS

SELECT ContactName, ContactTitle

FROM Customers WHERE CustornerID = @CustomerID

SELECT ProductName. Total=SUM(Quantity)

FROM Products P. [Order Details] OD, Orders O. Customers C

WHERE

C. CustomerID = @CustomerID AND C.CustomerID = O. Customer_ID

AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

GROUP BY ProductName

ORDER BY Total DESC

GO

 

У наведеному коді по відношенню до попереднього можна бачити декілька відмінностей:

· Замінене ключове слово CREATE на ALTER. При зміні тексту процедури ключове слово ALTER дозволяє змінювати збережену процедуру без втрати будь-яких її властивостей.

· З метою відсортування кількість замовлень по убуванню додано наступний оператор: ORDER BY Total DESC

Виконаємо запит.

Щоб переконатися, що потрібні зміни внесені виконаємо наступний оператор о продивимося результати:

sp_helptext custorderhistrep

 

Видалення збереженої процедури

DROP PROCEDURE dbo.custorderhistrep

 




Поделиться с друзьями:


Дата добавления: 2015-06-29; Просмотров: 786; Нарушение авторских прав?; Мы поможем в написании вашей работы!


Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет



studopedia.su - Студопедия (2013 - 2024) год. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав! Последнее добавление




Генерация страницы за: 0.048 сек.