Функции в 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
для сортировки данных по группам. Они позволяют задавать желаемый порядок сортировки для групп.