Главная › Настройки › Как в sql запросе convert. Неявное преобразование типов. Основные арифметические операторы SQL

Как в sql запросе convert. Неявное преобразование типов. Основные арифметические операторы SQL

В реализациях языка SQL может быть выполнено неявное преобразование типов. Так, например, в T-SQL при сравнении или комбинировании значений типов smallint и int , данные типа smallint неявно преобразуются к типу int . Подробно о явном и неявном преобразовании типов в MS SQL Server можно прочитать в BOL.

Пример. Вывести среднюю цену ПК-блокнотов с предваряющим текстом "средняя цена = ".
Попытка выполнить запрос

в результате получим то, что требовалось:

даст результат 1926. В принципе все правильно, т.к. мы получили в результате то, что просили - ГОД. Однако среднее арифметическое будет составлять примерно 1926,2381. Тут следует отметить, что агрегатные функции (за исключением функции COUNT , которая всегда возвращает целое число) наследуют тип данных обрабатываемых значений. Поскольку поле launched - целочисленное, мы и получили среднее значение с отброшенной дробной частью (заметьте - не округленное).
А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно,
Результат - 1926.238095. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:

Т.е. мы использовали неявное преобразование целочисленного аргумента к точному числовому типу (EXACT NUMERIC), умножив его на вещественную единицу, после чего применили явное приведения типа результата агрегатной функции.

Аналогичные преобразования типа можно выполнить с помощью функции CONVERT :

Здесь мы преобразуем строковое представление даты к типу datetime , после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано, используется значение по умолчанию (0 или 100). В результате получим

Перечень всех возможных значений аргумента стиль можно посмотреть в BOL.

Оператор CASE

Пусть требуется вывести список всех моделей ПК с указанием их цены. При этом если модель отсутствует в продаже (нет в таблице РС), то вместо цены вывести текст: "Нет в наличии".
Список всех моделей ПК с ценами можно получить с помощью запроса:

SELECT DISTINCT product.model, price FROM product LEFT JOIN pc c
ON product.model=c.model
WHERE product.type="pc";

В результирующем наборе отсутствующая цена будет заменена NULL-значением:

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	NULL
2112	NULL

Чтобы заменить NULL-значения нужным текстом, можно воспользоваться оператором CASE :
Оператор CASE в зависимости от указанных условий возвращает одно из множества возможных значений. В нашем примере условием является проверка на NULL. Если это условие выполняется, то возвращается текст "Нет в наличии", в противном случае (ELSE ) возвращается значение цены. Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT всегда является таблица, то все значения любого столбца должны иметь один и тот же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду с ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	Нет в наличии
2112	Нет в наличии

Оператор CASE может быть использован в одной из двух синтаксических форм записи:

1-я форма
CASE <проверяемое выражение>
WHEN <сравниваемое выражение 1>
THEN <возвращаемое значение 1>
…
WHEN <сравниваемое выражение N>
THEN <возвращаемое значение N>
END

2-я форма
CASE
WHEN <предикат 1>
THEN <возвращаемое значение 1>
…
WHEN <предикат N>
THEN <возвращаемое значение N>
END

Все предложения WHEN должны иметь одинаковую синтаксическую форму, т.е. нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN . При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN . Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE . При отсутствии ELSE , будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них.
В приведенном выше примере была использована вторая форма оператора CASE .
Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора - COALESCE . Этот оператор имеет произвольное число параметров и возвращает значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE :

CASE WHEN A IS NOT NULL THEN A ELSE B END

Решение рассмотренного выше примера при использовании оператора COALESCE можно переписать следующим образом:

Использование первой синтаксической формы оператора CASE можно продемонстрировать на следующем примере : Вывести все имеющиеся модели ПК с указанием цены. Отметить самые дорогие и самые дешевые модели.

SELECT DISTINCT model, price,
CASE price WHEN (SELECT MAX(price) FROM pc) THEN "Самый дорогой"
WHEN (SELECT MIN(price) FROM pc) THEN "Самый дешевый"
ELSE "Средняя цена" END comment
FROM pc ORDER BY price;

В результате выполнения запроса получим

model	price	comment
1232	350	Самый дешевый
1260	350	Самый дешевый
1232	400	Средняя цена
1233	400	Средняя цена
1233	600	Средняя цена
1121	850	Средняя цена
1233	950	Средняя цена
1233	980	Самый дорогой

