14) Хранимая процедура и функции

Параметр:

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

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

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

1. IN параметр
2. Выходной параметр
3. Параметр IN OUT

IN параметр:

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

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

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

Параметр IN OUT:

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

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

ВОЗВРАЩЕНИЕ

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

Обычно родительский или основной блок вызывает подпрограммы, а затем элемент управления переключается с родительского блока на вызываемые подпрограммы. RETURN в подпрограмме вернет элемент управления обратно в родительский блок. В случае функций оператор RETURN также возвращает значение. Тип данных этого значения всегда упоминается во время объявления функции. Тип данных может иметь любой допустимый тип данных PL / SQL.

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

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

• Процедуры — это отдельные блоки программы, которые могут храниться в базе данных.
• Вызвать эти процедуры можно, обратившись к их имени, чтобы выполнить операторы PL / SQL.
• Он в основном используется для выполнения процесса в PL / 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 инструктирует компилятор создать новую процедуру. Ключевое слово «ИЛИ ЗАМЕНИТЬ» дает указание компиляции заменить существующую процедуру (если она есть) на текущую.
• Имя процедуры должно быть уникальным.
• Ключевое слово «IS» будет использоваться, когда процедура вложена в некоторые другие блоки. Если процедура автономна, то будет использоваться «AS». Кроме этого стандарта кодирования, оба имеют одинаковое значение.

Пример 1: создание процедуры и вызов ее с помощью EXEC

В этом примере мы собираемся создать процедуру, которая принимает имя в качестве входных данных и печатает приветственное сообщение в качестве выходных данных. Мы будем использовать команду 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». Процедура выполняется, и сообщение распечатывается как «Welcome Guru99».

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

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

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

PL / SQL содержит различные встроенные функции для работы со строками и датой. Здесь мы рассмотрим часто используемые функции и их использование.

Функции преобразования

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

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

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

Функции даты

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

Резюме

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

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