Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ
Использование выражений
Разделы на этой странице:
- Поля, создаваемые в операторах
- Алиасы получаемых полей и столбцов
- Константы и переменные в качестве вывода времени выполнения
- Использование контекстных переменных
- Выражения, использующие CASE() и дружественные функции
- CASE()
- Доступность
- Синтаксис
- Дополнительные ключевые слова
- Аргументы
- Возвращаемое значение
- Замечания
- Примеры
- COALESCE()
- Доступность
- Синтаксис
- Аргументы
- Возвращаемое значение
- Замечания
- Пример
- Связанные или похожие функции
- NULLIF()
- Доступность
- Синтаксис
- Аргументы
- Замечания
- Пример
- Связанные или похожие функции
Вычисляемые столбцы
Полезной возможностью SQL является способность генерировать во время выполнения выходные поля с использованием выражений. Firebird поддерживает два вида вычисляемого вывода: поля, создаваемые в операторах DML, и столбцы, которые с помощью DDL были определены в таблице с использованием ключевых слов COMOTED BY как контекстные выражения. Обычно такие поля получаются из хранимых данных, хотя и не обязательно. Они могут быть константами или, в общем виде, контекстными переменными или значениями, получаемыми из контекстных переменных.
Поля, создаваемые в операторах
Когда выходной столбец создается с использованием выражения, он называется вычисляемым выходным столбцом. Выходное значение всегда используется только для чтения, потому что оно не является хранимым значением. Любое выражение, которое выполняет сравнение, вычисление или преобразование и возвращает в качестве результата единственное значение, может включать в себя и такие столбцы.
Такое часто происходит, когда выражение использует более одной "формулы" для получения результата. В одном из наших ранних примеров функция (EXTRACT()) и операция (конкатенация) были использованы для получения строки BIRTHDAY, которая была выходной в списке. Такие сложные выражения вовсе не являются редкими.
Алиасы получаемых полей и столбцов
Выражения, вывод подзапросов и константы могут быть использованы в Firebird для передачи получаемых, или "искусственных" полей, в выходные наборы. Для предоставления возможности назначать имена во время выполнения Firebird поддерживает стандарт SQL назначения алиасов столбцам, что позволяет выводить любые столбцы с алиасами из одного или более символов.
Например, следующий оператор
SELECT COL_ID, COLA || ',' ||COLB AS comput col
FROM TABLEA;
возвращает столбец с именем comput_col, являющийся конкатенацией значений двух столбцов, разделенных запятыми.
! ! !
ВНИМАНИЕ! Когда два столбца соединяются подобным образом в любом выражении, выходное поле будет NULL, если любой из столбцов является NULL. Это также является стандартным поведением SQL.
. ! .
В следующем примере используется скалярный подзапрос к другой таблице для создания выходного поля времени выполнения:
SELECT COL_ID,
COLA,
COLB,
(SELECT SOMECOL FROM TABLEB
WHERE UNIQUE_ID = '99') AS B_SOMECOL
FROM TABLEA
Скалярный (под)запрос - это тот, который возвращает значение одного столбца из одной строки. Подзапросы обсуждаются в главе 22.
Firebird позволяет стандарту слегка расслабиться в отношении ключевого слова AS - оно необязательно. Опускать его не рекомендуется, потому что это может усложнить поиск алиаса столбца в исходном коде.
Другая часть стандарта требует, чтобы вычисляемые столбцы или получаемые из оператора подзапроса были явно поименованы с помощью алиаса. Текущая версия сервера Firebird позволяет вам опускать имя алиаса для вычисляемых столбцов или столбцов, полученных из подзапроса. Для примера следующий запрос
SELECT CAST(CURRENT_DATE as VARCHAR(10)) || '-' || REGISTRATION
FROM AIRCRAFT;
генерирует такой вывод:
<пробельная титульная строка>
===============
2003- 01-01-GORILLA
2004- 02-28-KANGAROO
. . .
Одни рассматривают это как ошибку, другие - как особенность. Для администратора базы данных удобнее быстро создать запрос, не беспокоясь о деталях. Это строго не рекомендуется использовать как "особенность" в приложениях. Выходные столбцы без имени слишком часто приводят к ошибкам и двусмысленностям в клиентских интерфейсах. Те же самые сложности могут появиться в интерфейсах приложений, если вы используете один и тот же алиас более одного раза в одном выходе.
Константы и переменные в качестве вывода времени выполнения
Существует возможность "создавать" выходной столбец в операторе SELECT, используя выражение, в которое включены не столбцы, а константы или контекстные переменные и алиасы. В следующем тривиальном примере запрос добавляет к каждой строке столбец, содержащий константное значение 'This is just a demo':
SELECT
LAST_NAME,
FIRST_NAME,
'This is just a demo' AS DEMO_STRING
FROM MEMBERSHIP;
Тривиальность, как она проявляется в этом примере, может быть удобным способом настройки вывода, особенно при использовании в выражении CASE.
Использование контекстных переменных
Firebird имеет набор переменных, предоставляющих мгновенный снимок значений сервера. Как вы уже видели, многие из этих значений могут быть использованы в выражениях, выполняющих вычисления. Они также могут быть использованы в выражениях, сохраняющих их в столбцах базы данных, или в спецификациях COMPUTED BY (см. разд. "Объявление столбцов COMPUTED BY").
Контекстные переменные также могут быть использованы в выражениях выходного столбца для передачи серверного значения непосредственно клиенту или модулю PSQL. Например:
SELECT
LOG_ID,
L0G_DATE,
. . .
CURRENT_DATE AS BASE_DATE,
СURRENT_TRANSACTION AS BASE_TRANSACTI0N,
. . .
FROM LOG
WHERE . . .
Подробный список доступных переменных и примеры их использования см. в разд. "Контекстные переменные" главы 8.
Выражения, использующие CASE() и дружественные функции
Firebird предоставляет из стандарта SQL-99 синтаксис выражения CASE и двух его "сокращенных" производных функций - COALESCE() и NULLIF(). Выражение CASE() может быть использовано для вывода константного значения, условно определенного во время выполнения в соответствии со значением указанного столбца в текущей строке запрашиваемой таблицы.
CASE()
Функция CASE() позволяет сделать вывод для столбца зависимым от результата вычисления группы взаимоисключающих условий.
Доступность
DSQL, PSQL, ISQL, ESQL, Firebird 1.5 и выше. Любая платформа.
Синтаксис
CASE {<значение 1> | <пустое-предложение>}
WHEN {{NULL | <значение 2>} | <предикат-поиска> }
THEN {<результат 1> | NULL }
WHEN...THEN {<результат 2> | NULL}
[WHEN...THEN {<результат N> | NULL}]
[ELSE {<результат (n + 1)> | NULL} ]
END [, ]
Дополнительные ключевые слова
WHEN. . .THEN являются ключевыми словами в каждом предложении условие/результат. Требуется, по меньшей мере, одно предложение условие/результат.
ELSE предшествует необязательному "последнему" результату, который возвращается, если не выполняется ни одно предыдущее предложение.
Аргументы
значение 1 является идентификатором столбца, значение которого вычисляется. Может быть опущен. В этом случае каждое предложение WHEN должно быть предикатом поиска, содержащим идентификатор этого столбца.
значение 2 является частью соответствия для условия поиска: простая константа или выражение, которое преобразуется в тип данных, совместимый с типом данных столбца.
* Если идентификатор столбца (значение 1) указан в предложении CASE, то значение 2 остается одиночным в каждом предложении WHEN и будет сравниваться со значением 1.
* Если идентификатор столбца не указан в предложении CASE, то и значение 1, и значение 2 включаются в каждое предложение WHEN в качестве предиката поиска в форме (значение 1 = значение 2).
результат 1 - тот результат, который будет возвращен в случае, когда значение i будет соответствовать значению 2.
результат 2- тот результат, Который будет возвращен в случае, когда значение 1 будет соответствовать значению 3 и т.д.
Возвращаемое значение
Предложение CASE возвращает единственное значение. Если не выполняется ни одно условие и не указано предложение ELSE, то возвращаемый результат будет NULL.
Замечания
Вы должны использовать одну форму синтаксиса или другую. Смешанный синтаксис недопустим.
Использование единственного предложения условие/результат имеет смысл, только если присутствует предложение ELSE. Хотя это менее элегантно, чем использование соответствующих функций COALESCE() и NULLIF().
Примеры
Следующие два примера демонстрируют, как могут быть использованы каждый из двух вариантов синтаксиса для оперирования с одним и тем же набором предикатов.
Простой синтаксис:
SELECT
о.ID,
о.Description,
CASE о.Status
WHEN 1 THEN 'confirmed'
WHEN 2 THEN 'in production'
WHEN 3 THEN 'ready'
WHEN 4 THEN 'shipped'
ELSE 'unknown status ''' || o.Status || ''''
END
FROM Orders o;
Этот синтаксис использует предикаты поиска в предложении WHEN:
SELECT
о.ID,
о.Description,
CASE
WHEN (о.Status IS NULL) THEN 'new'
WHEN (o.Status = 1) THEN 'confirmed'
WHEN (o.Status = 3) THEN 'in production'
WHEN (o.Status = 4) THEN 'ready'
WHEN (o.Status = 5) THEN 'shipped'
ELSE 'unknown status ''' || o.Status || ''''
END
FROM Orders o;
COALESCE()
Функция COALESCE() позволяет вычислять значение столбца с использованием серии выражений, из которых первое выражение, возвращающее непустое значение, вернет выходное значение.
Доступность
DSQL, PSQL, ISQL, ESQL, Firebird 1.5 и выше. Любая платформа.
Синтаксис
COALESCE (<значение 1> { , значение 2 [, ... значение n]})
Аргументы
значение 1 - значение столбца или выражение, которое будет вычисляться. Если это не NULL, то оно будет возвращаемым значением.
значение 2 - значение столбца или выражение, которое будет вычисляться, если значение 1 будет NULL.
значение n - последнее значение или выражение, которое будет вычисляться, если предыдущие значения в списке будут NULL.
Возвращаемое значение
Возвращает первое не NULL значение из списка.
Замечания
Функция COALESCE о может быть использована для вычисления пары условий или списка из трех или более условий.
В первом варианте (простого, двоичного) синтаксиса COALESCE (значение1, Значение2) логика вычисления эквивалентна:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
Если присутствуют три или более аргумента - COALESCE (значение1, значение2, ... значениеN), - логика вычисления эквивалентна следующей:
CASE
WHEN V1 IS NOT NULL THEN V1
ELSE COALESCE (V2,...,Vn)
END
Последнее значение в списке должно быть задано, чтобы быть уверенным, что что-то будет возвращено.
Пример
В первом запросе если соединение не находит соответствия в таблице EMPLOYEE для TEAM_LEADER из таблицы PROJECT, то запрос вернет строку ' [Not assigned]' (Не назначено) вместо NULL, которое в противном случае должно было вернуть внешнее соединение в качестве значения FULL NAME:
SELECT
PROJ_NAME AS Projectname,
COALESCE (e. FULL_NAME, ' [Not assigned]') AS Employeename
FROM PROJECT p
LEFT JOIN EMPLOYEE e ON (e.EMP_NO = p. TEAM_LEADER) ;
В следующем запросе вычисление начинается с самой левой позиции в списке. Если присутствует значение PHONE, запрос проверяет, присутствует ли значение MOBILEPHONE, иначе он возвращает строку 'unknown' (Неопределенный):
SELECT
COALESCE(Phone, MobilePhone, 'Unknown') AS Phonenumber
FROM Relations
Связанные или похожие функции
Пользователи Firebird 1.0.x, смотрите внешние функции INVL() и SNVL().
NULLIF()
NULLIF() возвращает NULL в случае соответствия двух непустых значений, иначе он возвращает значение подвыражения.
Доступность
DSQL, PSQL, ISQL, ESQL, Firebird 1.5 и выше. Любая платформа.
Синтаксис
NULLIF (значение 1, значение 2)
Аргументы
значение 1 - столбец или вычисляемое выражение.
значение 2- константа или выражение, с которым сравнивается значение 1. Если они соответствуют, NULLIF возвращает NOLL.
Возвращаемое значение
Возвращаемое значение будет NULL, если значение 1 и значение 2 соответствуют друг другу. ЕСЛИ нет соответствия, возвращается значение 1.
Замечания
NULLIFO является сокращением для следующего выражения CASE: CASE WHEN(VALUE_1 = value_2) THEN NULL ELSE VALUE_1 END
Пример
Этот оператор устанавливает значение столбца STOCK из таблицы PRODUCTS в ULL для всех строк, где его текущее значение равно нулю:
UPDATE PRODUCTS
SET STOCK = NULLIF(STOCK, 0)
Связанные или похожие функции
Пользователи Firebird 1.0.x, смотрите INULLIF() и SNULLIF().
- Разбор XPath-выражений
- Поиск файлов с использованием регyлярных выражений
- Синтаксический анализ выражений
- 4.12. Вычисление выражений
- Использование регулярных выражений
- Использование контролируемых выражений для назначения точек останова
- Курс на использование условных выражений
- Работа с окном Add Watch
- 4.16. Повышение привилегий
- Блоки
- Основная форма If...Then
- ГЛАВА 21. Выражения и предикаты.