В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой . Преобразование может выполняться двумя способами:

Неявно - поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
Явно - преобразование выполняется вызовом функции или соответствующим оператором PL/SQL .

В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.

Неявное преобразование типов

Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу . Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.

Рис. 1

Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:

DECLARE a_number NUMBER; BEGIN a_number:= "125"; END;

Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:

PROCEDURE change_hiredate (emp_id_in IN INTEGER, hiredate_in IN DATE) change_hiredate (1004, "12-DEC-94");

Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.

Ограничения неявного преобразования

Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:

DECLARE a_number NUMBER; BEGIN a_number:= "abc"; END;

В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.

Недостатки неявного преобразования

Неявное преобразование типов имеет ряд недостатков.

PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации - таких, как ясность и надежность кода.
Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности - если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.

Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование - это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.

Явное преобразование типов

Oracle предоставляет обширный набор функций и операторов, с помощью которых можно выполнить преобразование типов данных в SQL и PL/SQL. Их полный список приведен в табл. 1. Большая часть функций описывается в других главах книги (для них в последнем столбце указан номер главы).

Таблица 1. Функции преобразования типов в PL/SQL

Функция	Выполняемое преобразование
ASCIISTR	Строку из любого набора символов в строку ASCII из набора символов базы данных
CAST	Одно значение встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию. Этот способ может использоваться вместо традиционных функций (таких, как TO_DATE )
CHARTOROWID	Строку в значение типа ROWID
CONVERT	Строку из одного набора символов в другой
FROM_TZ	В значение типа TIMESTAMP добавляет информацию о часовом поясе, преобразуя его тем самым в значение типа TIMESTAMP WITH TIME ZONE
HEXTORAW	Значение из шестнадцатеричной системы в значение типа RAW
MULTISET	Таблицу базы данных в коллекцию
NUMTODSINTERVAL	INTERVAL DAY TO SECOND
NUMTOYMINTERVAL	Число (или числовое выражение) в литерал INTERVAL YEAR TO MONTH
RAWTOHEX, RAWTONHEX	Значение типа RAW в шестнадцатеричный формат
REFTOHEX	Значение типа REF в символьную строку, содержащую его шестнадцатеричное представление
ROWIDTOCHAR, ROWIDTONCHAR	Двоичное значение типа ROWID в символьную строку
TABLE	Коллекцию в таблицу базы данных; по своему действию обратна функции MULTISET
THE	Значение столбца в строку виртуальной таблицы базы данных
TO_BINARY_FLOAT	Число или строку в BINARY_FLOAT
TO_BINARY_DOUBLE	Число или строку в BINARY_DOUBLE
TO_CHAR, TO_NCHAR (числовая версия)	Число в строку (VARCHAR2 или NVARCHAR2 соответственно)
TO_CHAR, TO_NCHAR (версия для дат)	Дату в строку
TO_CHAR, TO_NCHAR (символьная версия)	Данные из набора символов базы данных в набор символов национального языка
TO_BLOB	Значение типа RAW в BLOB
TO_CLOB, TO_NCLOB	Значение типа VARCHAR2 , NVARCHAR2 или NCLOB в CLOB (либо NCLOB )
TO_DATE	Строку в дату
TO_DSINTERVAL	Символьную строку типа CHAR, VARCHAR2 , NCHAR или NVARCHAR2 в тип INTERVAL DAY TO SECOND
TO_LOB	Значение типа LONG в LOB
TO_MULTI_BYTE	Однобайтовые символы исходной строки в их многобайтовые эквиваленты (если это возможно)
TO_NUMBER	Строку или число (например, BINARY_FLOAT ) в NUMBER
TO_RAW	Значение типа BLOB в RAW
TO_SINGLE_BYTE	Многобайтовые символы исходной строки в соответствующие однобайтовые символы
TO_TIMESTAMP	TIMESTAMP
TO_TIMESTAMP_TZ	Символьную строку в значение типа TO_TIMESTAMP_TZ
TO_YMINTERVAL	Символьную строку типа CHAR, VARCHAR2 , NCHAR или NVARCHAR2 в значение типа INTERVAL YEAR TO MONTH
TRANSLATE ... USING	Текст в набор символов, заданный для преобразования набора символов базы данных в национальный набор символов
UNISTR	Строку произвольного набора символов в Юникод

