Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

Учебник SQL

Урок 8. Использование функций манипулирования данными

В этом уроке вы узнаете, что такое функции, какие типы функций поддерживаются в СУБД и как их можно применять. Вы также узнаете, почему использование SQL-функций может быть проблематичным.

Что такое функция

Как и в большинстве других языков программирования, в SQL поддерживается использование функций для манипулирования данными. Функции – это операции, которые обычно производятся над данными, чаще всего для облегчения преобразований и манипулирования.

Примером может служить функция RTRIM(), которую мы использовали в предыдущем уроке для удаления пробелов в конце строки.

Проблемы с функциями

Перед тем как начать урок и рассмотреть примеры, обращаю ваше внимание на то, что использование SQL-функций может быть проблематичным.

В отличие от SQL-операторов (например, SELECT), которые в основном поддерживаются всеми СУБД одинаково, в разных СУБД могут применяться различные функции. Только некоторые функции в различных СУБД выполняются одинаково. И хотя все типы функций обычно доступны в каждой СУБД, реализация этих функций может значительно отличаться. Чтобы стало понятно, насколько это может быть проблематичным, в табл. 8.1 перечислены три наиболее часто используемые функции и их синтаксис в различных СУБД.

Таблица 8.1. Различия в функциях СУБД

Функция Синтаксис
Выборка части строки В Access используется функция MID(). В DB2, Oracle и PostgreSQL используется функция SUBSTR(). В MySQL, SQL Server и Sybase – SUBSTRING()
Преобразование типа данных В Access и Oracle используются несколько функций, по одной на каждый тип преобразования. В DB2 и PostgreSQL используется функция CAST(). В MySQL, SQL Server и Sybase используется функция CONVERT()
Получение текущей даты В Access используется функция NOW(). В DB2 и PostgreSQL – CURRENT_DATE. В MySQL используется функция CURDATE() . В Oracle – SYSDATE. В SQL Server и Sybase – GETDATE()

Как видите, в отличие от SQL-операторов, SQL-функции не относятся к числу переносимых. Это означает, что код, который вы напишете для одной реализации SQL, может не работать в другой.

Переносимый код

Код, который может работать в разных системах.

Учитывая переносимость кода, многие SQL-программисты стараются не использовать зависящие от реализации функции. Несмотря на то что это довольно благородная и в чем-то идеальная позиция, она не всегда вписывается в интересы приложения с точки зрения производительности. Ему приходится использовать другие методы выполнения того, что СУБД сделала бы более эффективно.

Стоит ли использовать функции?

Итак, вы пытаетесь решить, использовать функции или нет. Это решение зависит от вас, и здесь нет правильного или неправильного выбора. Если вы решили использовать функции, дописывайте подробные комментарии к коду, чтобы в будущем вы (или другой разработчик) могли узнать, для какой реализации SQL писался данный код.

Использование функций

В большинстве реализаций SQL поддерживаются следующие типы функций.

  • Текстовые функции; используются для управления текстовыми строками (например, для обрезания или заполнения значений и преобразования значений в верхний или нижний регистр).
  • Числовые функции; используются для выполнения математических операций над числовыми данными (например, для вычисления абсолютных значений и выполнения алгебраических вычислений).
  • Функции даты и времени; используются для управления значениями даты и времени и для выборки отдельных частей этих значений (например, для возвращения разницы между датами и проверки даты на корректность).
  • Системные функции; возвращают информацию, специфичную для используемой СУБД (например, возвращают регистрационную информацию пользователя).

В предыдущем уроке встречалась функция, которая использовалась в списке столбцов выражения SELECT, но это допустимо не для всех функций. Функции можно использовать как в других частях оператора SELECT (например, в условии WHERE), так и в других SQL-операторах (об этом вы узнаете в дальнейших уроках).

Функции манипулирования текстом

В примере функций манипулирования текстом в седьмом уроке функция RTRIM() использовалась для удаления пробелов в конце значения столбца. Ниже приведен еще один пример, в котором используется функция UPPER():

SELECT vend_name UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

Вывод:

