Що таке процедури в бд. Збережені процедури в середовищі ms 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. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа в певному діапазоні.

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

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

CALL процедура (...)

EXECUTE процедура (...)

Збережені процедури можуть повертати безлічі результатів, тобто результати запиту SELECT. Такі безлічі результатів можуть оброблятися, використовуючи курсори, іншими збереженими процедурами, повертаючи покажчик результуючого безлічі, або ж додатками. Збережені процедури можуть також містити оголошені змінні для обробки даних і курсорів, які дозволяють організувати цикл з кількох рядках в таблиці. Стандарт SQL надає для роботи вираження IF, LOOP, REPEAT, CASE і багато інших. Збережені процедури можуть приймати змінні, повертати результати або змінювати змінні і повертати їх, в залежності від того, де змінна оголошена.

Реалізація процедур варіюється від однієї СУБД до іншої. Більшість великих постачальників баз даних підтримують їх в тій чи іншій формі. Залежно від СУБД, збережені процедури можуть бути реалізовані на різних мовах програмування, таких, як SQL, Java, C або C ++. Збережені процедури написані не SQL можуть самостійно виконувати SQL-запити, а можуть і не виконувати.

за

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

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

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

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

проти

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

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

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

Міграція з однієї СУБД на іншу (DB2, SQL Server і ін.) Може привести до проблем.

Призначення і переваги збережених процедур

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

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

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

У більшості СУБД при першому запуску процедури, що вона компілюється (виконується синтаксичний аналіз і генерується план доступу до даних). Надалі її обробка здійснюється швидше. У СУБД Oracle виконується інтерпретація зберігається процедурного коду, що зберігається в словнику даних. Починаючи з версії Oracle 10g підтримується так звана природна компіляція (native compilation) зберігається процедурного коду в Сі і потім в машинний код цільової машини, після чого при виклику процедури, що відбувається пряме виконання її скомпільованої об'єктного коду.

можливості програмування

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

Безпека

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

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

Знижується ймовірність таких дій як «впровадження SQL-коду», оскільки добре написані збережені процедури додатково перевіряють вхідні параметри перед тим, як передати запит СУБД.

Реалізація процедур

Збережені процедури зазвичай створюються за допомогою мови SQL або конкретної його реалізації в обраній СУБД. Наприклад, для цих цілей в СУБД Microsoft SQL Server існує мова Transact-SQL, в Oracle - PL / SQL, в InterBase і Firebird - PSQL, в PostgreSQL - PL / pgSQL, PL / Tcl, PL / Perl, PL / Python, в IBM DB2 - SQL / PL (англ. ), в Informix - SPL. MySQL досить близько відповідає стандарту SQL: 2003, її мова схожа на SQL / PL.

У деяких СУБД можливе використання збережених процедур, написаних на будь-якій мові програмування, за якої можливе утворення незалежні виконувані файли, наприклад, на C ++ або Delphi. У термінології Microsoft SQL Server такі процедури називаються розширеними збереженими процедурами і є просто функціями, що містяться в Win32-DLL. А, наприклад, в Interbase і Firebird для функцій, що викликаються з DLL / SO, визначено інша назва - UDF (User Defined Function). В MS SQL 2005 з'явилася можливість написання збережених процедур на будь-якому язике.NET, а від розширених збережених процедур в майбутньому планується відмовитися. СУБД Oracle, в свою чергу, допускає написання збережених процедур на мові Java. У IBM DB2 написання збережених процедур і функцій на звичайних мовах програмування є традиційним способом, підтримуваним з самого початку, а процедурне розширення SQL було додано в цю СУБД тільки в досить пізніх версіях, після його включення в стандарт ANSI. Також процедури на Java і С підтримує Informix.

У СУБД Oracle збережені процедури можуть об'єднуватися в так звані пакети (англ. Packages). Пакет складається з двох частин - специфікації (англ. Package specification), в якій вказується визначення процедури, що, і тіла (англ. Package body), де знаходиться її реалізація. Таким чином Oracle дозволяє відокремити інтерфейс програмного коду від його реалізації.

В СУБД IBM DB2 збережені процедури можна об'єднувати в модулі.

синтаксис

CREATE PROCEDURE `p2` ()

SQL SECURITY DEFINER

COMMENT "A procedure"

SELECT "Hello World!";

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

4 характеристики збереженої процедури:

Language: з метою забезпечення переносимості, за замовчуванням вказано SQL.

Deterministic: якщо процедура весь час повертає один і той же результат, і приймає одні і ті ж входять параметри. Це для реплікації і процесу реєстрації. Значення за замовчуванням - NOT DETERMINISTIC.

SQL Security: під час виклику йде перевірка прав користувача. INVOKER - це користувач, що викликає збережену процедуру. DEFINER - це "творець" процедури. Значення за замовчуванням - DEFINER.

Comment: з метою документування, значення за замовчуванням - ""

Виклик процедури, що

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1 (10, "string parameter", @parameter_var);

Зміна процедури,

В MySQL є вираз ALTER PROCEDURE для зміни процедур, але воно підходить для зміни лише деяких характеристик. Якщо вам потрібно змінити параметри або тіло процедури, вам слід видалити і створити її заново.

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

DROP PROCEDURE IF EXISTS p2;

Це проста команда. Вираз IF EXISTS відловлює помилку в разі, якщо такої процедури не існує.

параметри

CREATE PROCEDURE proc1 (): порожній список параметрів

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один вхідний параметр. Слово IN необов'язково, тому що параметри за замовчуванням - IN (вхідні).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один повертається параметр.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одночасно входить і повертається.

Синтаксис оголошення змінної виглядає так:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

оголошення процедури

CREATE PROCEDURE [({IN | OUT | INOUT} [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC]

END

Ключові слова
. IN (Input) - вхідний параметр
. OUT (Output) - вихідний параметр
. INOUT - вхідний і вихідний, а також поле (без параметрів)
. DYNAMIC RESULT SET показує, що процедура може відкрити вказане число курсорів, які залишаться відкритими після повернення з процедури

Примітки
Не рекомендується використовувати багато параметрів в збережених процедурах (в першу чергу великих чисел і символьних рядків) з-за перевантаження мережі і стека. На практиці в існуючих діалектах Transact-SQL, PL / SQL і Informix спостерігається істотна відмінність від стандарту, як в оголошенні і використанні параметрів, оголошенні змінних, так і у виклику підпрограм. Microsoft рекомендує застосовувати наступну апроксимацію для оцінки розміру кеша процедур:
\u003d (Максимальна кількість одночасно працюючих користувачів) * (розмір самого великого плану виконання) * 1.25. Визначення розміру плану виконання в сторінках можна зробити за допомогою команди: DBCC MEMUSAGE.

виклик процедури

У багатьох існуючих СУБД виклик збережених процедур виконується за допомогою оператора:

EXECUTE PROCEDURE [(][)]

Примітка: Виклик збережених процедур може бути зроблений з програми, інший процедури, або в інтерактивному режимі.

Приклад оголошення процедури

CREATE PROCEDURE Proc1 AS // оголошуємо процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating\u003e 200 // оголошуємо курсор
OPEN Cur1 // відкриваємо курсор
FETCH NEXT FROM Cur1 // зчитуємо дані з курсора
WHILE @@ Fetch_Status \u003d 0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 // закриваємо курсор
DEALLOCATE Cur1
EXECUTE Proc1 // запускаємо процедуру

поліморфізм
Дві підпрограми з одним і тим же ім'ям можуть бути створені в одній і тій же схемі, якщо параметри цих двох підпрограм є в таке міру відмінними один від одного, щоб їх можна було розрізняти. Для того, щоб розрізняти дві підпрограми з одним і тим же ім'ям в одній схемі, кожній з них дається альтернативне і унікальне ім'я (specific name). Таке ім'я може бути явно вказано, коли визначається підпрограма. При виклику підпрограм при наявності декількох однакових імен визначення потрібної підпрограми здійснюється в кілька етапів:
. Спочатку визначаються всі процедури з зазначеним ім'ям, А якщо таких немає, то всі функції з заданим ім'ям.
. Для подальшого аналізу залишаються тільки ті підпрограми, по відношенню до яких даний користувач має привілей на виконання (EXECUTE).
. Для них відбираються ті, у яких число параметрів відповідає числу аргументів виклику. Перевіряються зазначені типи даних у параметрів і їх позиції.
. Якщо залишилося більше однієї підпрограми, то вибирається та, кваліфікаційне ім'я якої коротше.
На практиці в Oracle поліморфізм підтримується для функцій, оголошених тільки в пакеті, [Email protected] - в різних схема, а в Sybase і MS SQL Server перевантаження заборонена.

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

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

ALTER PROCEDURE [([{IN | OUT | INOUT}])]
BEGIN [ATOMIC]

END

Привілеї на виконання процедур

GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]

Системні процедури
Багато СУБД (включаючи SQL Server) мають певний набір вбудованих системних збережених процедур, які можна використовувати в своїх цілях.

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

У першій статті цього циклу, опублікованій в № 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

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

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Здесь код процедури
SELECT column1 FROM dbo.TblTable1
--Перключеніе SET NOCOUNT в початковий стан
SET NOCOUNT OFF;
GO

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

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

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

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

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name \u003d "MyTable" AND type \u003d "U")

5. Використовуй TRY-Catch для вилову помилок: До 2005 сервера після кожного запиту в процедурі писалося величезна кількість перевірок на помилки. Більше коду завжди споживає більше ресурсів і більше часу. З 2005 SQL Server "ом з'явився більш правильний і зручний спосіб вирішення цієї проблеми:

BEGIN TRY
--код
END TRY
BEGIN CATCH
--код вилову помилки
END CATCH

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

P.S.
Мій перший пост, не судіть строго.