Функция CHARTOROWID

Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:

FUNCTION CHARTOROWID (исходная_строка IN CHAR) RETURN ROWID FUNCTION CHARTOROWID (исходная_строка IN VARCHAR2) RETURN ROWID

Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:

ООООООФФФББББББССС

где ОООООО - номер объекта данных, ФФФ - относительный номер файла базы данных, ББББББ - номер блока в файле, а ССС - номер строки в блоке. Все четыре компонента задаются в формате Base64 . Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .

Функция CAST

Функция CAST является очень удобным и гибким механизмом преобразования данных. Она преобразует значение любого (или почти любого) встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию, и скорее всего, будет знакома всем программистам с опытом работы на объектно-ориентированных языках.

С помощью функции CAST можно преобразовать неименованное выражение (число, дату, NULL и даже результат подзапроса) или именованную коллекцию (например, вложенную таблицу) в тип данных или именованную коллекцию совместимого типа. Допустимые преобразования между встроенными типами данных показаны на рис. 2. Необходимо соблюдать следующие правила:

не допускается преобразование типов данных LONG , LONG RAW , любых типов данных LOB и типов, специфических для Oracle;
обозначению « DATE » на рисунке соответствуют типы данных DATE , TIMESTAMP , TIMESTAMP WITH TIMEZONE , INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH ;
для преобразования именованной коллекции определенного типа в именованную коллекцию другого типа нужно, чтобы элементы обеих коллекций имели одинаковый тип;

Рис. 2.

тип UROWID не может быть преобразован в ROWID , если UROWID содержит значение ROWID индекс-таблицы.

Ниже приведен пример использования функции CAST для преобразования скалярных типов данных. Ее вызов может быть включен в SQL-команду :

SELECT employee_id, cast (hire_date AS VARCHAR2 (30)) FROM employee;

Также возможен вызов в синтаксисе PL/SQL:

DECLARE hd_display VARCHAR2 (30); BEGIN hd_display:= CAST (SYSDATE AS VARCHAR2); END;

Намного более интересное применение CAST встречается при работе с коллекциями PL/SQL (вложенными таблицами и VARRAY ), поскольку эта функция позволяет преобразовывать коллекцию из одного типа в другой. Кроме того, CAST может использоваться для работы (из инструкций SQL) с коллекцией, объявленной как переменная PL/SQL.

Обе темы подробно рассматриваются в главе 12, а следующий пример дает некоторое представление о синтаксисе и возможностях преобразования. Сначала мы создаем два типа вложенных таблиц и одну реляционную таблицу:

CREATE TYPE names_t AS TABLE OF VARCHAR2 (100); CREATE TYPE authors_t AS TABLE OF VARCHAR2 (100); CREATE TABLE favorite_authors (name VARCHAR2(200))

/* Файл в Сети: cast.sql */ 1 DECLARE 2 scifi_favorites authors_t 3:= authors_t ("Sheri S. Tepper", "Orson Scott Card", "Gene Wolfe"); 4 BEGIN 5 DBMS_OUTPUT.put_line ("I recommend that you read books by:"); 6 7 FOR rec IN (SELECT column_value favs 8 FROM TABLE (CAST (scifi_favorites AS names_t)) 9 UNION 10 SELECT NAME 11 FROM favorite_authors) 12 LOOP 13 DBMS_OUTPUT.put_line (rec.favs); 14 END LOOP; 15 END;

В строках 2 и 3 объявляется локальная вложенная таблица, заполняемая именами нескольких популярных авторов. В строках 7–11 с помощью оператора UNION объединяются строки таблиц favorite_authors и scifi_favorites . Для этого вложенная таблица scifi_favorites (локальная и не видимая для ядра SQL) преобразуется с использованием функции CAST в коллекцию типа names_t . Такое преобразование возможно благодаря совместимости их типов данных. После преобразования вызов команды TABLE сообщает ядру SQL, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:

I recommend that you read books by: Gene Wolfe Orson Scott Card Robert Harris Sheri S. Tepper Tom Segev Toni Morrison

Функция CONVERT

Преобразует строку из одного набора символов в другой. Синтаксис функции:

FUNCTION CONVERT (исходная_строка IN VARCHAR2, новый_набор_символов VARCHAR2 [, старый_набор_символов VARCHAR2]) RETURN VARCHAR2

Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.

Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.

