Параметр по умолчанию oracle
До смешного мелкий вопрос, и все же гугл плохо реагирует на спец знаки, выдавая вариации на тему "больше или равно" >=, но тут у нас какой-то переход и я не понимаю смысл (пытаюсь разобрать чужой код).
Есть вызов функции:
Вот и возникает вопрос, что это за морфема в месте ввода параметров, где имена параметров отличаются только регистром?
ищите по строке oracle named parameters по крайней мере в большинстве языков такой знак означает именно задание имени именованного параметра или что нибудь из той же оперы. первый раз вижу в оракле, но судя по выдаче гугла это то о чем я подумал
Пока в предложенных ответах не упомянается явно, как это собственно называется: positional notation vs. named notation, что быстро находится гуглом.
Передача значений NULL
При передаче NULL в качестве параметра подстановки — например, как в команде:
Что же делать, если вам потребуется передать в динамический код значение NULL ? Это можно сделать двумя способами.
Во-первых, значение можно скрыть в переменной, для чего проще всего использовать неинициализированную переменную подходящего типа:
Во-вторых, с помощью функции преобразования типа можно явно преобразовать NULL в типизированное значение:
Функция PL/SQL представляет собой модуль, который возвращает значение командой RETURN (вместо аргументов OUT или IN OUT ). В отличие от вызова процедуры, который представляет собой отдельный оператор, вызов функции всегда является частью исполняемого оператора, то есть включается в выражение или служит в качестве значения по умолчанию, присваиваемого переменной при объявлении.
Возвращаемое функцией значение принадлежит к определенному типу данных. Функция может использоваться вместо выражения, которое имеет тот же тип данных, что и возвращаемое ею значение.
Функции играют важную роль в реализации модульного подхода. К примеру, реализацию отдельного бизнес-правила или формулы в приложении рекомендуется оформить в виде функции. Вместо того чтобы писать один и тот же запрос снова и снова («Получить имя работника по идентификатору», «Получить последнюю строку заказа из таблицы order для заданного идентификатора компании» и т. д.), поместите его в функцию и вызовите эту функцию в нужных местах. Такой код создает меньше проблем с отладкой, оптимизацией и сопровождением.
Некоторые программисты предпочитают вместо функций использовать процедуры, возвращающие информацию через список параметров. Если вы принадлежите к их числу, проследите за тем, чтобы ваши бизнес-правила, формулы и однострочные запросы были скрыты в процедурах!
В приложениях, которые не определяют и не используют функции, со временем обычно возникают трудности с сопровождением и расширением.
Тело функции
В теле функции содержится код, необходимый для реализации этой функции; тело состоит из объявления, исполняемого раздела и раздела исключений этой функции. Все, что следует за ключевым словом IS , образует тело функции.
Как и в случае с процедурами, разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите функцию командой END . Если объявления отсутствуют, команда BEGIN просто следует непосредственно за ключевым словом IS.
Исполняемый раздел функции должен содержать команду RETURN . Функция откомпилируется и без него, но если выполнение функции завершится без выполнения команды RETURN, Oracle выдаст ошибку: ORA-06503: PL/SQL: Function returned without value .
Эта ошибка не выдается, если функция передает наружу свое необработанное исключение.
Режимы передачи параметров
При определении параметра также указывается допустимый способ его применения. Он задается с помощью одного из трех указанных в таблице режимов.
Режим | Предназначение | Использование параметров |
IN | Только для чтения | Значение параметра может применяться, но не может быть изменено в модуле. Если режим параметра не задан, используется режим IN |
OUT | Только для записи | В модуле можно присвоить значение параметру, но нельзя использовать его. Впрочем, это «официальное» определение — на самом деле Oracle позволяет читать значение параметра OUT в подпрограмме |
IN OUT | Для чтения и записи | В модуле можно использовать и изменять значение параметра |
Режим параметра указывается непосредственно после его имени, но перед типом данных и необязательным значением по умолчанию. В следующем заголовке процедуры задействованы все три режима передачи параметров:
Процедура predict_activity принимает дату последнего действия ( last_date ) и описание этого действия ( task_desc_inout ). Возвращает она два значения: описание действия (возможно, модифицированное) и дату следующего действия (next_date_out). Поскольку параметр task_desk_inout передается в режиме IN OUT , программа может читать и изменять его значение.
Режим IN
Режим IN используется по умолчанию; если режим параметра не задан, параметр автоматически считается определенным в режиме IN. Тем не менее я рекомендую всегда указывать режим параметра, чтобы предполагаемое использование было явно указано в коде.
В заголовке программы параметрам IN могут присваиваться значения по умолчанию (см. раздел «Значения по умолчанию»).
Фактическим значением параметра IN может быть переменная, именованная константа, литерал или сложное выражение. Все следующие вызовы display_title допустимы:
А если вам потребуется передать данные из своей программы? В таком случае используйте параметр OUT или IN OUT — или рассмотрите возможность преобразования процедуры в функцию.
Режим OUT
Как вы, вероятно, уже поняли, параметр OUT по смыслу противоположен параметру IN. Параметры OUT могут использоваться для возвращения значений из программы вызывающему блоку PL/SQL. Параметр OUT сходен с возвращаемым значением функции, но он включается в список параметров, и количество таких параметров не ограничено (строго говоря, PL/SQL разрешает использовать до 64 000 параметров, но на практике это вряд ли можно считать ограничением).
В программе параметр OUT работает как неинициализированная переменная. Собственно, параметр OUT вообще не содержит никакого значения до успешного завершения вызванной программы (если только вы не использовали ключевое слово NOCOPY . Во время выполнения программы все операции присваивания параметру OUT в действительности выполняются с внутренней копией параметра. Когда программа успешно завершается и возвращает управление вызывающему блоку, значение локальной копии перемещается в параметр OUT. После этого значение становится доступным в вызывающем блоке PL/SQL.
У правил, относящихся к параметрам OUT , есть несколько практических следствий:
- Параметрам OUT нельзя задавать значения по умолчанию. Значение параметра OUT может задаваться только в теле модуля.
- Все операции присваивания параметрам OUT отменяются при инициировании исключения в программе. Так как значение параметра OUT присваивается только в случае успешного завершения программы, все промежуточные присваивания игнорируются. Если обработчик не перехватит исключение и не присвоит значение параметру OUT, параметр останется неизменным. Переменная сохранит значение, которое она имела до вызова программы.
- Фактический параметр, соответствующий формальному параметру OUT, не может быть константой, литералом или выражением. Иначе говоря, он должен поддерживать присваивание.
Как упоминается выше в таблице, Oracle позволяет прочитать значение параметра OUT в подпрограмме. Это значение изначально всегда равно NULL , но после его присваивания в подпрограмме оно становится «видимым», как показывает следующий сценарий:
Режим IN OUT
В параметре IN OUT можно передавать значения программе и возвращать их на сторону вызова (либо исходное, неизменное значение, либо новое значение, заданное в программе). На параметры IN OUT распространяются два ограничения параметров OUT:
- Параметр IN OUT не может быть константой, литералом или выражением.
- Фактический параметр IN OUT должен быть переменной. Он не может быть константой, литералом или выражением, потому что эти форматы не могут использоваться PL/SQL в качестве приемника для размещения исходящих значений.
Других ограничений для параметров IN OUT нет.
Параметры IN OUT могут использоваться в обеих сторонах присваивания, потому что они работают как инициализированные переменные. PL/SQL не теряет значение параметра IN OUT в начале выполнения программы. Это значение может использоваться в программе там, где это необходимо.
Процедура combine_and_format_names объединяет имя и фамилию в заданном формате (« LAST, FIRST » или « FIRST LAST »). Процедура получает имя и фамилию, которые преобразуются к верхнему регистру. В ней продемонстрированы все три режима параметров:
Параметры имени и фамилии должны задаваться в режиме IN OUT . Параметр full_name_ out должен быть параметром OUT , потому что процедура возвращает результат объединения имени и фамилии. Наконец, параметр name_format_in, содержащий форматную строку, объявляется в режиме IN, потому что он описывает способ форматирования, но никак не изменяется в процедуре.
Каждый режим параметров имеет свои характеристики и предназначение. Тщательно выбирайте режим, назначаемый вашим параметрам, чтобы они правильно использовались в модулях.
Определяйте формальные параметры в режимах OUT и IN OUT только в процедурах. Функции должны возвращать всю свою информацию исключительно командой RETURN . Выполнение этой рекомендации упростит понимание и использование ваших подпрограмм. Кроме того, функции с параметрами OUT и IN OUT не могут вызываться из команд SQL .
Структура функции
Функция (рис. 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 не имеет раздела исключений.
3 ответа 3
В Oracle PL\SQL при вызовах процедур и функций можно явно указать какому параметру какое значение передается.
- Если у нас есть несколько перегруженных функций, с разными типами параметров, то иногда это единственный способ вызвать нужную нам функцию.
- Можно указывать параметры в произвольном порядке
- Улучшает читаемость кода
Правильно ли я понимаю, что если в параметр при вызове вписано
Существуют три способа задать параметры в вызове подпрограмм:
Positional notation - актуальные параметры задаются в том же порядке, в котором они объявлены при декларации.
Named notation - актуальные параметры задаются в любом порядке используя синтаксис:
formal => actual
Mixed notation - сначала задаются параметры используя positional notation, затем используется named notation для остальных параметров.
Отличие в регистре в примере вопроса не играет никакой роли, до символа => формальный параметр (formal), а после актуальный (actual). Их имена могут полностью совпадать.
Named notation имеет ряд преимуществ по сравнению с positional и mixed notation:
Лучшая читаемость кода.
Порядок указания параметров не важен и не может быть неправильным.
Вызов подпрограммы должен быть изменён только если в декларации добавлен новый обязательный формальный параметр.
Позволяет избежать трудно диагностируемых ошибок при нарушении порядка указания актуальных параметров, особенно если они литералы.
и является рекомендуемым для вызова подпрограмм при написании нового или рефакторинге старого кода, и особенно тех, которые поддерживаются кем-то другим.
Для передачи информации между модулем и вызывающим блоком PL/SQL используются параметры.
Параметры модуля, являющиеся частью его заголовка (или сигнатуры), являются не менее важными компонентами модуля, чем находящиеся в нем исполняемые команды. Заголовок программы иногда называется контрактом между автором и пользователями. Конечно, автор должен позаботиться о том, чтобы модуль выполнял свою задачу. Но ведь модули создаются именно для того, чтобы их можно было вызывать повторно — в идеале более чем из одного модуля. Если список параметров плохо составлен, то другим программистам будет сложно применять такой модуль. В таком случае уже будет неважно, насколько хорошо он написан.
Многие разработчики не уделяют должного внимания наборам параметров своих модулей. Следующие рекомендации помогут вам сделать правильный выбор:
- Количество параметров. Если процедура или функция имеет слишком мало параметров, это снижает ее универсальность; с другой стороны, слишком большое количество параметров усложняет ее повторное использование. Конечно, количество параметров в основном определяется требованиями программы, но существуют разные варианты их определения (например, несколько параметров можно объединить в одну запись).
- Типы параметров. При выборе типа необходимо учитывать, для каких целей будут использоваться параметры: только для чтения, только для записи, для чтения и записи.
- Имена параметров. Параметрам следует присваивать простые имена, отражающие их назначение в модуле.
- Значения по умолчанию. Когда параметру следует задать значение по умолчанию, а когда нужно заставить программиста ввести определенное значение?
PL/SQL предоставляет много средств эффективного планирования. В этом разделе представлены все элементы определения параметров.
Заголовок функции
Часть определения функции, предшествующая ключевому слову IS , называется заголовком функции, или сигнатурой. Заголовок предоставляет программисту всю информацию, необходимую для вызова функции:
- Имя функции.
- Модификаторы определения и поведения функции (детерминированность, возможность параллельного выполнения и т. д.).
- Список параметров (если имеется).
- Тип возвращаемого значения.
В идеале программист при виде заголовка функции должен понять, что делает эта функция и как она вызывается.
Заголовок упоминавшейся ранее функции total_sales выглядит так:
Он состоит из типа модуля, имени и списка из двух параметров и возвращаемого типа NUMBER . Это означает, что любое выражение или команда PL/SQL , в которых задействовано числовое значение, может вызвать total_sales для получения этого значения. Пример:
Значения по умолчанию
Как было показано в предыдущих примерах, параметрам IN можно задать значения по умолчанию. Если параметр IN имеет значение по умолчанию, включать этот параметр в вызов программы не обязательно. Значение по умолчанию параметра вычисляется и используется программой только в том случае, если параметр не был включен в список при вызове. Конечно, для всех параметров IN OUT фактические параметры должны включаться в список. Значение по умолчанию определяется для параметра так же, как для объявляемой переменной. Предусмотрены два способа задания значений по умолчанию — с ключевым словом DEFAULT и с оператором присваивания ( := ):
Значения по умолчанию позволяют вызывать программы с разным количеством фактических параметров. Программа использует значения по умолчанию для всех незаданных параметров, а для параметров в списке значения по умолчанию заменяются указанными значениями. Несколько примеров разных вариантов использования связывания по позиции:
В первом вызове оба параметра задаются явно. Во втором вызове задается только первый фактический параметр, поэтому born_at_in присваивается текущая дата и время. В третьем вызове параметры вообще не указаны, поэтому круглые скобки отсутствуют (то же относится и к последнему вызову, в который включены пустые круглые скобки). Оба значения по умолчанию используются в теле процедуры.
Чтобы пропустить начальные параметры, имеющие значения по умолчанию, необходимо использовать связывание по имени. Включая имена формальных параметров, можно указать только те параметры, для которых необходимо передать значения:
Что ж давайте покончим с этими параметрами. Осталось рассмотреть тип применения параметров при передаче их в процедуру. Рассмотрим пример. Создадим процедуру следующего вида:
Ничего особенного она проделывать не будет, но зато с явным энтузиазмом будет принимать аж четыре параметра! Компилируем:
Все прошло успешно, вот и славно! А вот теперь запишем такой анонимный блок:
Запускаем и получаем:
Смотрите, мы объявили четыре параметра и передали их нашей функции, в данном конкретном случае мы применили так называемое - "позиционное представление" (positional notation)! Такой тип передачи параметров применяется во всех языках программирования, например в таком как C и C++! Я сразу рекомендую вам пользоваться именно таким способом передачи! Хотя это еще далеко не все!
Запишем следующий анонимный блок:
В данном случае я использовал - "именное представление" (named notation), которое PL/SQL унаследовал от языка Ada. В данном случае указываются как формальные, так и фактические параметры. Идем далее. Запишем следующий анонимный блок:
В этом примере хорошо видно, что именное представление позволяет изменить порядок следования параметров и вызывать их, так как вам того хотелось бы. Хотя может это не всегда оправдано! :) Далее запишем следующий анонимный блок:
Здесь хорошо видно, что позиционное и именное представление можно комбинировать, и использовать совместно. Хотя я думаю, что так легче запутать код, чтобы потом никто нифига не понял! :) Хотя это все оставляется на усмотрение программиста и стиль написания кода! Так же смею заметить, что - чем больше параметров в процедуре, тем сложнее ее вызывать и тем труднее убеждаться в наличии всех требуемых параметров. Если необходимо передать в процедуру или получить из нее достаточно большое число параметров, то рекомендуется определить тип записи, полями которой будут эти параметры. Затем можно использовать единственный параметр имеющий тип записи. В PL/SQL - так же не установлено явное ограничение на количество передаваемых в процедуру параметров.
Значение параметров по умолчанию.
Дело в том, что как и все переменные формальные параметры процедуры могут иметь значения по умолчанию. В таком случае значение параметру, имеющему такое определение можно не передавать. Если же фактический параметр все-таки передан, то принимается именно его значение. Итак, значение по умолчанию указывается вот так:
Давайте перепишем нашу первую процедуру с параметрами по умолчанию:
К слову, используйте параметры по умолчанию в конце списка всех параметров процедуры, при этом будет возможность использовать как именное, так и позиционное представление. Вот и все с параметрами процедур, теперь вам стало понятнее как все это работает в PL/SQL! Пробуйте!
Мы рассмотрели несколько примеров использования параметров с NDS . Давайте познакомимся с различными правилами и специальными ситуациями, которые могут вам встретиться при передаче параметров.
SQL -запросу могут передаваться только выражения (литералы, переменные, сложные выражения), заменяющие формальные параметры в строке значениями данных. Не допускается передача имен элементов схемы (таблиц, столбцов и т. д.) или целых фрагментов кода SQL (например, условий WHERE ), которые должны строиться посредством конкатенации.
Допустим, вы хотите создать процедуру для очистки заданного представления или таблицы. Первая версия может выглядеть примерно так:
При упрощении процедуры до следующего вида:
Почему же в NDS (как, впрочем, и в пакете DBMS_SQL ) имеется такое ограничение? При передаче строки команде EXECUTE IMMEDIATE исполняющее ядро должно прежде всего выполнить синтаксический анализ команды, чтобы убедиться в правильности ее определения. PL/SQL может определить, что следующая команда определена правильно, даже не зная значения параметра :xyz:
Но корректность следующий команды PL/SQL проверить не сможет:
По этой причине в данной ситуации необходимо использовать конкатенацию:
Связывание формальных и фактических параметров в PL/SQL
Каким образом при выполнении программы определяется, какому формальному параметру должен соответствовать фактический параметр? PL/SQL предлагает два метода установления такого соответствия:
- по позиции (неявное связывание);
- по имени (явное связывание с указанием имени формального параметра и обозначения = >).
Позиционное связывание
Во всех приводившихся ранее примерах применялось связывание параметров в соответствии с их позицией. При использовании этого способа PL/SQL принимает во внимание относительные позиции параметров, то есть N-й фактический параметр в вызове программы связывается с N-м формальным параметром в заголовке программы. В следующем примере PL/SQL связывает первый фактический параметр : order. company_id с первым формальным параметром company_id_in , а второй фактический параметр 'N' — со вторым формальным параметром status_in :
Метод позиционного связывания параметров, безусловно, является более наглядным и понятным (рис. 3).
Рис. 3. Позиционное связывание параметров
связывание по имени
Чтобы установить соответствие параметров по имени, следует при вызове подпрограммы явно связать формальный параметр с фактическим. Для этого используется комбинация символов =>:
Поскольку имя формального параметра указано явно, PL/SQL уже не нужно учитывать порядок параметров. Поэтому в данном случае при вызове модуля не обязательно перечислять параметры в порядке их следования в заголовке. Функцию total_sales можно вызывать любым из двух способов:
В одном вызове можно комбинировать оба метода связывания фактических и формальных параметров:
При этом все «позиционные» параметры должны быть перечислены перед «именованными», как в приведенном примере. Позиционному методу необходима начальная точка для отсчета позиций, которой может быть только первый параметр. Если разместить «именованные» параметры перед «позиционными», PL/SQL собьется со счета. Оба вызова total_sales , приведенные ниже, недопустимы. В первой команде «именованные» параметры указываются до «позиционных», а во второй используется позиционная запись, но параметры перечислены в неверном порядке. В этом случае PL/SQL пытается преобразовать 'N' к типу NUMBER (для параметра company_id ):
Преимущества связывания по имени
Возникает резонный вопрос: зачем использовать связывание по имени? У него есть два основных преимущества:
- Повышение информативности. При использовании связывания по имени вызов программы содержит сведения о формальных параметрах, с которыми ассоциируются фактические значения. Если вы не знаете, что делают модули, вызываемые приложением, наличие имен формальных параметров помогает понять назначение конкретного программного вызова. В некоторых средах разработки именно по этой причине связывание по имени считается стандартным. Эта причина особенно актуальна в том случае, когда имена формальных параметров строятся по схеме с присоединением суффикса режима. Только взглянув на вызов процедуры или функции, разработчик сразу видит, в каком направлении передаются те или иные данные.
- Гибкость при описании параметров. Вы имеете возможность размещать параметры в удобном для работы порядке (конечно, это не значит, что при вызове аргументы можно перечислять так, как вам заблагорассудится!) и включать в список только те из них, которые действительно необходимы. В сложных приложениях иногда создаются процедуры с десятками параметров. Но как вы знаете, любой параметр, значение которого определяется по умолчанию, при вызове модуля может быть опущен. Разработчик может передать процедуре только те значения, которые необходимы для решения текущей задачи.
Давайте посмотрим, как реализовать все эти преимущества в программе. Возьмем для примера следующий заголовок:
Анализ списка параметров приводит нас к следующим выводам:
- Минимальное количество аргументов, которые должны передаваться business_as_ usual , равно 3. Чтобы определить его, сложите количество параметров IN, не имеющих значения по умолчанию, с количеством параметров OUT и IN OUT.
- При использовании связывания по позиции программа может вызываться с четырьмя или пятью аргументами, потому что последний параметр объявлен в режиме IN со значением по умолчанию.
- Для хранения значений, возвращаемых параметрами OUT и IN OUT, понадобятся как минимум две переменные.
С таким списком параметров программа может вызываться несколькими способами:
- Связывание только по позиции: задаются все фактические параметры. Обратите внимание, как трудно вспомнить, какой формальный параметр связывается с каждым из этих значений (и каков его смысл):
- Связывание только по позиции: минимальное количество фактических параметров. Понять смысл вызова все еще нелегко:
- Связывание только по имени с сохранением исходного порядка. Обратите внимание: смысл вызова business_as_usual вполне понятен и не требует пояснений:
- Все параметры IN пропускаются, и для них используются значения по умолчанию (другая важная возможность связывания по имени):
- Связывание по имени с изменением порядка следования фактических параметров и передачей неполного списка:
- Объединение связывания по имени и по позиции. Список параметров начинается со связывания по позиции, а после перехода на связывание по имени вернуться к позиционному связыванию уже не удастся:
Как видите, механизм передачи аргументов в PL/SQL весьма гибок. Как правило, связывание по имени способствует написанию кода, который лучше читается и создает меньше проблем с сопровождением. От вас потребуется совсем немного: найти и записать имена параметров.
Квалификатор режима параметра NOCOPY
PL/SQL предоставляет возможность изменить определение параметра при помощи конструкции NOCOPY . NOCOPY требует, чтобы компилятор PL/SQL не создавал копии аргументов OUT и IN — и в большинстве случаев компилятор это требование удовлетворяет. NOCOPY используется прежде всего для повышения эффективности передачи больших конструкций (например, коллекций) в аргументах IN OUT .
Функции без параметров
Если функция не имеет параметров, ее вызов может записываться с круглыми скобками или без них. Следующий код демонстрирует эту возможность на примере вызова метода age объектного типа pet_t:
Команда 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 независимо от того, как было обработано исходное исключение.
Возвращаемый тип
Функция PL/SQL может возвращать данные практически любого типа, поддерживаемого PL/SQL , — от скаляров (единичных значений вроде даты или строки) до сложных структур: коллекций, объектных типов, курсорных переменных и т. д.
Несколько примеров использования RETURN :
- Возвращение строки:
- Возвращение числа функцией-членом объектного типа:
- Возвращение записи, имеющей ту же структуру, что и у таблицы books:
- Возвращение курсорной переменной с заданным типом REF CURSOR (базирующемся на типе записи):
Вызов функции
Функция может вызываться из любой части исполняемой команды PL/SQL , где допускается использование выражения. Следующие примеры демонстрируют вызовы функций, определения которых приводились в предыдущем разделе.
- Присваивание переменной значения по умолчанию вызовом функции:
- Использование функции-члена для объектного типа в условии:
- Вставка в запись строки с информацией о книге:
- Вызов пользовательской функции PL/SQL из запроса:
Вызов написанной вами функции из команды CREATE VIEW с использованием выражения CURSOR для передачи результирующего набора в аргументе функции:
В PL/SQL , в отличие от некоторых других языков программирования, невозможно просто проигнорировать возвращаемое значение функции, даже если оно не представляет интереса для вас. Например, для следующего вызова функции:
будет выдана ошибка PLS-00221: ‘FAVORITE_NICKNAME’ is not a procedure or is undefined .
Функцию нельзя использовать так, как если бы она была процедурой.
Режимы передачи параметров в SQL
При передаче значений параметров команды SQL можно использовать один из трех режимов: IN ( только чтение, действует по умолчанию), OUT (только запись) или IN OUT (чтение и запись). При выполнении динамического запроса все параметры команды SQL , за исключением параметра в секции RETURNING , должны передаваться в режиме IN :
Параметры подстановки команды SQL , передаваемые в режимах OUT и IN OUT , используются прежде всего при выполнении динамического PL/SQL . В этом случае режимы передачи параметров соответствуют аналогичным режимам обыкновенных программ PL/SQL , а также использованию переменных в динамических блоках PL/SQL . Несколько общих рекомендаций, касающихся использования секции USING при выполнении динамического PL/SQL :
- В качестве параметра подстановки, передаваемого в режиме IN , может быть задан любой элемент соответствующего типа: литеральное значение, именованная константа, переменная или сложное выражение. Такой элемент сначала вычисляется, а затем передается в динамический блок PL/SQL .
- Для значения параметра динамической команды в режиме OUT или IN OUT следует объявить переменную.
- Значения можно подставлять только вместо тех параметров динамического блока PL/SQL , тип которых поддерживается SQL . Например, если параметр процедуры имеет тип BOOLEAN, его значение нельзя задать или считать с помощью секции USING .
Это ограничение частично снято в версии 12.1 и выше. Теперь разрешается подстановка многих типов PL/SQL , включая типы записей и коллекций, но подстановка BOOLEAN по-прежнему запрещена.
Давайте рассмотрим механизм передачи параметров на примерах. Вот заголовок процедуры с параметрами IN, OUT и IN OUT :
Пример блока с динамическим вызовом этой процедуры:
Поскольку процедура имеет четыре параметра, в секции USING также должно быть указано четыре элемента. Для первых двух параметров, передаваемых в режиме IN , следует задать литеральные значения или выражения, а следующие два элемента должны быть именами переменных, так как для них заданы режимы OUT и IN OUT .
Но что, если два и более формальных параметра имеют одинаковые имена?
Метка END
Вы можете указать имя функции за завершающим ключевым словом END :
Имя служит меткой, явно связывающей конец программы с ее началом. Привыкните к использованию метки END. Она особенно полезна для функций, занимающих несколько страниц или входящих в серию процедур и функций в теле пакета.
Определение параметров
Формальные параметры определяются в списке параметров программы. Синтаксис определения параметров очень близок к синтаксису объявления переменных в разделе объявлений блока PL/SQL . Впрочем, существуют два важных различия : во-первых, для параметров определяется режим передачи, которого нет у объявлений переменных; во-вторых, объявление параметра должно быть неограниченным.
Ограниченное объявление устанавливает некоторые ограничения для значений, которые могут присваиваться переменной, объявленной с этим типом. Например, следующее объявление переменной company_name ограничивает переменную 60 символами:
При объявлении параметра ограничивающая часть опускается:
Формальные и фактические параметры
Очень важно понимать различия между формальными и фактическими параметрами. Формальные параметры представляют собой имена, объявленные в списке параметров заголовка модуля, тогда как фактические параметры — это значения и выражения, которые помещаются в список параметров при вызове модуля.
Различия между формальными и фактическими параметрами нам также поможет понять функция total_sales . Ее заголовок выглядит так:
Формальные параметры total_sales :
company_id_in — первичный ключ (идентификатор компании).
status_in — статус заказов, включаемых в вычисление.
Эти формальные параметры не существуют за пределами модуля. Их можно рассматривать как своего рода «заместителей» реальных значений, передаваемых модулю при его использовании в программе (то есть фактических параметров).
При вызове total_sales необходимо предоставить два аргумента, которыми могут быть переменные, константы или литералы (для параметров в режимах OUT и IN OUT это должны быть переменные). В следующем примере переменная company_id содержит первичный ключ, указывающий на запись компании. В первых трех вызовах total_sales функции передаются жестко запрограммированные значения статуса заказов, а в последнем вызове статус не указан; в этом случае функция присваивает параметру status_in значение по умолчанию, указанное в заголовке:
При вызове total_sales вычисляются значения всех фактических параметров. Результаты вычислений присваиваются соответствующим формальным параметрам внутри функции (обратите внимание: это относится только к параметрам IN и IN OUT; параметры режима OUT не копируются).
Формальные параметры и соответствующие им фактические параметры (указанные при вызове) должны относиться к одинаковым или совместимым типам данных. Во
многих ситуациях PL/SQL выполняет преобразования данных автоматически, однако лучше по возможности обходиться без неявных преобразований. Используйте такие функции, как TO_CHAR и TO_DATE , чтобы точно знать, какие данные получает модуль.
Дублирование формальных параметров
При выполнении динамической команды SQL связь между формальными и фактическими параметрами устанавливается в соответствии с их позициями. Однако интерпретация одноименных параметров зависит от того, какой код используется — SQL или PL/SQL .
- При выполнении динамической команды SQL (DML - или DDL-строки, не оканчивающейся точкой с запятой) параметр подстановки нужно задать для каждого формального параметра, даже если их имена повторяются.
- При выполнении динамического блока PL/SQL (строки, оканчивающейся точкой с запятой) нужно указать параметр подстановки для каждого уникального формального параметра.
Далее приведен пример динамической команды SQL с повторяющимися формальными параметрами. Особое внимание обратите на повторяющийся параметр подстановки val_in в секции USING :
А вот динамический блок PL/SQL с повторяющимися формальными параметрами — для него в секции USING параметр val_in задан только один раз:
Читайте также:
- Лучшие это навигаторы prestigio его мы благополучно и приобрели на следующий
- Бликует экран планшета что делать
- Не восстанавливается резервная копия iphone из itunes с компьютера
- Настройки дисплея nvidia недоступны в системе не обнаружена видеоплата nvidia
- Для чего служит фильтрация контента в компьютерной сети