Можно ли использовать функцию в функции oracle
Содержание статьи
Вызов пользовательских функций в SQL и PL/SQL
Oracle позволяет вызывать пользовательские функции в коде SQL. Фактически это позволяет адаптировать язык SQL под требования конкретных приложений.
Каждый раз, когда исполнительное ядро SQL вызывает функцию PL/SQL, оно должно «переключаться» на исполнительное ядро PL/SQL. При многократном вызове функции затраты на переключение контекстов могут быть весьма значительными.
Требования к вызываемым функциям
Чтобы определяемую программистом функцию 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.
Ограничения для пользовательских функций, вызываемых в 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, допускается передача параметров по имени и смешанная запись.
Непротиворечивость чтения и пользовательские функции
Модель непротиворечивости чтения в базе данных Oracle проста и понятна: после выполнения запрос «видит» данные в том состоянии, в котором они существовали (были зафиксированы в базе данных) на момент начала запроса, с учетом результатов изменений, вносимых командами DML текущей транзакции. Таким образом, если мой запрос был выполнен в 9:00 и продолжает работать в течение часа, даже если за это время другой пользователь внесет в данные изменения, они не отразятся в моем запросе.
Но если не принять специальных мер предосторожности с пользовательскими функциями в ваших запросах, может оказаться, что ваш запрос будет нарушать (по крайней мере на первый взгляд) модель непротиворечивости чтения базы данных Oracle. Чтобы понять этот аспект, рассмотрим следующую функцию и запрос, в котором она вызывается:
FUNCTION total_sales (id_in IN account.account_id%TYPE)
RETURN NUMBER
IS
CURSOR tot_cur
IS
SELECT SUM (sales) total
FROM orders
WHERE account_id = id_in
AND TO_CHAR (ordered_on, ‘YYYY’) = TO_CHAR (SYSDATE, ‘YYYY’);
tot_rec tot_cur%ROWTYPE;
BEGIN
OPEN tot_cur;
FETCH tot_cur INTO tot_rec;
CLOSE tot_cur;
RETURN tot_rec.total;
END;
SELECT name, total_sales (account_id)
FROM account
WHERE status = ‘ACTIVE’;
Таблица account содержит 5 миллионов активных строк, а таблица orders — 20 миллионов. Я запускаю запрос в 10:00, на его завершение уходит около часа. В 10:45 приходит некто, обладающий необходимыми привилегиями, удаляет все строки из таблицы orders и закрепляет транзакцию. По правилам модели непротиворечивости чтения Oracle сеанс, в котором выполняется запрос, не должен рассматривать эти строки как удаленные до завершения запроса. Но при следующем вызове из запроса функция total_sales не найдет ни одной строки и вернет NULL — и так будет происходить до завершения запроса.
При выполнении запросов из функций, вызываемых в коде SQL, необходимо внимательно следить за непротиворечивостью чтения. Если эти функции вызываются в продолжительных запросах или транзакциях, вероятно, вам стоит выполнить следующую команду для обеспечения непротиворечивости чтения между командами SQL текущей транзакции: SET TRANSACTION READ ONLY
В этом случае необходимо позаботиться о наличии достаточного табличного пространства отмены.
Определение подпрограмм PL/SQL в командах SQL (12.1 и выше)
Разработчики уже давно могли вызывать свои функции PL/SQL из команд SQL. Допустим, я создал функцию с именем BETWNSTR, которая возвращает подстроку с заданной начальной и конечной позицией:
FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN PLS_INTEGER
, end_in IN PLS_INTEGER
) продолжение #
RETURN VARCHAR2
IS
BEGIN
RETURN ( SUBSTR (
string_in, start_in, end_in — start_in + 1 ));
END;
Функция может использоваться в запросах следующим образом:
SELECT betwnstr (last_name, 3, 5)
FROM employees
Эта возможность позволяет «расширить» язык SQL функциональностью, присущей конкретному приложению, и повторно использовать алгоритмы (вместо копирования). К недостаткам выполнения пользовательских функций в SQL следует отнести необходимость переключения контекста между исполнительными ядрами SQL и PL/SQL. Начиная с Oracle Database 12c вы можете определять функции и процедуры PL/SQL в секции WITH подзапроса, чтобы затем использовать их как любую встроенную или пользовательскую функцию. Эта возможность позволяет консолидировать функцию и запрос в одной команде:
WITH
FUNCTION betwnstr (
string_in IN VARCHAR2,
start_in IN PLS_INTEGER,
end_in IN PLS_INTEGER)
RETURN VARCHAR2
IS
BEGIN
RETURN (SUBSTR (
string_in,
start_in,
end_in — start_in + 1));
END;
SELECT betwnstr (last_name, 3, 5)
FROM employees
Главное преимущество такого решения — повышение производительности, так как при таком определении функций затраты на переключение контекста с ядра SQL на ядро PL/SQL существенно снижаются. С другой стороны, за него приходится расплачиваться возможностью повторного использования логики в других частях приложения.
Впрочем, для определения функций в секции WITH есть и другие причины. В SQL можно вызвать пакетную функцию, но нельзя сослаться на константу, объявленную в пакете (если только команда SQL не выполняется внутри блока PL/SQL), как показано в следующем примере:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 year_number CONSTANT INTEGER := 2013;
4 END;
5 /
Package created.
SQL> SELECT pkg.year_number FROM employees
2 WHERE employee_id = 138
3 /
SELECT pkg.year_number FROM employees
*
ERROR at line 1:
ORA-06553: PLS-221: ‘YEAR_NUMBER’ is not a procedure or is undefined
Классическое обходное решение основано на определении функции в пакете и ее последующем вызове:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 FUNCTION year_number
4 RETURN INTEGER;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 IS
3 c_year_number CONSTANT INTEGER := 2013;
4
5 FUNCTION year_number
6 RETURN INTEGER
7 IS
8 BEGIN
9 RETURN c_year_number;
10 END;
11 END;
12 /
Package body created.
SQL> SELECT pkg.year_number
2 FROM employees
3 WHERE employee_id = 138
4 /
YEAR NUMBER
————
2013
Для простого обращения к значению константы в команде SQL потребуется слишком много кода и усилий. Начиная с версии 12.1 это стало излишним — достаточно создать функцию в секции WITH:
WITH
FUNCTION year_number
RETURN INTEGER
IS
BEGIN
RETURN pkg.year_number;
END;
SELECT year_number
FROM employees
WHERE employee_id = 138
Функции PL/SQL, определяемые в SQL, также пригодятся при работе с автономными базами данных, доступными только для чтения. Хотя в таких базах данных невозможно создавать «вспомогательные» функции PL/SQL, вы можете определять их прямо в запросах.
Механизм WITH FUNCTION стал чрезвычайно полезным усовершенствованием языка SQL. Тем не менее каждый раз, когда вы планируете его использование, стоит задать себе один вопрос: «Потребуется ли эта функциональность в нескольких местах приложения?»
Если вы ответите на него положительно, следует решить, компенсирует ли выигрыш по производительности от применения WITH FUNCTION потенциальные потери от копирования и вставки этой логики в нескольких командах SQL.
Учтите, что в версии 12.1 в блоках PL/SQL невозможно выполнить статическую команду select с секцией with function. Безусловно, это выглядит очень странно, и я уверен, что в 12.2 такая возможность появится, но пока при попытке выполнения следующего кода будет выдана ошибка:
SQL> BEGIN
2 WITH FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2)
3 RETURN VARCHAR2
4 IS
5 BEGIN
6 RETURN fname_in || ‘ ‘ || lname_in;
7 END;
8
9 SELECT LENGTH (full_name (first_name, last_name))
10 INTO c
11 FROM employees;
12
13 DBMS_OUTPUT.put_line (‘count = ‘ || c);
14 END;
15 /
WITH FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2)
*
ERROR at line 2:
ORA-06550: line 2, column 18:
PL/SQL: ORA-00905: missing keyword
Помимо конструкции WITH FUNCTION, в версии 12.1 также появилась директива UDF для улучшения быстродействия функций PL/SQL, выполняемых из SQL.
Вас заинтересует / Intresting for you:
Источник
Группировки и оконные функции в Oracle
Привет, Хабр! В компании, где я работаю, часто проходят (за мат извините) митапы. На одном из них выступал мой коллега с докладом об оконных функциях и группировках Oracle. Эта тема показалась мне стоящей того, чтобы сделать о ней пост.
С самого начала хотелось бы уточнить, что в данном случае Oracle представлен как собирательный язык SQL. Группировки и методы их применения подходят ко всему семейству SQL (который понимается здесь как структурированный язык запросов) и применимы ко всем запросам с поправками на синтаксис каждого языка.
Всю необходимую информацию я постараюсь кратко и доступно объяснить в двух частях. Пост скорее будет полезен начинающим разработчикам. Кому интересно — добро пожаловать под кат.
Часть 1: предложения Order by, Group by, Having
Здесь мы поговорим о сортировке — Order by, группировке — Group by, фильтрации — Having и о плане запроса. Но обо всем по-порядку.
Order by
Оператор Order by выполняет сортировку выходных значений, т.е. сортирует извлекаемое значение по определенному столбцу. Сортировку также можно применять по псевдониму столбца, который определяется с помощью оператора.
Преимущество Order by в том, что его можно применять и к числовым, и к строковым столбцам. Строковые столбцы обычно сортируются по алфавиту.
Сортировка по возрастанию применяется по умолчанию. Если хотите отсортировать столбцы по убыванию — используйте дополнительный оператор DESC.
Синтаксис:
SELECT column1, column2, … (указывает на название)
FROM table_name
ORDER BY column1, column2… ASC|DESC;
Давайте все рассмотрим на примерах:
В первой таблице мы получаем все данные и сортируем их по возрастанию по столбцу ID.
Во второй мы также получаем все данные. Сортируем по столбцу ID по убыванию, используя ключевое слово DESC.
В третьей таблице используется несколько полей для сортировки. Сначала идет сортировка по отделу. При равенстве первого оператора для полей с одинаковым отделом применяется второе условие сортировки; в нашем случае — это зарплата.
Все довольно просто. Мы можем задать более одного условия сортировки, что позволяет более грамотно сортировать выходные списки.
Group by
В SQL оператор Group by собирает данные, полученные из базы данных в определенных группах. Группировка разделяет все данные на логические наборы, что дает возможность выполнять статистические вычисления отдельно в каждой группе.
Этот оператор используется для объединения результатов выборки по одному или нескольким столбцам. После группировки будет только одна запись для каждого значения, использованного в столбце.
С использованием оператора SQL Group by тесно связано использование агрегатных функций и оператор SQL Having. Агрегатная функция в SQL — это функция, возвращающая какое-либо одно значение по набору значений столбца. Например: COUNT(), MIN(), MAX(), AVG(), SUM()
Синтаксис:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Group by стоит после условного оператора WHERE в запросе SELECT. По желанию можно использовать ORDER BY, чтобы отсортировать выходные значения.
Итак, опираясь на таблицу из предыдущего примера, нам нужно найти максимальную зарплату сотрудников каждого отдела. В итоговой выборке должно получиться название отдела и максимальная зарплата.
Решение 1 (без использования группировки):
SELECT DISTINCT
ie.department
ie.slary
FROM itx_employee ie
WHERE ie.salary = (
SELECT
max(ie1.salary)
FROM itx_employee ie1
WHERE ie.department = ie1.department
)
Решение 2 (с использованием группировки):
SELECT
department,
max(salary)
FROM itx_employee
GROUP BY department
В первом примере решаем задачу без использования группировки, но с использованием подселекта, т.е. в один селект вкладываем второй. Во втором решении используем группировку.
Второй пример вышел короче и читабельнее, хотя выполняет такие же функции, что и первый.
Как у нас работает Group by: сначала разбивает два отдела на группы qa и dev. Потом для каждого из них ищет максимальную зарплату.
Having
Having это инструмент фильтрации. Он указывает на результат выполнения агрегатных функций. Предложение Having используется в SQL там, где нельзя применить WHERE.
Если предложение WHERE определяет предикат для фильтрации строк, то Having используется после группировки для определения логичного предиката, фильтрующего группу по значениям агрегатных функций. Предложение необходимо для проверки значений, полученных при помощи агрегатных функций из групп строк.
Синтаксис:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
Сначала мы выводим отделы со средней зарплатой больше 4000. Затем выводим максимальную зарплату с применением фильтрации.
Решение 1 (без использования GROUP BY и HAVING):
SELECT DISTINCT
ie.department AS «DEPARTMENT»,
(
(SELECT
AVG(ie1.salary)
FROM itx_employee ie1
WHERE ie1.department = ie.department)
) AS «AVG SALARY»
FROM itx_employee ie
where (SELECT
AVG(ie1.salary)
FROM itx_employee ie1
WHERE ie1.department = ie.department) > 4000
Решение 2 (с использованием GROUP BY и HAVING):
SELECT
department,
AVG(salary)
FROM itx_employee
GROUP BY department
HAVING AVG(salary) > 4000
В первом примере используется два подселекта: один для нахождения максимальной зарплаты, другой для фильтрации средней зарплаты. Второй пример, опять же, вышел намного проще и лаконичнее.
План запроса
Нередко бывают ситуации, когда запрос работает долго, потребляя значительные ресурсы памяти и дисков. Чтобы понять, почему запрос работает долго и неэффективно, мы можем посмотреть план запроса.
План запроса — это предполагаемый план выполнения запроса, т.е. как СУБД будет его выполнять. СУБД распишет все операции, которые будут выполняться в рамках подзапроса. Проанализировав все, мы сможем понять, где в запросе слабые места и с помощью плана запроса сможем оптимизировать их.
Исполнение любого SQL предложения в Oracle извлекает так называемый “план исполнения”. Этот план исполнения запроса является описанием того, как Oracle будет осуществлять выборку данных, согласно исполняемому SQL предложению. План представляет собой дерево, которое содержит порядок шагов и связь между ними.
К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются: cost — стоимость выполнения и cardinality (или rows) — кардинальность (или количество строк).
Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже можно увидеть анализ плана запроса. В первом решении используется подселект, во втором — группировка. Обратите внимание, что в первом решении обработано 22 строки, во втором — 15.
Анализ плана запроса:
Ещё один анализ плана запроса, в котором применяется два подселекта:
Этот пример приведен как вариант нерационального использования средств SQL и я не рекомендую вам его использовать в своих запросах.
Все перечисленные выше функции упростят вам жизнь при написании запросов и повысят качество и читабельность вашего кода.
Часть 2: Оконные функции
Оконные функции появились ещё в Microsoft SQL Server 2005. Они осуществляют вычисления в заданном диапазоне строк внутри предложения Select. Если говорить кратко, то “окно” — это набор строк, в рамках которого происходит вычисление. “Окно” позволяет уменьшить данные и более качественно их обработать. Такая функция позволяет разбивать весь набор данных на окна.
Оконные функции обладают огромным преимуществом. Нет необходимости формировать набор данных для расчетов, что позволяет сохранить все строки набора с их уникальными ID. Результат работы оконных функций добавляется к результатирующей выборке в еще одно поле.
Синтаксис:
SELECT column_name(s)
Агрегирующая функция (столбец для вычислений)
OVER ([PARTITION BY столбец для группировки]
FROM table_name
[ORDER BY столбец для сортировки]
[ROWS или RANGE выражение для ограничения строк в пределах группы])
OVER PARTITION BY — это свойство для задания размеров окна. Здесь можно указывать дополнительную информацию, давать служебные команды, например добавить номер строки. Синтаксис оконной функции вписывается прямо в выборку столбцов.
Давайте рассмотрим все на примере: в нашу таблицу добавился еще один отдел, теперь в таблице 15 строк. Мы попытаемся вывести работников, их з/п, а также максимальную з/п организации.
В первом поле мы берем имя, во втором — зарплату. Дальше мы применяем оконную функцию over(). Используем её для получения максимальной зарплаты по всей организации, так как не указаны размеры “окна”. Over() с пустыми скобками применяется для всей выборки. Поэтому везде максимальная зарплата — 10 000. Результат действия оконной функции добавляется к каждой строчке.
Если убрать из четвертой строки запроса упоминание оконной функции, т.е. остается только max (salary), то запрос не сработает. Максимальную зарплату просто не удалось бы посчитать. Так как данные обрабатывались бы построчно, и на момент вызова max (salary) было бы только одно число текущей строки, т.е. текущего работника. Вот тут и можно заметить преимущество оконной функции. В момент вызова она работает со всем окном и со всеми доступными данными.
Давайте рассмотрим еще один пример, где нужно вывести максимальную з/п каждого отдела:
Фактически мы задаем рамки для “окна”, разбивая его на отделы. В качестве ранжирующего примера мы указываем department. У нас есть три отдела: dev, qa и sales.
“Окно” находит максимальную зарплату для каждого отдела. В результате выборки мы видим, что оно нашло максимальную зарплату сначала для dev, затем для qa, потом для sales. Как уже упоминалось выше, результат оконной функции записывается в результат выборки каждой строки.
В предыдущем примере в скобках после over не было указано. Здесь мы использовали PARTITION BY, которое позволило задать размеры нашего окна. Здесь можно указывать какую-то доп информацию, передавать служебные команды, например, номер строки.
Заключение
SQL не так прост, как кажется на первый взгляд. Все описанное выше — это базовые возможности оконных функций. С их помощью можно “упростить” наши запросы. Но в них скрыто намного больше потенциала: есть служебные операторы (например ROWS или RANGE), которые можно комбинировать, добавляя больше функциональности запросам.
Надеюсь, пост был полезен для всех интересующихся данной темой.
Источник