Два самых распространенных набора символов - WE8MSWIN1252 (8-разрядный набор символов Microsoft Windows, кодовая страница 1252) и AL16UTF16 (16-разрядный набор символов Юникод).

Функция HEXTORAW

Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :

FUNCTION HEXTORAW (исходная_строка IN CHAR) RETURN RAW FUNCTION HEXTORAW (исходная_строка IN VARCHAR2) RETURN RAW

Функция RAWTOHEX

Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :

FUNCTION RAWTOHEX (двоичное_значение IN RAW) RETURN VARCHAR2

Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.

Функция ROWIDTOCHAR

Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :

FUNCTION ROWIDTOCHAR (исходная_строка IN ROWID) RETURN VARCHAR2

Возвращаемая функцией строка имеет следующий формат:

ООООООФФФББББББССС

где ОООООО - номер объекта данных, ФФФ - относительный номер файла базы данных, ББББББ - номер блока в файле, а ССС - номер строки в блоке PL/SQL . Все четыре компонента задаются в формате Base64. Пример:

AAARYiAAEAAAEG8AAB

Пример. Вывести среднюю цену ПК-блокнотов с предваряющим текстом «средняя цена = «.
Попытка выполнить запрос

в результате получим то, что требовалось:

Мы использовали выражение явного преобразования типов CAST для приведения среднего значения цены к строковому представлению. Синтаксис выражения CAST очень простой:

CAST(<выражение> AS <тип данных>)

При этом следует иметь в виду, во-первых, что не любые преобразования типов возможны (стандарт содержит таблицу допустимых преобразований типов данных). Во-вторых, результат функции CAST для значения выражения, равного NULL, тоже будет NULL.
Рассмотрим еще один пример : определить средний год спуска на воду кораблей из таблицы Ships. Запрос

SELECT AVG(launched) FROM ships;

даст результат 1926. В принципе все правильно, т.к. мы получили в результате то, что просили — ГОД. Однако среднее арифметическое будет составлять примерно 1926,2381. Тут следует отметить, что агрегатные функции (за исключением функции COUNT , которая всегда возвращает целое число) наследуют тип данных обрабатываемых значений. Поскольку поле launched — целочисленное, мы и получили среднее значение с отброшенной дробной частью (заметьте — не округленное).
А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно,

Результат — 1926.238095. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:

Аналогичные преобразования типа можно выполнить с помощью функции CONVERT :

Ниже приведены некоторые другие значения аргумента стиль и результат, полученный на приведенном выше примере. Заметим, что значения стиль большие 100 приводят к четырехзначному отображению года.

Перечень всех возможных значений аргумента стиль можно посмотреть в BOL.

Пусть требуется вывести список всех моделей ПК с указанием их цены. При этом если модель отсутствует в продаже (нет в таблице РС), то вместо цены вывести текст: «Нет в наличии».
Список всех моделей ПК с ценами можно получить с помощью запроса:

Чтобы заменить NULL-значения нужным текстом, можно воспользоваться оператором CASE :

Оператор CASE в зависимости от указанных условий возвращает одно из множества возможных значений. В нашем примере условием является проверка на NULL. Если это условие выполняется, то возвращается текст «Нет в наличии», в противном случае (ELSE ) возвращается значение цены. Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT всегда является таблица, то все значения любого столбца должны иметь один и тот же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду с ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим

Оператор CASE может быть использован в одной из двух синтаксических форм записи:

2-я форма
CASE
WHEN <предикат 1>
THEN <возвращаемое значение 1>
…
WHEN <предикат N>
THEN <возвращаемое значение N>
END

Все предложения WHEN должны иметь одинаковую синтаксическую форму, т.е. нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN . При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN . Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE . При отсутствии ELSE , будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них.
В приведенном выше примере была использована вторая форма оператора CASE .
Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора — COALESCE . Этот оператор имеет произвольное число параметров и возвращает значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE :

CASE WHEN A IS NOT NULL THEN A ELSE B END

Решение рассмотренного выше примера при использовании оператора COALESCE можно переписать следующим образом:

SELECT DISTINCT model, price,
CASE price WHEN (SELECT MAX(price) FROM pc) THEN ‘Самый дорогой’
WHEN (SELECT MIN(price) FROM pc) THEN ‘Самый дешевый’
ELSE ‘Средняя цена’ END comment
FROM pc ORDER BY price;

