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

Автор: Фіона Браун Фіона Браун
Hours оновлений

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

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

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

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

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

Параметр

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

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

За призначенням параметри класифікуються як

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

Параметр IN

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

Параметр OUT

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

Параметр IN OUT

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

Ці типи параметрів повинні бути згадані під час створення підпрограм.

ПОВЕРНЕННЯ

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

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

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

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

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

Нижче наведено характеристики підрозділу підпрограми Procedure у PL/SQL:

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

синтаксис

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE наказує компілятору створити нову процедуру в Oracle. Ключове слово 'OR REPLACE' наказує компілятору замінити існуючу процедуру (якщо така є) на поточну.
  • Ім'я процедури має бути унікальним.
  • Ключове слово "IS" буде використано, коли збережена процедура в Oracle вкладено в деякі інші блоки. Якщо процедура є автономною, тоді використовуватиметься «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’);

Пояснення коду:

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

СТАТТІ ПО ТЕМІ

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

Functions — це окрема підпрограма PL/SQL. Як і процедура PL/SQL, функції мають унікальне ім’я, за яким на них можна посилатися. Вони зберігаються як об’єкти бази даних PL/SQL. Нижче наведено деякі характеристики функцій.

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

Функції в PL/SQL

синтаксис

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl 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 для виклику функції.

Функції в PL/SQL

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
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;

Пояснення коду:

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

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

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

Процедура Vs. Функція: ключові відмінності

Процедура функція
Використовується в основному для виконання певного процесу Використовується в основному для виконання деяких розрахунків
Неможливо викликати оператор SELECT Функцію, яка не містить операторів DML, можна викликати в операторі SELECT
Використовуйте параметр OUT, щоб повернути значення Щоб повернути значення, використовуйте RETURN
Повертати значення необов’язково Повернення вартості є обов’язковим
RETURN просто вийде з підпрограми. RETURN вийде з підпрограми, а також поверне значення
Тип повернених даних не буде вказано під час створення Повернутий тип даних є обов’язковим під час створення

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

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

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

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

Назва функції Використання Приклад
TO_CHAR Перетворює інший тип даних на символьний TO_CHAR(123);
TO_DATE ( рядок, формат ) Перетворює заданий рядок на дату. Рядок має відповідати формату.

TO_DATE('2015-JAN-15', 'РРРР-ПН-ДД');

Вихід: 1 / 15 / 2015
TO_NUMBER (текст, формат)

Перетворює текст на числовий тип заданого формату.

Informat '9' позначає кількість цифр

 Виберіть TO_NUMBER('1234′,'9999') з dual;

Вихід: 1234

Виберіть TO_NUMBER('1,234.45′,'9,999.99') з dual;

Вихід: 1234

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

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

Назва функції Використання Приклад
INSTR(текст, рядок, початок, поява) Задає позицію певного тексту в заданому рядку.

  • текст – Основний рядок
  • рядок – текст, який потрібно знайти
  • start – початкова позиція пошуку (опціонально)
  • відповідність – входження шуканого рядка (необов’язково)
 Виберіть INSTR('AEROPLANE','E',2,1) з dual

Вихід: 2

Виберіть INSTR('AEROPLANE','E',2,2) з dual

Вихід: 9 (2nd поява E)
SUBSTR (текст, початок, довжина) Надає значення підрядка основного рядка.

  • текст – основний рядок
  • старт – вихідне положення
  • length – довжина підрядка
 виберіть substr('aeroplane',1,7) з dual

Вихід: аероплан
ВЕРХНИЙ ( текст ) Повертає верхній регістр наданого тексту Виберіть upper('guru99') з dual;

Вихід: GURU99
НИЖНИЙ ( текст ) Повертає нижній регістр наданого тексту Виберіть нижчий ('AerOpLane') з подвійного;

Вихід: літак
INITCAP (текст) Повертає заданий текст із початковою літерою у верхньому регістрі. Виберіть ('guru99') із подвійного

Вихід: Guru99

Виберіть («моя історія») з подвійного

Вихід: Моя історія
LENGTH ( текст ) Повертає довжину заданого рядка Виберіть ДОВЖИНУ ('guru99') з dual;

Вихід: 6
LPAD (текст, довжина, pad_char) Доповнює рядок у лівій частині заданої довжини (загальний рядок) заданим символом Виберіть LPAD('guru99', 10, '$') із dual;

Вихід: $$$$guru99
RPAD (текст, довжина, pad_char) Доповнює рядок у правій частині заданої довжини (загальний рядок) заданим символом Виберіть RPAD('guru99′,10,'-') з dual

Вихід: guru99—-
LTRIM ( текст ) Вирізає порожній простір із тексту Виберіть LTRIM(' Guru99') з подвійного;

Вихід: Guru99
RTRIM ( текст ) Вирізає пробіли в кінці тексту Виберіть RTRIM('Guru99 ') з dual;

Вихід; Гуру99

Функції дати

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

Назва функції Використання Приклад
ADD_MONTHS (дата, кількість місяців) Додає вказані місяці до дати ADD_MONTH('2015-01-01',5);

Вихід: 05 / 01 / 2015
SYSDATE Повертає поточну дату й час сервера Виберіть SYSDATE з dual;

Вихід: 10 4:2015:2
ТРАНК Округлення змінної дати до найменшого можливого значення вибрати sysdate, TRUNC(sysdate) з dual;

Вихід: 10 4:2015:2 12
КРУГЛИЙ Округлює дату до найближчої межі, більшої або меншої Виберіть sysdate, ROUND(sysdate) з dual

Вихід: 10 4:2015:2 14
MONTHS_BETWEEN Повертає кількість місяців між двома датами Виберіть MONTHS_BETWEEN (системна дата+60, системна дата) з dual

Вихід: 2

Підсумки

У цьому розділі ми дізналися наступне.

  • Як створити процедуру та різні способи її виклику
  • Як створити функцію та різні способи її виклику
  • Подібності та відмінності між процедурою та функцією
  • Загальна термінологія параметрів і RETURN у підпрограмах PL/SQL
  • Загальні вбудовані функції в Oracle PL / SQL

Вам може сподобатися: