Съхранена процедура на Oracle PL / SQL & Функции с примери

Съдържание:

Anonim

В този урок ще видите подробно описание за това как да създавате и изпълнявате посочените блокове (процедури и функции).

Процедури и функции са подпрограмите, които могат да бъдат създадени и записани в базата данни като обекти на база данни. Те могат да бъдат извикани или препратени и в другите блокове.

Отделно от това ще разгледаме основните разлики между тези две подпрограми. Също така ще обсъдим вградените функции на Oracle.

В този урок за съхранена процедура на Oracle ще научите -

  • Терминологии в PL / SQL подпрограми
  • Какво представлява процедурата в PL / SQL?
  • Какво е функция?
  • Прилики между процедура и функция
  • Процедура Vs. Функция: Основни разлики
  • Вградени функции в 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. Ключовата дума „OR REPLACE“ възлага на компилацията да замени съществуващата процедура (ако има такава) с текущата.
  • Името на процедурата трябва да е уникално.
  • Ключовата дума „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 инструктира компилатора да създаде нова функция. Ключовата дума „OR REPLACE“ възлага на компилатора да замени съществуващата функция (ако има такава) с текущата.
  • Името на функцията трябва да е уникално.
  • Трябва да се спомене типът ВРЪЩАНЕ.
  • Ключовата дума „IS“ ще се използва, когато процедурата е вложена в някои други блокове. Ако процедурата е самостоятелна, тогава ще се използва „AS“. Освен този стандарт за кодиране, и двата имат едно и също значение.

Пример1: Създаване на функция и извикване с помощта на Anonymous Block

В тази програма ще създадем функция, която приема името като вход и връща приветственото съобщение като изход. Ще използваме анонимен блок и изберете оператор, за да извикаме функцията.

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.

Процедура Vs. Функция: Основни разлики

Процедура Функция
  • Използва се главно за изпълнение на определен процес
  • Използва се главно за извършване на някои изчисления
  • Не може да се извика в оператора 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.1.2015 г.
TO_NUMBER (текст, формат) Преобразува текста в числов тип на дадения формат. Информацията '9' означава броя на цифрите Изберете TO_NUMBER ('1234', '9999') от dual; Изход: 1234 Изберете TO_NUMBER ('1,234.45', '9,999.99') от dual; Изход: 1234

Струнни функции

Това са функциите, които се използват за типа данни на знака.

Име на функцията Употреба Пример
INSTR (текст, низ, начало, поява) Дава позицията на определен текст в дадения низ.
  • текст - Основен низ
  • низ - текст, който трябва да се търси
  • start - начална позиция на търсенето (по избор)
  • съответствие - поява на търсения низ (по избор)
 Изберете INSTR ('AIRPLANE', 'E', 2,1) от двоен изход : 2 Изберете INSTR ('AIRPLANE', 'E', 2,2) от dual output: 9 (2- ра поява на E)
SUBSTR (текст, начало, дължина) Дава стойността на подниза на основния низ.
  • текст - основен низ
  • старт - изходна позиция
  • length - дължина, която трябва да бъде поднизана
 изберете substr („самолет“, 1,7) от двоен изход : aeropla
ГОРЕ (текст) Връща главния регистър на предоставения текст Изберете горна ('guru99') от двойна; Изход : GURU99
НАДОЛНО (текст) Връща малките букви на предоставения текст Изберете по-ниско („самолет“) от двойно; Изход : самолет
INITCAP (текст) Връща дадения текст с началната буква с главни букви. Select ('guru99') from dual Output : Guru99 Select ('my story') from dual Output : My Story
ДЪЛЖИНА (текст) Връща дължината на дадения низ Изберете LENGTH ('guru99') от dual; Изход : 6
LPAD (текст, дължина, pad_char) Поставя низа в лявата страна за дадената дължина (общ низ) с дадения знак Изберете LPAD ('guru99', 10, '$') от dual; Резултат : $$$$ guru99
RPAD (текст, дължина, pad_char) Поставя низа в дясната страна за дадената дължина (общ низ) с дадения знак Изберете RPAD ('guru99', 10, '-') от двоен изход : guru99 ----
LTRIM (текст) Подрязва водещото бяло пространство от текста Изберете LTRIM ('Guru99') от dual; Резултат : Guru99
RTRIM (текст) Подрязва задното бяло пространство от текста Изберете RTRIM ('Guru99') от dual; Изход ; Гуру99

Функции за дата

Това са функции, които се използват за манипулиране с дати.

Име на функцията Употреба Пример
ADD_MONTHS (дата, брой месеци) Добавя дадените месеци към датата ADD_MONTH ('2015-01-01', 5); Изход : 01.05.2015
SYSDATE Връща текущата дата и час на сървъра Изберете SYSDATE от dual; Изход : 4.10.2015 г. 14:11:43
ТРАНС Кръг на променливата за дата до възможно най-ниската стойност изберете sysdate, TRUNC (sysdate) от dual; Изход : 4.10.2015 г. 14:12:39 04.10.2015
КРЪГЪЛ Закръглява датата до най-близкия лимит или по-висока или по-ниска Изберете sysdate, ROUND (sysdate) от двоен изход : 4.10.2015 14:14:34 05.10.2015
МЕСЕЦИ_МЕЖДУ Връща броя на месеците между две дати Изберете MONTHS_BETWEEN (sysdate + 60, sysdate) от двоен изход : 2

Обобщение

В тази глава научихме следното.

  • Как да създадете процедура и различни начини да я извикате
  • Как да създадете функция и различни начини да я извикате
  • Прилики и разлики между процедура и функция
  • Параметри и RETURN общи терминологии в PL / SQL подпрограми
  • Общи вградени функции в Oracle PL / SQL