В результате выполнения запроса получим

model	price	comment
1232	350	Самый дешевый
1260	350	Самый дешевый
1232	400	Средняя цена
1233	400	Средняя цена
1233	600	Средняя цена
1121	850	Средняя цена
1233	950	Средняя цена
1233	980	Самый дорогой

блузы и рубашки от производителя
niko-opt.com
Электрокардиограф экзт 12-03 альта купить cardiomc.com.ua
cardiomc.com.ua
Производство бытовок
Каталог продукции, технические характеристики. Каталог продукции.
module-house.ru

Преобразование данных

Последнее обновление: 29.07.2017

Когда мы присваиваем значение одного одного типа столбцу, который хранит данные другого типа, либо выполняем операции, которые вовлекают данные разных типов, SQL Server пытается выполнить преобразование и привести используемое значение к нужному типу. Но не все преобразования SQL Server может выполнить автоматически. SQL Server может выполнять неявные преобразования от типа с меньшим приоритетом к типу с большим приоритетом. Таблица приоритетов (чем выше, тем больший приоритет):

datetime

smalldatetime

float

real

decimal

money

smallmoney

int

smallint

tinyint

bit

nvarchar

nchar

varchar

char

То есть SQL Server автоматически может преобразовать число 100.0 (float) в дату и время (datetime).

В тех случаях, когда необходимо выполнить преобразования от типов с высшим приоритетом к типам с низшим приоритетом, то надо выполнять явное приведение типов. Для этого в T-SQL определены две функции: CONVERT и CAST.

Функция CAST преобразует выражение одного типа к другому. Она имеет следующую форму:

CAST(выражение AS тип_данных)

Для примера возьмем следующие таблицы:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL); CREATE TABLE Customers (Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL); CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL);

Например, при выводе информации о заказах преобразует числовое значение и дату в строку:

SELECT Id, CAST(CreatedAt AS nvarchar) + ‘; total: ‘ + CAST(Price * ProductCount AS nvarchar) FROM Orders

Convert

Большую часть преобразований охватывает функция CAST.

Функции CAST и CONVERT (Transact-SQL)

Если же необходимо какое-то дополнительное форматирование, то можно использовать функцию CONVERT. Она имеет следующую форму:

CONVERT(тип_данных, выражение [, стиль])

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

или — формат даты «Mon dd yyyy hh:miAM/PM» (значение по умолчанию)

или — формат даты «mm/dd/yyyy»

или — формат даты «dd/mm/yyyy»

или — формат даты «Mon dd, yyyy hh:miAM/PM»

или — формат даты «hh:mi:ss»

или — формат даты «mm-dd-yyyy»

или — формат даты «hh:mi:ss:mmmm» (24-часовой формат времени)

Некоторые значения для форматирования данных типа money в строку:

— в дробной части числа остаются только две цифры (по умолчанию)

— в дробной части числа остаются только две цифры, а для разделения разрядов применяется запятая

— в дробной части числа остаются только четыре цифры

Например, выведем дату и стоимость заказов с форматированием:

SELECT CONVERT(nvarchar, CreatedAt, 3), CONVERT(nvarchar, Price * ProductCount, 1) FROM Orders

TRY_CONVERT

При использовании функций CAST и CONVERT SQL Server выбрасывает исключение, если данные нельзя привести к определенному типу. Например:

SELECT CONVERT(int, ‘sql’)

Чтобы избежать генерации исключения можно использовать функцию TRY_CONVERT. Ее использование аналогично функции CONVERT за тем исключением, что если выражение не удается преобразовать к нужному типу, то функция возвращает NULL:

SELECT TRY_CONVERT(int, ‘sql’) — NULL SELECT TRY_CONVERT(int, ’22’) — 22

Дополнительные функции

Кроме CAST, CONVERT, TRY_CONVERT есть еще ряд функций, которые могут использоваться для преобразования в ряд типов:

STR(float [, length [,decimal]]): преобразует число в строку. Второй параметр указывает на длину строки, а третий — сколько знаков в дробной части числа надо оставлять

CHAR(int): преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры

ASCII(char): преобразует символ в числовой код ASCII

NCHAR(int): преобразует числовой код UNICODE в символ

UNICODE(char): преобразует символ в числовой код UNICODE

