Создание функций в Oracle

Обновлено: 02.07.2024

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

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

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

В этом учебном пособии по хранимым процедурам Oracle вы узнаете:

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

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

Параметр:

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

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

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

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

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

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

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

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

Параметры IN OUT:

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

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

ВОЗВРАТ

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

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

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

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

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

Ниже приведены характеристики модуля подпрограммы «Процедура» в PL/SQL:

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

Синтаксис:

CREATE PROCEDURE указывает компилятору создать новую процедуру в Oracle. Ключевое слово «OR REPLACE» указывает компилятору заменить существующую процедуру (если есть) на текущую.
Имя процедуры должно быть уникальным.
Ключевое слово «IS» будет использоваться, когда хранимая процедура в Oracle вложена в некоторые другие блоки. Если процедура является автономной, будет использоваться «AS». За исключением этого стандарта кодирования, оба имеют одинаковое значение.

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

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

Пояснение кода:

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

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

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

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

Синтаксис

CREATE FUNCTION указывает компилятору создать новую функцию. Ключевое слово OR REPLACE указывает компилятору заменить существующую функцию (если есть) текущей.
Имя функции должно быть уникальным.
Следует указать тип данных RETURN.
Ключевое слово «IS» будет использоваться, когда процедура вложена в некоторые другие блоки. Если процедура является автономной, будет использоваться «AS». За исключением этого стандарта кодирования, оба имеют одинаковое значение.

Пример 1: создание функции и вызов ее с помощью анонимного блока

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

Пояснение кода:

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

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

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

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

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

Используется в основном для выполнения некоторых расчетов.

Невозможно вызвать оператор SELECT

Функция, не содержащая операторов DML, может быть вызвана в операторе SELECT

Используйте параметр OUT, чтобы вернуть значение

Используйте RETURN, чтобы вернуть значение

Возвращать значение не обязательно

Обязательно возвращать значение

RETURN просто выйдет из управления из подпрограммы.

RETURN закроет элемент управления из подпрограммы, а также вернет значение

Тип возвращаемых данных не будет указан во время создания

Тип возвращаемых данных является обязательным во время создания

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

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

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

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

Команда CREATE FUNCTION создает или заменяет автономную функцию или спецификацию вызова.

Автономная функция – это функция (подпрограмма, возвращающая одно значение), которая хранится в базе данных.

Отдельная функция, которую вы создаете с помощью оператора CREATE FUNCTION, отличается от функции, которую вы объявляете и определяете в блоке или пакете PL/SQL. Дополнительные сведения см. в разделах «Объявление и определение функции» и Оператор CREATE PACKAGE.

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

Чтобы хранимая функция могла вызываться из операторов SQL, она должна подчиняться определенным правилам, управляющим побочными эффектами. См. «Побочные эффекты подпрограмм».

Чтобы создать или заменить автономную функцию в вашей схеме, вы должны иметь системную привилегию CREATE PROCEDURE. Чтобы создать или заменить автономную функцию в схеме другого пользователя, вы должны иметь системную привилегию CREATE ANY PROCEDURE.

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

Чтобы встроить оператор CREATE FUNCTION в программу прекомпилятора Oracle, вы должны завершить оператор ключевым словом END-EXEC, за которым следует встроенный терминатор оператора SQL для определенного языка.

Для получения дополнительной информации о таких предварительных условиях:

Повторно создает функцию, если она существует, и перекомпилирует ее.

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

Если какие-либо индексы, основанные на функциях, зависят от функции, то база данных помечает индексы как ОТКЛЮЧЕННЫЕ .

Указывает, является ли функция измененным или необработанным объектом, если изменение включено для типа объекта схемы FUNCTION в схеме . По умолчанию: РЕДАКЦИОННЫЙ . Для получения информации об измененных и необработанных объектах см. Руководство по разработке баз данных Oracle.

Имя схемы, содержащей функцию. По умолчанию: ваша схема.

Имя создаваемой функции.

Если вы планируете вызывать сохраненную подпрограмму с помощью заглушки, сгенерированной SQL*Module, то имя сохраненной подпрограммы также должно быть допустимым идентификатором на языке вызывающего хоста 3GL, таком как Ada или C.

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

Oracle SQL не поддерживает вызов функций с логическими параметрами или возвратами. Поэтому, чтобы операторы SQL могли вызывать пользовательские функции, вы должны спроектировать их так, чтобы они возвращали числа (0 или 1) или строки символов ("ИСТИНА" или "ЛОЖЬ").

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

Если тип возвращаемого значения — ANYDATASET и вы намерены использовать функцию в предложении FROM запроса, необходимо также указать предложение PIPELINED и определить метод описания ( ODCITableDescribe ) как часть типа реализации функции.

Вы не можете ограничить этот тип данных (например, с помощью NOT NULL ).

Типы данных PL/SQL, для получения информации о типах данных PL/SQL

Руководство разработчика Oracle Database Data Cartridge для получения информации об определении функции ODCITableDescribe

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

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

