Управління збереженими процедурами

Реальне управління збереженими процедурами легко порівняти із закладеною в них логікою Якщо ви знаєте основні факти і синтаксис, то управління збереженими процедурами не складе для вас ніяких труднощів

Інструкції CREATE, ALTER І DROP

Збережені процедури управляються за допомогою інструкцій мови визначення даних (DDL) CREATE, ALTER і DROP Інструкція CREATE повинна бути першою в пакеті термінатор пакета завершує створення збереженої процедури У наступному прикладі створюється проста процедура, що зберігається, яка витягує дані з таблиці ProductCategory бази даних OBXKites:

USE OBXKites go

CREATE PROCEDURE CategoryList AS

SELECT ProductCategoryName, ProductCategoryDescription FROM dboProductCategory

RETURN

Протягом всієї цієї глави ми будемо додавати в збережену процедуру CategoryList нові функції

Інструкція DROP видаляє збережену процедуру з бази даних Інструкція ALTER змінює вміст всієї збереженої процедури Для внесення змін переважніше використовувати інструкцію ALTER, а не комбінацію інструкцій видалення і створення, так як останній метод видаляє всі дозволи

Збереженими процедурами можна управляти з вікна Object Explorer, однак я настійно рекомендую використовувати для цього сценарії (файли Sql), які можна організувати в систему управління версіями

Повернення набору записів

Якщо процедура, є збереженим пакетом, тоді те, що може зробити пакет, може зробити і збережена процедура Як пакет може повернути набір записів із запиту SELECT, так і збережена процедура може повернути набір записів із запиту

Повертаючись до збереженій процедурі, створеної в попередньому розділі, можна відзначити, що при її виконанні запит, що міститься в ній, поверне всі рядки з таблиці productcategory:

Ехес CategoryList Ь

Результат виконання процедури (скорочений):

ProductCategoryName ProductCategoryDescription

Accessory                   kite flying accessories

Book                           Outer Banks books

Clothing                      OBX t-shirts, hats, jackets

Компіляція збережених процедур

Компіляція збережених процедур виконується автоматично при першому їх запуску, після чого скомпільований код зберігається в памяті (точніше, SQL Sewer створює план виконання запитів та програмного коду збереженої процедури, після чого той зберігається в памяті)

Для відстеження скомпільованих обєктів SQL Server використовує таблицю Master, dbo SysCacheObjects Щоб переглянути скомпільований код, запустіть на виконання наступний запит:

SELECT cast (Csql as Char (35)) as StoredProcedure, cacheobjtype, usecounts as Count FROM MasterdboSysCacheObjects З JOIN MasterdboSysDatabases D ON Cdbid = Cdbid WHERE DName = DB_Name ()

AND Obj Type = Proc1 ORDER BY StoredProcedure

Результат виконання запиту (скорочений):

StoredProcedure                                           cacheobjtype Count ObjType

CREATE PROCEDURE                            [dbo][CleanBatchR       Compiled Plan       2              Proc

CREATE PROCEDURE                            [dbo][CleanEventR       Compiled Plan        1              Proc

CREATE PROCEDURE                            [dbo][CleanExpire        Compiled Plan        2              Proc

CREATE PROCEDURE                            [dbo][CleanExpire        Compiled Plan        2              Proc

План виконання запиту збереженої процедури може застаріти, поки знаходиться в памяті Якщо радикально змінюється розподіл даних, додаються або видаляються індекси, то перекомпіляція збереженої процедури може значно підвищити продуктивність Щоб вручну ініціювати процес перекомпіляції збереженої процедури, використовується системна процедура sp_recompile Вона позначає збережену процедуру або тригер, щоб при наступному її запуску вона автоматично була перекомпілювати:

Ехес sp_recompile CategoryList

Результат виконання буде наступним:

Object CategoryList was successfully marked for recompilation

Додаткова Більш докладно про плани виконання запитів і методах їх зберігання в памя-інформація ти йтиметься у розділі 50

Шифрування збережених процедур

Коли створюється збережена процедура, її текст зберігається в таблиці SysComment s Цей текст зберігається трохи для її виконання, а для подальшої можливої ​​модифікації

Системна збережена процедура sp_helptext може витягти вихідний текст збереженої процедури з таблиці SysComments, наприклад: sp_helptext CategoryList

Результат буде наступним:

Text

CREATE PROCEDURE CategoryList AS

SELECT *

FROM dboProductcategory

Якщо процедура, створюється з параметром WITH ENCRYPTION, то її текст в таблиці SysComments неможливо прочитати Як правило, сторонні виробники програмного забезпечення шифрують свої збережені процедури Наступна інструкція ALTER збереже процедуру CategoryList з параметром WITH ENCRYPTION, після чого ми спробуємо прочитати її вихідний текст:

ALTER PROCEDURE CategoryList WITH ENCRYPTION AS

SELECT *

FROM dboProductcategory

sp_helptext CategoryList

В результаті отримаємо наступне повідомлення:

The text for object CategoryList is encrypted

Системні збережені процедури

Основний синтаксис мови SQL містить всього десять інструкцій: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE і DENY У той же час компанія Microsoft заклала в базу даних master масу збережених процедур, що дозволяють виконати сотні завдань Щоб ці процедури стали доступні всім базам даних, потрібно знати правила убавленія їх областю видимості Будь збережена процедура, що починається з символів sp_ і яка знаходиться в базі даних master, може бути виконана в будь-якій базі даних Якщо виникає конфлікт між іменами системної збереженої процедури та іншої, що міститься в локальній базі даних, то виконується остання

При створенні збережених процедур керуйтеся загальноприйнятими угодами про імена Не починайте імена створюваних збережених процедур з символів sp, інакше ви потенційно стаєте жертвою конфліктів імен та

непорозумінь Я починаю імена своїх збережених процедур з символу р

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*