Как вызвать функцию в oracle

Как вызвать функцию в oracle

Use the CALL statement to execute a routine (a standalone procedure or function, or a procedure or function defined within a type or package) from within SQL.

The restrictions on user-defined function expressions specified in «Function Expressions» apply to the CALL statement as well.

Oracle Database PL/SQL Language Reference for information on creating such routine

You must have EXECUTE privilege on the standalone routine or on the type or package in which the routine is defined.

You can execute a routine in two ways. You can issue a call to the routine itself by name, by using the routine_clause , or you can invoke a routine inside the type of an expression, by using an object_access_expression .

Specify the name of the function or procedure being called, or a synonym that resolves to a function or procedure.

When you call a member function or procedure of a type, if the first argument ( SELF ) is a null IN OUT argument, then Oracle Database returns an error. If SELF is a null IN argument, then the database returns null. In both cases, the function or procedure is not invoked.

Restriction on Functions

If the routine is a function, then the INTO clause is required.

Specify the schema in which the standalone routine, or the package or type containing the routine, resides. If you do not specify schema , then Oracle Database assumes the routine is in your own schema.

type or package

Specify the type or package in which the routine is defined.

In a distributed database system, specify the name of the database containing the standalone routine, or the package or function containing the routine. If you omit dblink , then Oracle Database looks in your local database.

«Calling a Procedure: Example» for an example of calling a routine directly

If you have an expression of an object type, such as a type constructor or a bind variable, then you can use this form of expression to call a routine defined within the type. In this context, the object_access_expression is limited to method invocations.

«Object Access Expressions» for syntax and semantics of this form of expression, and «Calling a Procedure Using an Expression of an Object Type: Example» for an example of calling a routine using an expression of an object type

Specify one or more arguments to the routine, if the routine takes arguments. You can use positional, named, or mixed notation for argument . For example, all of the following notations are correct:

Restrictions on Applying Arguments to Routines

The argument is subject to the following restrictions:

The data types of the parameters passed by the CALL statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN .

An argument cannot be a pseudocolumn or either of the object reference functions VALUE or REF .

Any argument that is an IN OUT or OUT argument of the routine must correspond to a host variable expression.

The number of arguments, including any return argument, is limited to 1000.

You cannot bind arguments of character and raw data types ( CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , RAW , LONG RAW ) that are larger than 4K.

The INTO clause applies only to calls to functions. Specify which host variable will store the return value of the function.

Specify the value or condition of the host variable.

Pro*C/C++ Programmer’s Guide for more information on host variables and indicator variables

Calling a Procedure: Example

The following statement removes the Entertainment department (created in «Inserting Sequence Values: Example» ) using uses the remove_dept procedure. See Oracle Database PL/SQL Language Reference for the example that creates this procedure.

Calling a Procedure Using an Expression of an Object Type: Example

The following examples show how call a procedure by using an expression of an object type in the CALL statement. The example uses the warehouse_typ object type in the order entry sample schema OE :

The next example shows how to use an external function to achieve the same thing:

IgorSikors.github.io

В PL/SQL существует несколько конструкций для разбиения кода на модули:

• Процедура. Программа, которая осуществляет одно или несколько действий и вызывается как исполняемый оператор PL/SQL. Передавать данные процедуре и получать их можно с помощью списка параметров.
• Функция. Программа, которая возвращает одно значение и используется как выражение PL/SQL. Для передачи информации функции используется ее список параметров. Параметры также могут использоваться для возврата информации из функции, но обычно это считается проявлением плохого стиля программирования.
• Триггер базы данных. Набор команд, который вызывается при выполнении некоторого события (подключение к базе данных, модификация строки таблицы или DDL-операция).
• Пакет. Именованный набор процедур, функций, типов и переменных. Пакет не является модулем (скорее, это мета-модуль), но он тесно связан с реализацией модульного подхода.
• Объектный тип или экземпляр объектного типа. Эмуляция объектно-ориентированного класса в Oracle. Объектный тип инкапсулирует состояние и поведение данных, комбинируя их (как реляционная таблица) с правилами (процедурами и функциями, которые манипулируют этими данными).

Процедуры

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

Основные элементы этой структуры:

