Sql server збережені процедури. процедура

Мета роботи - навчитися створювати і використовувати збережені процедури на сервері БД.

1. Опрацювання всіх прикладів, аналіз результатів їх виконання в утиліті SQL Server Management Studio. Перевірка наявності створених про- цедур в поточній БД.

2. Виконання всіх прикладів і завдань по ходу лабораторної роботи.

3. Виконання індивідуальних завдань за варіантами.

Пояснення до виконання роботи

Для освоєння програмування процедур використовуємо при- заходів бази даних c назвою DB_Books, Яка була створена в лабора- битим роботі №1. При виконанні прикладів і завдань звертайте увага при -маніє на відповідність назв БД, таблиць та інших об'єктів проекту.

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

Типи процедур

Системні збережені процедури призначені для виконання роз- особистих адміністративних дій. Практично всі дії по адми- ністрірованію сервера виконуються з їх допомогою. Можна сказати, що системні збережені процедури є інтерфейсом, що забезпечує роботу з системними таблицями. Системні збережені процедури мають префікс sp_, зберігаються в системній базі даних і можуть бути викликані в контексті будь-якої іншої бази даних.

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

Тимчасові збережені процедури існують лише деякий час, після чого автоматично знищуються сервером. Вони діляться на ло- Кальне і глобальні. Локальні тимчасові процедури, що можуть бути викликані тільки з того з'єднання, в якому створені. При созда- ванні такої процедури їй необхідно дати ім'я, що починається з одного символу #. Як і всі тимчасові об'єкти, збережені процедури цього типу автоматично видаляються при відключенні користувача, перезапуску або зупинці сервера. Глобальні тимчасові процедури, що доступні для будь-яких з'єднань сервера, на якому є така ж процедура. Для її визначення достатньо дати їй ім'я, яке починається з символів ##. Видаляються ці процедури при перезапуску або зупинці сервера, а також при закритті з'єднання, в контексті якого вони були створені.

Створення, зміна процедур

Створення збереженої процедури передбачає вирішення таких за- дач: планування прав доступу. При створенні збереженої процедури слід враховувати, що вона буде мати ті ж права доступу до об'єктів бази даних, що і створив її користувач; визначення параметрів храни- мій процедури, процедури, що можуть мати вхідними і вихід-ними параметрами; розробка коду збереженої процедури. Код процедури може містити послідовність будь-яких команд SQL, включаючи виклик інших процедур.

Синтаксис оператора створення нової або зміни існуючої процедури, що в позначеннях MS SQL Server:

(CREATE | ALTER) PROC [EDURE] імя_процедури [; номер] [(@ імя_параметра тип_даних) [VARYING] [\u003d DEFAULT] [OUTPUT]] [, ... n] [WITH (RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION)] [FOR REPLICATION] AS sql_оператор [... n]

Розглянемо параметри даної команди.

Використовуючи префікси sp_, #, ##, створювану процедуру можна визначити як системної або тимчасовою. Як видно з синтаксису команди, не допускається вказувати ім'я власника, якому належатиме створювана процедура, а також ім'я бази даних, де вона повинна бути розміщена. Таким чином, щоб розмістити створювану збережену процедуру в конкретній базі даних, необхідно виконати команду CREATE PROCEDURE в контексті цієї бази даних. При зверненні з тіла збереженої процедури до об'єктів тієї ж бази даних можна використовувати укорочені імена, т. Е. Без вказівки імені бази даних. Коли ж потрібно звернутися до об'єктів, розташованих в інших базах даних, вказівка \u200b\u200bімені бази даних обов'язково.

Для передачі вхідних і вихідних даних в створюваній збереженій процедурі імена параметрів повинні починатися з символу @. В одній збереженій процедурі можна задати безліч параметрів, розділених комами. У тілі процедури не повинні застосовуватися локальні змінні, чиї імена збігаються з іменами параметрів цієї процедури. Для визначення типу даних параметрів процедури підходять будь-які типи даних SQL, включаючи певні користувачем. Однак тип даних CURSOR може бути використаний тільки як вихідний параметр збереженої процедури, тобто із зазначенням ключового слова OUTPUT.

Наявність ключового слова OUTPUT означає, що відповідний параметр призначений для повернення даних з збереженої процедури. Однак це зовсім не означає, що параметр не підходить для передачі значень в збережену процедуру. Вказівка \u200b\u200bключового слова OUTPUT наказує сервера при виході з процедури, що привласнити поточне значення параметра локальної змінної, яка була вказана при виклику процедури в якості значення параметра. Відзначимо, що при вказівці ключового слова OUTPUT значення відповідного параметра при виклику процедури може бути поставлено лише за допомогою локальної змінної. Не дозволяється використання будь-яких виразів або констант, допустимий для звичайних параметрів. Ключове слово VARYING застосовується спільно з параметром OUTPUT, що має тип CURSOR. Воно визначає, що вихідним параметром буде результуюча безліч.

Ключове слово DEFAULT є значення, яке буде приймати відповідний параметр за замовчуванням. Таким чином, при виклику процедури можна не вказувати явно значення відповідного параметра.

Так як сервер кешируєт план виконання запиту і компілює код, при наступному виклику процедури будуть використовуватися вже готові значення. Однак в деяких випадках все ж потрібно виконувати перекомпіляцію коду процедури. Вказівка \u200b\u200bключового слова RECOMPILE наказує системі створювати план виконання процедури при кожному її виклику.

Параметр FOR REPLICATION затребуваний при реплікації даних і включенні створюваної процедури, як статті в публікацію. Ключове слово ENCRYPTION наказує сервера виконати шифрування коду збереженої процедури, що може забезпечити захист від використання авторських алгоритмів, що реалізують роботу збереженої процедури. Ключове слово AS розміщується на початку власне тіла збереженої процедури. У тілі процедури можуть застосовуватися практично всі команди SQL, оголошуватися транзакції, встановлюватися блокування і викликатися інші процедури, що зберігаються. Вихід з процедури, що можна здійснити за допомогою команди RETURN.

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

DROP PROCEDURE (імя_процедури) [... n]

Виконання процедури,

