Основні об’єкти. Збережені процедури і функції

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

• Утримувати параметри (аргументи);

• Викликати інші процедури;

• Повертати свій статус викликає процедурі або пакету, який вказує на успішне закінчення або помилку, і в разі помилки на її причину;

• Повертати значення параметрів викликає процедурі або пакету;

• Виконується завжди на стороні сервера.

• Функція крім того повертає результат через своє ім’я.

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

Збережені процедури здійснюються командою create procedure. Функції здійснюються командою – create function. Для виконання процедури, як системної, так і певної користувачем, використовується команда execute (виконати). Можна також просто вказати назву процедури, що, якщо воно є першим словом в операторі або пакеті.

Процедура або функція має дві частини:

• Специфікація, яка оголошує процедуру або функцію і складається з такої інформації:

o Імені процедури

o Імен і типів даних аргументів, якщо є

o Крім цього, ТІЛЬКИ для функцій – типу даних повертається

• Тіло, яке визначає процедуру або функцію. Тіло процедури складається з блоку T–SQL (який містить пропозиції SQL і процедурного розширення).

Основні об’єкти. Тригери

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

Лекція 10:

Збережені процедури і функції. Типи параметрів, синтаксис опису формальних параметрів

SQL Server

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

• Утримувати параметри (аргументи);

• Викликати інші процедури;

• Повертати свій статус викликає процедурі або модулю T_SQL, який вказує на успішне закінчення або помилку, і в разі помилки на її причину;

• Повертати значення параметрів викликає процедурі або модулю T-SQL;

Синтаксис для створення процедури:

create procedure [власник.] названіе_процедури [; номер] [

[(] @ Названіе_параметра тип_даних [= default] [output]

[@ Названіе_параметра тип_даних [= default] [output]] ... [)]] [with

recompile]

as sql_оператори

Оператор execute:

[Execute] [return_status =]

[[[сервер.]база_данных.]владелец.]название_процедуры[;номер]

[[@ Названіе_параметра =] значення |

[@ Названіе_параметра =] @ змінна [output]

[, [@ Названіе_параметра =] значення |

[@ Названіе_параметра =] @ змінна [output] ...]]

[With recompile]

Параметри

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

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

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

CREATE proc dbo.tran_Begin / *

 * Ініціалізація транзакції

 * Якщо процедура виконується в транзакції, то створюється savepoint

 * Інакше ініціалізується транзакція

 * На виході передається null для транзакції і ім’я точки збереження для savepoint

 * /

(trName DObjectName output,explicitSavepointNameFlag DLogical = 0)

as

declared Datetime

begin

– Return 0

if@@trancount = 0

begin

selecttrName = null

begin transaction

end

else

begin

– Якщо не задано ім’я і не встановлено прапор використання переданого імені, то генерується ім’я

ifexplicitSavepointNameFlag = 0 ortrName is null

begin

selectd = getdate ()

selecttrName = ‘tr _’ + ISNULL (convert (varchar,@@nestlevel), ‘‘) + ‘_’ + ISNULL (convert (varchar (50),

rand (datepart (mm,d) * 100000 + datepart (ss,d) * 1000 + datepart (ms,d))), ‘‘)

end

save trantrName

end

if@@error <> 0

begin

– Помилка створення транзакції

return 5

end

return 0

end

CREATE proc dbo.tran_Commit / *

 * Підтвердження транзакції

 * Працює в парі з tran_Begin

 * На вхід передається ім’я точки збереження (транзакції), повернене tran_Begin

 * Якщо виклик йде з транзакції іtrName одно null, то виконується commit,

 * Інакше нічого не робиться

 * /

(trName DObjectName)

as

begin

if@@trancount> 0 andtrName is null

begin

commit

if@@error <> 0

begin

– Помилка підтвердження транзакції

return 6

end

end

return 0

end

CREATE proc dbo.tran_Rollback / *

 * Відкат транзакції

 * Працює в парі з tran_Begin

 * На вхід передається ім’я точки збереження (транзакції), повернене tran_Begin

 * Якщо виклик йде з транзакції іtrName одно null, то виконується відкат всій транзакції,

 * Інакше виконується відкат до точки збереженняtrName

 * /

(trName DObjectName)

as

begin

if@@trancount> 0

begin

iftrName is null

rollback tran

else

rollback trantrName

if@@error <> 0

begin

– Помилка відкату транзакції

return 7

end

end

return 0

end

Лістинг 10.1.