vend_name       vend_name_upcase
--------------------------------
Bear Emporium   BEAR EMPORIUM
Beras R Us      BERAS R US
Doll House Inc. DOLL HOUSE INC.
Fun and Games   FUN AND GAMES
Furball Inc.    FURBALL INC.
Jouets et ours  JOUETS ET OURS

Функция UPPER() преобразует текст в верхний регистр и, таким образом, в этом примере имя каждого изготовителя перечислено дважды: первый раз в таком виде, в каком оно хранится в таблице Vendors, а второй раз – будучи преобразованным в верхний регистр, в виде столбца vend_name_upcase.

В табл. 8.2 перечислены наиболее часто используемые функции манипулирования текстом.

Один элемент из табл. 8.2 требует более подробного объяснения. SOUNDEX – это алгоритм, преобразующий текстовую строку в буквенно-цифровой шаблон, описывающий фонетическое представление данного текста. Функция SOUNDEX берет в расчет похожие по звучанию буквы и слоги, позволяя сравнивать строки не по тому, как они пишутся, а по тому, как они звучат. Хотя SOUNDEX не подпадает под основные концепции SQL, большинство СУБД осуществляют поддержку этой функции.

Таблица 8.2. Наиболее часто используемые функции манипулирования текстом

Функция Описание
LEFT() (или функция подстроки) Возвращает символы из левой части строки
LENGTH (а также DATALENGTH() ИЛИ LEN()) Возвращает длину строки
LOWER() (LCASE () в Access) Преобразует строку в нижний регистр
LTRIM() Удаляет пробелы в левой части строки
RIGHT() (или функция подстроки) Возвращает символы из правой части строки
RTRIM() Удаляет пробелы в правой части строки
SOUNDEX() Возвращает значение SOUNDEX строки
UPPER() (UCASE в Access) Преобразует текст строки в верхний регистр

Поддержка SOUNDEX

Функция SOUNDEX() не поддерживается Microsoft Access или PostgreSQL, поэтому следующий пример не будет работать в этих СУБД.

Ниже приведен пример использования функции SOUNDEX (). Клиент Kids Place находится в таблице Customers и имеет контактное лицо Michelle Green. Но что, если это опечатка и на самом деле контактное лицо пишется как Michael Green? Очевидно, поиск по корректному имени ничего не даст, это показано ниже:

SELECT cust_name cust_contract
FROM Customers
WHERE cust_contract = 'Michael Green';

Вывод:

cust_name    cust_contract
--------------------------

А теперь попробуйте выполнить поиск при помощи функции SOUNDEX(), чтобы найти все имена контактных лиц, которые звучат как Michael Green:

SELECT cust_name cust_contract
FROM Customers
WHERE SOUNDEX(cust_contract) = SOUNDEX('Michael Green');

Вывод:

cust_name   cust_contract
--------------------------
Kids Place  Michelle Green

В этом примере в предложении WHERE используется функция SOUNDEX () для преобразования значения столбца cust_contact и искомой строки в их SOUNDEX-значения. Так как Michael Green и Michelle Green звучат одинаково, их SOUNDEX-значения совпадут и предложение WHERE корректно отфильтрует необходимые данные.

Функции манипулирования датой и временем

Дата и время хранятся в таблицах с использованием соответствующих типов данных, каждая СУБД использует свои собственные типы. Значения даты и времени хранятся в специальном формате, поэтому их можно быстро и эффективно сохранить или отфильтровать, а также сохранить физическое пространство на диске.

Формат, в котором хранятся дата и время, обычно нельзя использовать в приложениях, поэтому почти всегда используются функции даты и времени для чтения, расширения и манипулирования этими значениями. Функции манипулирования датой и временем являются одними из наиболее важных функций в SQL. К сожалению, они меньше всего поддаются переносу на другие платформы и реализации SQL.

Чтобы продемонстрировать процедуру использования функции манипулирования датой и временем, приведем простой пример. В таблице Orders все заказы хранятся с датой заказа. Чтобы извлечь список всех заказов, сделанных в 2004 году, в SQL Server и Sybase необходимо выполнить следующее:

SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2004;

Вывод:

order_num
---------
20005 
20006 
20007 
20008 
20009