Пример 14-14 Создание функции

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

Функция get_bal возвращает баланс указанной учетной записи.

При вызове функции необходимо указать в качестве аргумента acc_no номер учетной записи, баланс которой запрашивается. Тип данных acc_no — NUMBER .

Функция возвращает баланс счета. Предложение RETURN оператора CREATE FUNCTION указывает, что тип данных возвращаемого значения должен быть NUMBER .

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

Функция, созданная в предыдущем примере, может использоваться в операторе SQL. Например:

Пример 14-15 Создание агрегатных функций

Следующий оператор создает агрегатную функцию SecondMax для агрегирования числовых значений. Предполагается, что подпрограммы ADT SecondMaxImpl содержат реализации подпрограмм ODCIAggregate:

Руководство разработчика Oracle Database Data Cartridge для полной реализации типа и тела типа для SecondMaxImpl

Используйте такую агрегатную функцию в запросе, подобном этому оператору, который запрашивает образец таблицы hr.employees :

Пример 14-16 Пакетная процедура в функции

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

В этой главе:

В других главах:

"Объявление и определение функции" для получения информации о создании функции в блоке PL/SQL

Справочник по языку Oracle Database SQL для получения информации об операторе CALL

Руководство по разработке баз данных Oracle для получения информации об ограничениях на пользовательские функции, вызываемые из операторов SQL

Руководство по разработке Oracle Database для получения дополнительной информации о спецификациях вызовов

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

Создание функции

Отдельная функция создается с помощью оператора CREATE FUNCTION. Упрощенный синтаксис оператора CREATE OR REPLACE PROCEDURE следующий:

имя-функции указывает имя функции.

Опция [ИЛИ ЗАМЕНИТЬ] позволяет изменить существующую функцию.

Необязательный список параметров содержит имя, режим и типы параметров. IN представляет значение, которое будет передано извне, а OUT представляет параметр, который будет использоваться для возврата значения вне процедуры.

Функция должна содержать оператор return.

Предложение RETURN указывает тип данных, которые вы собираетесь вернуть из функции.

тело функции содержит исполняемую часть.

Ключевое слово AS используется вместо ключевого слова IS для создания отдельной функции.

Пример

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

Мы будем использовать таблицу CUSTOMERS, созданную в главе «Переменные PL/SQL» —

Когда приведенный выше код выполняется с использованием приглашения SQL, он дает следующий результат —

Вызов функции

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

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

Чтобы вызвать функцию, вам просто нужно передать необходимые параметры вместе с именем функции, и если функция возвращает значение, вы можете сохранить возвращенное значение.Следующая программа вызывает функцию totalCustomers из анонимного блока —

Когда приведенный выше код выполняется в приглашении SQL, он дает следующий результат —

Пример

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

Когда приведенный выше код выполняется в приглашении SQL, он дает следующий результат —

Рекурсивные функции PL/SQL

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

Чтобы проиллюстрировать концепцию, давайте вычислим факториал числа. Факториал числа n определяется как -

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

Когда приведенный выше код выполняется в приглашении SQL, он дает следующий результат —

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

Создание функции PL/SQL

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

Функция состоит из заголовка и тела.

Заголовок функции содержит имя функции и предложение RETURN, указывающее тип данных возвращаемого значения. Каждый параметр функции может быть в режиме IN, OUT или INOUT. Дополнительные сведения о режиме параметров см. в руководстве по процедурам PL/SQL

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

Декларативная часть находится между ключевыми словами IS и BEGIN. Здесь вы объявляете переменные, константы, курсоры и пользовательские типы.
Исполняемый раздел находится между ключевыми словами BEGIN и END. Здесь вы размещаете исполняемые операторы. В отличие от процедуры, в исполняемой инструкции должен быть хотя бы один оператор RETURN.
В разделе обработки исключений размещается код обработчика исключений.

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

Пример функции PL/SQL

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

Чтобы скомпилировать функцию в Oracle SQL Developer, нажмите кнопку "Выполнить оператор", как показано на рисунке ниже:

После того как функция будет успешно скомпилирована, вы сможете найти ее в узле "Функции":

Вызов функции PL/SQL

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

1) в операторе присваивания:

2) в логическом выражении

3) в операторе SQL

Редактирование функции

Чтобы отредактировать и перекомпилировать существующую функцию, выполните следующие действия:

Удаление функции

ФУНКЦИЯ DROP удаляет функцию из базы данных Oracle. Синтаксис удаления функции прост:

За ключевыми словами DROP FUNCTION следует имя функции, которую вы хотите удалить.

Например, следующий оператор удаляет функцию GET_TOTAL_SALES:

Oracle выдал сообщение о том, что функция GET_TOTAL_SALES была удалена:

Если вы хотите удалить функцию с помощью SQL Developer, выполните следующие действия:

Теперь вы должны знать, как разработать функцию PL/SQL и вызвать ее в своей программе.

Читайте также: