Lab_04_Oracle %28PL_SQL%29 / Додатково_Теорія_02_Процедури_функції_мови_PL_SQL
.pdf2.Процедури й функції мови PL/SQL.
2.1.Теоретичні відомості
Реалізація всякої розвиненої мови програмування припускає можливість створення й підтримки процедур і функції. У цій роботі розглядаються можливості мови PL/SQL і її засобів, призначених для створення ефективних програм доступу до баз даних за допомогою збережених процедур і функцій.
Недоліком виконання файлів сценаріїв SQL у розподіленому обчислювальному середовищі є те, що необхідно зберігати актуальну версію цих сценаріїв на великій кількості комп'ютерів. Програмне забезпечення набагато простіше зберігати в базі даних (в одному місці), щоб до нього міг звернутися кожний. Для цього використовуються збережені процедури (функції й пакети).
Процедури й функції PL/SQL дуже схожі на процедури й функції інших мов третього покоління й мають багато їхніх властивостей.
Особливістю Oracle є те, що процедури й функції є об'єктами бази даних. Це означає, що їхні описи зберігаються в словнику даних, а властне код зберігається не в бібліотеках файлової системи, як це прийнято в мовах програмування, а безпосередньо в базі даних.
Процедури й функції – це іменовані програми PL/SQL, які можуть використовувати параметри й викликатися додатком. У базі даних Oracle можна зберігати як об'єкти схем підпрограми двох типів:
збережені процедури – це підпрограми PL/SQL, що виконують різні операції;
збережені функції – це підпрограми PL/SQL, що обчислюють значення
йповертають їх у викликаюче середовище.
Уцій лабораторній роботі втримується досить докладний огляд можливостей PL/SQL, що дозволяє підготуватися до процесу програмування системи Oracle Server, будувати різні програмні одиниці для доступу до баз даних.
Для створення, зміни й видалення в базі даних Oracle збереженої процедури використовується команда CREATE PROCEDURE, ALTER
PROCEDURE і DROP PROCEDURE відповідно.
2.1.1. Команда CREATE PROCEDURE
Призначення. Створює незалежну збережену процедуру. ПРОЦЕДУРА – це сукупність команд PL/SQL, яку можна викликати за ім’ям.
Умови. Щоб створити процедуру у своїй власній схемі, треба мати системний привілей CREATE PROCEDURE. Щоб створити процедуру в схемі іншого користувача, ви повинні мати системний привілей CREATE
ANY PROCEDURE.
Синтаксис.
CREATE PROCEDURE команда ::=
де OR REPLACE заміняє процедуру, якщо вона вже існує. Користувачі, яким раніше були призначені привілеї щодо обумовленої процедури, можуть, як і раніше, звертатися до цієї процедури без повторного призначення привілеїв;
схема – це така схема, що містить процедуру. Якщо цей параметр опущений, ORACLE вважає, що процедура перебуває у вашій схемі;
процедура – ім'я створюваної процедури; аргумент – ім'я аргументу процедури. Якщо дана процедура не
приймає аргументів, то ви можете опустити круглі дужки за ім'ям процедури;
IN указує, що ви зобов'язані надавати значення для аргументу при виклику процедури;
OUT указує, що процедура передає значення цього аргументу назад у визиваюче середовище після свого виконання;
IN OUT указує, що ви зобов'язані надавати значення для аргументу при виклику процедури і що процедура передає значення цього аргументу
назад у викликаюче середовище після свого виконання. Якщо ви опускаєте опції IN, OUT і IN OUT, то за замовчуванням приймається IN;
тип_данных – тип даних аргументу. Аргумент може мати будь-який тип даних, що підтримується в PL/SQL. Тип даних тут не може специфікувати довжину, точність або масштаб. ORACLE обчислює довжину, точність і масштаб аргументу з середовища, що викликало процедуру;
тело_подпрограммы_pl/sql – визначення процедури. Визначення процедур пишуться на PL/SQL.
Зауваження стосовно використання. Процедура – це сукупність пропозицій PL/SQL, які ви можете викликати за ім'ям. Збережені процедури в багатьох аспектах аналогічні збереженим функціям.
Як і інші оператори CREATE, створення процедури є операцією DDL, тому до й після створення процедури неявно виконуються оператори COMMIT. При цьому можна використовувати еквівалентні один одному ключові слова IS і AS.
У PL/SQL ви можете групувати множинні оператори SQL разом із процедурними операторами PL/SQL, аналогічними тим, які існують у мовах програмування – таких, як Ada або C. За допомогою команди CREATE PROCEDURE ви можете створити процедуру й зберегти її в базі даних. Потім ви можете викликати збережену процедуру з будь-якого середовища, з якого ви можете видавати пропозиції SQL.
Збережені процедури пропонують вам переваги в наступних областях: розробці, цілісності, захисту, продуктивності, розподілі пам'яті.
Команда CREATE PROCEDURE створює процедуру як незалежний об'єкт схеми. Крім цього, ви також можете створити процедуру як частину пакета.
Наприклад, створити процедуру CREDIT у схемі SAM, що кредитує зазначений банківський рахунок заданою сумою.
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER)
AS BEGIN
UPDATE accounts
SET balance = balance + amount WHERE account_id = acc_no;
END;
При виклику цієї процедури ви повинні специфікувати наступні аргументи:
ACC_NO – цей аргумент представляє номер банківського рахунку, який потрібно кредитувати;
AMOUNT – цей аргумент задає величину кредиту. Обидва аргументи мають тип даних NUMBER.
Процедура використовує пропозицію UPDATE, щоб збільшити значення стовпця BALANCE таблиці ACCOUNTS на значення аргументу AMOUNT для рахунку,що ідентифікується аргументом ACC_NO.
2.1.2. Команда ALTER PROCEDURE
Призначення. Перекомпілювати незалежну збережену процедуру. Умови. Процедура повинна бути у вашій схемі, або ви повинні мати
системний привілей ALTER ANY PROCEDURE.
Синтаксис.
ALTER PROCEDURE команда ::=
де схема – це така схема, що містить процедуру. Якщо цей параметр опущений, ORACLE вважає, що процедура перебуває у вашій схемі;
процедура – ім'я процедури, котра перекомпілюється;
COMPILE змушує ORACLE перекомпілювати процедуру. Ключове слово COMPILE обов'язкове.
Команди ALTER PROCEDURE і ALTER FUNCTION зовсім ідентичні.
Наступне обговорення явної перекомпіляції процедур застосовне також і до функцій.
Ви можете використовувати команду ALTER PROCEDURE для явної перекомпіляції недійсної процедури. Явна перекомпіляція усуває необхідність у неявній перекомпіляції часу виконання й запобігає пов'язаним із цим помилкам компіляції й накладним витратам.
Коли ви видаєте пропозицію ALTER PROCEDURE, ORACLE перекомпілює процедуру незалежно від того, дійсна вона чи ні.
Ви можете використовувати команду ALTER PROCEDURE для перекомпіляції лише незалежної процедури. Коли ви перекомпілюєте
процедуру, ORACLE спочатку перекомпілює об'єкти, від яких залежить ця процедура, якщо ці об'єкти недійсні. ORACLE також робить недійсними всі локальні об'єкти, що залежать від цієї процедури, – наприклад, процедури, які викликають її, або тіла пакетів, у яких визначені процедури, що викликають перекомпільовану процедуру. Якщо перекомпіляція процедури проходить успішно, процедура стає дійсною. Якщо при перекомпіляції процедури зустрічаються помилки, то ORACLE повертає помилку, і процедура залишається недійсною. Ви можете потім виконати налагодження процедури за допомогою визначеного пакета
DBMS_OUTPUT.
Наприклад, наступний оператор явно перекомпілює процедуру CLOSE_ACCT, що належить користувачеві HENRY:
ALTER PROCEDURE henry.close_acct
COMPILE
Якщо ORACLE успішно перекомпілює процедуру CLOSE_ACCT, то ця процедура стає дійсною, і її можна згодом виконувати без пере-компіляції під час виконання. Якщо перекомпіляція CLOSE_ACCT приводить до помилок компіляції, то ORACLE повертає повідомлення про помилку, а процедура залишається недійсною.
ORACLE також робить недійсними всі об'єкти, що залежать від CLOSE_ACCT. Якщо ви згодом звернетеся до одного з таких об'єктів без його явної попередньої перекомпіляції, то ORACLE неявно перекомпілює його під час виконання.
2.1.3. Команда DROP PROCEDURE
Призначення. Видаляє незалежну збережену процедуру з бази даних. Умови. Процедура повинна бути у вашій схемі, або ви повинні мати
системний привілей DROP ANY PROCEDURE.
Синтаксис.
де схема – це схема, що містить процедуру. Якщо СХЕМА опущена, то ORACLE вважає, що процедура перебуває у вашій схемі;
процедура – ім'я процедури, що видаляється.
Коли ви видаляєте процедуру, ORACLE робить недійсними всі локальні об'єкти, які залежать від цієї процедури (тобто викликають її). Якщо ви згодом звертаєтеся до одного з таких об'єктів, то ORACLE намагається перекомпілювати його і повертає помилку, якщо виявиться, що ви ще не перетворили вилучену процедуру.
Ви можете за допомогою цієї команди видалити тільки незалежну процедуру. Наприклад, оператор, що випливає, видаляє процедуру TRANSFER, яка належить користувачеві KERNER:
DROP PROCEDURE kerner.transfer;
Коли ви видаляєте процедуру TRANSFER, ORACLE робить недійсними всі об'єкти, які залежать від цієї процедури.
2.1.4. Подання команд створення, зміни й видалення процедур у форматі BNF
Команда для створення (CREATE) або заміни (OR REPLACE) незалежної (не вхідної до складу пакета) процедури має вигляд:
CREATE [OR REPLACE] PROCEDURE [schema.]name [ (argument [ IN | OUT | IN OUT] datatype
[, argument [ IN | OUT | IN OUT] datatype ] ...) ] IS pl/sql_subprogram_body
END [name];
де schema – необов'язковий параметр, що ідентифікує схему, в якій повинна бути створена процедура;
name – ім'я процедури, формоване за загальними правилами; argument – ім'я параметра, формоване за тими ж правилами, що й ім'я
процедури;
IN | OUT | IN OUT використовуються для позначення параметрів, значення яких:
(IN) передаються в процедуру й не можуть бути в ній змінені (установлюється за замовчуванням);
(OUT) повинні бути повернуті з процедури (такі значення не можна привласнювати іншим змінним або перепривласнювати);
(IN OUT) можуть передаватися в процедуру, вертатися з неї й використовуватися як звичайні змінні усередині процедури;
datatype – тип даних значень параметра (типи даних PL/SQL );
pl/sql_subprogram_body – тіло процедури.
Для видалення опису процедури і її перекомпіляцій використовуються команди:
DROP PROCEDURE [schema.]name ;
й
ALTER PROCEDURE [schema.]name COMPILE ;
Створимо збережену процедуру, яка виконує тої ж дії, що й анонімний
блок.
Текст її тіла відрізняється від тексту тіла анонімного блоку лише відсутністю звертання до утиліти DBMS_OUTPUT.PUT_LINE, використовуваної для виводу результату:
CREATE OR REPLACE PROCEDURE p_fio ---- ім'я процедури
(tekst VARCHAR2,
-- вхідний параметр (опущений умовчується мода IN)
vychod IN OUT VARCHAR2) -- параметр із модою IN OUT дозво-ляє передавати в процедуру початкове значення й повертати оновлене
-- значення підпрограмі, що викликає дану процедуру
IS
--- опис локальних змінних, використовуваних у тілі процедури kol INTEGER;
BEGIN
--тіло процедури, що відрізняються від
--тексту тіла анонімного блоку
--лише відсутністю в його останньому рядку команди
DBMS_OUTPUT.PUT_LINE(vychod)
. . .
END p_fio;
/
Також, як і текст анонімного блоку, текст команди щодо створення процедури можна ввести за допомогою текстового редактора у файл (наприклад, з ім'ям p_fio.sql) і виконати в SQL*Plus команду @p_fio.sql або вводити рядок за рядком прямо в SQL*Plus.
Після створення процедури p_fio її можна викликати з іншої процедури або анонімного блоку:
DECLARE
vchod VARCHAR2(20) := 'Жан - Жак Руссо'; rezult VARCHAR2(20);
BEGIN
p_fio (vchod, rezult); dbms_output.put_line(rezult);
END;
/
Звертання до процедури здійснюється з використанням позиційної (як це показано вище) або іменної нотації:
p_fio (vychod => rezult, tekst => vchod);
При звертанні до процедури як фактичні параметри можна використовувати будь-які вирази й, природно, їх складові, наприклад, константи:
p_fio ('Жан - Жак Руссо', rezult);
або
p_fio ('Жан'||' - Жак'||' Руссо', rezult);
2.1.5. Робота з функціями
Для створення, зміни й видалення в базі даних Oracle збереженої функції використовується команда CREATE FUNCTION, ALTER FUNCTION і DROP FUNCTION відповідно. Синтаксичні діаграми й опис параметрів команди наступні.
CREATE FUNCTION
Призначення. Створюємо незалежну збережену функцію. ЗБЕРЕЖЕНА ФУНКЦІЯ – це сукупність пропозицій PL/SQL, що ви можете викликати за ім'ям. Збережені функції аналогічні процедурам з тією різницею, що функція повертає значення в те середовище, з якого вона викликана.
Умови. Щоб створити функцію у своїй власній схемі, ви повинні мати системний привілей CREATE PROCEDURE. Щоб створити функцію в схемі іншого користувача, ви повинні мати системний привілей CREATE ANY
PROCEDURE.
Синтаксис.
CREATE FUNCTION команда ::=
де OR REPLACE заміняє функцію, якщо вона вже існує. Ви можете використовувати цю опцію, щоб замінити визначення існуючої функції без того, щоб видаляти її, знову створювати й заново призначати всі об'єктні привілеї, які раніше були призначені за цією функцією. Коли ви перевизначаєте функцію, ORACLE перекомпілює її. Користувачі, яким раніше були призначені привілеї щодо переобумовленої функції, можуть, як і раніше, звертатися до цієї функції без повторного призначення привілеїв;
схема – це схема, що містить функцію. Якщо цей параметр опущений, ORACLE вважає, що функція перебуває у вашій схемі;
функция – ім'я створюваної функції; аргумент – аргумент функції. Якщо дана функція не приймає
аргументів, то ви можете опустити круглі дужки за ім'ям функції (у версіях більше ORACLE можна використовувати порожні круглі дужки «()»;
IN показує, що ви зобов'язані надавати значення для аргументу при виклику функції. Це завжди справедливо для аргументів функцій, так що це ключове слово не обов'язкове;
тип_даных – тип даних аргументу. Аргумент може мати будь-який тип даних, що підтримується в PL/SQL.Тип даних тут не може специфицировать довжину, точність або масштаб. ORACLE обчислює довжину, точність і масштаб аргументу з середовища, що викликало функцію;
RETURN специфікує тип даних значення, що повертається функцією. Оскільки кожна функція повинна повертати значення, ця фраза обов'язкова. Значення, яке повертається, може мати будь-який тип даних, що підтримується в PL/SQL;
тело_подпрограмы_pl/sql – визначення функції. Визначення функцій пишуться на PL/SQL.
ФУНКЦИЯ – це сукупність пропозицій PL/SQL, які ви можете викликати за ім'ям. Збережені функції аналогічні процедурам, з тією різницею, що функція повертає значення в те середовище, з якого вона викликана. Команда CREATE FUNCTION створює функцію як незалежний об'єкт схеми. Крім цього, ви також можете створити функцію як частину пакета.
Наприклад, оператор створює функцію GET_BAL:
CREATE FUNCTION get_bal ( acc_no IN NUMBER ) RETURN NUMBER
IS
acc_bal NUMBER(11,2); BEGIN
SELECT balance INTO acct_bal FROM accounts
WHERE account_id = acc_no; RETURN ( acct_bal );
END
Функція GET_BAL повертає значення балансу для заданого особового рахунку.
При виклику цієї функції ви повинні специфікувати аргумент ACC_NO, номер рахунку, для якого відшукується баланс. Тип даних аргументу повинен бути NUMBER.