SELECT STR(123.4567, 6,2) — 123.46 SELECT CHAR(219) — Ы SELECT ASCII(‘Ы’) — 219 SELECT NCHAR(1067) — Ы SELECT UNICODE(‘Ы’) — 1067

Справочник по SQL

Преобразование типов

Множество типов разрешенные для преобразования в констркуции CAST AS определяется реализацией СУБД. Так в MySQL может преобразовать только следующие типы: binary[(n)], char[(n)], date, datetime, decimal[(m[,d])], signed , time, unsigned . А в Oracle, кроме преобразования встроенных типов, можно преобразовывать выборки со множеством записей в массивы.

В PostgreSQL более расширенные возможности по преобразованию. Во-первых, можно добавить собственное преобразование для встроенных и пользовательских типов. Во-вторых, есть собственный более удобный оператор преобразования типов::.

В большинстве случае необходимо преобразование в строку либо из строки.

Основные арифметические операторы SQL

Для этого случая СУБД предоставляют дополнительные функции.

функции Oracle

to_char (date [,format[,nlsparams]]) — дату в строку;
to_char (number [,format[,nlsparams]]) — число в строку;
to_date (string[,format[,nlsparams]]) — строку в дату;
to_number (string [ ,format[, nlsparams] ]) — строку в число;
to_timestamp (string, format) — строку во время.

В этих функциях format описание формата даты или числа, а nlsparams — национальные параметры. Формат строки для даты задается следующими элементами:

"" — вставляет указанный в ковычках текст;
AD, A.D. — вставляет AD с точками или без точек;
ВС, B.C. — вставляет ВС с точками или без точек;
СС, SCC — вставляет век, SCC возвращает даты ВС как отрицательные числа;
D — вставляет день недели;
DAY — вставляет имя дня, дополненное пробелами до длины в девять символов;
DD — вставляет день месяца;
DDD — вставляет день года;
DY1 — вставляет сокращенное название дня;
FF2 — вставляет доли секунд вне зависимости от системы счисления;
НН, НН12 — вставляет час дня (от 1 до 12);
НН24 — вставляет час дня (от 0 до 23);
MI — вставляет минуты;
MM — вставляет номер месяца;
MOMn — вставляет сокращенное название месяца;
MONTHn — вставляет название месяца, дополненное пробелами до девяти символов;
RM — вставляет месяц римскими цифрами;
RR — вставляет две последние цифры года;
RRRR — вставляет весь год;
SS — вставляет секунды;
SSSSS — вставляет число секунд с полуночи;
WW — вставляет номер недели года (неделя — 7 дней от первого числа, а не от понедельника до воскресенья);
W — вставляет номер недели месяца;
Y.YYY — вставляет год с запятой в указанной позиции;
YEAR, SYEAR — вставляет год, SYEAR возвращает даты ВС как отрицательные числа;
YYYY, SYYYY — вставляет год из четырех цифр, SYYYY возвращает даты ВС как отрицательные числа;
YYY, YY, Y — вставляет соответствующее число последних цифр года.

Формат числовой строки задается следующими элементами:

$ — вставляет знак доллара перед числом;
В — вставляет пробелы для целой части десятичного числа, если она равна нулю;
MI — вставляет знак минус в конце (например, "999.999mi");
S — вставляет знак числа в начале или в конце (например,"s9999" или "9999s");
PR — записывает отрицательное число в уголвых скобках (например,"999.999pr");
D — вставляет разделитель десятичной точки в указанной позиции (например, "999D999");
G — вставляет групповой разделитель в указанной позиции (например,"9G999G999"). При этом дробная часть числа отбрасывается;
С — вставляет ISO идентификатор валюты в начале или в конце числа (например, "с9999" или "9999с");
L — вставляет локальный символ валюты в в начале или в конце числа (например, "l9999" или "9999l");
, — вставляет запятую в указанной позиции вне зависимости от группового разделителя;
. — вставляет десятичную точку в указанной позиции вне зависимости от разделителя десятичной точки;
V — возвращает значение, умноженное на 10^n, где n равно числу девяток после V. В случае необходимости это значение округляется;
ЕЕЕЕ — 9.99ЕЕЕЕ возвращает значение в экспоненциальной форме записи;
RM — RM значение будет записано римскими цифрами в верхнем регистре;
rm — rm значение будет записано римскими цифрами в нижнем регистре;
0 — вставляет нули, вместо пробелов в начале строки или в конце, например, 9990 вставляет нули, вместо пробелов в конце строки;
9 — каждая 9 определяет значащую цифру.

