Oracle функция без параметров
Условные функции - это такие функции, которые могут возвращать разные результаты в зависимости от выполнения тех или иных условий.
В качестве тестовых данным будем использовать таблицу из части про функции для работы с NULL.
Вызов функции
Функция может вызываться из любой части исполняемой команды PL/SQL , где допускается использование выражения. Следующие примеры демонстрируют вызовы функций, определения которых приводились в предыдущем разделе.
- Присваивание переменной значения по умолчанию вызовом функции:
- Использование функции-члена для объектного типа в условии:
- Вставка в запись строки с информацией о книге:
- Вызов пользовательской функции PL/SQL из запроса:
Вызов написанной вами функции из команды CREATE VIEW с использованием выражения CURSOR для передачи результирующего набора в аргументе функции:
В PL/SQL , в отличие от некоторых других языков программирования, невозможно просто проигнорировать возвращаемое значение функции, даже если оно не представляет интереса для вас. Например, для следующего вызова функции:
будет выдана ошибка PLS-00221: ‘FAVORITE_NICKNAME’ is not a procedure or is undefined .
Функцию нельзя использовать так, как если бы она была процедурой.
Заголовок функции
Часть определения функции, предшествующая ключевому слову IS , называется заголовком функции, или сигнатурой. Заголовок предоставляет программисту всю информацию, необходимую для вызова функции:
- Имя функции.
- Модификаторы определения и поведения функции (детерминированность, возможность параллельного выполнения и т. д.).
- Список параметров (если имеется).
- Тип возвращаемого значения.
В идеале программист при виде заголовка функции должен понять, что делает эта функция и как она вызывается.
Заголовок упоминавшейся ранее функции total_sales выглядит так:
Он состоит из типа модуля, имени и списка из двух параметров и возвращаемого типа NUMBER . Это означает, что любое выражение или команда PL/SQL , в которых задействовано числовое значение, может вызвать total_sales для получения этого значения. Пример:
Условные функции в WHERE части
Условные функции спокойно могут использоваться в WHERE-части запроса, как и другие функции:
В примере выше выражение case вернет 0 в тех случаях, когда логин пользователя не будет логином администратора. Сразу после окончания выражения мы сравниваем его с нулем, тем самым получая только не-администраторов. Подобные способы, конечно, лучше не использовать, а вместо них прибегать к классическому варианту написания запроса, который будет более понятным:
При группировке условные функции, как и все другие, должны быть полностью продублированы в GROUP BY , использовать псевдоним колонки не получится:
Запрос выше выведет статистику о количестве логинов пользователей с определенной длиной - меньше пяти символов, больше пяти символов, или с длиной логина ровно в пять символов.
Последний запрос можно переписать с использованием подзапроса, чтобы не дублировать CASE в GROUP BY :
Функция PL/SQL представляет собой модуль, который возвращает значение командой RETURN (вместо аргументов OUT или IN OUT ). В отличие от вызова процедуры, который представляет собой отдельный оператор, вызов функции всегда является частью исполняемого оператора, то есть включается в выражение или служит в качестве значения по умолчанию, присваиваемого переменной при объявлении.
Возвращаемое функцией значение принадлежит к определенному типу данных. Функция может использоваться вместо выражения, которое имеет тот же тип данных, что и возвращаемое ею значение.
Функции играют важную роль в реализации модульного подхода. К примеру, реализацию отдельного бизнес-правила или формулы в приложении рекомендуется оформить в виде функции. Вместо того чтобы писать один и тот же запрос снова и снова («Получить имя работника по идентификатору», «Получить последнюю строку заказа из таблицы order для заданного идентификатора компании» и т. д.), поместите его в функцию и вызовите эту функцию в нужных местах. Такой код создает меньше проблем с отладкой, оптимизацией и сопровождением.
Некоторые программисты предпочитают вместо функций использовать процедуры, возвращающие информацию через список параметров. Если вы принадлежите к их числу, проследите за тем, чтобы ваши бизнес-правила, формулы и однострочные запросы были скрыты в процедурах!
В приложениях, которые не определяют и не используют функции, со временем обычно возникают трудности с сопровождением и расширением.
Вызов процедуры
Процедура вызывается как исполняемая команда PL/SQL. Другими словами, ее вызов должен заканчиваться точкой с запятой (;) и может предшествовать другим командам SQL либо PL/SQL (если таковые имеются) в исполняемом разделе блока PL/SQL или следовать за ними:
Если процедура не имеет параметров, она может вызываться с пустыми круглыми скобками или без них:
Метка END
Вы можете указать имя процедуры за завершающим ключевым словом END :
Имя служит меткой, явно связывающей конец программы с ее началом. Привыкните к использованию метки END . Она особенно полезна для процедур, занимающих несколько страниц или входящих в серию процедур и функций в теле пакета.
Определение подпрограмм PL/SQL в командах SQL (12.1 и выше)
Разработчики уже давно могли вызывать свои функции PL/SQL из команд SQL . Допустим, я создал функцию с именем BETWNSTR , которая возвращает подстроку с заданной начальной и конечной позицией:
Функция может использоваться в запросах следующим образом:
Эта возможность позволяет «расширить» язык SQL функциональностью, присущей конкретному приложению, и повторно использовать алгоритмы (вместо копирования). К недостаткам выполнения пользовательских функций в SQL следует отнести необходимость переключения контекста между исполнительными ядрами SQL и P L/SQL . Начиная с Oracle Database 12c вы можете определять функции и процедуры PL/SQL в секции WITH подзапроса, чтобы затем использовать их как любую встроенную или пользовательскую функцию. Эта возможность позволяет консолидировать функцию и запрос в одной команде:
Главное преимущество такого решения — повышение производительности, так как при таком определении функций затраты на переключение контекста с ядра SQL н а ядро PL/SQL существенно снижаются. С другой стороны, за него приходится расплачиваться возможностью повторного использования логики в других частях приложения.
Впрочем, для определения функций в секции WITH есть и другие причины. В SQL можно вызвать пакетную функцию, но нельзя сослаться на константу, объявленную в пакете (если только команда SQL не выполняется внутри блока PL/SQL ), как показано в следующем примере:
Классическое обходное решение основано на определении функции в пакете и ее последующем вызове:
Для простого обращения к значению константы в команде SQL потребуется слишком много кода и усилий. Начиная с версии 12.1 это стало излишним — достаточно создать функцию в секции WITH :
Функции PL/SQL , определяемые в SQL, также пригодятся при работе с автономными базами данных, доступными только для чтения. Хотя в таких базах данных невозможно создавать «вспомогательные» функции PL/SQL , вы можете определять их прямо в запросах.
Механизм WITH FUNCTION стал чрезвычайно полезным усовершенствованием языка SQL. Тем не менее каждый раз, когда вы планируете его использование, стоит задать себе один вопрос: «Потребуется ли эта функциональность в нескольких местах приложения?»
Если вы ответите на него положительно, следует решить, компенсирует ли выигрыш по производительности от применения WITH FUNCTION потенциальные потери от копирования и вставки этой логики в нескольких командах SQL.
Учтите, что в версии 12.1 в блоках PL/SQL невозможно выполнить статическую команду select с секцией with function . Безусловно, это выглядит очень странно, и я уверен, что в 12.2 такая возможность появится, но пока при попытке выполнения следующего кода будет выдана ошибка:
Помимо конструкции WITH FUNCTION, в версии 12.1 также появилась директива UDF для улучшения быстродействия функций PL/SQL, выполняемых из SQL.
Процедура представляет собой модуль, выполняющий одно или несколько действий. Поскольку вызов процедуры в PL/SQL является отдельным исполняемым оператором, блок кода PL/SQL может состоять только из вызова процедуры. Процедуры относятся к числу ключевых компонентов модульного кода, обеспечивающих оптимизацию и повторное использование программной логики.
Общий формат процедуры PL/SQL выглядит так:
Основные элементы этой структуры:
- схема — имя схемы, которой будет принадлежать процедура (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания процедуры в другой схеме.
- имя — имя процедуры.
- параметр — необязательный список параметров, которые применяются для передачи данных в процедуру и возврата информации из процедуры в вызывающую программу.
- AUTHID — определяет, с какими разрешениями будет вызываться процедура: создателя (владельца) или текущего пользователя. В первом случае процедура выполняется с правами создателя, во втором — с правами вызывающего.
- объявления — объявления локальных идентификаторов этой процедуры. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
- ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к процедуре программными модулями, перечисленными в круглых скобках.
- исполняемые команды — команды, выполняемые процедурой при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
- обработчики исключений — необязательные обработчики исключений для процедуры. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END .
На рис. 1 показан код процедуры apply_discount , который содержит все четыре раздела, характерных для именованных блоков PL/SQL.
Рис. 1. Код процедуры
Требования к вызываемым функциям
Чтобы определяемую программистом функцию PL/SQL можно было вызывать из команд SQL , она должна отвечать следующим требованиям:
- Все параметры функции должны иметь режим использования IN . Режимы IN OUT и OUT в функциях, встраиваемых в SQL-код, недопустимы.
- Типы данных параметров функций и тип возвращаемого значения должны распознаваться сервером Oracle. PL/SQL дополняет основные типы Oracle, которые пока не поддерживаются базой данных. Речь идет о типах BOOLEAN , BINARY_INTEGER , ассоциативных массивах, записях PL/SQL и определяемых программистом подтипах.
- Функция должна храниться в базе данных. Функция, определенная на стороне клиента, не может вызываться в командах SQL, так как SQL не сможет разрешить ссылку на эту функцию.
По умолчанию пользовательские функции, вызываемые в SQL , оперируют данными одной строки, а не столбца (как агрегатные функции SUM , MIN и AVG ). Чтобы создать агрегатные функции, вызываемые в SQL , необходимо использовать интерфейс ODCIAggregate , который является частью среды Oracle Extensibility Framework . За подробной информацией по этой теме обращайтесь к документации Oracle.
Метка END
Вы можете указать имя функции за завершающим ключевым словом END :
Имя служит меткой, явно связывающей конец программы с ее началом. Привыкните к использованию метки END. Она особенно полезна для функций, занимающих несколько страниц или входящих в серию процедур и функций в теле пакета.
Тело функции
В теле функции содержится код, необходимый для реализации этой функции; тело состоит из объявления, исполняемого раздела и раздела исключений этой функции. Все, что следует за ключевым словом IS , образует тело функции.
Как и в случае с процедурами, разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите функцию командой END . Если объявления отсутствуют, команда BEGIN просто следует непосредственно за ключевым словом IS.
Исполняемый раздел функции должен содержать команду RETURN . Функция откомпилируется и без него, но если выполнение функции завершится без выполнения команды RETURN, Oracle выдаст ошибку: ORA-06503: PL/SQL: Function returned without value .
Эта ошибка не выдается, если функция передает наружу свое необработанное исключение.
Ограничения для пользовательских функций, вызываемых в SQL
С целью защиты от побочных эффектов и непредсказуемого поведения хранимых процедур Oracle не позволяет им выполнять следующие действия:
- Хранимые функции не могут модифицировать таблицы баз данных и выполнять команды DDL ( CREATE TABLE , DROP INDEX и т. д.), INSERT , DELETE , MERGE и UPDATE . Эти ограничения ослабляются, если функция определена как автономная транзакция. В таком случае любые вносимые ею изменения осуществляются независимо от внешней транзакции, в которой выполняется запрос.
- Хранимые функции, которые вызываются удаленно или в параллельном режиме, не могут читать или изменять значения переменных пакета. Сервер Oracle не поддерживает побочные эффекты, действие которых выходит за рамки сеанса пользователя.
- Хранимая функция может изменять значения переменных пакета, только если она вызывается в списке выборки либо в предложении VALUES или SET . Если хранимая функция вызывается в предложении WHERE или GROUP BY , она не может изменять значения переменных пакета.
- До выхода Oracle8 пользовательские функции не могли вызывать процедуру RAISE_ APPLICATION_ERROR .
- Хранимая функция не может вызывать другой модуль (хранимую процедуру или функцию), не соответствующий приведенным требованиям.
- Хранимая функция не может обращаться к представлению, которое нарушает любое из предшествующих правил. Представлением (view) называется хранимая команда SELECT , в которой могут вызываться хранимые функции.
- До выхода Oracle11g для передачи параметров функциям могла использоваться только позиционная запись. Начиная с Oracle11g, допускается передача параметров по имени и смешанная запись.
Структура функции
Функция (рис. 1) имеет почти такую же структуру, как и процедура, не считая того, что ключевое слово RETURN в ней играет совершенно другую роль:
Основные элементы этой структуры:
- схема — имя схемы, которой будет принадлежать функция (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания функции в другой схеме.
- имя — имя функции.
- параметр — необязательный список параметров, которые применяются для передачи данных в функцию и возврата информации из нее в вызывающую программу.
- возвращаемый_тип — задает тип значения, возвращаемого функцией. Возвращаемый тип должен быть указан в заголовке функции; он более подробно рассматривается в следующем разделе.
- AUTHID — определяет, с какими разрешениями будет вызываться функция: создателя (владельца) или текущего пользователя. В первом случае (используется по умолчанию) применяется модель прав создателя, во втором — модель прав вызывающего.
- DETERMINISTIC — определяет функцию как детерминированную, то есть возвращаемое значение полностью определяется значениями ее аргументов. Если включить эту секцию, ядро SQL сможет оптимизировать выполнение функции при ее вызове в запросах.
- PARALLEL_ENABLE — используется для оптимизации и позволяет функции выполняться параллельно в случае, когда она вызывается из команды SELECT .
- PIPELINED — указывает, что результат табличной функции должен возвращаться в итеративном режиме с помощью команды PIPE ROW .
- RESULT_CACHE — указывает, что входные значения и результат вызова функции должен быть сохранен в кэше результатов. Эта возможность, появившаяся в Orade11g.
- ACCESSIBLE BY (Oracle Database 12c) — ограничивает доступ к функции программными модулями, перечисленными в круглых скобках.
- AGGREGATE — используется при определении агрегатных функций.
- EXTERNAL — определяет функцию с «внешней реализацией» — то есть написанную на языке C.
- объявления — объявления локальных идентификаторов этой функции. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
- исполняемые команды — команды, выполняемые функцией при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
Рис. 1. Код функции
- обработчики исключений — необязательные обработчики исключений для функции. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END .
На рис. 1 изображено строение функции PL/SQL и ее различных разделов. Обратите внимание: функция total_sales не имеет раздела исключений.
Команда RETURN
Ключевое слово RETURN обычно ассоциируется с функциями, поскольку они должны возвращать значения. Однако PL/SQL позволяет использовать команду RETURN в процедурах. Версия этой команды для процедур не принимает выражений и не может возвращать значения в вызывающий программный модуль — она просто прекращает выполнение процедуры и возвращает управление вызывающему коду.
Использовать эту разновидность RETURN не рекомендуется, поскольку в этом случае в процедуре появляются две и более точки выхода, а это усложняет логику выполнения. Избегайте использования RETURN и GOTO для обхода нормальной управляющей структуры в программных элементах.
в чем разница между функцией и процедурой в PL/SQL ?
процедура не имеет возвращаемого значения, тогда как функция.
обратите внимание, как функция имеет предложение return между списком параметров и ключевым словом "as". Это означает, что ожидается, что последний оператор внутри тела функции будет читать что-то вроде:
где my_varchar2_local_variable-это некоторый varchar2, который должен быть возвращен этой функцией.
функция может быть встроена в инструкцию SQL, например
что нельзя сделать с помощью хранимой процедуры. Архитектура оптимизатора запросов ограничивает то, что можно сделать с функциями в этом контексте, требуя, чтобы они были чистыми (т. е. одни и те же входы всегда производят один и тот же выход). Это ограничивает то, что можно сделать в функции, но позволяет использовать ее в строке запроса, если она определена как "чистая".
в противном случае, функция (не обязательно детерминированный) может возвращать переменную или результирующий набор. В случае функции, возвращающей результирующий набор, его можно объединить с каким-либо другим выбором в запросе. Однако вы не можете использовать недетерминированную функцию, подобную этой, в коррелированном подзапросе, поскольку оптимизатор не может предсказать, какой результирующий набор будет возвращен (это вычислительно сложно, как и проблема остановки).
хранимые процедуры и функции называются блоками, которые находятся в базе данных и могут выполняться по мере необходимости .
1.Хранимая процедура может дополнительно возвращать значения с помощью параметров out, но также может быть записана без возврата значения .Но функция должна возвращать значение
2.Хранимая процедура не может использоваться в инструкции select, где функции могут использоваться в select заявление.
практически говоря, я бы пошел на хранимую процедуру для определенной группы требований и функцию для общего требования ,которое может быть разделено между несколькими сценариями для g : сравнение между двумя строками или их обрезка или взятие последней части ,если у нас есть функция для этого ,мы могли бы глобально использовать ее для любого приложения, которое у нас есть
ниже приведены основные различия между процедурой и функцией,
- процедура называется блоком PL / SQL, который выполняет одну или несколько задач. где функция называется PL / SQL блок, который выполняет определенное действие.
- процедура может или не может возвращать значение, где функция должна возвращать одно значение.
- мы можем вызывать функции в инструкции Select, где в качестве процедуры мы не можем.
мертвым простым способом это делает этот смысл.
функции :
эти подпрограммы возвращают одно значение; в основном используется для вычисления и возвращает значение.
процедура :
эти подпрограммы не возвращает значение сразу; главным образом использованный для того чтобы выполнить действие.
Пример:
выполнение отдельного Процедура:
автономную процедуру можно вызвать двумя способами:
• С помощью EXECUTE ключевое слово * Вызов имени процедуры из блока PL/SQL
процедура также может быть вызвана из другого блока PL / SQL:
функция:
следующая программа вызывает функцию totalCustomers из другого блока
в нескольких словах - функция возвращает что-то. Вы можете использовать функцию в SQL query. Процедура является частью кода, чтобы сделать что-то с данными, но вы не можете вызвать процедуру из запроса, вы должны запустить ее в блоке PL/SQL.
функции должны возвращать значение, процедура может возвращать одно или несколько значений с помощью параметра OUT или вообще не возвращать значение.
функции могут быть вызваны из sql, где as процедуры не могут.
функции предназначены для вычисления, где, как процедуры для businiess логики.
процедура предварительно скомпилирована, функции-нет.
процедура поддерживает deffered разрешение имени, где в качестве функции выигрыш.
- мы можем вызвать хранимую процедуру внутри хранимой процедуры, функцию внутри функции, StoredProcedure внутри функции, но мы не можем вызвать функцию внутри хранимой процедуры.
- мы можем вызвать функцию внутри оператора SELECT.
- мы можем вернуть значение из функции без передачи выходного параметра в качестве параметра в хранимую процедуру.
вот в чем разница, которую я нашел. Пожалуйста, дайте мне знать, если есть .
Возвращаемый тип
Функция PL/SQL может возвращать данные практически любого типа, поддерживаемого PL/SQL , — от скаляров (единичных значений вроде даты или строки) до сложных структур: коллекций, объектных типов, курсорных переменных и т. д.
Несколько примеров использования RETURN :
- Возвращение строки:
- Возвращение числа функцией-членом объектного типа:
- Возвращение записи, имеющей ту же структуру, что и у таблицы books:
- Возвращение курсорной переменной с заданным типом REF CURSOR (базирующемся на типе записи):
Функции без параметров
Если функция не имеет параметров, ее вызов может записываться с круглыми скобками или без них. Следующий код демонстрирует эту возможность на примере вызова метода age объектного типа pet_t:
Заголовок процедуры
Часть определения процедуры, предшествующая ключевому слову IS, называется заголовком процедуры, или сигнатурой. Заголовок предоставляет программисту всю информацию, необходимую для вызова процедуры:
- Имя процедуры.
- Условие AUTHID (если имеется).
- Список параметров (если имеется).
- Список ACCESSIBLE BY (если имеется — новая возможность Oracle Database 12c).
В идеале программист при виде заголовка процедуры должен понять, что делает эта процедура и как она вызывается.
Заголовок процедуры apply_discount из предыдущего раздела выглядит так:
Он состоит из типа модуля, имени и списка из двух параметров.
Тело процедуры
В теле процедуры содержится код, необходимый для реализации этой процедуры; тело состоит из объявления, исполняемого раздела и раздела исключений этой процедуры. Все, что следует за ключевым словом IS, образует тело процедуры. Разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите процедуру командой END .
Непротиворечивость чтения и пользовательские функции
Модель непротиворечивости чтения в базе данных Oracle проста и понятна: после выполнения запрос «видит» данные в том состоянии, в котором они существовали (были зафиксированы в базе данных) на момент начала запроса, с учетом результатов изменений, вносимых командами DML текущей транзакции. Таким образом, если мой запрос был выполнен в 9:00 и продолжает работать в течение часа, даже если за это время другой пользователь внесет в данные изменения, они не отразятся в моем запросе.
Но если не принять специальных мер предосторожности с пользовательскими функциями в ваших запросах, может оказаться, что ваш запрос будет нарушать (по крайней мере на первый взгляд) модель непротиворечивости чтения базы данных Oracle. Чтобы понять этот аспект, рассмотрим следующую функцию и запрос, в котором она вызывается:
Таблица account содержит 5 миллионов активных строк, а таблица orders — 20 миллионов. Я запускаю запрос в 10:00, на его завершение уходит около часа. В 10:45 приходит некто, обладающий необходимыми привилегиями, удаляет все строки из таблицы orders и закрепляет транзакцию. По правилам модели непротиворечивости чтения Oracle сеанс, в котором выполняется запрос, не должен рассматривать эти строки как удаленные до завершения запроса. Но при следующем вызове из запроса функция total_sales не найдет ни одной строки и вернет NULL — и так будет происходить до завершения запроса.
При выполнении запросов из функций, вызываемых в коде SQL, необходимо внимательно следить за непротиворечивостью чтения. Если эти функции вызываются в продолжительных запросах или транзакциях, вероятно, вам стоит выполнить следующую команду для обеспечения непротиворечивости чтения между командами SQL текущей транзакции: SET TRANSACTION READ ONLY
В этом случае необходимо позаботиться о наличии достаточного табличного пространства отмены.
Команда RETURN
В исполняемом разделе функции должна находиться по меньшей мере одна команда RETURN . Команд может быть и несколько, но в одном вызове функции должна выполняться только одна из них. После обработки команды RETURN выполнение функции прекращается, и управление передается вызывающему блоку PL/SQL .
Если ключевое слово RETURN в заголовке определяет тип данных возвращаемого значения, то команда RETURN в исполняемом разделе задает само это значение. При этом тип данных, указанный в заголовке, должен быть совместим с типом данных выражения, возвращаемого командой RETURN .
Любое допустимое выражение
Команда RETURN может возвращать любое выражение, совместимое с типом, обозначенным в секции RETURN . Это выражение может включать вызовы других функций, сложные вычисления и даже преобразования данных. Все следующие примеры использования RETURN допустимы:
Вы также можете возвращать сложные типы данных — экземпляры объектных типов, коллекции и записи.
Выражение в команде RETURN вычисляется в момент выполнения RETURN . При возврате управления в вызывающий блок также передается результат вычисленного выражения.
множественные команды RETURN
В функции total_sales на рис. 2 я использую две разные команды RETURN для обработки разных ситуаций в функции: если из курсора не удалось получить информацию, возвращается NULL (не нуль). Если же от курсора было получено значение, оно возвращается вызывающей программе. В обоих случаях команда RETURN возвращает значение: в одном случае NULL , в другом — переменную return_value.
Конечно, наличие нескольких команд RETURN в исполняемом разделе функции разрешено, однако лучше ограничиться одной командой RETURN, размещаемой в последней строке исполняемого раздела. Причины объясняются в следующем разделе.
RETURN как последняя исполняемая команда
В общем случае команду RETURN желательно делать последней исполняемой командой; это лучший способ гарантировать, что функция всегда возвращает значение. Объявите переменную с именем return_value (которое четко указывает, что в переменной будет храниться возвращаемое значение функции), напишите весь код вычисления этого значения, а затем в самом конце функции верните значение return_value командой RETURN :
Переработанная версия логики на рис. 2, в которой решена проблема множественных команд RETURN , выглядит так:
Остерегайтесь исключений! Помните, что инициированное исключение может «перепрыгнуть» через последнюю команду прямо в обработчик. Если обработчик исключения не содержит команды RETURN , то будет выдана ошибка ORA-06503: Function returned without value независимо от того, как было обработано исходное исключение.
Oracle позволяет вызывать пользовательские функции в коде SQL . Фактически это позволяет адаптировать язык SQL под требования конкретных приложений.
Каждый раз, когда исполнительное ядро SQL вызывает функцию PL/SQL, оно должно «переключаться» на исполнительное ядро PL/SQL. При многократном вызове функции затраты на переключение контекстов могут быть весьма значительными.
DECODE
Функция DECODE в общем случае имеет следующий вид:
Первым DECODE принимает параметр, значение которого будет сравниваться по очереди со списком значений, и в случае, когда он совпадет с одним из перечисленных, будет возвращен соответствующий результат. Если совпадений не найдено, будет возвращено значение по-умолчанию. Если значение по-умолчанию не указано, будет возвращен NULL .
Аргументы могут быть числового, строкового типа, или датой.
DECODE может сравнивать NULL значения:
Перед сравнением Oracle автоматически приводит первый параметр и все значения к типу первого значения в списке параметров. Результат функции автоматически приводится к типу первого результата в списке параметров. Если первый результат в списке - NULL , результат функции DECODE будет приведен к строковому типу VARCHAR2 .
Например, следующий запрос не выполнится из-за ошибки ORA-01722: invalid number :
Тип возвращаемого значения определяется первым результатом в списке параметров, в данном случае - числом “10”. Но значение по-умолчанию имеет строковый тип, что и приводит к ошибке. Чтобы ошибки не было, нужно либо значение по-умолчанию заменить на число, либо заменить число 10 на любой строковый тип.
Любой из следующих запросов отработает без ошибок:
В качестве проверяемого значения не обязательно должна быть колонка таблицы. В следующем примере проверяем длину логина пользователя:
Максимальное количество параметров в функции DECODE - 255.
Предыдущий пример, только с использованием вложенного DECODE :
Здесь в качестве значения по-умолчанию выступает еще один DECODE .
На практике вложенных decode следует избегать, ровно как и decode с большим количеством параметров.
Одна из распространенных ошибок - использовать DECODE для того, чтобы преобразовать какие-либо флаги в их строковые эквиваленты (при их большом количестве):
Для подобных ситуаций лучше создать отдельную таблицу с кодом статуса и его строковым значением, и использовать соединения:
Выражение CASE во многом похоже на DECODE , но обладает большими возможностями. Данное выражение позволяет реализовать полноценную условную логику в SQL запросе.
CASE может использоваться в двух вариантах - простом(англ. simple case expression) и поисковом(англ. searched case expression).
Простой CASE по принципу работы идентичен DECODE :
user_flag здесь - псевдоним для столбца. Само выражение начинается с ключевого слова case и заканчивается ключевым словом end .
Как и в DECODE , для проверяемого значения начинают производиться сравнения со значениями в блоках WHEN . При первом же совпадении функция завершает работу и возвращает соответствующий результат (указанный после then ). В случае, если ни одного совпадения не было найдено, возвращается значение, указанное в блоке ELSE . Если значение по-умолчанию не указано, будет возвращен NULL .
Searched case expression, в отличие от simple case expression, является куда более мощным инструментом. В отличие от последнего, в searched case expression в блоках when указываются условия, а не просто значения для сравнения:
В общем и целом, лучше использовать DECODE для небольших, простых сравнений, и CASE для более сложных, т.к. он лучше читается.
Читайте также: