Учебники

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

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

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

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

В этом уроке вы узнаете

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

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

Параметр:

Параметр является переменной или заполнителем любого допустимого типа данных 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?

Процедура – это подпрограмма, состоящая из группы операторов 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

Синтаксис

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 указывает компилятору создать новую функцию. Ключевое слово «ИЛИ ЗАМЕНИТЬ» указывает компилятору заменить существующую функцию (если есть) на текущую.
  • Имя функции должно быть уникальным.
  • Возвращаемый тип данных должен быть упомянут.
  • Ключевое слово «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 : Создание функции с именем ‘welcome_msg_func’ и одним параметром ‘p_name’ типа ‘IN’.
  • Строка кода 2 : объявление типа возвращаемого значения как VARCHAR2
  • Строка кода 5 : Возвращает объединенное значение «Welcome» и значение параметра.
  • Строка кода 8 : анонимный блок для вызова вышеуказанной функции.
  • Строка кода 9 : Объявление переменной с типом данных таким же, как и у возвращаемого типа данных функции.
  • Строка кода 11 : вызов функции и заполнение возвращаемого значения в переменной ‘lv_msg’.
  • Строка кода 12 : печать значения переменной. Вывод, который вы получите здесь: «Welcome Guru99»
  • Строка кода 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-JAN-15’, ‘YYYY-MON-DD’);

Выход: 15.01.2015

TO_NUMBER (текст, формат) Преобразует текст в числовой тип данного формата. Информат «9» обозначает количество цифр Выберите TO_NUMBER («1234», «9999») из двойного;

Выход: 1234

Выберите TO_NUMBER (‘1234,45’, ‘9,999,99’) из двойного;

Выход: 1234

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

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

Имя функции Применение ПРИМЕР
INSTR (текст, строка, начало, вхождение) Дает положение конкретного текста в данной строке.

  • текст – основная строка
  • строка – текст, который необходимо найти
  • start – начальная позиция поиска (необязательно)
  • соответствие – вхождение искомой строки (необязательно)
Выберите INSTR (‘AIRPLANE’, ‘E’, 2,1) из двойного
выхода : 2 Выберите INSTR (‘AIRPLANE’, ‘E’, 2,2) из ​​двойного
выхода: 9 (2- е вхождение E)
SUBSTR (текст, начало, длина) Дает значение подстроки основной строки.

  • текст – основная строка
  • старт – стартовая позиция
  • длина – длина, которая будет подстрокой
выберите substr (‘самолет’, 1,7) из двойного
вывода : aeropla
UPPER (текст)

Возвращает верхний регистр предоставленного текста Выберите верхний (‘guru99’) из двойного;
Выход : GURU99
НИЖНЯЯ (текст)

Возвращает нижний регистр предоставленного текста Выберите более низкий («самолет») из двойного;
Выход : самолет
INITCAP (текст)

Возвращает данный текст с начальной буквой в верхнем регистре. Выбрать (‘guru99’) из двойного
выхода : Guru99
Выбрать (‘моя история’) из двойного
вывода : Моя история
ДЛИНА (текст)

Возвращает длину данной строки Выберите ДЛИНУ (‘guru99’) из двойного;
Выход : 6
LPAD (текст, длина, pad_char) Заполняет строку в левой части для заданной длины (общая строка) с заданным символом Выберите LPAD (‘guru99’, 10, ‘$’) из двойного;
Вывод : $$$$ guru99
RPAD (текст, длина, pad_char) Заполняет строку в правой части для заданной длины (общая строка) с заданным символом Выберите RPAD (‘guru99’, 10, ‘-‘) из двойного
вывода : guru99 —-
LTRIM (текст)

Обрезает начальный пробел из текста Выберите LTRIM («Guru99») из двойного;
Выход : гуру99
RTRIM (текст)

Обрезает конечный пробел из текста Выберите RTRIM («Guru99») из двойного;
Вывод ; Guru99

Функции даты

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

Имя функции Применение ПРИМЕР
ADD_MONTHS (дата, количество месяцев) Добавляет указанные месяцы к дате ADD_MONTH ( ‘2015-01-01’, 5);
Выходные данные : 01.05.2015
SYSDATE

Возвращает текущую дату и время сервера Выберите SYSDATE из двойного;
Выходной : 04.10.2015 14:11:43
TRUNC

Округление переменной даты до минимально возможного значения выберите sysdate, TRUNC (sysdate) из двойного; Выходной : 04.10.2015 14:12:39 04.10.2015
КРУГЛЫЙ

Округляет дату до ближайшего лимита выше или ниже Выберите sysdate, ROUND (sysdate) из двойного вывода : 04.10.2015 14:14:34 10.10.2015
MONTHS_BETWEEN

Возвращает количество месяцев между двумя датами Выберите MONTHS_BETWEEN (sysdate + 60, sysdate) из двух
выходов : 2

Резюме

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

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