функции PostgreSQL

to_char (timestamp, format) — время в строку;
to_char (interval, format) — интервал времени в строку;
to_char (number, format) — число в строку;
to_date (str, format) — строку в дату;
to_number (str, format) — строку в число;
to_timestamp (str, format) — строку во время.

Основные элементы форматирования совпадают с Oracle.

функции MySQL

date_format (date,format) — дату в строку;
time_format (time,format) — время в строку;
format (number,precision) — число в cтроку типа "#,###,###.##", где число знаков определяется вторым аргументом.

Ниже приведен список основных элементов форматирования для даты и времени:

%c — месяц числом;
%d — день месяца;
%H — часы (от 0 до 24);
%h — часы (1 до 12);
%i — минуты;
%s — секунды;
%T — время в формате «hh:mm:ss»;
%Y — год, четыре цифры;
%y — год, две цифры.

Основные операторы языка SQL.

SQL (Structured Query Language - «язык структурированных запросов») - универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. Язык SQL основывается на реляционной алгебре и представляет собой совокупность операторов.

Существует 4 группы операторов. Рассмотрим группу операторов манипуляции данными (Data Manipulation Language, DML, SQL DML )

Выбор данных

Выбор данных представляет собой наиболее часто встречающуюся операцию, выполняемую с помощью SQL. Оператор SELECT - один из самых важных операторов этого языка, применяемый для выбора данных. Синтаксис этого оператора имеет следующий вид:

SELECT column FROM table

Операторы SELECT должны содержать слова SELECT и FROM; другие ключевые слова являются необязательными.

За ключевым словом SELECT следуют сведения о том, какие именно поля необходимо включить в результирующий набор данных. Звездочка (*) обозначает все поля таблицы, например:

Для выбора одной колонки применяется следующий синтаксис:

SELECT Company

Пример выбора нескольких колонок имеет вид:

SELECT Company, Phone, Mail

Для указания имен таблиц, из которых выбираются записи, применяется ключевое слово FROM, например:

SELECT * FROM Customers

Этот запрос возвратит все поля из таблицы Customers.

Для фильтрации результатов, возвращаемых оператором SELECT, можно использовать предложение WHERE (необязательное)

SELECT * FROM Products WHERE Category = 4

В предложении WHERE можно использовать различные выражения,

WHERE expression1 [{AND | OR} expression2 …]

например:

SELECT * FROM Products WHERE Category = 2 AND Postavshik > 10

SELECT Name, Price FROM Products WHERE Category= 3 OR Price < 50

Можно использовать такие операторы:

< Меньше

<= Меньше или равно

<> Не равно

> Больше

>= Больше или равно

Предложение ORDER BY (необязательное) применяется для сортировки результирующего набора данных по одной или нескольким колонкам.

SQL Server функция CAST

Для определения порядка сортировки используются ключевые слова ASC (по возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по возрастанию.

Модификация данных

Помимо извлечения данных язык SQL может быть использован для обновления и удаления данных, копирования записей в другие таблицы и выполнения других операций. Ниже мы рассмотрим операторы UPDATE, DELETE и INSERT, используемые для решения некоторых из этих задач.

Оператор UPDATE

Для изменения значений в одной или нескольких колонках таблицы применяется оператор UPDATE. Синтаксис этого оператора имеет вид:

UPDATE table SET column1 = expression1 WHERE criteria

Выражение в предложении SET может быть константой или результатом вычислений. Например, для повышения цен всех продуктов, стоящих меньше 10 долл., можно выполнить следующий запрос:

UPDATE Products SET Price = Price * 1.1 WHERE Price < 10

Оператор DELETE

Для удаления строк из таблиц следует использовать оператор DELETE, синтаксис которого имеет вид:

DELETE FROM table WHERE criteria

Удалить все продукты стоимость которых меньше 100:

DELETE FROM Products WHERE Price < 100

Оператор INSERT

Для добавления записей в таблицы следует использовать оператор INSERT, синтаксис которого имеет вид:

INSERT INTO table ( VALUES (expression [, …])

Например, для добавления нового клиента в таблицу Customers можно использовать следующий запрос.