Функции в Oracle применяются для осуществления манипуляций над данными и возвращения результата. Встроенные функции Oracle помогают выполнять многие преобразования очень быстро без написания всякого кода. Помимо использования встроенных функций, также допускается создавать и свои собственные функции. В целом, все функции делятся на несколько групп: однострочные функции, агрегатные функции, числовые и календарные функции, универсальные и условные функции и аналитические функции.
Однострочные функции
Однострочные функции (single-row functions) обычно применяются для выполнения задач вроде преобразования слова из нижнего регистра в верхний или наоборот или замены какой-нибудь части текста в строке. Ниже перечислены наиболее важные однострочные функции, которые часто используются в Oracle.
CONCAT. Соединяет или объединяет две или более строк в одну.LENGTH. Вычисляет длину строки символов.LOWER. Преобразует буквы из верхнего регистра в нижний, как показано в следующем примере:
SQL> SELECT LOWER('SHANNON ALAPATI') from dual;
LOWER('SHANNONALAPATI')
-----------------------
shannon alapati
SQL>
SUBSTR. Возвращает часть строки.INSTR. Возвращает число, обозначающее позицию, в которой в строке начинается определенное строковое значение.LPAD. Дополняет строку пробелами (padding) слева для достижения ею указанной длины и затем возвращает ее.RPAD. Дополняет строку пробелами справа.TRIM. Обрезает строку символов указанным образом.REPLACE. Заменяет каждое вхождение заданной строки той строкой, которая была указана в качестве замены.
Агрегатные функции SQL
Агрегатные функции (aggregate functions) применяются для вычисления деталей наподобие средних и суммарных значений выбранного столбца в запросе. Ниже перечислены наиболее важные агрегатные функции.
MIN. Возвращает наименьшее значение и используется, например, так:
SELECT MIN(join_date) FROM employees;
MAX. Возвращает наибольшее значение.AVG. Вычисляет среднее значения столбца.SUM. Вычисляет суммарное значение столбца:
SQL> SELECT SUM(bytes) FROM dba_free_space;
COUNT. Возвращает информацию об общем количестве столбцов.COUNT(*). Возвращает информацию о количестве строк в таблице.
Числовые и календарные функции SQL
В состав Oracle входит несколько числовых функций (number functions), которые принимают числовые входные параметры и возвращают числовые значения. Календарные функции (date functions) помогают форматировать значения даты и времени несколькими способами. Ниже перечислены некоторые наиболее важные числовые и календарные функции.
ROUND. Возвращает число, округленное на указанное количество позиций справа от десятичной запятой.TRUNC. Возвращает результат усечения даты в указанном формате.SYSDATE. Применяется довольно часто и возвращает текущее значение даты и времени:
SQL> SELECT sysdate FROM dual;
SYSDATE
--------------------
07/AUG/2008
SQL>
TO_TIMESTAMP. Преобразует тип данныхCHARилиVARCHARв тип данныхTIMESTAMP.TO_DATE. Служит для изменения текущего формата даты. Стандартный формат даты в Oracle выглядит так: ДД-МММ-ГГГГ (07-AUG-2008).- Функция
TO_DATEпринимает символьную строку с действительными данными внутри и преобразует ее в принятый по умолчанию формат даты в Oracle. Она может также изменять формат даты, как показано ниже:
SQL> SELECT TO_DATE('August 20,2008', 'MonthDD,YYYY') FROM dual;
TO_DATE('AUGUST20,2008'
-----------------------
08/20/2008
SQL>
TO_CHAR. Преобразует дату в символьную строку, как показано в следующем примере:
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-----------
04-AUG-2008
SQL>
SQL> SELECT TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY') FROM DUAL;
TO_CHAR(SYSDATE,'DAY,DDTHMON
--------------------------------
THURSDAY , 04TH AUGUST 2008
SQL>
TO_NUMBER. Преобразует символьную строку в числовой формат:
SQL> UPDATE employees SET salary = salary +
TO_NUMBER('100.00', '9G999D99')
WHERE last_name = 'Alapati';
Универсальные и условные функции SQL
В Oracle предлагаются кое-какие очень мощные универсальные (general) и условные (conditional) функции, которые позволяют увеличивать мощь простых SQL-операторов до уровня, подобного конструкциям традиционных языков программирования. Условные функции помогают делать выбор между несколькими вариантами. Ниже перечислены наиболее важные универсальные и условные функции, которые могут применяться в Oracle.
NVL. Заменяет значение в столбце таблицы тем, что указано после запятой, если в столбце содержится нулевое значение (NULL). То есть она заботится о значениях столбцов, если те являются нулевыми, и преобразует их в ненулевые:
SQL> SELECT last_name, title,
salary * NVL (commission_pct,0)/100 COMM
FROM employees; COALESCE. Похожа на функциюNVL, но возвращает первое ненулевое значение в списке:
SQL> COALESCE(region1, region2, region3, region4)
DECODE. Применяется для включения в SQL-код базовой логикиif-then. В следующем примере всем избирателям в таблице присваивается имя соответствующей партии на основе значения в столбце членства. В случае если у избирателя нет никакого значения в столбце членства, ему присваивается статус независимого избирателя (Independent):
SQL> SELECT DECODE(affiliation, 'D', 'Democrat',
'R', 'Republican', 'Independent') FROM voters;
CASE. Предоставляет те же функциональные возможности, что и функцияDECODE, но гораздо более понятным и элегантным образом. Ниже приведен пример использования функцииCASE, где она помогает включить в код логикуif-then:
SQL> SELECT ename,
(CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END)
department
FROM employees;
Аналитические функции SQL
Предлагаемые в Oracle аналитические функции SQL являются мощными инструментами для приложений бизнес-аналитики. В Oracle заявляют, что в случае применения этих функций скорость выполнения запросов может увеличиваться на 200–500%. Они предназначены для выполнения сложных суммарных вычислений без написания слишком большого объема кода. Ниже перечислены наиболее важные аналитические функции SQL, которые могут применяться в базе данных Oracle.
Функции ранжирования (ranking functions). Такие функции позволяют ранжировать элементы набора данных в соответствии с определенными критериями. В Oracle доступно несколько функций ранжирования: RANK, DENSE_RANK, CUME_DIST, PERCENT_RANK и NTILE. В листинге 1 приведен простой пример,
показывающий, как функция ранжирования помогает расположить в нужном порядке данные по продажам.
SQL> SELECT sales_type,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES,
RANK() OVER (ORDER BY SUM(amount_sold) ) AS original_rank,
RANK() OVER (ORDER BY SUM(amount_sold)
DESC NULLS LAST) AS derived_rank
FROM sales, products, customers, time_frame, sales_types
WHERE sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
sales.time_id=time_frame.time_id AND
sales.sales_type_id=sales_types.sales_type_id AND
timeframe.calendar_month_desc IN ('2008-07', '2008-08')
AND country_id='INDIA'
GROUP BY sales_type;
SALES_TYPE SALES ORIGINAL_RANK DERIVED_RANK
------------- --------- -------------- ------------
Direct Sales 5,744,263 5 1
Internet 3,625,993 4 2
Catalog 1,858,386 3 3
Partners 1,500,213 2 4
Tele Sales 604,656 1 5
SQL>
- Функции агрегирования со скользящим окном (moving-window aggregates). Эти функции позволяют получать кумулятивные суммарные и скользящие средние значения.
- Функции сравнения периодов (period-over-period comparison). Эти функции позволяют сравнивать два периода (например, “насколько, в процентном отношении, показатели первого квартала 2008 г. увеличились по сравнению с показателями первого квартала 2006 г.?”).
- Функции сравнения показателей соотношения для составления отчетов (ratio-toreport comparisons). Эти функции позволяют сравнивать показатели соотношения (например, “сколько, в процентном отношении, людей зарегистрировали партийную принадлежность в августе по сравнению со всем годом?”).
- Статистические функции (statistical functions). Эти функции вычисляют корреляционные и регрессивные показатели и тем самым позволяют анализировать причинно-следственные связи между данными.
- Инверсные процентные функции (inverse percentiles). Эти функции помогают находить данные, соответствующие значению процентиля (например, получать имена тех агентов по продаже, объем продаж у которых соответствует срединному значению продаж).
- Функция условного ранжирования и распространения (hypothetical ranks and distributions). Эти функции помогают вычислять, как новое значение для столбца впишется в существующие данные с точки зрения ранжирования и распространения.
- Гистограммные функции (histograms). Эти функции возвращают подходящее для каждой строки в таблице количество гистограммных данных.
- Агрегатные функции определения значений первой и последней записи (first/last aggregate). Такие функции удобно применять в случае использования конструкции
GROUP BYдля сортировки данных по группам. Они позволяют задавать желаемый порядок сортировки для групп.
