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

Что такое подпрограммы PL/SQL?

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

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

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

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

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

Параметр

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

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

В зависимости от своего назначения параметры классифицируются следующим образом:

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

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

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

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

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

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

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

Тип параметра следует указывать при создании подпрограмм.

ВЕРНУТЬ

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

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

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

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

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

Характеристики подпрограммы процедуры в PL/SQL следующие:

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

Синтаксис

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Команда CREATE PROCEDURE указывает компилятору создать новую процедуру. Ключевое слово 'OR REPLACE' указывает ему заменить существующую процедуру (если таковая имеется) текущей.
• Название процедуры должно быть уникальным.
• Ключевое слово «IS» используется, когда хранимая процедура вложена в другой блок кода. Если процедура является автономной, используется «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');

Code Объяснение:

• Code строка 1: Создание процедуры с именем 'welcome_msg' и одним параметром 'p_name' типа 'IN'.
• Code строка 4: Вывод приветственного сообщения путем объединения введенных имен.
• Процедура успешно скомпилирована.
• Code строка 7: Вызов процедуры с помощью EXEC с параметром 'Guru99'. Процедура выполняется и выводит «Добро пожаловать». Guru99 ".

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

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

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

Синтаксис

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 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 для её вызова.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
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;

Code Объяснение:

• Code строка 1: Создание функции с именем 'welcome_msg_func' и одним параметром 'p_name' типа 'IN'.
• Code строка 2: Объявление типа возвращаемого значения как VARCHAR2.
• Code строка 5: Возвращает объединенное значение 'Welcome' и значение параметра.
• Code строка 8: Анонимный блок для вызова указанной выше функции.
• Code строка 9: Объявление переменной с тем же типом данных, что и тип возвращаемого значения функции.
• Code строка 11: Вызов функции и заполнение возвращаемого значения переменной 'lv_msg'.
• Code строка 12: Вывод значения переменной. Результат: «Добро пожаловать». Guru99 ".
• Code строка 14: Вызов той же функции через оператор SELECT. Возвращаемое значение направляется на стандартный вывод.

Сходства между процедурой и функцией

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

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

Встроенные функции в PL/SQL

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

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

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

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

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

Дата Функции

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

Часто задаваемые вопросы (FAQ)