• схема — имя схемы, которой будет принадлежать процедура (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания процедуры в другой схеме.
• имя — имя процедуры.
• параметр — необязательный список параметров, которые применяются для передачи данных в процедуру и возврата информации из процедуры в вызывающую программу.
• AUTHID — определяет, с какими разрешениями будет вызываться процедура: создателя (владельца) или текущего пользователя. В первом случае процедура выполняется с правами создателя, во втором — с правами вызывающего.
• объявления — объявления локальных идентификаторов этой процедуры. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
• ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к процедуре программными модулями, перечисленными в круглых скобках.
• исполняемые команды — команды, выполняемые процедурой при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
• обработчики исключений — необязательные обработчики исключений для процедуры. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END.

Вызов процедуры

Процедура вызывается как исполняемая команда PL/SQL. Другими словами, ее вызов должен заканчиваться точкой с запятой (;) и может предшествовать другим командам SQL либо PL/SQL (если таковые имеются) в исполняемом разделе блока PL/SQL или следовать за ними:

Если процедура не имеет параметров, она может вызываться с пустыми круглыми скобками или без них:

Заголовок процедуры

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

• Имя процедуры.
• Условие AUTHID (если имеется).
• Список параметров (если имеется).
• Список ACCESSIBLE BY (если имеется — новая возможность Oracle Database 12c).

В идеале программист при виде заголовка процедуры должен понять, что делает эта процедура и как она вызывается. Заголовок процедуры apply_discount из предыдущего раздела выглядит так:

Он состоит из типа модуля, имени и списка из двух параметров.

Тело процедуры

В теле процедуры содержится код, необходимый для реализации этой процедуры; тело состоит из объявления, исполняемого раздела и раздела исключений этой процедуры. Все, что следует за ключевым словом IS, образует тело процедуры. Разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите процедуру командой END.

Метка END

Вы можете указать имя процедуры за завершающим ключевым словом END:

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

Функции

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

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

Функции играют важную роль в реализации модульного подхода. К примеру, реализацию отдельного бизнес-правила или формулы в приложении рекомендуется оформить в видефункции. Вместо того чтобы писать один и тот же запрос снова и снова («Получитьимя работника по идентификатору», «Получить последнюю строку заказа из таблицы order для заданного идентификатора компании» и т. д.), поместите его в функцию и вызовите эту функцию в нужных местах. Такой код создает меньше проблем с отладкой, оптимизацией и сопровождением.

Структура функции Функция имеет почти такую же структуру, как и процедура, не считая того, что ключевое слово RETURN в ней играет совершенно другую роль:

Основные элементы этой структуры:

• схема — имя схемы, которой будет принадлежать функция (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания функции в другой схеме.
• имя — имя функции.
• параметр — необязательный список параметров, которые применяются для передачи данных в функцию и возврата информации из нее в вызывающую программу.
• возвращаемый_тип — задает тип значения, возвращаемого функцией. Возвращаемый тип должен быть указан в заголовке функции.
• AUTHID — определяет, с какими разрешениями будет вызываться функция: создателя (владельца) или текущего пользователя. В первом случае (используется по умолчанию) применяется модель прав создателя, во втором — модель прав вызывающего.
• DETERMINISTIC — определяет функцию как детерминированную, то есть возвращаемое значение полностью определяется значениями ее аргументов. Если включить эту секцию, ядро SQL сможет оптимизировать выполнение функции при ее вызове в запросах.
• PARALLEL_ENABLE — используется для оптимизации и позволяет функции выполняться параллельно в случае, когда она вызывается из команды SELECT.
• PIPELINED — указывает, что результат табличной функции должен возвращаться в итеративном режиме с помощью команды PIPE ROW.
• RESULT_CACHE — указывает, что входные значения и результат вызова функции должен быть сохранен в кэше результатов.
• ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к функции программными модулями, перечисленными в круглых скобках.
• AGGREGATE — используется при определении агрегатных функций.
• EXTERNAL — определяет функцию с «внешней реализацией» — то есть написанную на языке C.
• объявления — объявления локальных идентификаторов этой функции. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
• исполняемые команды — команды, выполняемые функцией при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
• обработчики исключений — необязательные обработчики исключений для функции. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END.

Возвращаемый тип

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

Oracle PL / SQL — пример функции CREATE

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

1. Пользовательские функции аналогичны процедурам. Разница лишь в том, что функция всегда возвращает значение.
2. Пользовательские функции могут использоваться как часть выражения SQL.

Заметка
Oracle SQL не поддерживает вызов функций с логическими параметрами или возвратами.

1. Функция — Получить отформатированный адрес

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

1.1 Создать таблицы и функции.

1.2 Вставьте данные для тестирования.

1.3 Показать данные.

1.4 Вызов функции. Мы можем вызвать функцию многими способами. Здесь сначала мы будем называть это в SELECT statement , И тогда мы будем называть это из dbms_output.put_line

2. Функция — Проверьте строку палиндрома

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

Палиндром — это слово, фраза, число или другая последовательность символов, которая читается так же, как и вперед, например, мадам или гоночный автомобиль.
https://en.wikipedia.org/wiki/Palindrome

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

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

3. Функция — Рассчитать подоходный налог

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

PL / SQL – Функции

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

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

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

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

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

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

Функция должна содержать инструкцию возврата .

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

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

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

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

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

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

Функция должна содержать инструкцию возврата .

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

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

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

пример

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

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

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

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

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

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

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

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

пример

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

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

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

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

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

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

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