Зберігається процедура Oracle PL / SQL & Функції з прикладами

Зміст:

Anonim

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

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

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

У цьому посібнику з зберігання процедур Oracle ви дізнаєтесь-

  • Термінології в підпрограмах PL / SQL
  • Що таке процедура в PL / SQL?
  • Що таке функція?
  • Подібність між процедурою та функцією
  • Процедура проти Функція: ключові відмінності
  • Вбудовані функції в PL / SQL

Термінології в підпрограмах 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?

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

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

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

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

Синтаксис:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE вказує компілятору створити нову процедуру в Oracle. Ключове слово "АБО ЗАМІНИТИ" вказує компілятору замінити існуючу процедуру (якщо така є) на поточну.
  • Назва процедури має бути унікальною.
  • Ключове слово "IS" буде використано, коли збережена процедура в Oracle буде вкладена в інші блоки. Якщо процедура автономна, тоді буде використано "AS". Крім цього стандарту кодування, обидва мають однакове значення.

Приклад1: Створення процедури та виклик її за допомогою EXEC

У цьому прикладі ми збираємося створити процедуру Oracle, яка приймає ім'я як вхід і друкує привітальне повідомлення як вихід. Ми будемо використовувати команду EXEC для виклику процедури.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);

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

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

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

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

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

Синтаксис

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION вказує компілятору створити нову функцію. Ключове слово "АБО ЗАМІНИ" вказує компілятору замінити існуючу функцію (якщо така є) на поточну.
  • Назва функції повинна бути унікальною.
  • Слід зазначити тип даних RETURN.
  • Ключове слово "IS" буде використано, коли процедура буде вкладена в деякі інші блоки. Якщо процедура автономна, тоді буде використано "AS". Крім цього стандарту кодування, обидва мають однакове значення.

Приклад1: Створення функції та виклик її за допомогою анонімного блоку

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

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_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 : Друк значення змінної. Результат, який ви отримаєте тут, - "Ласкаво просимо Guru99"
  • Кодовий рядок 14 : Виклик тієї самої функції за допомогою оператора SELECT. Повернене значення направляється безпосередньо до стандартного виводу.

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

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

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

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

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

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

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

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

Назва функції Використання Приклад
TO_CHAR Перетворює інший тип даних на тип даних символу TO_CHAR (123);
TO_DATE (рядок, формат) Перетворює заданий рядок на дату. Рядок повинен відповідати формату. TO_DATE ('2015-JAN-15', 'YYYY-MON-DD'); Вихід: 15.01.2015
TO_NUMBER (текст, формат) Перетворює текст у числовий тип заданого формату. Інформація '9' позначає кількість цифр Виберіть TO_NUMBER ('1234', '9999') із подвійних; Вихід: 1234 Виберіть TO_NUMBER ('1234,45', '9,999.99') із подвійного; Вихід: 1234

Функції рядка

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

Назва функції Використання Приклад
INSTR (текст, рядок, старт, подія) Дає позицію конкретного тексту в заданому рядку.
  • текст - основний рядок
  • рядок - текст, який потрібно шукати
  • старт - початкова позиція пошуку (необов’язково)
  • соответствие - поява шуканого рядка (необов’язково)
 Виберіть INSTR ('AIRPLANE', 'E', 2,1) із подвійного виходу : 2 Виберіть INSTR ('AIRPLANE', 'E', 2,2) з подвійного виходу: 9 (2- е місце E)
SUBSTR (текст, початок, довжина) Дає значення підрядка основного рядка.
  • текст - основний рядок
  • старт - вихідне положення
  • length - довжина, яку потрібно підрядити
 виберіть подстрок («літак», 1,7) з подвійного виходу : аероплан
ВЕРХНІЙ (текст) Повертає верхній регістр наданого тексту Виберіть верхній ('guru99') з подвійного; Вихід : GURU99
ВНИЗ (текст) Повертає нижній регістр наданого тексту Виберіть нижній («літак») з подвійного; Вихід : літак
INITCAP (текст) Повертає заданий текст із початковою літерою у верхньому регістрі. Виберіть ('guru99') з подвійного виходу : Guru99 Select ('моя історія') з подвійного виводу : Моя історія
LENGTH (текст) Повертає довжину заданого рядка Виберіть LENGTH ('guru99') із подвійного; Вихід : 6
LPAD (текст, довжина, pad_char) Розміщує рядок у лівій частині на задану довжину (загальний рядок) із заданим символом Виберіть LPAD ('guru99', 10, '$') з подвійного; Результат : $$$$ guru99
RPAD (текст, довжина, pad_char) Розміщує рядок у правій частині на задану довжину (загальний рядок) із заданим символом Виберіть RPAD ('guru99', 10, '-') з подвійного виходу : guru99 ----
LTRIM (текст) Обрізає пробіл, що виводиться з тексту Виберіть LTRIM ('Guru99') із подвійного; Результат : Guru99
RTRIM (текст) Обрізає кінцевий пробіл від тексту Виберіть RTRIM ('Guru99') із подвійного; Вихід ; Гуру99

Функції дати

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

Назва функції Використання Приклад
ДОДАТИ_МІСЯЦІ (дата, кількість місяців) Додає задані місяці до дати ADD_MONTH ('01.01.2015', 5); Вихід : 01.05.2015
SYSDATE Повертає поточну дату та час сервера Виберіть SYSDATE із подвійного; Вихід : 4.10.2015 14:11:43
ТРАНК Раунд змінної дати до нижчого можливого значення вибрати sysdate, TRUNC (sysdate) з подвійного; Вихід : 04.10.2015 14:12:39 04.10.2015
КРУГЛИЙ Округлює дату до найближчого обмеження або вище, або нижче Виберіть sysdate, ROUND (sysdate) із подвійного виходу : 4.10.2015 14:14:34 5.10.2015
МІСЯЦІ_МІЖ Повертає кількість місяців між двома датами Виберіть MONTHS_BETWEEN (sysdate + 60, sysdate) з подвійного виходу : 2

Резюме

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

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