В Access используйте следующую версию примера:

SELECT order_num
FROM Orders
WHERE DATEPART('yyyy', order_date) = 2004;

В этом примере (в версиях для SQL Server и Sybase и в Access) используется функция DATEPART (), которая, как видно из названия, возвращает только часть даты. В функции DATEPART() используются два параметра: часть, подлежащая возвращению, и дата, из которой эта часть возвращается. В рассматриваемом примере функция DATEPART () из столбца order_column возвращает только год. Путем сравнения полученного значения со значением 2004 предложение WHERE выбирает только те заказы, которые были сделаны в этом году.

Ниже приведена версия данного примера для PostgreSQL, в которой используется похожая функция DATE_PART():

SELECT order_num
FROM Orders
WHERE DATE_PART('year', order_date) = 2004;

В MySQL, помимо DATEPART(), есть множество других функций, предназначенных для манипулирования значениями дат. Пользователи MySQL могут использовать функцию YEAR() для выборки из даты значения года:

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2004;

В Oracle также нет функции DATEPART(), но существуют несколько других функций манипулирования датой, которые можно использовать с этой же целью. Рассмотрим пример:

SELECT order_num
FROM Orders
WHERE to_number(to_char(order_date, 'YY')) = 2004;

В этом примере функция to_char () используется для извлечения части даты, а функция to_number() – для преобразования этой части в числовое значение, чтобы его можно было сравнить со значением 2004. Тех же результатов можно добиться при помощи оператора BETWEEN:

SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date('01-JAN-2004')
AND to_date('31-DEC-2004');

В этом примере функция Oracle to_date() используется для преобразования двух строк в даты. В одной содержится дата 1 января 2004, а в другой – 31 декабря 2004. Стандартный оператор BETWEEN используется для поиска всех заказов, сделанных в период между этими двумя датами. Этот код не будет работать в SQL Server, так как в этой СУБД не поддерживается функция to_date(). Однако если заменить функцию to_date() функцией DATAPART(), этот оператор можно будет использовать.

Даты в Oracle

Даты в формате ДД-МММ-ГГГГ (как в предыдущих примерах) системой Oracle обычно обрабатываются, даже если они не приведены к тому виду, как при использовании функции to_date (). Однако для надежности лучше всегда использовать эту функцию.

В приведенных примерах выбиралась и использовалась только часть даты (год). Чтобы выбрать заказы по месяцу, необходимо сделать то же самое, указав ключевое слово AND для сравнения месяца и года.

СУБД обычно могут выполнять гораздо больше действий, чем просто выборка части даты. В большинстве из них присутствуют функции для сравнения дат, выполнения простых арифметических операций с датами, опции форматирования дат и многое другое. Но, как вы уже заметили, функции манипулирования датой и временем различны для разных СУБД. Обратитесь к документации по своей СУБД и уточните, какие функции манипулирования датой и временем в ней поддерживаются.

Функции для манипулирования числами

Числовые функции предназначены для манипулирования числовыми данными. Эти функции используются только для алгебраических, тригонометрических и геометрических вычислений, поэтому они используются не так часто, как функции манипулирования датой и временем.

По иронии судьбы среди всех функций в большинстве СУБД именно числовые функции наиболее стандартизированы. В табл. 8.3 перечислены наиболее часто используемые функции манипулирования числовыми данными.

Таблица 8.3. Наиболее часто используемые функции манипулирования числами

Функция Описание
ABS() Возвращает абсолютное значение числа
COS() Возвращает косинус указанного угла
ЕХР() Возвращает экспоненту указанного числа
PI() Возвращает значение числа пи
SIN() Возвращает синус указанного угла
SQRT() Возвращает квадратный корень указанного числа
TAN() Возвращает тангенс указанного угла

Обратитесь к документации по вашей СУБД, чтобы определить, какие функции манипулирования числовыми данными она поддерживает.

Резюме

В этом уроке объяснялось, как можно использовать SQL-функции манипулирования данными. Несмотря на то, что эти функции могут быть очень полезными при форматировании, манипулировании и фильтрации данных, они весьма различны для разных реализаций SQL.