Использование функций SQL в запросах к базе данных Oracle

Vovan_ST

Vovan_ST

ИТ специалист со стажем. Автор статьи. Профиль

Использование SQL функций в базе данных Oracle на примерахФункции в 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 с примерами 

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

Вас заинтересует / Intresting for you:

Oracle и непроцедурный доступ ...
Oracle и непроцедурный доступ ... 8510 просмотров Antoni Tue, 21 Nov 2017, 13:32:50
Создание базы данных Oracle
Создание базы данных Oracle 34263 просмотров Александров Попков Wed, 14 Nov 2018, 12:44:39
Видеокурс по администрированию...
Видеокурс по администрированию... 10719 просмотров Илья Дергунов Mon, 14 May 2018, 05:08:47
СУБД Oracle: обзор характерист...
СУБД Oracle: обзор характерист... 15794 просмотров Antoni Fri, 24 Nov 2017, 07:35:05
Войдите чтобы комментировать