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 в подпрограмата ще върне контрола обратно към техния родителски блок. В случай на функции оператор RETURN също връща стойността. Типът данни на тази стойност винаги се споменава по време на декларирането на функцията. Типът данни може да бъде от всеки валиден PL/SQL тип данни.

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

A Процедура в PL/SQL е подпрограмна единица, която се състои от група PL/SQL изрази, които могат да бъдат извикани по име. Всяка процедура в PL/SQL има свое собствено уникално име, с което може да бъде препращана и извикана. Тази подпрограмна единица в Oracle базата данни се съхранява като обект на база данни.

Забележка: Подпрограмата не е нищо друго освен процедура и трябва да бъде създадена ръчно според изискването. Веднъж създадени, те ще бъдат съхранени като обекти на база данни.

По-долу са характеристиките на подпрограмната единица Процедура в 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'. Процедурата се изпълнява и съобщението се отпечатва като „Добре дошъл Guru99“.

СВЪРЗАНИ СТАТИИ

Какво е функция?

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: Отпечатване на стойността на променливата. Резултатът, който ще получите тук, е „Добре дошъл 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-ЯН-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(текст, низ, начало, поява) Дава позицията на определен текст в даден низ.

  • текст – Основен низ
  • string – текст, който трябва да се търси
  • начало – начална позиция на търсенето (по избор)
  • съответствие – срещане на търсения низ (по избор)
 Изберете INSTR('AEROPLANE','E',2,1) от dual

Продукция: 2

Изберете INSTR('AEROPLANE','E',2,2) от dual

Продукция: 9 (2nd поява на E)
SUBSTR (текст, начало, дължина) Дава стойността на подниза на главния низ.

  • текст – основен низ
  • начало – начална позиция
  • дължина – дължина на подниз
 изберете 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') от dual;

Продукция: 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 (sysdate+60, sysdate) от dual

Продукция: 2

Oбобщение

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

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