Вариационная функция Oracle
Обновлено: 21.11.2024
CREATE FUNCTION определяет новую функцию. CREATE OR REPLACE FUNCTION либо создаст новую функцию, либо заменит существующее определение. Чтобы иметь возможность определить функцию, пользователь должен иметь привилегию USAGE для языка.
Если указано имя схемы, функция создается в указанной схеме. В противном случае он создается в текущей схеме. Имя новой функции не должно совпадать ни с одной существующей функцией с теми же типами входных аргументов в той же схеме. Однако функции с разными типами аргументов могут иметь общее имя (это называется перегрузка).
Чтобы заменить текущее определение существующей функции, используйте СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ. Невозможно изменить имя или типы аргументов функции таким образом (если бы вы попытались, вы фактически создали бы новую, отдельную функцию). Кроме того, CREATE OR REPLACE FUNCTION не позволит вам изменить тип возвращаемого значения существующей функции. Чтобы сделать это, вы должны удалить и воссоздать функцию. (При использовании параметров OUT это означает, что вы не можете изменить типы любых параметров OUT, кроме как путем удаления функции.)
Когда CREATE OR REPLACE FUNCTION используется для замены существующей функции, право собственности и разрешения функции не меняются. Всем остальным свойствам функций присваиваются значения, указанные или подразумеваемые в команде. Вы должны владеть функцией, чтобы заменить ее (в том числе быть членом роли-владельца).
Если вы удалите, а затем создадите заново функцию, новая функция не будет той же сущностью, что и старая; вам придется отказаться от существующих правил, представлений, триггеров и т. д., которые ссылаются на старую функцию. Используйте CREATE OR REPLACE FUNCTION, чтобы изменить определение функции, не нарушая объекты, которые ссылаются на функцию. Кроме того, ALTER FUNCTION можно использовать для изменения большинства вспомогательных свойств существующей функции.
Пользователь, создавший функцию, становится ее владельцем.
Параметры
Имя (необязательно дополненное схемой) создаваемой функции.
Режим аргумента: IN, OUT, INOUT или VARIADIC. Если опущено, по умолчанию используется IN. Только аргументы OUT могут следовать за аргументом VARIADIC. Кроме того, аргументы OUT и INOUT нельзя использовать вместе с нотацией RETURNS TABLE.
Имя аргумента. Некоторые языки (включая PL/pgSQL, но в настоящее время не SQL) позволяют использовать имя в теле функции. Для других языков имя входного аргумента является просто дополнительной документацией, если речь идет о самой функции; но вы можете использовать имена входных аргументов при вызове функции для улучшения читаемости (см. Раздел 4.3). В любом случае имя выходного аргумента важно, поскольку оно определяет имя столбца в типе строки результата. (Если вы не укажете имя выходного аргумента, система выберет имя столбца по умолчанию.)
Тип(ы) данных аргументов функции (необязательно с уточнением схемы), если они есть. Типы аргументов могут быть базовыми, составными или доменными, а также могут ссылаться на тип столбца таблицы.
В зависимости от языка реализации также может быть разрешено указывать "псевдотипы", такие как cstring. Псевдотипы указывают на то, что фактический тип аргумента либо указан не полностью, либо находится вне набора обычных типов данных SQL.
Тип столбца обозначается записью table_name.column_name%TYPE. Использование этой функции иногда может помочь сделать функцию независимой от изменений в определении таблицы.
Выражение, используемое в качестве значения по умолчанию, если параметр не указан. Выражение должно быть приведено к типу аргумента параметра. Только входные (включая INOUT) параметры могут иметь значение по умолчанию. Все входные параметры, следующие за параметром со значением по умолчанию, также должны иметь значения по умолчанию.
Тип возвращаемых данных (возможно, дополненный схемой). Тип возвращаемого значения может быть базовым, составным или типом домена или может ссылаться на тип столбца таблицы. В зависимости от языка реализации также может быть разрешено указывать "псевдотипы", такие как cstring. Если функция не должна возвращать значение, укажите void в качестве возвращаемого типа.
При наличии параметров OUT или INOUT предложение RETURNS можно опустить. Если он присутствует, он должен соответствовать типу результата, подразумеваемому выходными параметрами: RECORD, если выходных параметров несколько, или того же типа, что и один выходной параметр.
Модификатор SETOF указывает, что функция вернет набор элементов, а не один элемент.
Тип столбца обозначается записью table_name.column_name%TYPE.
Имя выходного столбца в синтаксисе RETURNS TABLE. Фактически это еще один способ объявления именованного параметра OUT, за исключением того, что RETURNS TABLE также подразумевает RETURNS SETOF.
Тип данных выходного столбца в синтаксисе RETURNS TABLE.
Имя языка, на котором реализована функция. Может быть SQL, C, внутреннее или имя пользователя. - определенный процедурный язык. Для обратной совместимости имя может быть заключено в одинарные кавычки.
WINDOW указывает, что функция является оконной функцией, а не простой функцией. В настоящее время это полезно только для функций, написанных на C. Атрибут WINDOW нельзя изменить при замене существующего определения функции.
Эти атрибуты информируют оптимизатор запросов о поведении функции. Можно указать не более одного варианта. Если ничего из этого не появляется, по умолчанию используется VOLATILE.
IMMUTABLE указывает, что функция не может изменять базу данных и всегда возвращает один и тот же результат при одинаковых значениях аргументов; то есть он не выполняет поиск в базе данных или иным образом не использует информацию, не представленную непосредственно в его списке аргументов. Если указан этот параметр, любой вызов функции с полностью постоянными аргументами может быть немедленно заменен значением функции.
STABLE указывает, что функция не может изменять базу данных и что при сканировании одной таблицы она будет неизменно возвращать один и тот же результат для одних и тех же значений аргументов, но ее результат может меняться в зависимости от операторов SQL. Это подходящий выбор для функций, результаты которых зависят от поиска в базе данных, переменных параметров (таких как текущий часовой пояс) и т. д. (Это не подходит для триггеров ПОСЛЕ, которые хотят запрашивать строки, измененные текущим команда.) Также обратите внимание, что семейство функций current_timestamp считается стабильным, поскольку их значения не меняются в рамках транзакции.
VOLATILE указывает, что значение функции может измениться даже в рамках одного сканирования таблицы, поэтому оптимизация невозможна. Относительно немногие функции базы данных являются изменчивыми в этом смысле; некоторые примеры: random(), currval(), timeofday(). Но обратите внимание, что любая функция, имеющая побочные эффекты, должна быть классифицирована как изменчивая, даже если ее результат вполне предсказуем, чтобы предотвратить оптимизацию вызовов; пример: setval().
Для получения дополнительной информации см. Раздел 35.6.
ВЫЗЫВАЕТСЯ ПРИ ВВОДЕ NULL
ВОЗВРАЩАЕТ NULL ПРИ ВВОДЕ NULL
STRICT
CALLED ON NULL INPUT (значение по умолчанию) указывает, что функция будет вызываться обычным образом, когда некоторые из ее аргументов равны null. В этом случае автор функции обязан проверить наличие нулевых значений, если это необходимо, и отреагировать соответствующим образом.
ВОЗВРАЩАЕТ NULL ПРИ ВВОДЕ NULL или STRICT указывает, что функция всегда возвращает значение null всякий раз, когда любой из ее аргументов имеет значение null. Если этот параметр указан, функция не выполняется при наличии нулевых аргументов; вместо этого автоматически предполагается нулевой результат.
[ ВНЕШНИЙ ] ОПРЕДЕЛИТЕЛЬ БЕЗОПАСНОСТИ
[ ВНЕШНИЙ ] ОПРЕДЕЛИТЕЛЬ БЕЗОПАСНОСТИ
SECURITY INVOKER указывает, что функция должна выполняться с привилегиями пользователя, который ее вызывает. Это значение по умолчанию. SECURITY DEFINER указывает, что функция должна выполняться с правами создавшего ее пользователя.
Ключевое слово EXTERNAL разрешено для соответствия SQL, но необязательно, так как, в отличие от SQL, эта возможность применяется ко всем функциям, а не только к внешним.
Положительное число, указывающее расчетную стоимость выполнения функции в единицах cpu_operator_cost. Если функция возвращает набор, это стоимость возвращаемой строки. Если стоимость не указана, для языка C и внутренних функций предполагается 1 единица, а для функций на всех остальных языках - 100 единиц. Большие значения заставляют планировщика избегать оценки функции чаще, чем это необходимо.
Положительное число, указывающее предполагаемое количество строк, которое планировщик должен ожидать от функции. Это разрешено только в том случае, если функция объявлена возвращающей набор. Предположение по умолчанию — 1000 строк.
Предложение SET приводит к тому, что указанному параметру конфигурации присваивается указанное значение при входе в функцию, а затем восстанавливается его предыдущее значение при выходе из функции. SET FROM CURRENT сохраняет значение параметра, которое является текущим, когда выполняется CREATE FUNCTION, как значение, которое будет применяться при входе в функцию.
Если к функции присоединено предложение SET, то действие команды SET LOCAL, выполняемой внутри функции для той же переменной, ограничивается функцией: предыдущее значение параметра конфигурации по-прежнему восстанавливается при выходе из функции. Однако обычная команда SET (без LOCAL) переопределяет предложение SET, так же как и для предыдущей команды SET LOCAL<. Команда /tt>: эффекты такой команды сохранятся после выхода из функции, если только текущая транзакция не будет отброшена.
См. SET и главу 18 для получения дополнительной информации о разрешенных именах и значениях параметров.
Строковая константа, определяющая функцию; значение зависит от языка. Это может быть имя внутренней функции, путь к объектному файлу, команда SQL или текст на процедурном языке.
Часто бывает полезно использовать долларовые кавычки (см. раздел 4.1.2.4) для записи строки определения функции, а не обычный синтаксис одинарных кавычек. Без долларовых кавычек любые одинарные кавычки или обратные косые черты в определении функции должны быть экранированы путем их удвоения.
Эта форма предложения AS используется для динамически загружаемых функций языка C, когда имя функции в исходном коде языка C не совпадает с именем функции SQL. Строка obj_file — это имя файла, содержащего динамически загружаемый объект, а link_symbol — символ ссылки функции, то есть имя функции в исходном коде на языке C. код. Если символ ссылки опущен, предполагается, что он совпадает с именем определяемой функции SQL.
Когда повторяющиеся вызовы CREATE FUNCTION относятся к одному и тому же объектному файлу, этот файл загружается только один раз за сеанс. Чтобы выгрузить и перезагрузить файл (возможно, во время разработки), запустите новый сеанс.
Эквивалентно STRICT или ВОЗВРАЩАЕТ NULL ПРИ ВВОДЕ NULL.
isCachable является устаревшим эквивалентом IMMUTABLE; он по-прежнему принимается из соображений обратной совместимости.
Имена атрибутов не чувствительны к регистру.
Дополнительную информацию о функциях записи см. в Разделе 35.3.
Перегрузка
PostgreSQL допускает перегрузку функций; то есть одно и то же имя может использоваться для нескольких разных функций, если они имеют разные типы входных аргументов. Однако имена C всех функций должны быть разными, поэтому вы должны давать перегруженным функциям C разные имена C (например, использовать типы аргументов как часть имен C).
Две функции считаются одинаковыми, если они имеют одинаковые имена и типы входных аргументов, игнорируя любые параметры OUT. Таким образом, например, эти объявления конфликтуют:
Функции, имеющие разные списки типов аргументов, не будут считаться конфликтующими во время создания, но если указаны значения по умолчанию, они могут конфликтовать при использовании. Например, рассмотрим
Вызов foo(10) завершится ошибкой из-за неоднозначности в отношении того, какую функцию следует вызывать.
Примечания
При замене существующей функции функцией CREATE OR REPLACE FUNCTION существуют ограничения на изменение имен параметров. Вы не можете изменить имя, уже присвоенное какому-либо входному параметру (хотя вы можете добавить имена к параметрам, которые раньше не имели). Если имеется более одного выходного параметра, вы не можете изменить имена выходных параметров, потому что это изменит имена столбцов анонимного составного типа, описывающего результат функции. Эти ограничения введены для того, чтобы существующие вызовы функции не переставали работать при ее замене.
Если функция объявлена STRICT с аргументом VARIADIC, проверка строгости проверяет, что массив переменных переменных в целом не равен нулю. Функция все равно будет вызываться, если массив содержит пустые элементы.
Примеры
Вот несколько простых примеров, которые помогут вам начать работу. Дополнительную информацию и примеры см. в Разделе 35.3.
Увеличить целое число, используя имя аргумента, в PL/pgSQL:
Вернуть запись, содержащую несколько выходных параметров:
То же самое можно сделать более подробно с явно названным составным типом:
Еще один способ вернуть несколько столбцов — использовать функцию TABLE:
Однако функция TABLE отличается от предыдущих примеров тем, что фактически возвращает набор записей, а не одну запись.
Написание безопасных функций SECURITY DEFINER
Поскольку функция SECURITY DEFINER выполняется с привилегиями создавшего ее пользователя, необходимо следить за тем, чтобы эта функция не использовалась не по назначению. В целях безопасности параметр search_path должен исключать любые схемы, доступные для записи ненадежным пользователям. Это предотвращает создание злоумышленниками объектов, маскирующих объекты, используемые функцией.Особенно важной в этом отношении является схема временной таблицы, которая по умолчанию просматривается первой и обычно доступна для записи любым пользователем. Надежное расположение может быть обеспечено принудительным поиском временной схемы в последнюю очередь. Для этого напишите pg_temp как последнюю запись в search_path. Эта функция иллюстрирует безопасное использование:
До версии PostgreSQL 8.3 параметр SET был недоступен, поэтому старые функции могут содержать довольно сложную логику для сохранения, установки и восстановления search_path. Для этой цели гораздо проще использовать параметр SET.
Еще один момент, о котором следует помнить, это то, что по умолчанию права на выполнение предоставляются PUBLIC для вновь созданных функций (дополнительную информацию см. в разделе GRANT). Часто вы захотите ограничить использование функции определения безопасности только некоторыми пользователями. Для этого вы должны отозвать привилегии PUBLIC по умолчанию, а затем выборочно предоставить привилегию выполнения. Чтобы не было окна, в котором новая функция доступна всем, создайте ее и установите привилегии в рамках одной транзакции. Например:
Совместимость
Команда CREATE FUNCTION определена в SQL:1999 и более поздних версиях. Версия PostgreSQL похожа, но не полностью совместима. Атрибуты не переносимы, равно как и разные доступные языки.
Для совместимости с некоторыми другими системами баз данных argmode может быть записан до или после argname. Но только первый способ соответствует стандарту.
Стандарт SQL не определяет значения параметров по умолчанию. Синтаксис с ключевым словом DEFAULT взят из Oracle и в некоторой степени соответствует духу стандарта: SQL/PSM использует его для значений переменных по умолчанию. Синтаксис с = используется в T-SQL и Firebird.
6.3 Функции с различными аргументами
В предыдущих реализациях вы не могли указать типы параметров, ожидаемые функцией, но ISO C рекомендует вам использовать прототипы именно для этого. Для поддержки таких функций, как printf(), синтаксис прототипов включает в себя специальный разделитель с многоточием (…). Поскольку реализации могут потребоваться необычные действия для обработки различного количества аргументов, ISO C требует, чтобы все объявления и определения такой функции включали в себя терминатор с многоточием.
Поскольку часть параметров «…» не имеет имени, специальный набор макросов, содержащийся в stdarg.h, предоставляет функции доступ к этим аргументам. Более ранние версии таких функций должны были использовать аналогичные макросы, содержащиеся в varargs.h.
Предположим, что функция, которую вы хотите написать, представляет собой обработчик ошибок с именем errmsg(), который возвращает void и единственным фиксированным параметром которого является int, который указывает подробности сообщения об ошибке. За этим параметром может следовать имя файла, номер строки или и то, и другое. За этими элементами следуют формат и аргументы, аналогичные параметрам printf(), которые определяют текст сообщения об ошибке.
Для компиляции этого примера более ранними компиляторами требуется широкое использование макроса __STDC__, который определен только для компиляторов ISO C. Объявление функции в соответствующем заголовочном файле:
В файле, содержащем определение errmsg(), старый и новый стили могут оказаться сложными. Во-первых, включаемый заголовок зависит от системы компиляции:
stdio.h включен, поскольку мы вызываем fprintf() и vfprintf() позже.
Далее идет определение функции. Идентификаторы va_alist и va_dcl являются частью старого интерфейса varargs.h.
Поскольку старый механизм переменных аргументов не позволял задавать какие-либо фиксированные параметры, доступ к ним должен осуществляться перед изменяющейся частью. Кроме того, из-за отсутствия имени для части параметров «…» новый макрос va_start() имеет второй аргумент, wick — это имя параметр, который стоит непосредственно перед терминатором «…».
В качестве расширения Oracle Solaris Studio ISO C позволяет объявлять и определять функции без фиксированных параметров, например:
Для таких функций va_start() следует вызывать с пустым вторым аргументом, например:
Следующий пример представляет собой тело функции:
Макросы va_arg() и va_end() работают одинаково для версий старого стиля и ISO C. Поскольку va_arg() изменяет значение ap, вызов vfprintf() не может быть:
Определения для макросов FILENAME, LINENUMBER и WARNING предположительно содержатся в том же заголовке, что и объявление errmsg ().
Он уже есть, просто сделайте его доступным для определенных пользователем единиц. Посмотрите на определение этого пакета и обратите внимание на многоточие:
Это позволяет использовать код в форме format_message('%s %s','первая строка','вторая строка'). Это может быть полезно во многих других контекстах. К сожалению, если вы попытаетесь использовать многоточие в своем собственном коде, вы получите следующую ошибку:
SQL> СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ my_parameter_array_func (ФОРМАТ В VARCHAR2, аргумент
с. ) ВОЗВРАТИТЬ VARCHAR2 КАК
Предупреждение: функция создана с ошибками компиляции.
Ошибки для FUNCTION MY_PARAMETER_ARRAY_FUNC:
1/55 PLS-00999: ограничение реализации (может быть временным) многоточие не
разрешено в этом контексте
Активный · Последнее обновление: 5 июля 2016 г., 17:36
Комментарии
Я бы предпочел добавить коллекцию в качестве параметра, но я вижу некоторые функции, где это полезно.
Я имею в виду, что есть несколько внутренних функций оракула, поведение которых, вероятно, реализовано с помощью многоточия.
К примеру, COALESCE.
Одним из обходных путей может быть использование параметров NULL по умолчанию или перегрузка определяемой пользователем функции несколькими параметрами. Список не будет бесконечным, но вы можете легко охватить кейсы с 3-10 параметрами.
Я только что заметил, что синтаксис с многоточием не включает тип данных. Мне интересно, использует ли Oracle внутри ANYTYPE?
Я бы совсем не возражал против этой функции, но я подозреваю, что для ее правильной реализации потребуется анонимный тип массива для любого скалярного типа, например. NUMBER(10)[] или VARCHAR2(50)[]. Обычно это было бы полезно (т. е. типы структурных массивов, а не номинальные типы VARRAY/TABLE). Как только это будет сделано и будут созданы все инструменты для итерации и управления такими массивами, varargs станет простым синтаксическим сахаром, как в Java
Я бы совсем не возражал против этой функции, но для того, чтобы правильно ее реализовать, я подозреваю, что сначала потребуется анонимный тип массива для любого скалярного типа, например. NUMBER(10)[] или VARCHAR2(50)[]. Обычно это было бы полезно (т. е. типы структурных массивов, а не номинальные типы VARRAY/TABLE). Как только это будет сделано и будут созданы все инструменты для итерации и управления такими массивами, varargs станет простым синтаксическим сахаром, как в Java
Мне нравится идея анонимных типов массивов. Возможно, это заслуживает отдельной идеи. Что-то вроде:
my_emps emp.empno%table := emp.empno%table(1,2,3);
которую компилятор мог бы расширить внутренне, таким образом вырезав строку объявления одноразового типа.
Существуют разные подходы к моделированию переменных аргументов. Однако, начиная с Oracle 8, существует лучший метод, который разработчики часто не рассматривают: использование простого VARRAY для переноса переменной части кода.
Если вы знаете много языков программирования, то вы
уже привыкли к возможности объявлять и использовать функции с переменным
количеством аргументов, где количество и тип аргументов не известны во время
проектирования. Были разные подходы (даже в собственных
пакетах Oracle), которые пытались имитировать эту функциональность, просто создавая как можно больше
аргументов по умолчанию.См.
листинг A для примера.
Этот подход работает какое-то время, но становится трудно
как сопровождающему функцию, так и вызывающей стороне обрабатывать этот список
аргументов. Я видел и другие подходы, которые пытаются закодировать список аргументов в
одной строке, а затем анализировать строку внутри функции. Начиная с
Oracle 8, существует лучший способ, который разработчики часто не рассматривают: использование
простого VARRAY для переноса переменной части:
Есть некоторые дополнительные преимущества использования этого метода. Например,
так как это коллекция; группы аргументов функций могут храниться
в таблицах базы данных. Функция переменного аргумента может быть объявлена для получения
типа VARRAY, который при вызове команды выглядит почти как переменный список аргументов:
В качестве более полного примера,
листинг B представляет собой PL/SQL-версию классической функции C sprintf,
реализованную на чистом PL/SQL. Во-первых, нам нужен вспомогательный пакет для очистки
кода и реализации каждого типа форматирования (он не на 100% совместим с C
). В листинге C показано,
как я создаю функцию для реализации sprintf вне пакета. Наконец,
листинг D
содержит PL/SQL sprintf
в действии. Код предназначен только для иллюстративных целей, и его все еще нужно доработать, чтобы он работал точно так же, как ANSI C-версия
sprintf из stdio.h.
Информационный бюллетень Oracle от TechRepublic посвящен автоматизации утилит Oracle, созданию оповещений базы данных, решению задач с ориентированным графом и многому другому. Подпишитесь автоматически сегодня!
Читайте также: