Oracle Хранимые процедуры и функции PL/SQL с примерами

В этом уроке вы увидите подробное описание того, как создавать и выполнять именованные блоки (процедуры и функции).

Процедуры и функции — это подпрограммы, которые можно создавать и сохранять в базе данных как объекты базы данных. Их также можно вызывать или ссылаться внутри других блоков.

Помимо этого, мы рассмотрим основные различия между этими двумя подпрограммами. Также мы собираемся обсудить Oracle встроенные функции.

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

Прежде чем мы узнаем о подпрограммах PL/SQL, мы обсудим различную терминологию, которая является частью этих подпрограмм. Ниже приведены термины, которые мы собираемся обсудить.

Параметр

Параметр является переменной или заполнителем любого допустимого значения. Тип данных PL/SQL посредством которого подпрограмма PL/SQL обменивается значениями с основным кодом. Этот параметр позволяет подавать входные данные в подпрограммы и извлекать данные из этих подпрограмм.

Эти параметры должны быть определены вместе с подпрограммами во время создания.
Эти параметры включены в оператор вызова этих подпрограмм для взаимодействия значений с подпрограммами.
Тип данных параметра в подпрограмме и вызывающем операторе должен быть одинаковым.
Размер типа данных не следует указывать во время объявления параметра, поскольку для этого типа размер является динамическим.

По назначению параметры классифицируются на

Входной параметр
ВЫХОДНОЙ параметр
ВХОД ВЫХ Параметр

Входной параметр

Этот параметр используется для ввода данных в подпрограммы.
Это переменная, доступная только для чтения внутри подпрограмм. Их значения не могут быть изменены внутри подпрограммы.
В вызывающем операторе эти параметры могут быть переменной, литеральным значением или выражением, например, это может быть арифметическое выражение типа «5*8» или «a/b», где «a» и «b» являются переменными. .
По умолчанию параметры имеют тип IN.

ВЫХОДНОЙ параметр

Этот параметр используется для получения вывода из подпрограмм.
Это переменная для чтения и записи внутри подпрограмм. Их значения можно изменить внутри подпрограмм.
В вызывающем операторе эти параметры всегда должны быть переменными, в которых будут храниться значения из текущих подпрограмм.

ВХОД ВЫХ Параметр

Этот параметр используется как для ввода, так и для получения вывода из подпрограмм.
Это переменная для чтения и записи внутри подпрограмм. Их значения можно изменить внутри подпрограмм.
В вызывающем операторе эти параметры всегда должны быть переменными, в которых будут храниться значения из подпрограмм.

Эти типы параметров должны быть упомянуты во время создания подпрограмм.

ВЕРНУТЬ

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».

Что такое функция?

Функции — это отдельная подпрограмма PL/SQL. Как и процедуры PL/SQL, функции имеют уникальное имя, по которому на них можно ссылаться. Они хранятся как объекты базы данных PL/SQL. Ниже приведены некоторые характеристики функций.

Функции представляют собой отдельный блок, который в основном используется для расчетов.
Функция использует ключевое слово RETURN для возврата значения, а тип данных определяется во время создания.
Функция должна либо возвращать значение, либо вызывать исключение, т. е. возврат является обязательным в функциях.
Функция без операторов DML может быть вызвана напрямую в запросе SELECT, тогда как функция с операцией DML может быть вызвана только из других блоков PL/SQL.
Он может иметь вложенные блоки или может быть определен и вложен внутри других блоков или пакетов.
Он содержит часть объявления (необязательно), часть исполнения и часть обработки исключений (необязательно).
Значения можно передавать в функцию или получать из процедуры через параметры.
Эти параметры должны быть включены в оператор вызова.
Функция PLSQL также может возвращать значение через параметры OUT, кроме использования RETURN.
Поскольку он всегда возвращает значение, в операторе вызова он всегда сопровождается оператором присваивания для заполнения переменных.

Синтаксис

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. Создание функции и ее вызов с использованием анонимного блока.

В этой программе мы собираемся создать функцию, которая принимает имя в качестве входных данных и возвращает приветственное сообщение в качестве выходных данных. Мы собираемся использовать анонимный блок и оператор выбора для вызова функции.

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.

Процедура против. Функция: Ключевые отличия

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

Строковые функции

Это функции, которые используются с символьным типом данных.

Имя функции	Применение	Пример
INSTR(текст, строка, начало, появление)	Возвращает позицию определенного текста в данной строке. текст – Основная строка строка – текст, который нужно найти start – начальная позиция поиска (необязательно) соответствие – появление искомой строки (необязательно)	Выберите INSTR('САМОЛЕТ','E',2,1) из двойного Результат: 2 Выберите INSTR('САМОЛЕТ','E',2,2) из двойного Результат: 9 (2^nd возникновение Е)
SUBSTR (текст, начало, длина)	Возвращает значение подстроки основной строки. текст – основная строка старт – исходное положение length – длина для подстроки	выберите substr('самолет',1,7) из двойного Результат: аэропла
ВЕРХНИЙ (текст)	Возвращает верхний регистр предоставленного текста	Выберите верхний('guru99') из двойного; Результат: ГУРУ99
НИЖНИЙ (текст)	Возвращает нижний регистр предоставленного текста	Выберите нижний («AerOpLane») из двойного; Результат: самолет
ИНИТКАП (текст)	Возвращает заданный текст с начальной буквой в верхнем регистре.	Выберите («guru99») из двойного Результат: Гуру99 Выберите («моя история») из двойного Результат: Моя история
ДЛИНА (текст)	Возвращает длину заданной строки	Выберите ДЛИНУ («guru99») из двойного; Результат: 6
LPAD (текст, длина, Pad_char)	Дополняет строку в левой части заданной длины (общая строка) заданным символом.	Выберите LPAD('guru99', 10, '$') из двойного; Результат: $$$$гуру99
RPAD (текст, длина, Pad_char)	Дополняет строку в правой части заданной длины (общая строка) заданным символом.	Выберите RPAD('guru99',10,'-') из двойного Результат: гуру99—-
LTRIM (текст)	Удаляет начальные пробелы в тексте	Выберите LTRIM('Guru99') из двойного; Результат: Гуру99
РТРИМ (текст)	Удаляет конечные пробелы из текста	Выберите RTRIM('Guru99') из двойного; Результат; Гуру99

Дата Функции

Это функции, которые используются для работы с датами.

Имя функции	Применение	Пример
ADD_MONTHS (дата, количество месяцев)	Добавляет указанные месяцы к дате	ADD_MONTH('2015',01); Результат: 05 / 01 / 2015
СИСДАТА	Возвращает текущую дату и время сервера	Выберите SYSDATE из двойного; Результат: 10 4:2015:2
TRUNC	Округление переменной даты до минимально возможного значения	выберите системную дату, TRUNC(sysdate) из двойного; Результат: 10 4:2015:2 12
КРУГЛЫЙ	Округляет дату до ближайшего предела в большую или меньшую сторону.	Выберите системную дату, ROUND(sysdate) из двойного Результат: 10 4:2015:2 14
MONTHS_BETWEEN	Возвращает количество месяцев между двумя датами	Выберите MONTHS_BETWEEN (sysdate+60, sysdate) из двойного Результат: 2

Резюме

В этой главе мы узнали следующее.

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