Oracle Збережені процедури та функції PL/SQL із прикладами

Що таке підпрограми PL/SQL?

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

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

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

Термінології в підпрограмах PL/SQL

Перш ніж вивчати підпрограми PL/SQL, ми обговоримо різні терміни, що входять до складу цих підпрограм.

Параметр

Параметр — це змінна або тимчасовий символ будь-якої дійсної Тип даних PL/SQL через який підпрограма PL/SQL обмінюється значеннями з основним кодом. Цей параметр дозволяє вводити дані в підпрограми та extracвилучення цінностей з них.

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

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

1. Параметр IN
2. Параметр OUT
3. Параметр IN OUT

Параметр IN

• Використовується для введення даних у підпрограми.
• Це змінна лише для читання всередині підпрограм; її значення не можна змінити всередині підпрограми.
• У викликаючому операторі це може бути змінна, літерал або вираз, наприклад, «5*8» або «a/b».
• За замовчуванням параметри мають тип IN.

Параметр OUT

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

Параметр IN OUT

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

Тип параметра слід згадати під час створення підпрограм.

ПОВЕРНЕННЯ

RETURN – це ключове слово, яке вказує компілятору переключити керування з підпрограми на викликаючу оператор. У підпрограмі RETURN просто означає, що керування має вийти з підпрограми; як тільки контролер знаходить RETURN, код після нього пропускається.

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

Що таке процедура в PL/SQL?

A Процедура У PL/SQL процедура — це підпрограма, що складається з групи операторів PL/SQL, які можна викликати за іменем. Кожна процедура має своє унікальне ім'я та зберігається в Oracle база даних як об'єкт бази даних.

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

Характеристики блоку підпрограми процедури в PL/SQL:

• Процедури – це окремі блоки, які можна зберігати в база даних.
• Їх можна викликати за їхнім іменем для виконання інструкцій PL/SQL.
• Вони в основному використовуються для виконання певного процесу.
• Вони можуть мати вкладені блоки або бути вкладеними всередину інших блоків чи пакетів.
• Вони містять частину оголошення (необов'язково), частину виконання та частину обробки винятків (необов'язково).
• Значення можна передавати в процедуру або отримувати з неї за допомогою параметрів.
• Ці параметри повинні бути включені в оператор виклику.
• Процедура може мати оператор RETURN для повернення керування викликаючому блоку, але вона не може повертати жодного значення через RETURN.
• Процедури не можна викликати безпосередньо з операторів SELECT; їх можна викликати з іншого блоку або за допомогою ключового слова EXEC.

синтаксис

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE PROCEDURE наказує компілятору створити нову процедуру. Ключове слово 'OR REPLACE' наказує йому замінити існуючу процедуру (якщо така є) поточною.
• Назва процедури має бути унікальною.
• Ключове слово «IS» використовується, коли збережена процедура вкладена в якийсь інший блок. Якщо процедура є автономною, використовується «AS». Окрім цього стандарту кодування, обидва мають однакове значення.

Приклад 1: Створення процедури та її виклик за допомогою EXEC. У цьому прикладі ми створюємо Oracle процедура, яка приймає ім'я на вхід та друкує вітальне повідомлення на вихід, використовуючи команду EXEC для її виклику.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Пояснення:

• Code рядок 1: Створення процедури з назвою 'welcome_msg' та одним параметром 'p_name' типу 'IN'.
• Code рядок 4: Друк вітального повідомлення шляхом об'єднання імені вхідного поля.
• Процедура успішно скомпільована.
• Code рядок 7: Виклик процедури за допомогою EXEC з параметром 'Guru99'. Процедура виконується та друкує «Ласкаво просимо» Guru99 ".

Що таке функція?

Функція — це окрема підпрограма PL/SQL. Як і процедура, функція має унікальне ім'я та зберігається як об'єкт бази даних PL/SQL. Її характеристики:

• Функції – це окремі блоки, що використовуються переважно для обчислень.
• Функція використовує ключове слово RETURN для повернення значення, тип даних якого визначено під час створення.
• Функція повинна або повертати значення, або викликати виняток; повернення є обов'язковим у функціях.
• Функцію без операторів DML можна викликати безпосередньо в запиті SELECT, тоді як функцію з DML можна викликати лише з інших блоків PL/SQL.
• Він може мати вкладені блоки або бути вкладеним всередину інших блоків чи пакетів.
• Він містить частину оголошення (необов'язково), частину виконання та частину обробки винятків (необов'язково).
• Значення можна передавати у функцію або отримувати з неї через параметри.
• Ці параметри повинні бути включені в оператор виклику.
• Функція також може повертати значення через параметри OUT, окрім використання RETURN.
• Оскільки викликаюча операція завжди повертає значення, вона завжди використовує оператор присвоєння для заповнення змінної.

синтаксис

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE FUNCTION наказує компілятору створити нову функцію. 'OR REPLACE' наказує йому замінити існуючу функцію (якщо така є) поточною.
• Ім'я функції має бути унікальним.
• Слід згадати тип даних RETURN.
• Ключове слово «IS» використовується, коли функція вкладена всередину іншого блоку. Якщо функція є автономною, використовується «AS».

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

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Пояснення:

• Code рядок 1: Створення функції з назвою 'welcome_msg_func' та одним параметром 'p_name' типу 'IN'.
• Code рядок 2: Оголошення типу повернення як VARCHAR2.
• Code рядок 5: Повертає об'єднане значення «Ласкаво просимо» та значення параметра.
• Code рядок 8: Анонімний блок для виклику вищезгаданої функції.
• Code рядок 9: Оголошення змінної з тим самим типом даних, що й тип повернення функції.
• Code рядок 11: Виклик функції та заповнення поверненим значенням змінної 'lv_msg'.
• Code рядок 12: Виведення значення змінної. Вивід: «Ласкаво просимо» Guru99 ".
• Code рядок 14: Виклик тієї ж функції через оператор SELECT. Повернене значення спрямовується на стандартний вивід.

Подібності між процедурою та функцією

• Обидва можуть бути викликані з інших блоків PL/SQL.
• Якщо виняток, викликаний у підпрограмі, не обробляється в її обробка винятків розділ, він поширюється на викликаючий блок.
• Обидва можуть мати скільки завгодно параметрів.
• Обидва вони розглядаються як об’єкти бази даних у PL/SQL.

Процедура проти функції: ключові відмінності

Вбудовані функції в PL/SQL

PL / SQL містить різні вбудовані функції для роботи з типами даних рядків та дат. Тут ми бачимо часто використовувані функції та їх використання.

Функції перетворення

Ці вбудовані функції перетворюють один тип даних на інший.

Рядкові функції

Ці функції використовуються для символьного типу даних.

Функції дати

Ці функції використовуються для маніпулювання датами.

Поширені запитання