Зберігається процедура (англ. stored procedure) - це іменований програмний об'єкт БД. У SQL Server є процедури, що зберігаються, декількох типів.
Системні процедури, що зберігаються (англ. system stored procedure) поставляються розробниками СУБД і використовуються для виконання дій із системним каталогом або отримання системної інформації. Їхні назви зазвичай починаються з префіксу "sp_". Запускаються процедури всіх типів, що зберігаються, за допомогою команди EXECUTE, яку можна скоротити до ЄХЕС. Наприклад, процедура sp_helplogins, запущена без параметрів, формує два звіти про імена облікових записів (Англ. logins) та відповідних їм у кожній БД користувачах (Англ. users).
EXEC sp_helplogins;
Щоб дати уявлення про дії, що виконуються за допомогою системних процедур, що зберігаються, в табл. 10.6 наведено деякі приклади. Всього ж системних процедур, що зберігаються в SQL Server більше тисячі.
Таблиця 10.6
Приклади системних процедур SQL Server
Користувачеві доступне створення збережених процедур у користувацьких БД і БД для тимчасових об'єктів. В останньому випадку процедура, що зберігається, буде тимчасової.Так само як у випадку з тимчасовими таблицями, назва тимчасової процедури, що зберігається, повинна починатися з префікса "#", якщо це локальна тимчасова збережена процедура, або з "##" - якщо глобальна. Локальна тимчасова процедура може використовуватися тільки в рамках з'єднання, в якому її створили, глобальна - і в інших з'єднаннях.
Програмовані об'єкти SQL Server можуть створюватися як за допомогою засобів Transact-SQL, так і за допомогою збірок (Англ. assembly) у середовищі CRL (Common Language Runtime) платформи Microsoft.Net Framework . У цьому підручнику розглядатиметься лише перший спосіб.
Для створення процедур, що зберігаються, використовується оператор CREATE PROCEDURE (можна скоротити до PROC), формат якого наведений нижче:
CREATE (PROC I PROCEDURE) proc_name [; number ]
[(gparameter data_type )
[“default] |
[ WITH
[ FOR REPLICATION ]
AS ([BEGIN]sql_statement[;][...n][END])
Якщо процедура, що зберігається (або тригер, функція, представлення) створюється з опцією ENCRYPTION, її код перетворюється таким чином, що текст стає нечитаним. У той же час, як зазначається в , алгоритм перенесений з ранніх версій SQL Server і не може розглядатися як надійний алгоритм захисту - існують утиліти, що дозволяють швидко виконати зворотне перетворення.
Опція RECOMPILE вказує на те, що при кожному виклик процедури система буде перекомпілювати текст. У звичайному випадку, скомпільована при першому запуску процедура зберігається в кеші, що дозволяє збільшити швидкодію.
EXECUTE AS визначає контекст безпеки, у якому має бути виконана процедура. Далі вказується одне із значень f CALLER | SELF | OWNER | "user_name"). CALLER є значенням за промовчанням і означає, що код буде виконуватись у контексті безпеки користувача, що викликає цей модуль. Відповідно, користувач повинен мати дозволи не тільки на сам програмований об'єкт, але і на інші об'єкти БД, що ним зачіпаються. EXECUTE AS SELF означає використання контексту користувача, який створює або змінює програмований об'єкт. OWNER вказує, що код буде виконуватись у контексті поточного власника процедури. Якщо для неї не визначено власника, то мається на увазі власник схеми, до якої вона належить. EXECUTE AS "user_name" дозволяє явно вказати ім'я користувача (в одинарних лапках).
Для процедури можуть бути вказані параметри. Це локальні змінні, що використовуються передачі значень в процедуру. Якщо параметр оголошений з ключовим словом OUTPUT (або скорочено OUT), він є вихідним: задане йому у процедурі значення після її закінчення може бути використане програмою, що викликала процедуру. Ключове слово READONLY означає, що значення параметра не може бути змінено всередині процедури, що зберігається.
Параметрам можуть бути призначені значення за промовчанням, які будуть використані, якщо під час виклику процедури значення параметра не буде вказано у явному вигляді. Розглянемо приклад:
CREATE PROC surma (@а int, @b int=0,
©result int OUTPUT) AS
SET @result=0a+0b
Ми створили процедуру з трьома параметрами, причому у параметра @b значення за замовчуванням =0, а параметр @result - вихідний: через нього повертається значення в програму, що викликала. Дія, що виконуються, досить прості – вихідний параметр отримує значення суми двох вхідних.
При роботі в SQL Server Management Studio створену процедуру, що зберігається, можна знайти в розділі програмованих об'єктів БД (Англ. Programmability) в підрозділі для процедур, що зберігаються (рис. 10.2).
При викликі процедури як вхідні параметри можна використовувати як змінні, так і константи. Розглянемо два приклади. У першому вхідні параметри процедури явно задані константами, для вихідного параметра виклику вказано ключове слово OUTPUT. У другому варіанті як перший вхідний параметр використовується значення змінної, а для другого параметра за допомогою ключового слова DEFAULT вказано, що має бути використане значення за замовчуванням:
Рис. 10.2.
DECLARE @з int;
EXEC summa 10,5, @ OUTPUT;
PRINT 0c; – буде виведено 15
DECLARE Gi int = 5;
– під час виклику використовуємо значення за замовчуванням
EXEC summa Gi, DEFAULT, 0с OUTPUT;
PRINT 0c; – буде виведено 5
Розглянемо тепер приклад із аналізом коду повернення, з яким закінчується процедура. Нехай треба підрахувати, скільки у таблиці Bookl книг, виданих у заданому діапазоні років. При цьому якщо початковий рік виявився більшим за кінцевий, процедура повертає "1" і підрахунок не проводить, інакше – рахуємо кількість книг і повертаємо 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
IF 0FirsYear>0LastYear RETURN 1
SET @result= (SELECT COUNT(*) FROM dbo.Bookl
WHERE BETWEEN 0FirsYear AND 0LastYear) ;
Розглянемо варіант виклику даної процедури, в якому код повернення зберігається в цілісній змінній 0ret, після чого аналізується його значення (в даному випадку це буде 1). Функція CAST, що використовується в операторі PRINT, служить для перетворення значення цілісної змінної Gres до рядкового типу:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret=l PRINT "Початковий рік більший за кінцевий"
PRINT "Кількість книг" + CAST(Gres as varchar(20))
Збережені процедури можуть як зчитувати дані з таблиці, а й змінювати дані і навіть створювати таблиці та інших об'єктів БД.
Однак створювати схеми, функції, тригери, процедури та уявлення зі збереженої процедури не можна.
Наступний приклад ілюструє ці можливості, і питання, пов'язані з областю видимості часових об'єктів. Наведена нижче процедура, що зберігається, перевіряє наявність тимчасової таблиці #ТаЬ2; якщо цієї таблиці немає, створює її. Після цього таблицю #ТаЬ2 заносяться значення двох стовпців, і вміст таблиці виводиться оператором SELECT:
CREATE PROC My_Procl (@id int, @name varchar(30))
IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL
INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)
SELECT * FROM dbo. #Tab2-№1
Перед першим викликом процедури, що зберігається, створимо використовується в ній тимчасову таблицю #ТаЬ2. Зверніть увагу на оператора ЄХЕС. У попередніх прикладах параметри передавалися в процедуру "по позиції", а в даному випадку використовується інший формат передачі параметрів - "на ім'я", явно вказується ім'я параметра та його значення:
CREATE TABLE dbo. # Tab2 (id int, name varchar (30));
EXEC My_Procl 0name="lvan", 0id=2;
SELECT * FROM dbo. # Tab2; -№2
У наведеному прикладі оператор SELECT відпрацює двічі: перший раз – усередині процедури, вдруге – із фрагмента коду, що викликає (відзначений коментарем "№ 2").
Перед другим викликом процедури видалимо тимчасову таблицю #ТаЬ2. Тоді однойменна тимчасова таблиця буде створена із процедури, що зберігається:
DROP TABLE dbo. # Tab2;
EXEC My_Procl 0name="Ivan", 0id=2;
SELECT * FROM dbo. # Tab2; -№2
У цьому випадку дані виведе лише оператор SELECT, що знаходиться всередині процедури (з коментарем "Ха 1"). Виконання SELECT "№ 2" призведе до помилки, так як створена в процедурі, що зберігається, тимчасова таблиця на момент повернення з процедури буде вже видалена з бази tempdb.
Видалити процедуру можна за допомогою оператора DROP PROCEDURE. Його формат наведено нижче. Одним оператором можна видалити кілька процедур, що зберігаються, перерахувавши їх через кому:
DROP (PROC I PROCEDURE) ( procedure ) [
Наприклад, видалимо раніше створену процедуру summa:
DROP PROC summa;
Внести зміни до існуючої процедури (а фактично – перевизначити її) можна за допомогою оператора ALTER PROCEDURE (додаток
стимо скорочення PROC). Крім ключового слова ALTER, формат оператора практично збігається з форматом CREATE PROCEDURE. Наприклад, змінимо процедуру dbo. rownum, встановивши їй опцію виконання у контексті безпеки власника:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner – опція, що встановлюється
IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN
SET 0result= (SELECT COUNT(*) FROM dbo.Bookl
WHERE BETWEEN SFirsYear AND SLastYear);
У деяких випадках може виникнути потреба у динамічному формуванні команди та виконанні її на сервері БД. Це завдання також може вирішуватись за допомогою оператора ЄХЕС. У наведеному нижче прикладі виконується вибірка записів з таблиці Bookl за умовою рівності атрибута Year значенню, що задається змінною:
DECLARE 0у int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE = " [email protected]) ;
Виконання динамічно сформованих інструкцій створює передумови реалізації комп'ютерних атак типу "SQL-ін'єкція" (Англ. SQL injection). Суть атаки полягає в тому, що порушник впроваджує в запит, що динамічно формується, власний код на SQL. Зазвичай це відбувається, коли параметри, що підставляються, беруть з результатів введення даних користувачем.
Дещо змінимо попередній приклад:
DECLARE 0у varchar(100);
SET 0у = "2ТОВ"; – це ми отримали від користувача
Якщо припустити, що рядкове значення, що привласнюється в операторі SET, ми отримали від користувача (неважливо яким чином, наприклад, через веб-додаток), то приклад ілюструє "штатне" поведінка нашого коду.
DECLARE 0у varchar(100);
SET 0у = "2000; DELETE FROM dbo.Book2"; – ін'єкція
EXEC ("SELECT * FROM dbo.Book2 WHERE ="+0y);
У рекомендується по можливості використовувати в подібних випадках системну процедуру sp_executcsql, що зберігається, яка дозволяє контролювати тип параметрів, що є одним з бар'єрів на шляху SQL-ін'єкцій. Не розглядаючи в подробицях її формат, розберемо приклад, аналогічний до представленого раніше:
EXECUTE sp_executesql
N"SELECT * FROM dbo.Bookl WHERE = 0y",
Тут явно вказується тип параметра, що використовується в запиті, і SQL Server при виконанні буде його контролювати. Літера "N" перед лапками вказує, що це літерна константа у форматі Unicode, як цього вимагає процедура. Параметру можна призначити як постійне значення, а й значення інший змінної.
Мета роботи– навчитися створювати та використовувати процедури, що зберігаються на сервері БД.
1. Опрацювання всіх прикладів, аналіз результатів їх виконання в утиліті SQL Server Management Studio. Перевірка наявності створених процедур у поточній БД.
2. Виконання всіх прикладів та завдань у процесі лабораторної роботи.
3. Виконання індивідуальних завдань за варіантами.
Пояснення до виконання роботи
Для освоєння програмування процедур, що зберігаються, використовуємо приклад бази даних з назвою DB_Books, яка була створена у лабораторній роботі №1. При виконанні прикладів та завдань звертайте увагу на відповідність назв БД, таблиць та інших об'єктів проекту.
Збережені процедуриявляють собою набір команд, що складається з одного або декількох операторів SQL або функцій, що зберігається в базі даних у відкомпілюваному вигляді.
Типи процедур, що зберігаються
Системні процедури, що зберігаються, призначені для виконання різних адміністративних дій. Практично всі дії з адміністрування сервера виконуються за їх допомогою. Можна сказати, що системні процедури, що зберігаються, є інтерфейсом, що забезпечує роботу з системними таблицями. Системні процедури, що зберігаються, мають префікс sp_, зберігаються в системній базі даних і можуть бути викликані в контексті будь-якої іншої бази даних.
Користувацькі процедури, що зберігаються, реалізують ті чи інші дії. Збережені процедури – повноцінний об'єкт бази даних. Внаслідок цього кожна процедура, що зберігається, розташовується в конкретній базі даних, де і виконується.
Тимчасові процедури, що зберігаються, існують лише деякий час, після чого автоматично знищуються сервером. Вони поділяються на локальні та глобальні. Локальні тимчасові процедури, що зберігаються, можуть бути викликані тільки з того з'єднання, в якому створені. Під час створення такої процедури їй необхідно дати ім'я, що починається з одного символу #. Як і всі часові об'єкти, процедури цього типу, що зберігаються, автоматично видаляються при відключенні користувача, перезапуску або зупинці сервера. Глобальні тимчасові процедури, що зберігаються, доступні для будь-яких з'єднань сервера, на якому є така ж процедура. Для її визначення достатньо дати їй ім'я, що починається із символів ##. Ці процедури видаляються при перезапуску або зупинці сервера, а також при закритті з'єднання, в контексті якого вони були створені.
Створення, зміна процедур, що зберігаються
Створення процедури, що зберігається, передбачає вирішення наступних завдань: планування прав доступу. При створенні збереженої процедури слід враховувати, що вона матиме ті ж права доступу до об'єктів бази даних, що і користувач, що її створив; визначення параметрів зберігання процедури, процедури, що зберігаються, можуть мати вхідні та вихідні параметри; розробка коду процедури, що зберігається. Код процедури може містити послідовність будь-яких команд SQL, включаючи виклик інших процедур, що зберігаються.
Синтаксис оператора створення нової або зміни наявної процедури в позначеннях MS SQL Server:
( CREATE | ALTER ) PROC[ EDURE] ім'я_процедури [ ;номер] [ ( @ім'я_параметра тип_даних ) [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE, ENC [ FOR REPLICATION] AS sql_оператор [ ... n]Розглянемо параметри цієї команди.
Використовуючи префікси sp_, #, ##, процедуру, що створюється, можна визначити як системну або тимчасову. Як видно з синтаксису команди, не допускається вказувати ім'я власника, якому належатиме створювана процедура, а також ім'я бази даних, де вона має бути розміщена. Таким чином, щоб розмістити створювану процедуру, що зберігається в конкретній базі даних, необхідно виконати команду CREATE PROCEDURE в контексті цієї бази даних. При зверненні з тіла процедури, що зберігається, до об'єктів тієї ж бази даних можна використовувати укорочені імена, тобто без вказівки імені бази даних. Коли потрібно звернутися до об'єктів, розташованих у інших базах даних, вказівка імені бази даних обов'язково.
Для передачі вхідних і вихідних даних у створюваній процедурі, що зберігається, імена параметрів повинні починатися з символу @. В одній процедурі, що зберігається, можна задати безліч параметрів, розділених комами. У тілі процедури не повинні застосовуватися локальні змінні, імена яких збігаються з іменами параметрів цієї процедури. Для визначення типу даних параметрів процедури, що зберігається, підходять будь-які типи даних SQL, включаючи певні користувачем. Однак тип даних CURSOR може бути використаний тільки як вихідний параметр процедури, що зберігається, тобто. із зазначенням ключового слова OUTPUT.
Наявність ключового слова OUTPUT означає, що відповідний параметр призначений для повернення даних із процедури, що зберігається. Однак це зовсім не означає, що параметр не підходить для передачі значень у процедуру, що зберігається. Вказівка ключового слова OUTPUT наказує серверу при виході з процедури, що зберігається, присвоїти поточне значення параметра локальної змінної, яка була вказана при виклику процедури як значення параметра. Зазначимо, що при вказівці ключового слова OUTPUT значення відповідного параметра під час виклику процедури може бути задано лише локальною змінною. Не дозволяється використовувати будь-які вирази або константи, допустимі для звичайних параметрів. Ключове слово VARYING застосовується разом із параметром OUTPUT, що має тип CURSOR. Воно визначає, що вихідним параметром буде результуюча множина.
Ключове слово DEFAULT є значенням, яке прийматиме відповідний параметр за замовчуванням. Таким чином, при виклику процедури можна вказувати явно значення відповідного параметра.
Оскільки сервер кешує план виконання запиту та компільований код, при подальшому виклику процедури використовуватимуться вже готові значення. Однак у деяких випадках все ж таки потрібно виконувати перекомпіляцію коду процедури. Вказівка ключового слова RECOMPILE наказує системі створювати план виконання процедури, що зберігається при кожному її викликі.
Параметр FOR REPLICATION затребуваний при реплікації даних і включенні створюваної процедури, що зберігається, як статті в публікацію. Ключове слово ENCRYPTION наказує серверу виконати шифрування коду процедури, що зберігається, що може забезпечити захист від використання авторських алгоритмів, що реалізують роботу процедури, що зберігається. Ключове слово AS розміщується на початку тіла, що зберігається процедури. У тілі процедури можуть застосовуватися практично всі команди SQL, оголошуватися транзакції, встановлюватися блокування та викликатися інші процедури, що зберігаються. Вихід із процедури, що зберігається, можна здійснити за допомогою команди RETURN.
Видалення процедури, що зберігається
DROP PROCEDURE ( ім'я_процедури) [ ,... n]
Виконання процедури, що зберігається
Для виконання процедури використовується команда: [ [ EXEC [ UTE] ім'я_процедури [ ;номер] [ [ @ім'я_параметра= ] ( значення | @ім'я_змінної) [ 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Перевірте результат.
Приклад створення процедури з вхідним параметром:
CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GOЗавдання 2. Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL server Management Studio. Запустіть її за допомогою команди
EXEC Count_Books_Pages 100Перевірте результат.
Приклад створення процедури з вхідними параметрами:
CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GOЗавдання 3.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL server Management Studio. Запустіть її за допомогою команди
EXEC Count_Books_Title 100 "П%"Перевірте результат.
Приклад створення процедури з вхідними параметрами та вихідним параметром:
CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @TitЗавдання 4.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL server Management Studio. Запустіть за допомогою набору команд:
Sql> Declare @q As int EXEC Count_Books_Itogo 100, "П%", @q output select @q
Перевірте результат.
Приклад створення процедури з вхідними параметрами та RETURN:
CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Пушкін А.С." RETURN 1 ELSE RETURN 2Завдання 5.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL server Management Studio. Запустіть її за допомогою команд:
DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_statusПриклад створення процедури без параметрів для збільшення значення ключового поля у таблиці Purchases у 2 рази:
CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = 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= @kЗавдання 7.
EXEC select_author "Пушкін А.С." або select_author @k= "Пушкін А.С." або EXEC select_author @k= "Пушкін А.С."Приклад створення процедури з вхідним параметром та значенням за замовчуванням для збільшення значення ключового поля в таблиці Purchases у задану кількість разів (за замовчуванням у 2 рази):
CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @pПроцедура не повертає жодних даних.
Завдання 8.Створіть цю процедуру в розділі Stored Procedures бази даних DB_Books через утиліту SQL server Management Studio. Запустіть її за допомогою команд:
EXEC update_proc 4 або EXEC update_proc @p = 4 або EXEC update_proc --застосовується значення за промовчанням.Приклад створення процедури із вхідним та вихідним параметрами. Створити процедуру визначення кількості замовлень, скоєних за вказаний период:
CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@,Завдання 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. Створити процедури, що зберігаються за допомогою операторів Create procedure, причому самостійно визначити імена процедур. Кожна процедура виконуватиме по одному SQL запиту, який був виконаний у другій лабораторній роботі. Причому код SQL запитів потрібно змінити в такий спосіб, щоб у них можна було передавати значення полів, якими здійснюється пошук.
Наприклад, вихідне завдання та запит у лабораторній роботі №2:
/*Вибрати з довідника постачальників (таблиця Deliveries) назви компаній, телефони та ІПН (поля Name_company, Phone та INN), у яких назва компанії (поле Name_company) „ВАТ СВІТ“.
SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "ВАТ СВІТ"*/ –У цій роботі буде створено процедуру:
CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp-Для запуску процедури використовується команда:
EXEC select_name_company "ВАТ СВІТ"Список завдань
В утиліті SQL Server Management Studio створити нову програму. Програмно зробити активною індивідуальну БД, створену у лабораторній роботі №1, за допомогою оператора Use. Створити процедури, що зберігаються за допомогою операторів 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. Вивести список зареєстрованих документів із зазначеним типом документа або з кодом документа у певному діапазоні.
Збережені процедури
Предметом цього розділу є один з найпотужніших інструментів, пропонованих розробникам додатків баз даних InterBase для реалізації бізнес-логіки. і зменшити кількість коду, необхідного для виконання поставлених завдань Практично будь-яка досить складна програма баз даних не обходиться без використання процедур, що зберігаються.
Крім цих широко відомих переваг використання процедур, що зберігаються, загальних для більшості реляційних СУБД, що зберігаються процедури InterBase можуть грати роль практично повноцінних наборів даних, що дозволяє використовувати повертаються ними результати в звичайних SQL-запитах.
Розробники, що часто починають, уявляють собі збережені процедури просто як набір специфічних SQL-запитів, які щось роблять усередині бази даних, причому існує думка, що працювати з процедурами, що зберігаються, набагато складніше, ніж реалізувати ту ж функціональність у клієнтському додатку, мовою високого рівня.
Так що ж таке процедури, що зберігаються в InterBase?
Процедура, що зберігається (ХП) - це частина метаданих бази даних, що являє собою відкомпільовану у внутрішнє уявлення InterBase підпрограму, написану спеціальною мовою, компілятор якої вбудований в ядро сервера InteiBase
Зберігаючу процедуру можна викликати з клієнтських програм, з тригерів та інших процедур, що зберігаються. Зберігається процедура виконується всередині серверного процесу і може маніпулювати даними в базі даних, а також повертати клієнту, що викликав її (те тригеру, ХП, додатку) результати свого виконання
Основою потужних можливостей, закладених у ХП, є процедурна мова програмування, що має у своєму складі як модифіковані пропозиції звичайного SQL, такі як INSERT, UPDATE і SELECT, так і засоби організації розгалужень і циклів (IF, WHILE), а також засоби обробки помилок і виняткових ситуацій Мова процедур, що зберігаються, дозволяє реалізувати складні алгоритми роботи з даними, а завдяки орієнтованості на роботу з реляційними даними ХП виходять значно компактніші за аналогічні процедури традиційними мовами.
Слід зазначити, що й тригерів використовується той самий мову програмування, крім низки особливостей та обмежень. Відмінності підмножини мови, що використовується в тригерах, від мови ХП докладно розглянуті у розділі "Трігери" (ч 1).
Приклад простої процедури, що зберігається
Настав час створити першу процедуру, що зберігається, і на її прикладі вивчити процес створення збережених процедур. Але для початку слід сказати кілька слів про те, як працювати з процедурами, що зберігаються Справа в тому, що своєю славою малозрозумілого і незручного інструменту ХП зобов'язані надзвичайно бідним стандартним засобам розробки і налагодження процедур, що зберігаються. У документації по InterBase рекомендується створювати процедури за допомогою файлів SQL-скриптів, що містять текст ХП, які подаються на вхід інтерпретатору isql, і таким чином проводити створення та модифікацію ХП Якщо в цьому SQL-скрипті на етапі компіляції тексту процедури в BLR (про BLR см Розділ "Структура бази даних InterBase" (ч. 4)) виникне помилка, то isql виведе повідомлення про те, на якому рядку файлу SQL-скрипта виникла ця помилка. Виправляйте помилку та повторюйте все спочатку. Про налагодження в сучасному розумінні цього слова, тобто про трасування виконання, з можливістю подивитися проміжні значення змінних, взагалі не йдеться. Очевидно, що такий підхід не сприяє зростанню привабливості процедур, що зберігаються в очах розробника.
Проте, крім стандартного мінімалістського підходу до розробки ХП<_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Синтаксис процедур, що зберігаються, описується наступним чином:
CREATE PROCEDURE name
[(param datatype [, param datatype...])]
)]
AS
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE var datatype;
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> =
(
Виглядає досить об'ємно і може бути навіть громіздко, але насправді все дуже просто Для того, щоб поступово освоїти синтаксис, давайте будемо розглядати приклади, що поступово ускладнюються.
Отже, ось приклад дуже простої процедури, що зберігається, яка приймає на вході два числа, складає їх і повертає отриманий результат:
CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result=first_arg+second_arg;
SUSPEND;
END
Як бачите, все просто: після команди CREATE PROCEDURE вказується ім'я новоствореної процедури (яке має бути унікальним у межах бази даних) - у даному випадку SP_Add, потім у дужках через кому перераховуються вхідні параметри ХП - first_arg та second_arg - із зазначенням їх типів.
Список вхідних параметрів є необов'язковою частиною оператора CREATE PROCEDURE - трапляються випадки, коли всі дані для своєї роботи процедура отримує за допомогою запитів до таблиць всередині тіла процедури.
У процедурах, що зберігаються, використовуються будь-які скалярні типи даних InteiBase He передбачено застосування масивів і типів, що визначаються користувачем, - доменів
Далі йде ключове слово RETURNS, після якого в дужках перераховуються параметри, що повертаються з зазначенням їх типів - в даному випадку тільки один - Result.
Якщо процедура не повинна повертати параметри, слово RETURNS і список параметрів, що повертаються, відсутні.
Після RETURNSQ вказано ключове слово AS. До ключового слова AS йде Заголовок,а після нього - течопроцедури.
Тіло процедури, що зберігається, являє собою перелік описів її внутрішніх (локальних) змінних (якщо вони є, докладніше розглянемо нижче), що розділяється точкою з комою (;), і блок операторів, укладений в операторні дужки BEGIN END. В даному випадку тіло ХП дуже просте - ми просимо складаємо два вхідні аргументи і привласнюємо їхній результат вихідному, а потім викликаємо команду SUSPEND. Трохи пізніше ми роз'яснимо суть дії цієї команди, а поки що відзначимо, що вона потрібна для передачі параметрів, що повертаються туди, звідки була викликана збережена процедура.
Розділювачі в процедурах, що зберігаються
Зверніть увагу, що оператор всередині процедури закінчується крапкою з комою (;). Як відомо, точка з комою є стандартним роздільником команд SQL - вона є сигналом інтерпретатору SQL, що текст команди введений повністю і треба починати його обробляти. Чи не вийде так, що, виявивши крапку з комою в середині ХП, інтерпретатор SQL визнає, що команда введена повністю і спробує виконати частину процедури, що зберігається? Це припущення не має сенсу. Справді, якщо створити файл, в який записати наведений вище приклад, додати команду з'єднання з бази даних і спробувати виконати цей SQL-скрипт за допомогою інтерпретатора isql, то буде повернена помилка, пов'язана з несподіваним, на думку інтерпретатора, закінченням команди створення процедури, що зберігається. Якщо створювати збережені процедури за допомогою файлів SQL-скриптів, без використання спеціалізованих інструментів розробника InterBase, то необхідно перед кожною командою створення ХП (те ж відносися до тригерів) міняти роздільник команд скрипту на інший символ, відмінний від точки з комою, а після тексту ХП відновлювати його. Команда isql, що змінює роздільник пропозицій SQL, виглядає так:
SET TERM
Для типового випадку створення процедури, що зберігається, це виглядає так:
SET TERM ^;
CREATE PROCEDURE some_procedure
... . .
END
^
SET TERM ;^
Виклик збереженої процедури
Але повернемося до нашої процедури, що зберігається. Тепер, коли вона створена, її треба якось викликати, передати їй параметри і отримати результати, що повертаються. Це зробити дуже просто – достатньо написати SQL-запит наступного вигляду:
SELECT *
FROM Sp_add (181.35, 23.09)
Цей запит поверне нам один рядок, що містить лише одне поле Result, в якому буде перебувати сума чисел 181.35 та 23.09, тобто 204.44.
Таким чином, нашу процедуру можна використовувати у звичайних SQL-запитах, що виконуються як у клієнтських програмах, так і в інших ХП або тригерах. Таке використання нашої процедури стало можливим через застосування команди SUSPEND в кінці процедури, що зберігається.
Справа в тому, що в InterBase (і у всіх його клонах) існують два типи процедур, що зберігаються: процедури-вибірки (selectable procedures) і виконувані процедури (executable procedures). Відмінність у роботі цих двох видів ХП полягає в тому, що процедури-вибірки зазвичай повертають безліч наборів вихідних параметрів, згрупованих рядково, які мають вигляд набору даних, а виконувані процедури можуть або взагалі не повертати параметри, або повертати тільки один набір вихідних параметрів , перерахованих в Returns, де один рядок параметрів. Процедури-вибірки викликаються у запитах SELECT, а процедури, що виконуються, - за допомогою команди EXECUTE PROCEDURE.
Обидва види процедур, що зберігаються, мають однаковий синтаксис створення і формально нічим не відрізняються, тому будь-яка можлива процедура може бути викликана в SELECT-запиті і будь-яка процедура-вибірка - за допомогою EXECUTE PROCEDURE. Питання в тому, як поведуться ХП при різних типах виклику. Іншими словами, різниця полягає у проектуванні процедури для певного типу виклику. Тобто процедура-вибірка спеціально створюється для виклику із запиту SELECT, а процедура, що виконується, - для виклику з використанням EXECUTE PROCEDURE. Давайте розглянемо, у чому полягають відмінності при проектуванні цих двох видів ХП.
Щоб зрозуміти, як працює процедура-вибірка, доведеться трохи заглибитися в теорію. Давайте уявімо простий SQL-запит типу SELECT ID, NAME FROM Table_example. В результаті його виконання ми отримуємо на виході таблицю, що складається з двох стовпців (ID та NAME) та деякої кількості рядків (рівної кількості рядків у таблиці Table_example). Повертається в результаті цього запиту таблиця називається також набором даних SQL. . Далі сервер зчитує кожен запис, що відповідає результатам запиту, вибирає з неї потрібні поля (у разі це ID і NAME) і відсилає їх клієнту. Потім процес повторюється знову - і так для кожного відібраного запису.
Все це відступ потрібно для того, щоб шановний читач зрозумів, що всі набори даних SQL формуються рядково, у тому числі і в процедурах, що зберігаються! І основна відмінність процедур-вибірок від процедур, що виконуються в тому, що перші спроектовані для повернення безлічі рядків, а другі - тільки для одного. Тому вони застосовуються по-різному: процедура-вибірка викликається за допомогою команди SELECT, яка "вимагає" від процедури віддати всі записи, які вона може повернути. Процедура, що виконується, викликається за допомогою EXECUTE PROCEDURE, яка "виймає" з ХП тільки один рядок, а решта (навіть якщо вони є!) ігнорує.
Давайте розглянемо приклад процедури-вибірки, щоб було зрозуміліше. Для > прощення створимо збережену процедуру, яка працює так само, як запит SELECT ID, NAME FROM Table_Example, тобто вона робить вибірку полів ID і NAME з усієї таблиці. Ось цей приклад:
CREATE PROCEDURE Simple_Select_SP
RETURNS (
procID INTEGER,
procNAME VARCHAR(80))
AS
BEGIN
FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN
SUSPEND;
END
END
Розберемо дії цієї процедури, названої Simple_Select_SP. Як бачите, вона не має вхідних параметрів і має два вихідні параметри – ID та NAME. Найцікавіше, звичайно, полягає в тілі процедури. Тут використано конструкцію FOR SELECT:
FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN
/*щось робимо зі змінними procID та procName*/
END
Цей шматочок коду означає наступне: для кожного рядка, вибраного з таблиці Table_example, помістити вибрані значення змінні procID і procName, а потім зробити якісь дії з цими змінними.
Ви можете зробити здивоване обличчя і запитати: "Змінні? Які ще змінні 9" Це щось подібне до сюрпризу цього розділу - те, що в процедурах, що зберігаються, ми можемо використовувати змінні. У мові ХП можна оголошувати як власні локальні змінні всередині процедури, і використовувати вхідні і вихідні параметри як змінних.
Для того щоб оголосити локальну змінну в процедурі, що зберігається, необхідно помістити її опис після ключового слова AS і до першого слова BEGIN Опис локальної змінної виглядає так:
DECLARE VARIABLE
Наприклад, щоб оголосити цілісну локальну змінну Mylnt, потрібно вставити між AS і BEGIN наступний опис
DECLARE VARIABLE Mylnt INTEGER;
Змінні в нашому прикладі починаються з двокрапки. Це зроблено тому, що звернення до них йде всередині SQL-команди FOR SELECT, тому для розрізнення полів у таблицях, що використовуються в SELECT, і змінних необхідно передувати останнім двокрапкам. Адже змінні можуть мати таку саму назву, як і поля в таблицях!
Але двокрапка перед ім'ям змінної необхідно використовувати лише всередині SQL-запитів. Поза текстами звернення до змінної робиться без двокрапки, наприклад:
procName="Some name";
Але повернемось до тіла нашої процедури. Пропозиція FOR SELECT повертає дані над вигляді таблиці - набору даних, а, по одному рядку. Кожне поле, що повертається, має бути поміщене в свою змінну: ID => procID, NAME => procName. У частині DO ці змінні посилаються клієнту, що викликав процед>р>, за допомогою команди SUSPEND
Таким чином, команда FOR SELECT... DO організує цикл із записів, що вибираються в частині SELECT цієї команди. У тілі циклу, який утворюється частиною DO, виконується передача чергового сформованого запису клієнту за допомогою команди SUSPEND.
Отже, процедура-вибірка призначена для повернення одного або більше рядків, для чого всередині тіла ХП організується цикл, що заповнює результуючі параметри-змінні. І наприкінці тіла цього циклу обов'язково стоїть команда SUSPEND, яка поверне черговий рядок даних клієнту.
Цикли та оператори розгалуження
Крім команди FOR SELECT... DO, що організує цикл із записами будь-якої вибірки, існує інший вид циклу - WHILE...DO, який дозволяє організувати цикл на основі перевірки будь-яких умов. Ось приклад ХП, що використовує цикл WHILE. DO. Ця процедура повертає квадрати цілих чисел від 0 до 99:
CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I = 1;
WHILE (i<100) DO
BEGIN
QUADRAT = I * I;
I=I+1;
SUSPEND;
END
END
В результаті виконання запиту SELECT FROM QUAD ми отримаємо таблицю, що містить один стовпець QUADRAT, у якому будуть квадрати цілих чисел від 1 до 99
Крім перебору результатів SQL-вибірки і класичного циклу, в мові процедур, що зберігаються, використовується оператор IF...THEN..ELSE, що дозволяє організувати розгалуження в залежності від виконання будь-яких \слів Його синтаксис схожий на більшість операторів розгалуження в мовах програмування високого рівня, на кшталт Паскаля та Сі.
Давайте розглянемо складніший приклад процедури, що зберігається, яка робить наступне.
- Обчислює середню ціну в таблиці Table_example (див. розділ "Таблиці Первинні ключі та генератори")
- Далі для кожного запису в таблиці робить наступну перевірку, якщо існуюча ціна (PRICE) більша за середню ціну, то встановлює ціну, рівну величині середньої ціни, плюс задається фіксований відсоток
- Якщо існуюча ціна менша або дорівнює середній ціні, то встановлює ціну, рівну колишній ціні, плюс половина різниці між колишньою та середньою ціною.
- Повертає всі змінені рядки у таблиці.
Для початку визначимо ім'я ХП, а також вхідні та вихідні параметри Все це прописується в заголовку процедури, що зберігається
CREATE PROCEDURE IncreasePrices (
Percent2lncrease DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECISION) AS
Процедура буде називатися IncreasePrices, у неї один вхідний параметр Peiceni21nciease, що має тип DOUBLE PRECISION, і 3 вихідні параметри - ID, NAME і new_pnce. Зверніть увагу, що перші два вихідні параметри мають такі ж імена, як і поля в таблиці Table_example, з якою ми збираємося працювати. Це допускається правилами мови процедур, що зберігаються.
Тепер ми повинні оголосити локальну змінну, яка буде використовуватися для зберігання середнього значення Його оголошення буде виглядати так:
DECLARE VARIABLE avg_price DOUBLE PRECISION;
Тепер перейдемо до тіла процедури, що зберігається Відкриємо тіло ХП
Ключове слово BEGIN.
Спочатку нам необхідно виконати перший крок нашого алгоритму – обчислити середню ціну. Для цього ми скористаємося запитом такого виду:
SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price,-
Цей запит використовує агрегатну функцію AVG, яка повертає середнє значення поля PRICE_1 серед відібраних рядків запиту – у нашому випадку середнє значення PRICE_1 по всій таблиці Table_example. Значення, що повертається запитом, поміщається в змінну avg_price. Зверніть увагу, що змінна avg_pnce передує двокрапкою для того, щоб відрізнити її від полів, що використовуються в запиті.
Особливістю даного запиту є те, що він завжди повертає строго один-єдиний запис. Такі запити називаються singleton-запитами І лише такі вибірки можна використовувати в процедурах, що зберігаються. Якщо запит повертає більше одного рядка, то його необхідно оформити у вигляді конструкції FOR SELECT...DO, яка організує цикл для обробки кожного рядка, що повертається.
Отже, ми набули середнього значення ціни. Тепер необхідно пройтись по всій таблиці, порівняти значення ціни в кожному запису із середньою ціною та вжити відповідних дій
З початку організуємо перебір кожного запису з таблиці Table_example
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_тут оОрсшатиьаем кожен запис*/
END
При виконанні цієї конструкції з таблиці Table_example рядково будуть вийматися дані та значення полів у кожному рядку будуть надані змінним ID, NAME та new_pnce. Ви, звичайно, пам'ятаєте, що ці змінні оголошені як вихідні параметри, але турбуватися, що вибрані дані будуть повернені як результати, не варто: той факт, що вихідним параметрам що-небудь присвоєно, не означає, що клієнт ХП клієнт негайно отримає ці значення ! Передача параметрів здійснюється тільки при виконанні команди SUSPEND, а до цього ми можемо використовувати вихідні параметри як звичайні змінні - у нашому прикладі ми саме так і робимо з параметром new_price.
Отже, всередині тіла циклу BEGIN.. END ми можемо обробити значення кожного рядка. Як ви пам'ятаєте, нам необхідно з'ясувати, як існуюча ціна співвідноситься із середньою, і вжити відповідних дій. Цю процедуру порівняння ми реалізуємо за допомогою оператора IF:
IF (new_price > avg_price) THEN /*якщо існуюча ціна більша за середню ціну*/
BEGIN
/*то встановимо нову ціну, рівну величині середньої ціни, плюс фіксований відсоток */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
ELSE
BEGIN
/* Якщо існуюча ціна менша або дорівнює середній ціні, то встановимо ціну, рівну колишній ціні, плюс половина різниці між колишньою та середньою ціною */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
END
Як бачите, вийшло досить велика конструкція IF, в якій важко було б розібратися, якби не коментарі, які містяться в символи /**/.
Для того, щоб змінити ціну відповідно до обчисленої різниці, ми скористаємося оператором UPDATE, який дозволяє модифікувати існуючі записи - один або кілька. Для того щоб однозначно вказати, в якому запису потрібно змінювати ціну, ми використовуємо за умови WHERE поле первинного ключа, порівнюючи його зі значенням змінної, в якій зберігається значення ID для поточного запису: ID=:ID. Зверніть увагу, що змінна ID передує двокрапкою.
Після виконання конструкції IF...THEN...ELSE у змінних ID, NAME та new_price знаходяться дані, які ми повинні повернути клієнт\, що викликав процедуру. Для цього після IF необхідно вставити команду SUSPEND, яка перешле дані туди, звідки викликали ХП. SELECT...DO не перебере всіх записів свого запиту.
Потрібно зазначити, що крім команди SUSPEND, яка тільки зупиняє дію процедури, що зберігається, існує команда EXIT, яка припиняє збережену процедуру після передачі рядка. Однак командою EXIT користуються досить рідко, оскільки вона потрібна в основному для того, щоб перервати цикл при досягненні будь-якої умови.
При цьому у випадку, коли процедура викликалася оператором SELECT і завершена за EXIT, останній витягнутий рядок не буде повернено. Тобто, якщо вам потрібно перервати процедуру і все-таки отримати цей рядок, треба скористатися послідовністю
SUSPEND;
EXIT;
Основне призначення EXIT – отримання singleton-наборів даних, що повертаються параметрів шляхом виклику через EXECUTE PROCEDURE. У цьому випадку встановлюються значення вихідних параметрів, але не формується набір даних SQL, і виконання процедури завершується.
Давайте запишемо текст нашої процедури повністю, щоб мати можливість охопити її логіку одним поглядом:
CREATE PROCEDURE IncreasePrices (
Percent2Increase DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(80),
new_price DOUBLE PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
BEGIN
SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price;
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*тут обробляємо кожен запис*/
IF (new_pnce > avg_price) THEN /*якщо існуюча ціна більша за середню ціну*/
BEGIN
/*встановимо нову ціну, рівну величині середньої ціни, плюс фіксований відсоток */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
ELSE
BEGIN
/* Якщо існуюча ціна менша або дорівнює середній ціні, то встановлює ціну, рівну колишній ціні, плюс половина різниці між колишньою та середньою ціною */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
SUSPEND;
END
END
Даний приклад процедури, що зберігається, ілюструє застосування основних конструкцій мови збережених процедур і тригерів. Далі ми розглянемо способи застосування процедур, що зберігаються для вирішення деяких часто виникаючих завдань.
Рекурсивні процедури, що зберігаються
Процедури InterBase, що зберігаються, можуть бути рекурсивними. Це означає, що з процедури, що зберігається, можна викликати саму себе. Допускається до 1000 рівнів вкладеності процедур, що зберігаються, однак треба пам'ятати про те, що вільні ресурси на сервері можуть закінчитися раніше, ніж буде досягнуто максимальної вкладеності ХП.
Одне з поширених застосувань процедур, що зберігаються - це обробка деревоподібних структур, що зберігаються в базі даних. Дерева часто використовуються в завданнях складу виробу, складських, кадрових та інших поширених додатках.
Давайте розглянемо приклад процедури, що зберігається, яка вибирає всі товари певного типу, починаючи з певного рівня вкладеності.
Нехай ми маємо наступну постановку завдання: маємо довідник товарів з ієрархічною структурою такого виду:
Товари
- Побутова техніка
- Холодильники
- Трикамерні
- Двокамерні
- Однокамерні
- Пральні машини
- Вертикальні
- фронтальні
- Класичні
- Вузькі
- Комп'ютерна техніка
....
Ця структура довідника категорій товарів може мати гілки різної глибини. а також наростати з часом. Наше завдання – забезпечити вибірку всіх кінцевих елементів із довідника з "розгортання повного імені", починаючи з будь-якого вузла. Наприклад, якщо ми вибираємо вузол "Пральні машини", то нам треба отримати такі категорії:
Пральні машини - Вертикальні
Пральні машини - Фронтальні Класичні
Пральні машини - Вузькі фронтальні
Визначимо структуру таблиць для зберігання інформації довідника товарів. Використовуємо спрощену схему для організації дерева в одній таблиці:
CREATE TABLE GoodsTree
(ID_GOOD INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
constraint pkGooci primary key (ID_GOOD));
Створюємо одну таблицю GoodsTree, в якій всього 3 поля: ID_GOOD - розумний ідентифікатор категорії, ID_PARENT_GOOD - ідентифікатор кшс горії-батька для даної категорії та GOOD_NAME - найменування катсш-рії. Щоб забезпечити цілісність даних у цій таблиці, накладемо на цю таблицю обмеження зовнішнього ключа:
ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID__GOOD)
Таблиця посилається сама на себе і цей зовнішній ключ стежить за тим. щоб у таблиці був посилань на неіснуючих батьків, і навіть перешкоджає спробам видалити категорії товарів, які мають нащадки.
Давайте занесемо до нашої таблиці наступні дані:
| ID_GOOD 1
|
ID_PARENT_GOOD 0
|
GOOD_NAME GOODS |
Тепер, коли у нас є місце для зберігання даних, ми можемо приступити до створення зберігання процедури, що виконує виведення всіх "остаточних" категорій товарів у "розгорнутому" вигляді - наприклад, для категорії "Трикамерні" повне ім'я категорії буде виглядати як "Побутова техніка Холодильники Трикамерні".
У процедурах, що зберігаються, деревоподібні структури, склалася своя термінологія. Кожен елемент дерева називають вузлом; а відносини між вузлами, що посилаються один на одного, називається відносинами батько-нащадок. Вузли, що знаходяться на самому кінці дерева і не мають нащадків, називаються "листами".
У каші процедури, що зберігається, вхідним параметром буде ідентифікатор категорії, починаючи з якого ми повинні будемо почати розгортку. Збережена процедура матиме такий вигляд:
CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGIN
/*0рганізуємо зовнішній цикл FOR SELECT за безпосередніми нащадками товару з ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
BEGIN
/"Перевірка за допомогою функції EXISTS, яка повертає TRUE, якщо запит у дужках поверне хоча б один рядок. Якщо у знайденого вузла ID_PARENT_GOOD = ID_CHILD_GOOD немає нащадків, то він є "листом" дерева і потрапляє в результати */
IF (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
THEN
BEGIN
/* Передаємо "аркуш" дерева в результати */
SUSPEND;
END
ELSE
/* Для вузлів, які мають нащадки*/
BEGIN
/*зберігаємо ім'я вузла-батька у тимчасовій змінній */
CURR_CHILD_NAME=full_goods_name;
/* рекурсивно запускаємо цю процедуру */
FOR
SELECT ID_CHILD_GOOD, full_goods_name
FROM GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
DO BEGIN
/*додаємо для вузла-батька до знайденого., імені нащадка за допомогою операції конкатенації рядків || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSPEND; /* повертаємо повне ім'я товару*/
END
END
END
END
Якщо ми виконаємо цю процедуру з вхідним параметром ID_GOOD2SHOW=1, то отримаємо таке:
Як бачите, за допомогою рекурсивної процедури, що зберігається, ми пройшлися по всьому дереву категорій і вивели повне найменування категорій-"листя", які знаходяться на самих кінчиках гілок.
Висновок
На цьому закінчимо розгляд основних можливостей мови процедур, що зберігаються. Очевидно, що повністю освоїти розробку процедур, що зберігаються при читанні одного розділу неможливо, проте тут ми постаралися уявити і пояснити основні концепції, пов'язані зі збереженими процедурами. Описані конструкції та прийоми проектування ХП можуть бути застосовані у більшості додатків баз даних
Частина важливих питань, пов'язаних з розробкою процедур, що зберігаються, буде розкрита в наступному розділі - "Розширені можливості мови збережених процедур InterBase", яка присвячена обробці винятків, вирішенню помилкових ситуацій у процедурах, що зберігаються, і роботі з масивами.
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-ін'єкції.
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, яке виводиться з деякої нетривіальної логіки. Для цього покоління я створюю процедуру, що зберігається, яка потім запускається тригером щоразу, коли оновлюється рядок (або інші вихідні дані).
Схоже, що це дещо поширене непорозуміння, що рівень доступу до даних – це база даних, і все, що стосується доступу до даних та даних, відбувається саме там. Це просто неправильно, але я бачу багато проектів, які випливають із цієї ідеї. Можливо це локальний феномонон.
Я можу просто відключити ідею SP, побачивши так багато погано спроектованих. Наприклад, в одному проекті, в якому я брав участь, використовувався набір CRUD для кожної таблиці і кожен можливий запит, з яким вони зіткнулися. При цьому вони просто додали ще один безглуздий шар. Боляче навіть думати про такі речі.
Оголошення процедури
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 рекомендує застосовувати наступну апроксимацію для оцінки розміру КЕШу процедур, що зберігаються:
=(максимальна кількість одночасно працюючих користувачів)*(розмір найбільшого плану виконання)*1.25. Визначення розміру плану виконання сторінок можна зробити за допомогою команди: DBCC MEMUSAGE.
Виклик процедури
У багатьох існуючих СУБД виклик процедур, що зберігаються, виконується за допомогою оператора:
EXECUTE PROCEDURE [(][)]
Примітка: Виклик збережених процедур може бути зроблений з програми, іншої процедури, що зберігається, або в інтерактивному режимі.
Приклад оголошення процедури
CREATE PROCEDURE Proc1 AS // оголошуємо процедуру
DECLARE Cur1 200 // оголошуємо курсор
OPEN Cur1 // відкриваємо курсор
FETCH NEXT FROM Cur1 //зчитуємо дані з курсору
WHILE @@Fetch_Status=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) мають певний набір вбудованих системних процедур, які можна використовувати в своїх цілях.