Для виконання процедури використовується команда: [[EXEC [UTE] імя_процедури [; номер] [[@ імя_параметра \u003d] (значення | @ ім'я_змінної) [OUTPUT] | [DEFAULT]] [, ... n]

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

Використання ключового слова OUTPUT при виклику процедури роз- вирішується тільки для параметрів, які були оголошені при створенні процедури з ключовим словом OUTPUT.

Коли ж при виклику процедури для параметра вказується ключове слово DEFAULT, то буде використано значення за замовчуванням. Естест- венно, вказане слово DEFAULT дозволяється тільки для тих параметрів, для яких визначено значення за замовчуванням.

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

Відзначимо, що при виклику процедури вказуються або імена пара- метрів зі значеннями, або тільки значення без імені параметра. Їх комбінування не допускається.

Використання RETURN в збереженій процедурі

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

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Завдання 1.

EXEC Count_Books

Перевірте результат.

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

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages\u003e \u003d @Count_pages GO

завдання 2. Створіть дану процедуру в розділі Stored Procedures ба- зи даних DB_Books через утиліту SQL server Management Studio. Запус- титі її за допомогою команди

EXEC Count_Books_Pages 100

Перевірте результат.

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

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT, @Title AS CHAR (10) AS SELECT COUNT (Code_book) FROM Books WHERE Pages\u003e \u003d @Count_pages AND Title_book LIKE @Title GO

Завдання 3. Створіть дану процедуру в розділі Stored Procedures ба- зи даних DB_Books через утиліту SQL server Management Studio. Запус- титі її за допомогою команди

EXEC Count_Books_Title 100, "П%"

Перевірте результат.

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

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT, @Title CHAR (10), @Itogo INT OUTPUT AS SELECT @Itogo \u003d COUNT (Code_book) FROM Books WHERE Pages\u003e \u003d @Count_pages AND Title_book LIKE @Title GO

Завдання 4. Створіть дану процедуру в розділі Stored Procedures ба- зи даних DB_Books через утиліту SQL server Management Studio. Запус- титі за допомогою набору команд:

Sql\u003e Declare @q As int EXEC Count_Books_Itogo 100, "П%", @q output select @q

Перевірте результат.

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

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author \u003d @param) \u003d "Пушкін А.С." RETURN 1 ELSE RETURN 2

Завдання 5. Створіть дану процедуру в розділі Stored Procedures ба- зи даних DB_Books через утиліту SQL server Management Studio. Запус- титі її за допомогою команд:

DECLARE @return_status INT EXEC @return_status \u003d checkname 1 SELECT "Return Status" \u003d @return_status

Приклад створення процедури без параметрів для збільшення значення ключового поля в таблиці Purchases в 2 рази:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase \u003d Code_purchase * 2

Завдання 6. Створіть дану процедуру в розділі Stored Procedures ба- зи даних DB_Books через утиліту SQL server Management Studio. Запус- титі її за допомогою команди

EXEC update_proc

Приклад процедури з вхідним параметром для отримання всієї ін- формації про конкретний автора:

CREATE PROC select_author @k CHAR (30) AS SELECT * FROM Authors WHERE name_author \u003d @k

Завдання 7.

EXEC select_author "Пушкін А.С." або select_author @ k \u003d "Пушкін А.С." або EXEC select_author @ k \u003d "Пушкін А.С."

Приклад створення процедури з вхідним параметром і значенням за замовчуванням для збільшення значення ключового поля в таблиці Purchases в задану кількість рази (за замовчуванням в 2 рази):

CREATE PROC update_proc @p INT \u003d 2 AS UPDATE Purchases SET Code_purchase \u003d Code_purchase * @p

Процедура не повертає ніяких даних.

Завдання 8. Створіть дану процедуру в розділі Stored Procedures ба- зи даних DB_Books через утиліту SQL server Management Studio. Запус- титі її за допомогою команд:

EXEC update_proc 4 або EXEC update_proc @p \u003d 4 або EXEC update_proc --будет використано значення за замовчуванням.

Приклад створення процедури з вхідним і вихідним параметрами. Створити процедуру для визначення кількості замовлень, зроблених за вказаний період:

CREATE PROC count_purchases @ d1 SMALLDATETIME, @ d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @ c \u003d COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @ d1 AND @ d2 SET @c \u003d ISNULL (@c, 0)

Завдання 9. Створіть дану процедуру в розділі Stored Procedures ба- зи даних DB_Books через утиліту SQL server Management Studio. Запус- титі її за допомогою команд:

DECLARE @ c2 INT EXEC count_purchases '01 - jun- 2006 ', '01 - jul- 2006', @ c2 OUTPUT SELECT @ c2

Варіанти завдань до лабораторній роботі №4

Загальні положення. В утиліті SQL Server Management Studio створити нову сторінку для коду (кнопка «Створити запит»). Програмно зробити активної створену БД DB_Books за допомогою оператора Use. Створити збережені процедури c допомогою операторів Create procedure, причому самостійно визначити імена процедур. Кожна процедура буде виконувати по одному SQL запиту, які були виконані в другій лабораторній роботі. Причому код SQL запитів потрібно змінити таким чином, щоб в них можна було передавати значення полів, за якими здійснюється пошук.

Наприклад, вихідне завдання і запит в лабораторній роботі №2:

/ * Вибрати з довідника постачальників (таблиця Deliveries) назви ком- паній, телефони та ІПН (поля Name_company, Phone і INN), у яких назва компанії (поле Name_company) "ВАТ МИР".

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company \u003d "ВАТ МИР"

* / -У цій роботі буде створена процедура:

CREATE PROC select_name_company @comp CHAR (30) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company \u003d @comp

-Для запуску процедури використовується команда:

EXEC select_name_company "ВАТ МИР"

список завдань

В утиліті SQL Server Management Studio створити нову програму. Програмно зробити активної індивідуальну БД, створену в лабораторній роботі №1, за допомогою оператора Use. Створити збережені процедури c допомогою операторів Create procedure, причому самостійно визначити імена процедур. Кожна процедура буде виконувати по одному SQL запиту, які представлені у вигляді окремих завдань за варіантами.

Варіант 1

1. Вивести список співробітників, у яких є хоча б одна дитина.

2. Вивести список дітей, яким видали подарунки в зазначений період.

3. Вивести список батьків, у яких є неповнолітні діти.

4. Вивести інформацію про подарунки з вартістю більше зазначеного числа, відсортованих за датою.

Варіант 2

1. Вивести список приладів із зазначеним типом.

2. Вивести кількість відремонтованих приладів і загальну вартість ремонтів у зазначеного майстра.

3. Вивести список власників приладів і кількість їх звернень, відсортований за кількістю звернень по спадаючій.

4. Вивести інформацію про майстрів з розрядом більше зазначеного числа або з датою прийому на роботу менше зазначеної дати.

варіант 3

2. Вивести список кодів продажів, за якими продано квітів на суму більше зазначеного числа.

3. Вивести дату продажу, суму, продавця і квітка за вказаним кодом продажу.

4. Вивести список квітів і сорт для квітів з висотою більше зазначеного числа або квітучий.

варіант 4

1. Вивести список ліків з зазначеним показанням до застосування.

2. Вивести список дат поставок, за якими продано більше зазначеного числа однойменного ліки.

3. Вивести дату поставки, суму, ПІБ керівника від постачальника і назву ліків за кодом надходження більше зазначеного числа.

варіант 5

2. Вивести список списаного обладнання із зазначеної причини.

3. Вивести дату надходження, назва обладнання, ПІБ відповідальної і дату списання для обладнання, списаного в зазначений період.

4. Вивести список обладнання з вказаним типом або з датою надходження більше певного значення

варіант 6

1. Вивести список страв з вагою більше зазначеного числа.

2. Вивести список продуктів, в назві яких зустрічається вказаний фрагмент слова.

3. Вивести обсяг продукту, назву страви, назва продукту з кодом страви від зазначеного початкового значення за певним кінцевому значенню.

4. Вивести порядок приготування страви і назва страви з кількістю вуглеводів більше певного значення або кількістю калорій більше зазначеного значення.

варіант 7

1. Вивести список співробітників з вказаною посадою.

3. Вивести дату реєстрації, тип документа, ПІБ реєстратора і назва організації для документів, зареєстрованих в зазначений період.

4. Вивести список зареєстрованих документів з певним типом документа або з датою реєстрації більше зазначеного значення.

варіант 8

1. Вивести список співробітників з вказаною причиною звільнення.

3. Вивести дату реєстрації, причину звільнення, ПІБ співробітника для документів, зареєстрованих в зазначений період.

варіант 9

1. Вивести список співробітників, які брали відпустку зазначеного типу.

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

3. Вивести дату реєстрації, тип відпустки, ПІБ співробітника для документів, зареєстрованих в зазначений період.

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

варіант 10

1. Вивести список співробітників з вказаною посадою.

2. Вивести список документів, в змісті яких зустрічається вказаний фрагмент слова.

3. Вивести дату реєстрації, тип документа, ПІБ відправника та назва організації для документів, зареєстрованих в зазначений період.

4. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа менше певного значення.

варіант 11

1. Вивести список співробітників, призначених на зазначену посаду.

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

3. Вивести дату реєстрації, посада, ПІБ співробітника для документів, зареєстрованих в зазначений період.

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

варіант 12

3. Вивести список осіб, що брали обладнання в прокат і кількість їх звернень, відсортований за кількістю звернень по спадаючій.

варіант 13

1. Вивести список обладнання з вказаним типом. 2. Вивести список устаткування, яке списав певний співробітник.

3. Вивести кількість списаного обладнання, згрупованого за типами обладнання.

4. Вивести інформацію про співробітників з датою прийому на роботу більше певної дати.

варіант 14

1. Вивести список квіток з вказаним типом листа.

2. Вивести список кодів надходжень, за якими продано квітів на суми більше певного значення.

3. Вивести дату надходження, суму, назви постачальника і квітів за певним кодом постачальника.

4. Вивести список квітів і сорт для квітів з висотою більше за певну кількість або квітучий.

варіант 15

1. Вивести список клієнтів, заїхали в номери в зазначений період.

2. Вивести загальну суму оплат за номери для кожного клієнта.

3. Вивести дату заїзду, тип номера, ПІБ клієнтів, зареєстрованих в зазначений період.

4. Вивести список зареєстрованих клієнтів в номерах певного типу.

варіант 16

1. Вивести список обладнання з вказаним типом.

2. Вивести список устаткування, яке брав в прокат певний клієнт.

3. Вивести список осіб, що брали обладнання в прокат і кількість їх звернень, відсортованих за кількістю звернень по спадаючій.

4. Вивести інформацію про клієнтів, відсортованих за адресами.

варіант 17

1. Вивести список цінностей з закупівельною вартістю більше певного значення або терміном гарантії більше зазначеного числа.

2. Вивести список місць знаходження матеріальних цінностей, в назві яких зустрічається вказане слово.

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

4. Вивести список матеріально відповідальних осіб з датою прийому на роботу в зазначеному діапазоні.

варіант 18

1. Вивести список ремонтних робіт, Виконаних певним майстром.

2. Вивести список етапів робіт, що входять в роботи, в назві яких зустрічається вказане слово.

3. Вивести суму вартості етапів ремонтних робіт для робіт з кодом в зазначеному діапазоні.

4. Вивести список майстрів з датою прийому на роботу в зазначеному діапазоні.

варіант 19

1. Вивести список ліків з певним показанням.

2. Вивести список номерів чеків, за якими продано більше за певну кількість ліків.

3. Вивести дату продажу, суму, ПІБ касира і ліки по чеку з зазначеним номером.

4. Вивести список ліків і одиниці вимірювання для ліків з кількістю в упаковці більше зазначеного числа або кодом ліки менше певного значення.

варіант 20

1. Вивести список співробітників з вказаною посадою.

2. Вивести список документів, в змісті яких зустрічається вказаний фрагмент слова.

3. Вивести дату реєстрації, тип документа, ПІБ виконавця і факт виконання для документів, зареєстрованих в зазначений період.

4. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа в певному діапазоні.

22 відповідей

У моєму досвіді написання в основному додатків WinForms Client / Server це прості висновки, до яких я прийшов:

Використовувати збережені процедури:

• Для будь-якої складної роботи з даними. Якщо ви збираєтеся робити щось дійсно потребує таблиць курсора або temp, це, як правило, самий швидкий спосіб зробити це в SQL Server.
• Якщо вам потрібно заблокувати доступ до даних. Якщо ви не надаєте доступ до таблиці користувачам (або ролі або чого-небудь ще), ви можете бути впевнені, що єдиний спосіб взаємодії з даними - через створюваний вами СП.

Використовувати спеціальні запити:

• Для CRUD, коли вам не потрібно обмежувати доступ до даних (або робите це по-іншому).
• Для простих пошуків. Створення SP для безлічі критеріїв пошуку - це біль і складність в обслуговуванні. Якщо ви можете створити досить швидкий пошуковий запит, Використовуйте це.

У більшості моїх програм я використовував як SP, так і ad-hoc sql, хоча я вважаю, що я використовую SP все менше і менше, оскільки вони в кінцевому підсумку є кодом, як С #, тільки складніше контролювати, тестувати і підтримувати. Я б рекомендував використовувати ad-hoc sql, якщо ви не можете знайти конкретну причину.

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

Вбудовуючи запити в ваш додаток, ви тісно пов'язуєте себе з цією моделлю даних.

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

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

Як людина з даними, я б не став розглядати роботу з базою даних, до якої звертаються через adhoc-запити, тому що їх важко ефективно налаштовувати або керувати. Як я можу дізнатися, що вплине на зміну схеми? Крім того, я не думаю, що користувачам слід надавати прямий доступ до таблиць бази даних з міркувань безпеки (і я маю на увазі не тільки атаки SQL-ін'єкцій, але також і тому, що це базовий внутрішній елемент управління, який не допускає прямих прав і вимагає від усіх користувачів використовуйте тільки procs, призначені для застосування, щоб запобігти можливому шахрайство. Будь-яка фінансова система, яка дозволяє безпосередньо вставляти, оновлювати або видаляти права на таблиці, має величезний ризик для шахрайства. Це погано.).

Бази даних не є об'єктно-орієнтованими, а код, який здається хорошим з об'єктно-орієнтованої точки зору, може бути вкрай поганим з точки зору бази даних.

Наші розробники повідомляють нам, що вони раді, що весь наш доступ до баз даних здійснюється через procs, тому що він значно прискорює виправлення помилки, яка залежить від даних, а потім просто запускає proc в робочому середовищі, а не створює нову гілку коду і перекомпіліровать і перезавантажити в виробництво. Ми вимагаємо, щоб усі наші процеси були в підривній діяльності, тому контроль джерела не є проблемою взагалі. Якщо він не знаходиться в Subversion, він буде періодично віддалятися dbas, тому немає ніякого опору використанню Source Control.

Збережені процедури, безумовно, підходять ... вони скомпільовані, мають план виконання перед початком роботи, і ви можете займатися управлінням правами на них.

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

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

Я хотів би процитувати Tom Kyte від Oracle тут ... Ось його правило про те, де писати код ... хоча і трохи непов'язаний, але добре знаю, я думаю.

У нашому додатку є шар коду, який надає вміст запиту (а іноді і виклик збереженої процедури). Це дозволяє нам:

• легко отримати всі запити при управлінні версіями
• щоб зробити всі зміни для кожного запиту для різних серверів баз даних
• виключає повторення одного і того ж коду запиту через наш код

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

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

Аргумент, що збережені процедури більш ефективні, більше не містить води. текст посилання

Виконання google для збереженої процедури vs Dynamic Query покаже гідні аргументи у будь-якому випадку і, ймовірно, краще для вас прийняти ваше власне рішення ...

Деякі речі, про які потрібно подумати: Кому потрібні процедури, що зберігаються, Anyways?

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

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

Я використовую ad-hoc для запитів, які динамічно генеруються на основі призначеного для користувача введення.

Procs з причин, згаданим іншими, а також простіше налаштувати proc за допомогою Профілювальники або частин proc. Таким чином, вам не потрібно розповідати кому-небудь про запуск свого застосування, щоб дізнатися, що відправляється на сервер SQL

Якщо ви використовуєте ad-hoc-запити, переконайтеся, що вони параметризованих

Параметрірованний SQL або SPROC ... не має значення з точки зору продуктивності ... ви можете запросити оптимізацію одного з них.

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

Я як і раніше вважаю за краще параметризрвані SQL, хоча ...

Аргумент продуктивності sproc є спірним - 3 верхніх RDBM використовують кешування плану запитів і деякий час. Його документально підтвердили ... Або ще 1995 рік?

Однак вбудовування SQL в ваше додаток також є жахливим дизайном - обслуговування коду, мабуть, є відсутньої концепцією для багатьох.

Якщо додаток може починатися з нуля за допомогою ORM (додатки з зеленим полем далекі від декількох!), Це відмінний вибір, оскільки модель вашого класу керує вашою моделлю БД і економить час.

Якщо структура ORM недоступна, ми використовували гібридний підхід створення XML файлу SQL-ресурсів для пошуку рядків SQL в міру необхідності (вони потім кешуються інфраструктурою ресурсів). Якщо SQL потребує будь-яких незначних маніпуляціях, виконаних в коді, - якщо потрібна велика маніпуляція рядком SQL, ми переосмислимо цей підхід.

Цей гібридний підхід полегшує управління розробниками (можливо, ми є меншістю, оскільки моя команда досить яскрава, щоб читати план запиту), а розгортання - це проста перевірка з SVN. Крім того, він спрощує комутацію RDBM - просто замініть файл ресурсів SQL (не так просто, як інструмент ORM, звичайно, але це працює з застарілими системами або не підтримує базою даних)

Мій досвід полягає в тому, що 90% запитів і / або процедур, що зберігаються взагалі не повинні записуватися (по крайней мере, вручну).

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

Я вважаю за краще зберігати всі дані доступ в коді програми, в якому рівень доступу до даних виконує прямі SQL-запити. З іншого боку, логіка управління, Яку я помістив в базу даних у вигляді тригерів, збережених процедур, призначених для користувача функцій і ще чогось. Прикладом того, що я вважаю гідним бази даних, є генерація даних - припустимо, що у нашого клієнта є ім'я FirstName і LastName. Тепер для призначеного для користувача інтерфейсу потрібно DisplayName, яке виводиться з деякої нетривіальною логіки. Для цього покоління я створюю збережену процедуру, яка потім запускається тригером щоразу, коли оновлюється рядок (або інші вихідні дані).

Схоже, що це кілька поширене непорозуміння, що рівень доступу до даних - це база даних, і все, що стосується доступу до даних і даними, відбувається саме там. Це просто неправильно, але я бачу багато проектів, які випливають з цієї ідеї. Можливо, це локальний феномонон.

У попередній статті даного циклу ми розглянули, як можна витягти дані з таблиць, модифікувати їх структуру, створювати, модифікувати і видаляти бази даних і об'єкти, в них містяться. У цій статті ми поговоримо більш докладно про об'єкти, характерних для серверних СУБД: уявленнях, триггерах і збережених процедурах.

У першій статті цього циклу, опублікованій в № 3'2000 нашого журналу, ми відзначали, що більшість сучасних серверних СУБД підтримують уявлення, тригери і процедури. Уявлення також підтримуються і багатьма настільними СУБД, наприклад Access, dBase, Clipper.

Слід зазначити, що тригери і процедури зазвичай пишуться на мовах програмування, що представляють собою процедурні розширення мови SQL. Ці розширення містять оператори, що дозволяють описувати алгоритми, наприклад do ... while, if ... then ... else, відсутні в самій мові SQL (якщо ви пам'ятаєте, SQL - непроцедурного мова, і на ньому можна сформулювати завдання, але не можна описувати алгоритми його виконання). На відміну від мови SQL, що підкоряється стандарту, його процедурні розширення ніяк не стандартизовані, і різні СУБД використовують різні синтаксичні конструкції для реалізації одних і тих же алгоритмічних конструкцій, але обговорення відмінностей в синтаксисі розширень SQL для різних СУБД виходить за рамки даної статті.

Для ілюстрації того, як можна використовувати уявлення, тригери і процедури, ми вибрали Microsoft SQL Server 7.0 і базу даних NorthWind, що входить в комплект поставки цієї СУБД.

Перш ніж виконувати приклади, зверніть увагу на те, що реалізація і спосіб зберігання тригерів і збережених процедур в використовуваної вами СУБД можуть відрізнятися від наведених у цій статті. Крім того, для створення серверних об'єктів слід мати відповідні дозволи, що надаються адміністратором бази даних.

Відзначимо також, що деякі ODBC-драйвери не підтримують виклик збережених процедур з клієнтських додатків, навіть якщо такі підтримуються самої СУБД. Однак в цьому випадку збережені процедури і раніше можуть бути викликані з тригерів.

Давайте почнемо з уявлень, потім обговоримо збережені процедури і закінчимо главу оглядом тригерів.

уявлення

Уявлення - це віртуальна таблиця, зазвичай містить набір колонок однієї або декількох таблиць. Насправді уявлення містить не дані, а лише SQL-запит типу SELECT, який вказує, які саме дані і з яких таблиць потрібно взяти при зверненні до цієї вистави. З цієї точки зору уявлення - це зберігається запит.

У більшості випадків уявлення використовуються для забезпечення безпеки даних. Наприклад, деякі категорії користувачів можуть мати доступ до подання, але не до таблиць, дані яких його формують; крім того, SQL-запит може містити параметр USER (ім'я, під яким зареєструвався користувач), і в цьому випадку дані, доступні при зверненні до подання, будуть залежати від імені користувача.

Нижче перераховані основні характеристики уявлень:

• уявлення поводяться подібно таблиць;
• уявлення не містять даних;
• уявлення можуть використовувати дані більш ніж з однієї таблиці.

Для створення уявлення ми можемо використовувати SQL-пропозиція CREATE VIEW, для його модифікації - пропозиція ALTER VIEW, а для видалення його - пропозиція DROP VIEW.

Ми почнемо з оператора CREATE VIEW, що дозволяє створити уявлення для поточної бази даних.

Пропозиція CREATE VIEW

Синтаксис пропозиції для створення уявлення нагадує SQL-пропозиція SELECT з декількома додатковими ключовими словами. Нижче наведено його спрощений синтаксис:

CREATE VIEW view_name AS select_statement

Аргумент view_name вказує на ім'я уявлення. Ключове слово, яке використовується в Microsoft SQL Server, дозволяє приховати вихідний текст пропозиції CREATE VIEW в таблиці syscomments.

Ключове слово AS показує, який запит SELECT реально буде виконуватися при зверненні до подання. Зверніть увагу на те, що цей запит не може містити ключових слів ORDER BY, COMPUTE або COMPUTE BY, INTO і не може посилатися на тимчасову таблицю.

Для модифікації створеного раніше уявлення слід використовувати пропозицію ALTER VIEW, коротко описаний у наступному розділі.

Пропозиція DROP VIEW

Ця пропозиція використовується для видалення уявлення з бази даних. Зверніть увагу на те, що при видаленні таблиці з бази даних видаляються і всі уявлення, що посилаються на неї. Використовуючи цю пропозицію, ми повинні вказати ім'я видаляється уявлення. Після того як уявлення видалено, вся інформація про нього видаляється з системних таблиць.

Ще один випадок, коли уявлення потрібно видалити, може виникнути за умови, що структура таблиць, на яких воно грунтується, змінилася після створення вистави. В цьому випадку можна видалити уявлення і потім створити його заново за допомогою пропозиції CREATE VIEW.

Створення і використання уявлень

Пропозиція CREATE VIEW використовується для створення уявлень, що дозволяють отримувати дані, що задовольняють певним вимогам. Подання створюється в поточній базі даних і зберігається як її окремий об'єкт.

Найкращий спосіб створити уявлення - створити запит SELECT і, перевіривши його, додати відсутню частину пропозиції CREATE VIEW. Давайте розглянемо вихідний текст подання Products by Category в базі даних NorthWind (лістинг 1).

Перший рядок, виділена жирним шрифтом, є те, чим відрізняється SQL-пропозиція для створення уявлення від звичайного запиту SELECT, залученого до робіт з вибору даних. Пропозиція SELECT, що міститься в цій виставі, вибирає поля з двох таблиць - поле CategoryName з таблиці CATEGORIES і поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued з таблиці PRODUCTS. Після цього дані двох таблиць зв'язуються по полю CategoryID, і тільки ті продукти, які ще є на складі (див. Критерій після ключового слова WHERE), включаються в результуючий набір даних. Результат звернення до цієї вистави показаний на рис. 1.

Тепер давайте створимо уявлення, що показує всі території східного регіону. Це уявлення базується на наступному запиті (лістинг 2).

Переконавшись в тому, що пропозиція SELECT повертає результати, які нам потрібні, ми додаємо оператор CREATE VIEW і присвоюємо створюваному поданням ім'я EASTTERR (лістинг 3).

Замість створення тексту подання вручну можна використовувати візуальні інструменти, зазвичай входять до складу СУБД. На рис. 2 показано, як те ж саме уявлення може бути створено за допомогою інструменту View Designer, який є складовою частиною Enterprise Manager, що входить в Microsoft SQL Server.

Верхня частина View Designer дозволяє вказати, як пов'язані таблиці і які поля будуть відображатися в поданні. Нижче ви можете вказати псевдоніми таблиць і полів, обмеження на їх значення, спосіб відображення. Далі наведені вихідний текст подання і результати його виконання.

Перш ніж ми закінчимо короткий огляд уявлень, поговоримо трохи про те, як отримати додаткову інформацію про них. У Microsoft SQL Server 7.0 ми можемо використовувати такі системні збережені процедури:

• для отримання відомостей про подання можна використовувати системну збережену процедуру sp_help. Наприклад, sp_help EastTerr поверне відомості про щойно створеному уявленні;
• для отримання вихідного тексту подання можна використовувати збережену процедуру sp_helptext;
• для того щоб знайти список таблиць, від якого залежить уявлення, можна використовувати системну збережену процедуру sp_depends;
• для перейменування уявлення можна використовувати системну збережену процедуру sp_rename.

В даному розділі ми розглянули, як використовувати уявлення для отримання даних, які відповідають тим чи іншим критеріям. Однак повернемося до останнього прикладу. У базі даних NorthWind є чотири регіони, і для отримання списку територій всіх регіонів нам потрібно чотири різних уявлення. Це завдання можна було б спростити, якщо б ми могли передати значення RegionID як параметр. Це можна зробити за допомогою процедури, що, про що ми поговоримо в наступному розділі.

збережені процедури

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

Збережені процедури дозволяють підвищити продуктивність додатків. По-перше, в порівнянні зі звичайними SQL-запитами, що посилаються з клієнтського застосування, вони вимагають менше часу для підготовки до виконання, оскільки вони вже скомпільовані і збережені. По-друге, мережевий трафік в цьому випадку також менше, ніж в разі передачі SQL-запиту, так як по мережі передається меншу кількість даних. Мал. 3 ілюструє виклик збереженої процедури клієнтським додатком.

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

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

Для створення, зміни і видалення процедур існують спеціальні SQL-пропозиції - CREATE PROCEDURE, ALTER PROCEDURE і DROP PROCEDURE. Ми розглянемо їх в наступному розділі.

Пропозиція CREATE PROCEDURE

Пропозиція CREATE PROCEDURE використовується для створення збереженої процедури. Воно має наступний спрощений синтаксис:

CREATE PROC proc_name [(@parameter data_type) [\u003d default]] [...] AS sql_statements

Аргумент proc_name встановлює ім'я процедури, яка повинна бути унікальною в рамках поточної бази даних. Аргумент @parameter визначає параметр процедури. У реченні CREATE PROCEDURE можна визначити один чи кілька критеріїв. Якщо для параметра немає значення за замовчуванням, він повинен бути переданий користувачем (або клієнтським додатком) при виклику процедури. У Microsoft SQL Server 7.0 число параметрів процедури не повинно перевищувати 1024; за замовчуванням вони можуть мати NULL-значення.

Відзначимо, однак, що деякі універсальні механізми доступу до даних можуть накладати додаткові обмеження на число параметрів збережених процедур. Наприклад, BDE-драйвер для Oracle 8 здатний працювати тільки з процедурами, число параметрів яких не перевищує 10.

Аргумент data_type вказує тип даних для параметра. Ключове слово default може бути використано для установки значень за замовчуванням - це може бути константа або NULL. Якщо вказано значення за замовчуванням, процедура може бути викликана без вказівки значення параметра. Якщо процедура використовує параметр з ключовим словом LIKE, її значення за замовчуванням може містити групові символи (%, _, і [^]).

Ключове слово OUTPUT показує, що це повертається параметр.

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

Процедура, створена за допомогою пропозиції CREATE PROCEDURE, буде збережена в поточній базі даних. У Microsoft SQL Server імена процедур містяться в системній таблиці sysobjects, а вихідний текст - в таблиці syscomments.

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

Пропозиція DROP PROCEDURE

Ця пропозиція використовується для видалення збережених процедур з бази даних. Пропозиція DROP PROCEDURE приймає один аргумент - ім'я видаляється процедури.

При видаленні збереженої процедури відомості про неї видаляються з системних таблиць sysobjects і syscomments.

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

У розділі, присвяченому уявленням, ми звертали увагу на те, що було б зручно, якби ми могли передати в уявлення параметр, який містить значення RegionID для вибору одного з чотирьох регіонів в базі даних NorthWind. Давайте ще раз розглянемо запит, який повертає список територій регіону:

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID \u003d Region.RegionID WHERE Territories.RegionID \u003d 1

Щоб вибрати інший регіон, нам потрібно змінити умова в пропозиції WHERE в останньому рядку запиту. Отже, якщо ми використовуємо змінну (назвемо її RegID), ми зможемо вибрати один з чотирьох регіонів без зміни інших частин запиту.

У базі даних NorthWind чотири регіони з номерами від 1 до 4. Це означає, що змінна RegID повинна бути цілого типу. Код збереженої процедури наведено нижче:

CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID \u003d Region.RegionID WHERE Territories.RegionID \u003d @RegID

Зверніть увагу на те, що ми залишили майже весь текст запиту SELECT недоторканим (він виділений курсивом) і тільки додали пропозицію CREATE PROCEDURE з ім'ям новоствореної збереженої процедури (в першому рядку), оголошення параметра (у другому рядку) і ключове слово AS, яке вказує на початок пропозицій, реально виконують дії.

Результат виконання створеної процедури в SQL Server Query Analyzer для RegID \u003d 2 показаний на рис. 3.

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

У Microsoft SQL Server 7.0 ми також можемо використовувати системні збережені процедури для роботи зі звичайними збереженими процедурами:

• sp_stored_procedures - показує список збережених процедур;
• sp_helptext - показує вихідний текст збереженої процедури;
• sp_depends - показує відомості про залежності збережених процедур;
• sp_procoption - встановлює опції збережених процедур або встановлює їх;
• sp_recompile - перекомпілюються процедуру в момент її наступного виклику;
• sp_rename - змінює ім'я процедури.

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

Оскільки ми говоримо про Microsoft SQL Server, слід зазначити величезну кількість системних збережених процедур, реалізованих в ньому. Імена системних збережених процедур починаються з SP_ або XP_ і зберігаються в базі даних master. Вище ми вже описували деякі з часто використовуваних системних збережених процедур.

Зверніть увагу на те, що тригери не повинні повертати користувачеві дані.

У реченні CREATE TRIGGER можна використовувати дві спеціальні таблиці. Наприклад, таблиці deleted і inserted мають ту ж структуру, що і таблиця, для якої визначено тригер, і містять старе і нове значення записів, змінених користувачем. Наприклад, ми можемо використовувати наступне SQL-пропозиція для пошуку віддалених записів:

SELECT * FROM deleted

У табл. 3 показано вміст таблиць deleted і inserted для всіх можливих змін даних.

Для зміни наявного тригера слід використовувати пропозицію ALTER TRIGGER. Ми поговоримо про нього в наступному розділі.

Для початку нам потрібно додати до таблиці два нових поля, в яких будуть міститися ці відомості. Назвемо їх UpdatedBy (ім'я менеджера, обновив запис останнім) і UpdatedWhen (час, коли була змінена запис). Потім створимо тригер з ім'ям KeepTrack. Ось його код:

CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy \u003d USER_NAME (), Customers.UpdatedWhen \u003d GETDATE () FROM inserted, Customers WHERE inserted.CustomerID \u003d Customers.CustomerID

Як видно з початкового тексту тригера, він виконується після кожної операції INSERT і UPDATE в таблиці Customers. Цей тригер буде зберігати ім'я менеджера (користувача бази даних) в поле Customers.UpdatedBy і дату і час зміни - в поле Customers.UpdatedWhen. Ці дані беруться з тимчасової таблиці inserted.

Як бачимо, цей тригер дозволяє стежити за змінами і вставкою нових записів в таблиці.

Перед тим як закінчити короткий огляд тригерів, ми повинні повідомити, де можна знайти відомості про наявні триггерах. Таблиця sysobjects зберігає відомості про тригерах і їх типах, а таблиця syscomments містить їх вихідний текст.

висновок

У цій частині ми розглянули кілька типів об'єктів баз даних - збережені процедури, подання та тригери. Ми дізналися наступне:

• Уявлення - це віртуальна таблиця, зазвичай створюється як підмножина стовпців однієї або декількох таблиць. Для створення уявлення застосовується пропозицію CREATE VIEW, для модифікації - пропозиція ALTER VIEW, а для видалення - пропозиція DROP VIEW.
• Процедура - це скомпільований набір SQL-пропозицій, збережений в базі даних як іменований об'єкт і виконується як єдиний фрагмент коду. Для створення збереженої процедури застосовується пропозицію CREATE PROCEDURE, для зміни - ALTER PROCEDURE, а для видалення - DROP PROCEDURE.
• Tріггер - це спеціальний тип збереженої процедури, яка автоматично викликається, коли дані в певній таблиці додаються, видаляються чи змінюються за допомогою SQL-пропозицій INSERT, DELETE або UPDATE. Тригери створюються за допомогою пропозиції CREATE TRIGGER. Для зміни тригера використовується пропозицію ALTER TRIGGER, а для видалення - пропозиція DROP TRIGGER.

КомпьютерПресс 12 «2000

процедура - це спеціальний тип пакета інструкцій Transact-SQL, створений, використовуючи мова SQL і процедурні розширення. Основна відмінність між пакетом і збереженої процедурою полягає в тому, що остання зберігається у вигляді об'єкта бази даних. Іншими словами, збережені процедури зберігаються на стороні сервера, щоб поліпшити продуктивність і сталість виконання повторюваних завдань.

Компонент Database Engine підтримує збережені процедури і системні процедури. Збережені процедури створюються таким же чином, як і всі інші об'єкти баз даних, тобто за допомогою мови DDL. Системні процедури надаються компонентом Database Engine і можуть застосовуватися для доступу до інформації в системному каталозі і її модифікації.

При створенні збереженої процедури можна визначити необов'язковий список параметрів. Таким чином, процедура буде приймати відповідні аргументи при кожному її виклику. Збережені процедури можуть повертати значення, що містить певну користувачем інформацію або, в разі помилки, відповідне повідомлення про помилку.

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

Збережені процедури можна також використовувати для наступних цілей:

для створення журналу логів про дії з таблицями баз даних.

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

Збережені процедури, які створюють логи операцій записи і / або читання таблиць, надають додаткову можливість забезпечення безпеки бази даних. Використовуючи такі процедури, адміністратор бази даних може відстежувати модифікації, внесені в базу даних користувачами або прикладними програмами.

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

Збережені процедури створюються за допомогою інструкції CREATE PROCEDURE, Яка має наступний синтаксис:

CREATE PROC proc_name [((@ param1) type1 [VARYING] [\u003d default1])] (...) AS batch | EXTERNAL NAME method_name Угоди по синтаксису

Параметр schema_name визначає ім'я схеми, яка призначається власником створеної збереженої процедури. Параметр proc_name визначає ім'я процедури. Параметр @ param1 є параметром процедури (формальним аргументом), чий тип даних визначається параметром type1. Параметри процедури є локальними в межах процедури, подібно до того, як локальні змінні є локальними в межах пакету. Параметри процедури - це значення, які передаються викликає об'єктом процедурі для використання в ній. Параметр default1 визначає значення за замовчуванням для відповідного параметра процедури. (Заводське також може бути NULL.)

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

Як уже згадувалося раніше, попередньо компілювати форма процедури зберігається в базі даних і використовується при кожному її виклику. Якщо ж з яких-небудь причин збережену процедуру потрібно компілювати при кожному її виклику, при оголошенні процедури використовується опція WITH RECOMPILE. Використання опції WITH RECOMPILE зводить нанівець одне з найбільш важливих переваг збережених процедур: поліпшення продуктивності завдяки одній компіляції. Тому опцію WITH RECOMPILE слід використовувати тільки при частих змінах використовуваних збереженої процедурою об'єктів бази даних.

Пропозиція EXECUTE AS визначає контекст безпеки, в якому повинна виконуватися процедура, що зберігається після її виклику. Ставлячи це контекст, за допомогою Database Engine можна управляти вибором облікових записів користувачів для перевірки повноважень доступу до об'єктів, на які посилається дана процедура, що зберігається.

Типовим стає інструкцію CREATE PROCEDURE можуть тільки члени зумовленої ролі сервера sysadmin і визначеною ролі бази даних db_owner або db_ddladmin. Але члени цих ролей можуть привласнювати це право іншим користувачам за допомогою інструкції GRANT CREATE PROCEDURE.

У прикладі нижче показано створення простої процедури, що для роботи з таблицею Project:

USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT \u003d 5) AS UPDATE Project SET Budget \u003d Budget + Budget * @ percent / 100;

Як говорилося раніше, для поділу двох пакетів використовується інструкція GO. Інструкцію CREATE PROCEDURE можна об'єднувати з іншими інструкціями Transact-SQL в одному пакеті. Процедура IncreaseBudget збільшує бюджети для всіх проектів на певне число відсотків, визначається за допомогою параметра @percent. У процедурі також визначається значення числа відсотків за замовчуванням (5), яке застосовується, якщо під час виконання процедури цей аргумент відсутній.

Збережені процедури можуть звертатися до неіснуючих таблицями. Це властивість дозволяє виконувати налагодження коду процедури, не створюючи спочатку відповідні таблиці і навіть не підключаючись до кінцевого сервера.

На відміну від основних процедур, які завжди зберігаються в поточній базі даних, можливо створення тимчасових процедур, які завжди містяться в тимчасову системну базу даних tempdb. Одним із приводів для створення тимчасових процедур може бути бажання уникнути повторюваного виконання певної групи інструкцій при з'єднанні з базою даних. Можна створювати локальні або глобальні тимчасові процедури. Для цього ім'я локальної процедури задається з одинарним символом # (#proc_name), а ім'я глобальної процедури - з подвійним (## proc_name).

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

Життєвий цикл процедури, що складається з двох етапів: її створення і її виконання. Кожна процедура створюється один раз, а виконується багаторазово. Процедура виконується за допомогою інструкції EXECUTE користувачем, який є власником процедури або володіє правом EXECUTE для доступу до цієї процедури. Інструкція EXECUTE повинен виглядати так:

[] [@Return_status \u003d] (proc_name | @proc_name_var) ([[@ parameter1 \u003d] value | [@ parameter1 \u003d] @variable] | DEFAULT) .. Угоди по синтаксису

За винятком параметра return_status, всі параметри інструкції EXECUTE мають таке ж логічне значення, як і однойменні параметри інструкції CREATE PROCEDURE. Параметр return_status визначає целочисленную змінну, в якій зберігається стан повернення процедури. Значення параметру можна привласнити, використовуючи або константу (value), або локальну змінну (@variable). Порядок значень іменованих параметрів не важливий, але значення різних неназваних параметрів повинні надаватися в тому порядку, в якому вони визначені в інструкції CREATE PROCEDURE.

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

Коли інструкція EXECUTE є першою інструкцією пакета, ключове слово EXECUTE можна опустити. Проте буде надійніше включати це слово в кожен пакет. Використання інструкції EXECUTE показано в прикладі нижче:

USE SampleDb; EXECUTE IncreaseBudget 10;

Інструкція EXECUTE в цьому прикладі виконує збережену процедуру IncreaseBudget, яка збільшує бюджет всіх проектів на 10%.

У прикладі нижче показано створення збереженої процедури для обробки даних в таблицях Employee і Works_on:

Процедура ModifyEmpId в прикладі ілюструє використання збережених процедур, як частина процесу забезпечення посилальної цілісності (в даному випадку між таблицями Employee і Works_on). Подібну процедуру, що можна використовувати всередині визначення тригера, який власне і забезпечує довідкову цілісність.

У прикладі нижче показано використання в збереженій процедурі пропозиції OUTPUT:

Дану процедуру, що можна запустити на виконання за допомогою наступних інструкцій:

DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @ empId \u003d 18316, @ [Email protected] OUTPUT; 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 вводиться пропозиція WITH RESULTS SETS, За допомогою якого при виконанні певних умов можна змінювати форму результуючого набору збереженої процедури.

Наступні два приклади допоможуть пояснити цю пропозицію. Перший приклад є вступним прикладом, який показує, як може виглядати результат, коли опущено пропозицію WITH RESULTS SETS:

Процедура EmployeesInDept - це проста процедура, яка відображає табельні номери та прізвища всіх співробітників, що працюють в певному відділі. Номер відділу є параметром процедури, і його потрібно вказати при її виклику. Виконання цієї процедури виводить таблицю з двома стовпцями, заголовки яких збігаються з назвами відповідних стовпців таблиці бази даних, тобто Id і LastName. Щоб змінити заголовки стовпців результату (а також їх тип даних), в SQL Server 2012 застосовується нова пропозиція WITH RESULTS SETS. Застосування цієї пропозиції показано в прикладі нижче:

USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS ((INT NOT NULL, [Прізвище] CHAR (20) NOT NULL));

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

Як можна бачити, запуск збереженої процедури з використанням пропозиції WITH RESULT SETS в інструкції EXECUTE дозволяє змінити найменування і тип даних стовпців результуючого набору, що видається цією процедурою. Таким чином, ця нова функціональність надає велику гнучкість у виконанні процедур і приміщенні їх результатів в нову таблицю.

Зміна структури збережених процедур

Компонент Database Engine також підтримує інструкцію ALTER PROCEDURE для модифікації структури збережених процедур. Інструкція ALTER PROCEDURE зазвичай застосовується для зміни інструкцій Transact-SQL всередині процедури. Всі параметри інструкції ALTER PROCEDURE мають таке ж значення, як і однойменні параметри інструкції CREATE PROCEDURE. Основною метою використання цієї інструкції є уникнення перевизначення існуючих прав збереженої процедури.

Компонент Database Engine підтримує тип даних CURSOR. До цього типу належить для оголошення курсорів в збережених процедурах. курсор - це конструкція програмування, що застосовується для зберігання результатів запиту (зазвичай набору рядків) і для надання користувачам можливості відображати цей результат за рядком.

Для видалення однієї або групи збережених процедур використовується інструкція DROP PROCEDURE. Видалити збережену процедуру може тільки її власник або члени визначених ролей db_owner і sysadmin.

Збережені процедури і середовище CLR

SQL Server підтримує загальномовного середовища виконання CLR (Common Language Runtime), яка дозволяє розробляти різні об'єкти баз даних (збережені процедури, визначені користувачем функції, тригери, визначені користувачем статистичні функції і призначені для користувача типи даних), застосовуючи мови C # і Visual Basic. Середа CLR також дозволяє виконувати ці об'єкти, використовуючи систему загального середовища виконання.

Середа CLR дозволяється і забороняється допомогою опції clr_enabled системної процедури sp_configure, Яка запускається на виконання інструкцією RECONFIGURE. У прикладі нижче показано, як можна за допомогою системної процедури sp_configure дозволити використання середовища CLR:

USE SampleDb; EXEC sp_configure "clr_enabled", 1 RECONFIGURE

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

Створити процедуру, що зберігається на мові C # або Visual Basic, а потім скомпілювати її, використовуючи відповідний компілятор.

використовуючи інструкцію CREATE ASSEMBLY, Створити відповідний виконуваний файл.

Виконати процедуру, використовуючи інструкцію EXECUTE.

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

Спочатку створіть необхідну програму в будь-якій середовищі розробки, наприклад Visual Studio. Скомпілюйте готову програму в об'єктний код, використовуючи компілятор C # або Visual Basic. Цей код зберігається в файлі динамічної бібліотеки (.dll), який служить джерелом для інструкції CREATE ASSEMBLY, що створює проміжний виконується код. Далі виконайте інструкцію CREATE PROCEDURE, щоб зберегти виконуваний код у вигляді об'єкта бази даних. Нарешті, запустіть процедуру на виконання, використовуючи вже знайому нам інструкцію EXECUTE.

У прикладі нижче показаний вихідний код процедури, що на мові C #:

Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures (public static int CountEmployees () (int rows; SqlConnection connection \u003d new SqlConnection ( "Context Connection \u003d true"); connection.Open (); SqlCommand cmd \u003d connection.CreateCommand (); cmd.CommandText \u003d "select count (*) as "Кількість працівників" "+" from Employee "; rows \u003d (int) cmd.ExecuteScalar (); connection.Close (); return rows;))

У цій процедурі реалізується запит для підрахунку числа рядків в таблиці Employee. У директивах using на початку програми вказуються простору імен, необхідні для її виконання. Застосування цих директив дозволяє вказувати в вихідному коді імена класів без явної вказівки відповідних просторів імен. Далі визначається клас StoredProcedures, для якого застосовується атрибут SqlProcedure, Який інформує компілятор про те, що цей клас є збереженої процедурою. Всередині коду класу визначається метод CountEmployees (). З'єднання з системою баз даних встановлюється за допомогою екземпляра класу SqlConnection. Щоб відкрити з'єднання, застосовується метод Open () цього примірника. А метод CreateCommand () дозволяє звертатися до примірника класу SqlCommnd, Якому передається потрібна SQL-команда.

У наступному фрагменті коду:

Cmd.CommandText \u003d "select count (*) as" Кількість працівників "" + "from Employee";

використовується інструкція SELECT для підрахунку кількості рядків в таблиці Employee і відображення результату. Текст команди вказується, привласнюючи властивості CommandText змінної cmd екземпляр, що повертається методом CreateCommand (). далі викликається метод ExecuteScalar () примірника SqlCommand. Цей метод повертає скалярний значення, яке перетворюється в цілочисельний тип даних int і присвоюється змінної rows.

Тепер ви можете скомпілювати цей код, використовуючи середу Visual Studio. Я додав цей клас в проект з ім'ям CLRStoredProcedures, тому Visual Studio скомпілює однойменну збірку з розширенням * .dll. У прикладі нижче показаний наступний крок у створенні збереженої процедури: створення виконуваного коду. Перш ніж виконувати код в цьому прикладі, необхідно дізнатися розташування скомпільованої dll-файлу (зазвичай знаходиться в папці Debug проекту).

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D: \\ Projects \\ CLRStoredProcedures \\ bin \\ Debug \\ CLRStoredProcedures.dll" WITH PERMISSION_SET \u003d SAFE

Інструкція CREATE ASSEMBLY приймає в якості введення керований код і створює відповідний об'єкт, для якого можна створювати збережені процедури середовища CLR, визначені користувачем функції і тригери. Ця інструкція має наступний синтаксис:

CREATE ASSEMBLY assembly_name [AUTHORIZATION owner_name] 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 ASSEMBLY видаляє вказану збірку і всі пов'язані з нею файли з поточної бази даних.

У прикладі нижче показано створення збереженої процедури на основі керованого коду, реалізованого раніше:

USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees

Інструкція CREATE PROCEDURE в прикладі відрізняється від такої ж інструкції в прикладах раніше тим, що вона містить параметр EXTERNAL NAME. Цей параметр вказує, що код створюється середовищем CLR. Ім'я в цій пропозиції складається з трьох частин:

assembly_name.class_name.method_name

assembly_name - вказує ім'я збірки;

class_name - вказує ім'я загального класу;

method_name - необов'язкова частина, вказує ім'я методу, який задається всередині класу.

Виконання процедури CountEmployees показано в прикладі нижче:

USE SampleDb; DECLARE @count INT EXECUTE @count \u003d CountEmployees PRINT @count - Чи поверне 7

Інструкція PRINT повертає поточну кількість рядків в таблиці Employee.

Вмикай в свої процедури рядок - SET NOCOUNT ON:

З кожним DML виразом, SQL server дбайливо повертає нам повідомлення містить кількість оброблених записів. Дана інформація може бути нам корисна під час налагодження коду, але після буде абсолютно марною. Прописуючи SET NOCOUNT ON, ми відключаємо цю функцію. Для процедур містять кілька виразів або \\ і цикли дана дія може дати значний приріст продуктивності, тому як кількість трафіку буде значно знижено.

Transact-SQL

Використовуй ім'я схеми з ім'ям об'єкта:

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

Transact-SQL

SELECT * FROM dbo.MyTable --Вот так робити добре - Замість SELECT * FROM MyTable --А так робити погано --Визов процедури EXEC dbo.MyProc --Опять ж добре --Вместо EXEC MyProc --Плохо!

Не використовуй префікс «sp_» в імені своїх збережених процедур:

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

Використовуй IF EXISTS (SELECT 1) замість IF EXISTS (SELECT *):

Щоб перевірити наявність запису в іншій таблиці, ми використовуємо вираз IF EXISTS. Цей вираз повертає true якщо з внутрішнього вираження повертається хоч одне ізначеніе, не має значення «1», все колонки або таблиця. Возращаться дані, в принципі ніяк не використовуються. Таким чином для стиснення трафіку під час передачі даних логічніше використовувати «1», як показано нижче.