Руководство по языку SQL СУБД Firebird 2.5 Firebird
Firebird Firebird

Содержание

Введение
1. Структура языка
Общие сведения
Основные сведения: операторы, предложения, ключевые слова
Идентификаторы
Литералы
Операторы и специальные символы
Комментарии
2. Типы данных
Целочисленные типы данных
Типы данных с плавающей точкой
Типы данных с фиксированной точкой
Типы данных для работы с датой и временем
Символьные типы данных
Бинарные типы данных
Специальные типы данных
Преобразование типов данных
Пользовательские типы данных — домены
3. Общие элементы языка
Выражения
Подзапросы
Предикаты
4. Операторы DDL
DATABASE
SHADOW
DOMAIN
TABLE
INDEX
VIEW
TRIGGER
PROCEDURE
EXTERNAL FUNCTION
FILTER
SEQUENCE (GENERATOR)
EXCEPTION
COLLATION
CHARACTER SET
ROLE
COMMENTS
5. Операторы DML
SELECT
INSERT
UPDATE
UPDATE OR INSERT
DELETE
MERGE
EXECUTE PROCEDURE
EXECUTE BLOCK
6. Процедурный язык PSQL
Элементы PSQL
Хранимые процедуры
Хранимые функции
PSQL блоки
Триггеры
Написание кода тела модуля
7. Встроенные функции и переменные
Контекстные переменные
Скалярные функции
Агрегатные функции
8. Управление транзакциями
Операторы управления транзакциями
9. Безопасность
Аутентификация пользователя
Операторы управления пользователями
SQL привилегии
A. Дополнительные статьи
Поле RDB$VALID_BLR
Замечание о равенстве
B. Обработка ошибок, коды и сообщения
Коды ошибок SQLSTATE и их описание
Коды ошибок GDSCODE их описание, и SQLCODE
C. Зарезервированные и ключевые слова
Зарезервированные слова
Ключевые слова
D. Описания системных таблиц
RDB$BACKUP_HISTORY
RDB$CHARACTER_SETS
RDB$CHECK_CONSTRAINTS
RDB$COLLATIONS
RDB$DATABASE
RDB$DEPENDENCIES
RDB$EXCEPTIONS
RDB$FIELD_DIMENSIONS
RDB$FIELDS
RDB$FILES
RDB$FILTERS
RDB$FORMATS
RDB$FUNCTION_ARGUMENTS
RDB$FUNCTIONS
RDB$GENERATORS
RDB$INDEX_SEGMENTS
RDB$INDICES
RDB$LOG_FILES
RDB$PAGES
RDB$PROCEDURE_PARAMETERS
RDB$PROCEDURES
RDB$REF_CONSTRAINTS
RDB$RELATION_CONSTRAINTS
RDB$RELATION_FIELDS
RDB$RELATIONS
RDB$ROLES
RDB$SECURITY_CLASSES
RDB$TRANSACTIONS
RDB$TRIGGER_MESSAGES
RDB$TRIGGERS
RDB$TYPES
RDB$USER_PRIVILEGES
RDB$VIEW_RELATIONS
E. Описания таблиц мониторинга
MON$ATTACHMENTS
MON$CALL_STACK
MON$CONTEXT_VARIABLES
MON$DATABASE
MON$IO_STATS
MON$MEMORY_USAGE
MON$RECORD_STATS
MON$STATEMENTS
MON$TRANSACTIONS
F. Наборы символов и порядки сортировки
Алфавитный указатель

Список таблиц

2.1. Типы данных Firebird
2.2. Способ физического хранения вещественных чисел
2.3. Арифметические операции для типов данных даты и времени
2.4. Последовательности сортировки для UTF8
2.5. Длина индексируемой строки и набор символов
2.6. Допустимые преобразования для функции CAST
2.7. Описание формата даты и времени
2.8. Литералы с предопределёнными значениями даты и времени
2.9. Возможности переопределения атрибутов столбцов на базе доменов
3.1. Описание элементов языка
3.2. Приоритеты типов операторов
3.3. Приоритет арифметических операторов
3.4. Приоритет операторов сравнения
3.5. Приоритет логических операторов
3.6. Параметры предиката LIKE
3.7. Параметры предиката SIMILAR TO
3.8. Результаты выполнения различных операторов сравнения
4.1. Параметры оператора CREATE DATABASE
4.2. Параметры оператора ALTER DATABASE
4.3. Параметры оператора CREATE SHADOW
4.4. Параметры оператора DROP SHADOW
4.5. Параметры оператора CREATE DOMAIN
4.6. Параметры оператора ALTER DOMAIN
4.7. Параметры оператора DROP DOMAIN
4.8. Параметры оператора CREATE TABLE
4.9. Параметры оператора ALTER TABLE
4.10. Параметры оператора DROP TABLE
4.11. Параметры оператора CREATE INDEX
4.12. Длина индексируемой строки и набор символов
4.13. Число индексов и количество столбцов
4.14. Параметры оператора ALTER INDEX
4.15. Параметры оператора DROP INDEX
4.16. Параметры оператора SET STATISTICS
4.17. Параметры оператора CREATE VIEW
4.18. Параметры оператора ALTER VIEW
4.19. Параметры оператора CREATE OR ALTER VIEW
4.20. Параметры оператора DROP VIEW
4.21. Параметры оператора RECREATE VIEW
4.22. Параметры оператора CREATE TRIGGER
4.23. Параметры оператора ALTER TRIGGER
4.24. Параметры оператора DROP TRIGGER
4.25. Параметры оператора CREATE PROCEDURE
4.26. Параметры оператора DROP PROCEDURE
4.27. Параметры оператора DECLARE EXTERNAL FUNCTION
4.28. Параметры оператора ALTER EXTERNAL FUNCTION
4.29. Параметры оператора DROP EXTERNAL FUNCTION
4.30. Параметры оператора DECLARE FILTER
4.31. Параметры оператора DROP FILTER
4.32. Параметры оператора CREATE SEQUENCE
4.33. Параметры оператора ALTER SEQUENCE
4.34. Параметры оператора DROP SEQUENCE
4.35. Параметры оператора SET GENERATOR
4.36. Параметры оператора CREATE EXCEPTION
4.37. Параметры оператора ALTER EXCEPTION
4.38. Параметры оператора CREATE OR ALTER EXCEPTION
4.39. Параметры оператора DROP EXCEPTION
4.40. Параметры оператора RECREATE EXCEPTION
4.41. Параметры оператора CREATE COLLATION
4.42. Список доступных специфичных атрибутов COLLATION
4.43. Параметры оператора DROP COLLATION
4.44. Параметры оператора ALTER CHARACTER SET
4.45. Параметры оператора CREATE ROLE
4.46. Параметры оператора DROP ROLE
4.47. Параметры оператора COMMENT ON
5.1. Параметры предложений FIRST и SKIP
5.2. Параметры списка полей оператора SELECT
5.3. Параметры предложения FROM
5.4. Параметры предложения JOIN
5.5. Параметры предложения WHERE
5.6. Параметры предложения GROUP BY
5.7. Параметры предложения PLAN
5.8. Параметры предложения ORDER BY
5.9. Параметры предложения ROWS
5.10. Влияние параметров TPB на явную блокировку
5.11. Параметры CTE
5.12. Параметры оператора INSERT
5.13. Параметры оператора UPDATE
5.14. Параметры оператора UPDATE OR INSERT
5.15. Параметры оператора DELETE
5.16. Параметры оператора MERGE
5.17. Параметры оператора EXECUTE PROCEDURE
5.18. Параметры оператора EXECUTE BLOCK
7.1. Переменные пространства имён SYSTEM
7.2. Таблица результатов функции SIGN
7.3. Спецификация опций функции TRIM
7.4. Типы и диапазоны результатов функции EXTRACT
7.5. Допустимые преобразования для функции CAST
8.1. Совместимости различных блокировок
9.1. Параметры операторов установки и отмены роли RDB$ADMIN
9.2. Параметры операторов установки и отмены роли RDB$ADMIN
9.3. Администраторы
9.4. Параметры оператора CREATE USER
9.5. Параметры оператора ALTER USER
9.6. Параметры оператора DROP USER
9.7. Параметры оператора GRANT
9.8. Параметры оператора REVOKE
B.1. Коды ошибок SQLSTATE
B.2. Коды ошибок GDSCODE, SQLCODE и их описание
D.1. Системные таблицы
D.2. Описание столбцов таблицы RDB$BACKUP_HISTORY
D.3. Описание столбцов таблицы RDB$CHARACTER_SETS
D.4. Описание столбцов таблицы RDB$CHECK_CONSTRAINTS
D.5. Описание столбцов таблицы RDB$COLLATIONS
D.6. Описание столбцов таблицы RDB$DATABASE
D.7. Описание столбцов таблицы RDB$DEPENDENCIES
D.8. Описание столбцов таблицы RDB$EXCEPTIONS
D.9. Описание столбцов таблицы RDB$FIELD_DIMENSIONS
D.10. Описание столбцов таблицы RDB$FIELDS
D.11. Описание столбцов таблицы RDB$FILES
D.12. Описание столбцов таблицы RDB$FILTERS
D.13. Описание столбцов таблицы RDB$FORMATS
D.14. Описание столбцов таблицы RDB$FUNCTION_ARGUMENTS
D.15. Описание столбцов таблицы RDB$FUNCTIONS
D.16. Описание столбцов таблицы RDB$GENERATORS
D.17. Описание столбцов таблицы RDB$INDEX_SEGMENTS
D.18. Описание столбцов таблицы RDB$INDICES
D.19. Описание столбцов таблицы RDB$LOG_FILES
D.20. Описание столбцов таблицы RDB$PAGES
D.21. Описание столбцов таблицы RDB$PROCEDURE_PARAMETERS
D.22. Описание столбцов таблицы RDB$PROCEDURES
D.23. Описание столбцов таблицы RDB$REF_CONSTRAINTS
D.24. Описание столбцов таблицы RDB$RELATION_CONSTRAINTS
D.25. Описание столбцов таблицы RDB$RELATION_FIELDS
D.26. Описание столбцов таблицы RDB$RELATIONS
D.27. Описание столбцов таблицы RDB$ROLES
D.28. Описание столбцов таблицы RDB$SECURITY_CLASSES
D.29. Описание столбцов таблицы RDB$TRANSACTIONS
D.30. Описание столбцов таблицы RDB$TRIGGER_MESSAGES
D.31. Описание столбцов таблицы RDB$TRIGGERS
D.32. Описание столбцов таблицы RDB$TYPES
D.33. Описание столбцов таблицы RDB$USER_PRIVILEGES
D.34. Описание столбцов таблицы RDB$VIEW_RELATIONS
E.1. Таблицы мониторинга
E.2. Описание столбцов таблицы MON$ATTACHMENTS
E.3. Описание столбцов таблицы MON$CALL_STACK
E.4. Описание столбцов таблицы MON$CONTEXT_VARIABLES
E.5. Описание столбцов таблицы MON$DATABASE
E.6. Описание столбцов таблицы MON$IO_STATS
E.7. Описание столбцов таблицы MON$MEMORY_USAGE
E.8. Описание столбцов таблицы MON$RECORD_STATS
E.9. Описание столбцов таблицы MON$STATEMENTS
E.10. Описание столбцов таблицы MON$TRANSACTIONS
F.1. Наборы символов и порядки сортировки

Список примеров

1.1. Комментарии
2.1. Использование типа BIGINT
2.2. Использование чисел типа BIGINT заданных шестнадцатеричном виде
3.1. Подзапрос в качестве выходного столбца в списке выбора
3.2. Подзапрос в предложении WHERE для получения значения максимальной зарплаты сотрудника и фильтрации по нему
3.3. Предикат IS NULL
3.4. Предикат EXISTS
3.5. Предикат NOT EXISTS
3.6. Предикат IN
3.7. Поисковый предикат IN
3.8. Предикат SINGULAR
3.9. Квантор ALL
3.10. Квантор ANY
4.1. Создание базы данных в операционной системе Windows
4.2. Создание базы данных в операционной системе Linux
4.3. Создание базы данных на удалённом сервере
4.4. Создание многофайловой базы данных
4.5. Создание многофайловой базы данных 2
4.6. Добавление вторичного файла в базу данных
4.7. Установка пути и имени файла дельты
4.8. Удаление описание файла дельты
4.9. Перевод базы данных в режим «безопасного копирования»
4.10. Возвращение базы данных в режим нормального функционирования из режима «безопасного копирования»
4.11. Удаление базы данных
4.12. Создание теневую копию базы данных с номером 1
4.13. Создание многофайловой теневой копии
4.14. Удаление теневой копии с номером 1
4.15. Создание домена, который может принимать значения больше 1000.
4.16. Создание домена, который может принимать значения 'Да' и 'Нет'.
4.17. Создание домена с набором символов UTF8 и порядком сортировки UNICODE_CI_AI.
4.18. Создание домена со значением по умолчанию.
4.19. Создание домена, определённого как массив из 2 элементов.
4.20. Изменение значения по умолчанию для домена.
4.21. Переименование домена.
4.22. Удаление значения по умолчанию и добавления ограничения для домена.
4.23. Изменение ограничения домена.
4.24. Изменение типа домена.
4.25. Удаление домена
4.26. Создание таблицы
4.27. Создание таблицы с заданием именованного первичного и уникального ключей
4.28. Таблица с полем массивом
4.29. Создание таблицы с ограничением первичного, внешнего и уникального ключа для которых заданы пользовательские имена индексов
4.30. Создание таблицы с вычисляемыми полями
4.31. Создание глобальной временной таблицы уровня соединения
4.32. Создание глобальной временной таблицы уровня транзакции ссылающейся внешним ключом на глобальную временную таблицу уровня соединения.
4.33. Добавление столбца в таблицу
4.34. Добавление столбца с ограничением уникальности и удаление другого столбца
4.35. Добавление столбца с ограничением NOT NULL
4.36. Добавление проверочного ограничения и внешнего ключа
4.37. Модификация сразу нескольких столбцов таблицы
4.38. Изменение вычисляемых столбцов
4.39. Удаление таблицы
4.40. Создание или пересоздание таблицы
4.41. Создание индекса
4.42. Создание индекса с сортировкой ключей по убыванию
4.43. Создание многосегментного индекса
4.44. Создание индекса, не допускающего дубликаты значений
4.45. Создание вычисляемого индекса
4.46. Перевод индекса в неактивное состояние
4.47. Возврат индекса в активное состояние
4.48. Удаление индекса
4.49. Пересчёт селективности индекса
4.50. Создание представления
4.51. Создание представления с проверкой условия фильтрации
4.52. Создание представления с использованием списка столбцов
4.53. Создание представления с использованием псевдонимов полей
4.54. Создание необновляемого представления с использованием хранимой процедуры
4.55. Создание обновляемого представления с использованием триггеров
4.56. Изменение представления
4.57. Создание нового или изменение существующего представления
4.58. Удаление представления
4.59. Создание нового или пересоздание существующего представления
4.60. Создание DML триггера в Legacy стиле
4.61. Создание DML триггера согласно стандарту SQL-2003
4.62. Создание DML триггера на несколько событий
4.63. Создание триггера на событие подключения к БД для логирования события
4.64. Создание триггера на событие подключения к БД для контроля доступа
4.65. Отключение (перевод в неактивное состояние) триггера
4.66. Изменение позиции триггера
4.67. Перевод триггера в неактивное состояние и изменение списка событий
4.68. Перевод триггера в активное состояние, изменение его позиции и его тела
4.69. Создание нового или изменение существующего триггера
4.70. Удаление триггера
4.71. Создание или пересоздание триггера
4.72. Создание хранимой процедуры
4.73. Изменение хранимой процедуры
4.74. Создание или изменение хранимой процедуры
4.75. Удаление хранимой процедуры
4.76. Создание новой или пересоздание существующей хранимой процедуры
4.77. Объявление внешней функции с передачей входных и выходных параметров по ссылке
4.78. Объявление внешней функции с передачей входных и выходных параметров по дескриптору
4.79. Объявление внешней функции с передачей входных параметров по ссылке, выходных по значению
4.80. Объявление внешней функции с передачей входных и выходных параметров по дескриптору. В качестве выходного параметра используется второй параметр функции.
4.81. Изменение точки входа для внешней функции
4.82. Изменение имени модуля для внешней функции
4.83. Удаление внешней функции
4.84. Создание BLOB фильтра с использованием номеров подтипов
4.85. Создание BLOB фильтра с использованием мнемоник подтипов
4.86. Удаление BLOB фильтра
4.87. Создание последовательности
4.88. Изменение последовательности
4.89. Удаление последовательности
4.90. Установка значения для последовательности
4.91. Создание пользовательского исключения
4.92. Изменение текста сообщения пользовательского исключения
4.93. Создание или изменение пользовательского исключения
4.94. Удаление пользовательского исключения
4.95. Создание или пересоздание пользовательского исключения
4.96. Создание сортировки с использованием имени, найденном в файле fbintl.conf (регистро-чувствительно).
4.97. Создание сортировки с использованием специального (заданного пользователем) названия («external» имя должно в точности соответствовать имени в файле fbintl.conf).
4.98. Создание регистронезависимой сортировки на основе уже присутствующей в базе данных.
4.99. Создание регистронезависимой сортировки на основе уже присутствующей в базе данных со специфичными атрибутами.
4.100. Создание регистронезависимой сортировки по значению чисел (так называемой натуральной сортировки).
4.101. Удаление сортировки
4.102. Установка сортировки UNICODE_CI_AI по умолчанию для кодировки UTF8
4.103. Создание роли.
4.104. Удаление роли.
4.105. Добавление комментария для текущей базы данных.
4.106. Добавление комментария для таблицы.
4.107. Добавление комментария для поля таблицы.
4.108. Добавление комментария для параметра процедуры.
5.1. Запрос с использованием CTE
5.2. Рекурсивное CTE
5.3. Использование предложения RETURNING в операторе INSERT
5.4. Использование предложения ROWS в операторе UPDATE
5.5. Использование предложения RETURNING в операторе UPDATE
5.6. Использование предложения WHERE в операторе DELETE
6.1. Задание альтернативного терминатора
6.2. Использование оператора присваивания
6.3. Различные способы объявления локальных переменных
6.4. Объявление именованного курсора
6.5. Использования именованных скриптов в PSQL блоке для получения списка скриптов для создания представлений
6.6. Использование BEGIN ... END
6.7. Использование оператора IF
6.8. Использование оператора WHILE ... DO
6.9. Использование оператора LEAVE
6.10. Использование оператора LEAVE с меткой
6.11. Использование оператора EXIT в процедуре выбора.
6.12. Использование оператора SUSPEND в процедуре выбора.
6.13. С именованными параметрами:
6.14. С позиционными параметрами:
6.15. Использование оператора FOR SELECT
6.16. Вложенный FOR SELECT
6.17. Использование предложения AS CURSOR для позиционного удаления записи
6.18. Использование оператора EXECUTE STATEMENT.
6.19. Использования оператора FETCH
6.20. Использования оператора FETCH со вложенными курсорами
6.21. Использование автономных транзакций
6.22. Оповещёние приложения о вставке записи в таблицу SALES
6.23. Вызов исключения
6.24. Вызов исключения с заменой исходного сообщения альтернативным
6.25. Регистрация ошибке в журнале и повторное её возбуждение в блоке WHEN
6.26. Замена стандартной ошибки своей.
6.27. Регистрация ошибке в журнале и повторное её возбуждение в блоке WHEN.
6.28. Обработка в одном WHEN … DO блоке нескольких ошибок
7.1. Использование переменной CURRENT_CONNECTION
7.2. Использование переменной CURRENT_DATE
7.3. Использование переменной CURRENT_ROLE
7.4. Использование переменной CURRENT_TIME
7.5. Использование переменной CURRENT_TIMESTAMP
7.6. Использование переменной CURRENT_TRANSACTION
7.7. Использование переменной CURRENT_USER
7.8. Использование переменной DELETING
7.9. Использование переменной GDSCODE
7.10. Использование переменной INSERTING
7.11. Использование переменной NEW
7.12. Использование переменной 'NOW'
7.13. Использование переменной OLD
7.14. Использование переменной ROW_COUNT
7.15. Использование переменной SQLCODE
7.16. Использование переменной SQLSTATE
7.17. Использование переменной 'TODAY'
7.18. Использование переменной 'TOMORROW'
7.19. Использование переменной UPDATING
7.20. Использование переменной 'YERSTERDAY'
7.21. Использование переменной USER
7.22. Использование функции RDB$GET_CONTEXT
7.23. Использование функции RDB$SET_CONTEXT
7.24. Использование функций для работы с контекстными переменными
7.25. Использование функции ROUND
7.26. Использование функции TRUNC
7.27. Использование функции BIT_LENGTH
7.28. Использование функции LEFT
7.29. Использование функции LOWER
7.30. Использование функции LPAD
7.31. Использование функции OCTET_LENGTH
7.32. Использование функции OVERLAY
7.33. Использование функции POSITION
7.34. Использование функции REPLACE
7.35. Использование функции REVERSE
7.36. Использование функции RIGHT
7.37. Использование функции RPAD
7.38. Использование функции SUBSTRING
7.39. Использование функции TRIM
7.40. Использование функции UPPER
7.41. Использование функции DATEADD
7.42. Использование функции DATEDIFF
7.43. Использование функции EXTRACT
7.44. Использование функции CHAR_TO_UUID
7.45. Использование функции GEN_UUID
7.46. Использование функции UUID_TO_CHAR
7.47. Использование функции GEN_ID
7.48. Использование функции COALESCE
7.49. Использование функции COALESCE с агрегатными функциями
7.50. Использование функции DECODE
7.51. Использование функции IIF
7.52. Использование функции MAXVALUE
7.53. Использование функции MINVALUE
7.54. Использование функции NULLIF
7.55. Использование функции AVG
7.56. Использование функции COUNT
7.57. Использование функции LIST
7.58. Использование функции MAX
7.59. Использование функции MIN
7.60. Использование функции SUM
8.1. DSQL сессия с использованием точек сохранения
9.1. Создание пользователя.
9.2. Создание пользователя John с дополнительными атрибутами (именем и фамилией).
9.3. Создание пользователя с возможностью управления пользователями.
9.4. Изменение пользователя и выдача ему привилегии управления пользователями.
9.5. Изменение дополнительных атрибутов своей учётной записи.
9.6. Отбор привилегии управления пользователями у пользователя.
9.7. Удаление пользователя.
9.8. Назначение привилегий для таблиц
9.9. Назначение привилегии EXECUTE
9.10. Назначение ролей для пользователей
9.11. Отзыв привилегий на таблицу
9.12. Отзыв привилегии EXECUTE
9.13. Отзыв ролей
9.14. Отзыв всех привилегий и ролей у пользователя
E.1. Получение сведений о клиентских приложениях
E.2. Отключение всех соединений, за исключением своего
E.3. Получение стека вызовов для всех подключений кроме своего
E.4. Получение всех сессионных контекстных переменных для текущего подключения
E.5. Получение 10 запросов потребляющих наибольшее количество памяти
E.6. Отображение активных запросов за исключением тех, что выполняются в своём соединении
E.7. Отмена всех активных запросов для заданного соединения
E.8. Получение уровня изолированности текущей транзакций

Введение

Это руководство описывает язык SQL, поддерживаемый СУБД Firebird 2.5.

Известно, что СУБД Firebird начала создаваться на основе открытого кода СУБД InterBase 6.0. В период с 2000 по 2014 год было выпущено 5 основных релизов Firebird: 1.0.х.х; 1.5.х; 2.0.х; 2.1.х; 2.5.х., релиз 3.0 запланирован на 2015 год.

Однако, за всё время развития проекта Firebird, а это более 10 лет, на русском языке до сих пор не было создано единой документации. Несколько переведённых материалов по отдельным вопросам можно было найти на официальном сайте http://www.firebirdsql.org/en/reference-manuals/. Но их, во-первых, очень мало, а во-вторых зачастую они уже неактуальны. Наибольшее количество русскоязычной информации о Firebird находится на сайте http://ibase.ru/develop.htm, за что огромное спасибо Дмитрию Кузьменко (IBSurgeon/iBase.ru).

Но единой русскоязычной документации о Firebird до сих пор не существовало.

Данное «Руководство по языку SQL СУБД Firebird» — это первый русскоязычный документ, полностью освещающий все аспекты и особенности работы с языком SQL Firebird для текущей (актуальной) на сегодняшний день версии СУБД Firebird 2.5. В руководстве также приводятся практические примеры использования SQL, многие из которых взяты из реальной практики.

Что содержит данный документ

Данный документ содержит описание языка SQL Firebird. Он охватывает следующие основные области:

  • Основные положения;

  • Зарезервированные и ключевые слова;

  • Типы и подтипы данных;

  • Операторы DDL (Data Definition Language — язык создания данных);

  • Операторы DML (Data Manipulation Language — язык обращения с данными);

  • Операторы управления транзакциями;

  • Обработка исключений;

  • Операторы PSQL (Procedural SQL — процедурный SQL, используется в хранимых процедурах, триггерах и выполнимых блоках);

  • Безопасность и операторы управления доступом;

  • Операторы и предикаты (утверждения);

  • Агрегатные функции;

  • Встроенные функции;

  • Коды ошибок и обработка исключительных ситуаций;

  • Описание системных таблиц и таблиц мониторинга;

  • Наборы символов и соответствующие им порядки сортировки.

Вопросы, не связанные с SQL в данном документе не рассматриваются.

Авторство

В работе над руководством принимали участие:

  • Симонов Денис;

  • Винкенуг Пол;

  • Дмитрий Филиппов;

  • Дмитрий Еманов;

  • Томас Воинк;

  • Александр Карпейкин;

  • Алексей Ковязин;

  • Дмитрий Кузьменко.

Редакторы – Александр Карпейкин, Дмитрий Кузьменко, Алексей Ковязин, Денис Симонов.

Спонсоры

Платиновым спонсором создания «Руководства по языку СУБД Firebird» является Московская Биржа www.moex.com.

Московская Биржа — крупнейший в России и Восточной Европе биржевой холдинг, образованный 19 декабря 2011 года в результате слияния биржевых групп ММВБ (основана в 1992) и РТС (основана в 1995). Московская Биржа входит в двадцатку ведущих мировых площадок по объему торгов ценными бумагами, суммарной капитализации торгуемых акций и в десятку крупнейших бирж производных финансовых инструментов.

Золотым спонсором «Руководства по языку СУБД Firebird» является IBSurgeon (iBase.ru) (www.ib-aid.com, www.ibase.ru): техническая поддержка и инструменты разработчика и администратора для СУБД Firebird.

Благодарности

Благодарим Влада Хорсуна, Александра Пешкова, Павла Зотова за помощь в создании этого документа.

Лицензионные замечания

Содержание данного Документа распространяется на условиях лицензии «Public Documentation License Version 1.0» (далее «Лицензия»); Вы можете использовать этот Документ, только если согласны с условиями Лицензии. Копии текста Лицензии доступны по адресам http://www.firebirdsql.org/pdfmanual/pdl.pdf (PDF) и http://www.firebirdsql.org/manual/pdl.html (HTML).

Оригинальное название документа «Руководство по языку SQL Firebird».

Copyright (C) 2015. Все права защищены. Адрес электронной почты для контакта:

Далее представлен оригинальный текст раздела, так как его перевод не имеет равноценной юридической силы.

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the "License"); you may only use this Documentation if you comply with the terms of this License. Copies of the License are available at http://www.firebirdsql.org/pdfmanual/pdl.pdf (PDF) and http://www.firebirdsql.org/manual/pdl.html (HTML).

Обновления

Так как СУБД Firebird постоянно развивается, то изменяется и улучшается его документация. Вы можете получить обновленные версии этого документа и других частей документации по Firebird на русском языке на сайте проекта документации: https://www.assembla.com/spaces/firebird-russian-documentation/wiki

Глава 1. Структура языка

Общие сведения

Подмножества SQL

SQL имеет четыре подмножества SQL, используемых в различных областях применения:

  • Динамический SQL (DSQL, Dynamic SQL)

  • Процедурный SQL (PSQL, Procedural SQL)

  • Встроенный SQL (ESQL, Embedded SQL)

  • Интерактивный SQL (ISQL, Interactive SQL)

Динамический SQL является основной частью языка, которая соответствует Части 2 (SQL/Foundation – SQL/Основы) спецификации SQL. DSQL представляет собой конструкции, которые передаются клиентскими приложениями с помощью Firebird API и обрабатываются сервером базы данных.

Процедурный SQL является расширением Динамического SQL, в котором дополнительно присутствуют составные операторы, содержащие локальные переменные, присваивание, циклы и другие процедурные конструкции. PSQL относится к Части 4 (SQL/PSM) спецификации SQL. Изначально расширение PSQL было доступно только лишь в постоянно хранимых в базе модулях (процедурах и триггерах), но сравнительно недавно они стали также доступны в Динамическом SQL (смотри EXECUTE BLOCK).

Встроенный SQL определяет подмножество DSQL, поддерживаемое средством Firebird GPRE. GPRE — приложение-препроцессор, которое позволяет вам внедрять SQL конструкции в ваш непосредственный язык программирования (C, C++, Pascal, Cobol и так далее) и производить обработку этих внедренных конструкций в правильные вызовы Firebird API. Обратите внимание, что ESQL поддерживает только часть конструкций и выражений DSQL.

Интерактивный SQL подразумевает собой язык, который может быть использован для работы с приложением командной строки Firebird ISQL для интерактивного доступа к базам данных. ISQL является обычным клиентским приложением. Для него обычный язык – это язык DSQL. Однако приложение поддерживает несколько дополнительных команд.

Оба языковых подмножества, как DSQL, так и PSQL полностью представлены в данном руководстве. Из набора инструментария ни ESQL, ни ISQL не описаны здесь отдельно, за исключением тех мест, где это не указано явно.

Диалекты SQL

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

  • В 1-м диалекте дата и время хранятся в типе данных DATE, и имеется тип данных TIMESTAMP, который идентичен DATE. Двойные кавычки используются для разграничения строковых данных. Точность типов данных NUMERIC и DECIMAL меньше, чем в 3-м диалекте и в случае, если значение точности более 9, Firebird хранит такие значения как длинные значения с плавающей точкой. BIGINT не является доступным типом данных. Идентификаторы являются регистро-независимыми. Значение генераторов хранится как 64 битное целое, а при выдаче значения усекается до 32 битного целого;

  • Диалект 2 доступен только в клиентском соединении к Firebird и не может быть применен к базе данных. Он предназначен для того, чтобы помочь в отладке в случае возможных проблем с целостностью данных при проведении миграции с диалекта 1 на 3;

  • Диалект 3 базы данных позволяет хранить числа (типы данных DECIMAL и NUMERIC) в базе данных как длинные значения с фиксированной точкой (масштабируемые целые числа) в случае если точность числа меньше чем 9. Тип данных TIME доступен и используется для хранения значения только времени. Тип данных DATE хранит информацию о дате. Тип данных BIGINT доступен в качестве целого 64-х битного типа данных. Двойные кавычки могут использоваться, но только для идентификаторов, которые являются зависимыми от регистра, а не для строковых данных, для которых используют одинарные кавычки. Значения генераторов хранятся как 64-ти битные целые значения. Новую базу данных Firebird создает в 3-м диалекте.

Целью 1-го диалекта является обеспечение поддержки для унаследованных (пре-версия IB6) Interbase приложений для работы с Firebird. Диалект 2 используется как промежуточный и предназначен для разрешения проблем при миграции с 1-го в 3-й диалект. Для вновь разрабатываемых баз данных и приложений настоятельно рекомендуется использовать 3-й диалект. Диалект при соединении с базой данных должен быть таким же, как и базы данных. Исключением является случай миграции с 1-го в 3-й диалект, когда в строке соединения с базой данных используется 2-й диалект.

Замечание:

По умолчанию это руководство описывает семантику SQL третьего диалекта, если только в тексте явно не указывается диалект.

Действия при ошибках

Обработка любого оператора либо успешно завершается, либо прерывается из-за вызванной определенными условиями ошибки. Обработку ошибок можно производить, как в клиентском приложении, так и на стороне сервера средствами SQL.

Основные сведения: операторы, предложения, ключевые слова

Основная конструкция SQL — оператор (statement). Оператор описывает, что должна выполнить система управления базами данных с конкретным объектом данных или метаданных, обычно не указывая, как именно это должно быть выполнено. Достаточно сложные операторы содержат более простые конструкции — предложения (clause) и варианты, альтернативы (options). Предложение описывает некую законченную конструкцию в операторе. Например, предложение WHERE в операторе SELECT и в ряде других операторов (UPDATE, DELETE) задаёт условия поиска данных в таблице (таблицах), подлежащих выборке, изменению, удалению. Предложение ORDER BY задаёт характеристики упорядочения выходного, результирующего, набора данных. Альтернативы, будучи наиболее простыми конструкциями, задаются при помощи конкретных ключевых слов и определяют некоторые дополнительные характеристики элементов предложения (допустимость дублирования данных, варианты использования и др.).

В SQL существуют ключевые слова и зарезервированные слова. Ключевые слова — это все слова, входящие в лексику (словарь) языка SQL. Ключевые слова можно (но не рекомендуется) использовать в качестве имён, идентификаторов объектов базы данных, внутренних переменных и параметров. Зарезервированные слова — это те ключевые слова, которые нельзя использовать в качестве имён объектов базы данных, переменных или параметров.

Например, следующий оператор будет выполнен без ошибок потому, что ABS является ключевым, но не зарезервированным словом.

CREATE TABLE T (ABS INT NOT NULL);
        

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

CREATE TABLE T (ADD INT NOT NULL);
        

Список зарезервированных и ключевых слов представлен в приложении Зарезервированные и ключевые слова.

Идентификаторы

Все объекты базы данных имеют имена, которые иногда называют идентификаторами. Максимальная длина идентификатора составляет 31 байт. Существует два типа имён — имена, похожие по форме на имена переменных в обычных языках программирования, и имена с разделителями (delimited name), которые являются отличительной особенностью языка SQL.

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

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

fullname
FULLNAME
FuLlNaMe
FullName
            

Синтаксис: 

<name> ::=
  <letter> |
  <name><letter> |
  <name><digit> |
  <name>_ |
  <name>$
  
<letter> ::= <upper letter> | <lower letter>

<upper letter> ::= A | B | C | D | E | F | G | H | I | J | K | L | M 
    | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

<lower letter> ::= a | b | c | d | e | f | g | h | i | j | k | l | m 
    | n | o | p | q | r | s | t | u | v | w | x | y | z

<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9  
                

Имя с разделителями заключается в двойные кавычки. Оно может содержать любые символы, включая буквы кириллицы, пробелы, специальные символы. В нем также могут присутствовать зарезервированные слова. Такое имя является чувствительным к регистру. Имена с разделителем доступны только в диалекте 3. Подробнее о диалектах см. Диалекты SQL

  
<delimited name> ::= "<ASCII char>[<ASCII char> …]"                
            

Следует иметь в виду, что конечные пробелы в именах с разделителями, как и в любых строковых константах, отбрасываются.

Существует определённая похожесть и отличие обычных имён и имён с разделителями. Такие имена с разделителями и обычные, как "FULLNAME" и FULLNAME являются одинаковыми, а "FullName" и FULLNAME (так же как, например, и FullName) отличаются.

Литералы

Литералы служат для непосредственного представления данных, ниже приведён список стандартных литералов:

  • целочисленные — 0, -34, 45;

  • вещественные — 0.0, -3.14, 3.23e-23;

  • строковые — 'текст', 'don''t!';

  • дата — DATE '10.01.2014';

  • время — TIME '15:12:56';

  • временная отметка — TIMESTAMP '10.01.2014 13:32:02';

  • неопределённое состояние — null.

Подробней о литералах для каждого из типов данных см. Типы и подтипы данных.

Операторы и специальные символы

Существует набор специальных символов, используемых в качестве разделителей.

 
<special char> ::= <space> | " | % | & | ' | ( | ) 
    | * | + | , | - | . | / | : | ; | < | = | > | ? | [ | ] 
    | ^ | { | } | |               
            

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

Синтаксис: 

  
<operator> ::=
    <string operator> |
    <arithmetic operator> |
    <comparison operator> |  
    <logical operator>
        
<string operator> ::= {||}
                    
<arithmetic operator> ::= * | / | + | - |
                    
<comparison operator> ::=
    {=} | {<>} | {!=} | {~=} | {^=} |
    {>} | {<} | {>=} | {<=} | {!>} | {~>} | {^>} |
    {!<} | {~<} | {^<}
    
<logical operator> ::= NOT | AND | OR                    
                

Подробнее об операторах см. Выражения.

Комментарии

В SQL скриптах, операторах SQL и PSQL модулях могут встречаться комментарии. Комментарий — это произвольный текст заданный пользователем, предназначенный для пояснения работы отдельных частей программы. Синтаксический анализатор игнорирует текст комментариев. В Firebird поддерживается два типа комментариев: блочные и однострочные.

Синтаксис: 

   
<comment> ::= <block comment> | <single-line comment>
    
<block comment> ::=
    /*<ASCII char>[<ASCII char> …]*/
    
<single-line comment> ::=
    --<ASCII char>[<ASCII char> …]<end line>                    
                

Блочные комментарии начинается с символов /* и заканчивается символами */. Блочные комментарии могут содержать текст произвольной длины и занимать несколько строк.

Примечание

Однако, если в блоке комментария присутствует последовательность символов '*/', то блочный комментарий будет немедленно завершён при обнаружении символов */.

Однострочные комментарии начинаются с символов -- и действуют до конца текущей строки.

Пример 1.1. Комментарии

CREATE PROCEDURE P(APARAM INT)
RETURNS (B INT)
AS
BEGIN
  /* Данный текст не будет учитываться
     при работе процедуры, т.к. является комментарием
   */
  B = A + 1; -- Однострочный комментарий
  SUSPEND;
END
                


Глава 2. Типы данных

Типы данных используются в случае:

  • определения столбца в таблице базы данных в операторе CREATE TABLE или для его изменения с использованием ALTER TABLE;

  • при объявлении и редактировании домена оператором CREATE DOMAIN/ALTER DOMAIN;

  • при объявлении локальных переменных в хранимых процедурах, PSQL-блоках и триггерах, при указании аргументов хранимых процедур;

  • при описании внешних функций (UDF – функций, определённых пользователем) для указания аргументов и возвращаемых значений;

  • при явном преобразовании типов данных в качестве аргумента для функции CAST.

Таблица 2.1. Типы данных Firebird

Название Размер Диапазон и точность Описание
BIGINT 64 бита -263 .. 263 - 1 Тип данных доступен только в 3 диалекте.
BLOB Переменный Нет. Размер сегмента BLOB ограничивается 64К. Максимальный размер поля BLOB 4 Гб. Для размера страницы 4096 максимальный размер BLOB поля несколько ниже 2 Гб. Тип данных с динамически изменяемым размером для хранения больших данных, таких как графика, тексты, оцифрованные звуки. Базовая структурная единица — сегмент. Подтип Blob описывает содержимое.

CHAR(n)

CHARACTER(n)

n символов (размер в байтах зависит от кодировки, кол-во байт на символ) от 1 до 32 767 байт Символьный тип данных фиксированной длины. При отображении данных, строка дополняется пробелами справа до указанной длины. Если количество символов n не указано, то по умолчанию принимается 1.
DATE 32 бита От 01.01.0001 н.э. до 31.12.9999 н.э. ISC_DATE
DECIMAL (precision, scale) Переменный (16, 32 или 64 бита)

precision = от 1 до 18, указывает, по меньшей мере, количество цифр для хранения;

scale = от 0 до 18. Задаёт количество знаков после разделителя

scale должно быть меньше или равно precision. Число с десятичной точкой, имеющей после точки scale разрядов. Пример: DECIMAL(10,3) содержит число точно в следующем формате: ppppppp.sss.
DOUBLE PRECISION 64 бита 2,225 x 10-308 .. 1,797 x 10308 IEEE двойной точности, 15 цифр, размер зависит от платформы
FLOAT 32 бита 1,175 x 10-38 .. 3,402 x 1038 IEEE одинарной точности, 7 цифр

INTEGER

INT

32 бита –2 147 483 648 .. 2 147 483 647 signed long
NUMERIC (precision, scale) Переменный (16, 32 или 64 бита)

precision = от 1 до 18, указывает, по меньшей мере, количество цифр для хранения;

scale = от 0 до 18. Задаёт количество знаков после разделителя.

scale должно быть меньше или равно precision. Число с десятичной точкой, имеющей после точки scale разрядов. Пример: NUMERIC(10,3) содержит число точно в следующем формате: ppppppp.sss.
SMALLINT 16 бит –32 768 .. 32 767 signed short (word)
TIME 32 бита От 0:00 до 23:59:59.9999 ISC_TIME
TIMESTAMP 64 бита От 01.01.0001 н.э. до 31.12.9999 н.э. Включает информацию и о времени

VARCHAR(n)

CHAR VARYING

CHARACTER VARYING

n символов (размер в байтах зависит от кодировки, кол-ва байт на символ) От 1 до 32 765 байтов Размер символов в байтах с учётом их кодировки не может быть больше 32765. Для этого типа данных, в отличие от CHAR (где по умолчанию предполагается количество символов 1), количество символов n обязательно должно быть указано.


Примечание

Следует иметь в виду, что временной ряд из дат прошлых веков рассматривается без учёта реальных исторических фактов и так, как будто бы во всем этом диапазоне ВСЕГДА действовал только Григорианский календарь.

Целочисленные типы данных

Для целых чисел используют целочисленные типы данных SMALLINT, INTEGER и BIGINT (в 3 диалекте). Firebird не поддерживает беззнаковый целочисленный тип данных.

SMALLINT

Тип данных SMALLINT представляет собой целочисленное компактное хранилище данных и применяется в случае, когда не требуется широкий диапазон возможных значений для хранения данных.

Числа типа SMALLINT находятся в диапазоне 2-15 .. 215 - 1, или -32 768 .. 32 767.

Примеры: 

  
CREATE DOMAIN DFLAG AS SMALLINT DEFAULT 0 NOT NULL
  CHECK (VALUE=-1 OR VALUE=0 OR VALUE=1);

CREATE DOMAIN RGB_VALUE AS SMALLINT;                
                    

INTEGER

Тип данных INTEGER представляет собой 4-байтовое целое. Сокращённый вариант записи типа данных INT.

Числа типа INTEGER находятся в диапазоне 2-31 .. 231 - 1, или -2 147 483 648 .. 2 147 483 647.

Примеры: 

  
CREATE TABLE CUSTOMER (
  CUST_NO INTEGER NOT NULL,
  CUSTOMER VARCHAR(25) NOT NULL,
  CONTACT_FIRST VARCHAR(15),
  CONTACT_LAST VARCHAR(20),
  CONSTRAINT INTEG_60,
  ...
  PRIMARY KEY (CUST_NO) 
);               
                    

BIGINT

BIGINT — это SQL-99-совместимый 64 битный целочисленный тип данных. Он доступен только в 3-м диалекте. При использовании клиентом диалекта 1, передаваемое сервером значение генератора усекается до 32-х битного целого (INTEGER). При подключении в 3-м диалекте значение генератора имеет тип BIGINT.

Числа типа BIGINT находятся в диапазоне 2-63 .. 263 - 1, или -9 223 372 036 854 775 808 .. 9 223 372 036 854 775 807.

Числа типа BIGINT могут быть заданы в шестнадцатеричном виде с 9 — 16 шестнадцатеричными цифрами. Более короткие шестнадцатеричные числа интерпретируются как тип данных INTEGER.

Примеры:

Пример 2.1. Использование типа BIGINT

  
CREATE TABLE WHOLELOTTARECORDS (
  ID BIGINT NOT NULL PRIMARY KEY,
  DESCRIPTION VARCHAR(32)
);


Пример 2.2. Использование чисел типа BIGINT заданных шестнадцатеричном виде

 
INSERT INTO MYBIGINTS VALUES (
  -236453287458723,
  328832607832,
  22,
  -56786237632476,
  0X6F55A09D42, -- 478177959234
  0X7FFFFFFFFFFFFFFF, -- 9223372036854775807
  0XFFFFFFFFFFFFFFFF, -- -1
  0X80000000, -- -2147483648, т.е. INTEGER
  0X080000000, -- 2147483648, т.е. BIGINT
  0XFFFFFFFF, -- -1, т.е. INTEGER
  0X0FFFFFFFF -- 4294967295, т.е. BIGINT
);               
                    


Шестнадцатеричный INTEGER автоматически приводится к типу BIGINT перед вставкой в таблицу. Однако это происходит после установки численного значения, так 0x80000000 (8 цифр) и 0x080000000 (9 цифр) будут сохранены в разных форматах. Значение 0x80000000 (8 цифр) будет сохранено в формате INTEGER, а 0x080000000 (9 цифр) как BIGINT.

Типы данных с плавающей точкой

Типы данных с плавающей точкой являются примерами данных, которые хранятся в СУБД с точностью, подходящей масштабу числа. Примерами таких данных являются FLOAT и DOUBLE PRECISION (DOUBLE).

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

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

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

FLOAT

Данный тип данных обладает приблизительной точностью 7 цифр после запятой. Для обеспечения надёжности хранения полагайтесь на 6 цифр.

DOUBLE PRECISION

При хранении данных, предполагается приблизительная точность 15 цифр.

Типы данных с фиксированной точкой

Данные типы данных позволяют применять их для хранения денежных значений и обеспечивают предсказуемость операций умножения и деления.

Firebird предлагает два типа данных с фиксированной точкой: NUMERIC и DECIMAL. В соответствии со стандартом оба типа ограничивают хранимое число объявленным масштабом (количеством чисел после запятой). При этом подход к тому, как ограничивается точность для типов разный: для столбцов NUMERIC точность является такой, «как объявлено», в то время как DECIMAL столбцы могут получать числа, чья точность, по меньшей мере, равна тому, что было объявлено.

Например, NUMERIC(4, 2) описывает число, состоящее в общей сложности из четырёх цифр, включая 2 цифры после запятой; итого 2 цифры до запятой, 2 после. При записи в столбец с этим типом данных значений 3,1415 в столбце NUMERIC(4, 2) будет сохранено значение 3,14.

Для данных с фиксированной точкой общим является форма декларации, например NUMERIC(p, s). Здесь важно понять, что в этой записи s — это масштаб, а не интуитивно предсказываемое «количество знаков после запятой». Для «визуализации» механизма хранения данных запомните для себя процедуру:

  • При сохранении в базу данных число умножается на 10s (10 в степени s), превращаясь в целое;

  • При чтении данных происходит обратное преобразование числа.

Способ физического хранения данных в СУБД зависит от нескольких факторов: декларируемой точности, диалекта базы данных, типа объявления.

Таблица 2.2. Способ физического хранения вещественных чисел

Точность Тип данных Диалект 1 Диалект 3
1 - 4 NUMERIC SMALLINT SMALLINT
1 - 4 DECIMAL INTEGER INTEGER
5 - 9 NUMERIC и DECIMAL INTEGER INTEGER
10 - 18 NUMERIC и DECIMAL DOUBLE PRECISION BIGINT


NUMERIC

Формат объявления данных:

 
NUMERIC(p, s)
                

В зависимости от точности p и масштаба s СУБД хранит данные по-разному.

Приведём примеры того, как СУБД хранит данные в зависимости от формы их объявления:

 
NUMERIC(4)      SMALLINT 
NUMERIC(4,2)    SMALLINT (data * 102)
NUMERIC(10,4)   DOUBLE PRECISION в 1-ом диалекте
                BIGINT в 3-ем диалекте (data * 104)
                

Внимание

Всегда надо помнить, что формат хранения данных зависит от точности. Например, вы задали тип столбца NUMERIC(2, 2), предполагая, что диапазон значений в нем будет — 0.99...0.99. Однако в действительности диапазон значений в столбце будет -327.68..327.68, что объясняется хранением типа данных NUMERIC(2, 2) в формате SMALLINT. Фактически типы данных NUMERIC(4, 2), NUMERIC(3, 2) и NUMERIC(2, 2) являются одинаковыми. Т.е. Для реального хранения данных в столбце с типом данных NUMERIC(2, 2) в диапазоне -0.99...0.99 для него надо создавать ограничение.

DECIMAL

Формат объявления данных:

 
DECIMAL(p, s)
                

Формат хранения данных в базе во многом аналогичен NUMERIC, хотя существуют некоторые особенности, которые проще всего пояснить на примере.

Приведём примеры того, как СУБД хранит данные в зависимости от формы их объявления:

 
DECIMAL(4)      INTEGER
DECIMAL(4,2)    INTEGER (data * 102)
DECIMAL(10,4)   DOUBLE PRECISION в 1-ом диалекте 
                BIGINT в 3-ем диалекте (data * 104)
                

Типы данных для работы с датой и временем

В СУБД Firebird для работы с данными, содержащими дату и время, используются типы данных DATE, TIME, TIMESTAMP. В 3-м диалекте присутствуют все три вышеназванных типа данных, а в 1-м для операций с датой и временем доступен только тип данных DATE, который не тождественен типу данных DATE 3-го диалекта, а напоминает тип данных TIMESTAMP из 3-го диалекта.

В диалекте 1 тип DATE может быть объявлен как TIMESTAMP. Такое объявление является рекомендуемым для новых баз данных в 1-м диалекте.

Доли секунды, если хранятся в типах данных даты и времени, являются десятитысячными долями секунды.

DATE

В 3-м диалекте тип данных DATE, как это и следует предположить из названия, хранит только одну дату без времени. В 1-м диалекте нет типа данных "только дата".

Допустимый диапазон хранения от 1 января 1 г. н.э. до 31 декабря 9999 года.

Подсказка

В случае необходимости сохранять в 1 диалекте только значения даты, без времени, при записи в таблицу добавляйте время к значению даты в виде литерала '00:00:00.0000'.

TIME

Этот тип данных доступен только в 3-м диалекте. Позволяет хранить время дня в диапазоне от 00:00:00.0000 до 23:59:59.9999.

При необходимости получения времени из DATE. в 1-м диалекте можно использовать функцию EXTRACT.

Примеры: 

EXTRACT (HOUR FROM DATE_FIELD)
EXTRACT (MINUTE FROM DATE_FIELD)
EXTRACT (SECOND FROM DATE_FIELD)                    
                    

TIMESTAMP

Этот тип данных доступен только в 3-м диалекте, состоит из двух 32-битных слов и хранит дату со временем. Такое хранение эквивалентно типу DATE 1-го диалекта.

Операции, использующие значения даты и времени

Благодаря способу хранения даты и времени с этими типами возможны арифметические операции вычитания из более поздней даты (времени) более раннюю. Дата представлена количеством дней с "нулевой даты" – 17 ноября 1858 г. Время представлено количеством секунд (с учётом десятитысячных долей), прошедших с полуночи.

Таблица 2.3. Арифметические операции для типов данных даты и времени

Операнд 1 Оператор Операнд 2 Результат
DATE + TIME TIMESTAMP
DATE + n DATE, увеличенная на n целых дней (дробная часть игнорируется).
TIME + DATE TIMESTAMP
TIME + n TIME, увеличенное на n секунд (дробная часть учитывается)
TIMESTAMP + n TIMESTAMP, где дни увеличены на целую часть числа n, плюс дробная часть числа n (если указана) как количество секунд в дне (с точностью до десятитысячных долей секунды).
DATE - DATE Количество дней в интервале DECIMAL (9, 0)
DATE - n DATE, уменьшенная на n целых дней (дробная часть игнорируется)
TIME - TIME Количество секунд в интервале DECIMAL (9, 4)
TIME - n TIME, уменьшенное на n секунд (дробная часть учитывается)
TIMESTAMP - TIMESTAMP Количество дней и части дня в интервале DECIMAL (18, 9)
TIMESTAMP - n TIMESTAMP, где дни уменьшены на целую часть числа n, плюс дробная часть числа n (если указана) как количество секунд в дне (с точностью до десятитысячных долей секунды).


Одно значение даты/времени может быть вычтено из другого если:

  • Оба значения имеют один и тот же тип даты/времени;

  • Первый операнд является более поздним, чем второй.

Примечание

В диалекте 1 тип DATE рассматривается как TIMESTAMP.

См. также:  DATEADD, DATEDIFF.

Символьные типы данных

В СУБД Firebird для работы с символьными данными есть тип данных фиксированной длины CHAR и строковый тип данных VARCHAR переменной длины. Максимальный размер текстовых данных, хранящийся в этих типах данных, составляет 32767 байт для типа CHAR и 32765 байт для типа VARCHAR. Максимальное количество символов, которое поместится в этот объём, зависит от используемого набора символов CHARACTER SET и/или заданного порядка сортировки, который для символьных данных задаётся предложением COLLATE.

В случае отсутствия явного указания набора символов при описании текстового объекта базы данных будет использоваться набор символов по умолчанию, заданный при создании базы данных. При отсутствии явного указания набора символов, а также отсутствия набора символов по умолчанию в базе данных, поле получает набор символов CHARACTER SET NONE.

Если база данных будет содержать строки только с русским алфавитом, то для неё рекомендуется к использованию кодировка WIN1251. При её использовании на один символ расходуется 1 байт, соответственно максимальный размер текстовых полей для данной кодировки будет 32767 символов. Для стандартных операций сортировки при работе с WIN1251 не требуется задавать порядок сортировки (COLLATE).

UNICODE

В настоящее время все современные средства разработки поддерживают Unicode. При возникновении необходимости использования восточноевропейских текстов в строковых полях базы данных или для более экзотических алфавитов, рекомендуется работать с набором символов UTF8. При этом следует иметь в виду, что на один символ в данном наборе приходится до 4 байт. Следовательно, максимальный размер символов в символьных полях составит 32676/4 (8192) байта на символ. При этом следует обратить внимание, что фактически значение параметра «байт на символ» зависит от диапазона, к которому принадлежит символ: английские буквы занимают 1 байт, русские буквы кодировки WIN1251 — 2 байта, остальные символы — могут занимать до 4-х байт.

Набор символов UTF8 поддерживает последнюю версию стандарта Unicode, до 4 байт на символ, поэтому для интернациональных баз рекомендуется использовать именно эту реализацию поддержки Unicode в Firebird.

Набор символов клиента

При работе со строками необходимо помнить и о параметре соединения клиентской программы к базе данных. В нём также задаётся набор символов. В случае различия набора символов, при выдаче результата для строковых столбцов происходит автоматическая перекодировка как при передаче данных с клиента на сервер, так и в обратном направлении с сервера на клиента. То есть, совершенно нормальной является ситуация, когда база создана в кодировке WIN1251, а в настройках клиента в параметрах соединения стоит KOI8R или UTF8.

Специальные наборы символов

Набор символов NONE относится к специальным наборам символов. Его можно охарактеризовать тем, что каждый байт является частью строки, но в системе хранится без указаний, к какому фактическому набору символов они относятся. Разбираться с такими данными должно клиентское приложение, на него возлагается ответственность в правильной трактовке символов из таких полей.

Также к специальным наборам символов относится OCTETS. В этом случае данные рассматриваются как байты, которые могут в принципе не интерпретироваться как символы. OCTETS позволяет хранить бинарные данные и/или результаты работы некоторых функций Firebird. Правильное отображение данных пользователю, хранящихся в полях с CHARACTER SET OCTETS, также становится заботой клиентской стороны. При работе с подобными данными следует также помнить, что СУБД не контролирует их содержимое и возможно возникновение исключения при работе кода, когда идёт попытка отображения бинарных данных в желаемой кодировке.

Последовательность сортировки

Каждый текстовый набор символов (CHARACTER SET) имеет последовательность сортировки (COLLATE) по умолчанию, задающий порядок сортировки и способы сравнения. Если необходимо нестандартное поведение строк при указанных выше действиях, то в описании строкового столбца может быть указан параметр COLLATE, который его опишет. Помимо описания объявления столбца, выражение COLLATE может быть добавлено в предложениях SELECT в секции WHERE, когда происходят операции сравнения больше — меньше, в секции ORDER BY при сортировке по символьному полю, а также при операциях группировки для указания специальной последовательности сортировки при выводе в предложении GROUP BY.

Регистронезависимый поиск

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

WHERE UPPER(name) = UPPER(:flt_name)                
            

Для строк с набором символов WIN1251 можно для этих же целей воспользоваться предложением COLLATE PXW_CYRL.

Пример: 

  
WHERE FIRST_NAME COLLATE PXW_CYRL >= :FLT_NAME                    
                

Пример сортировки независимой от регистра символов: 

  
ORDER BY NAME COLLATE PXW_CYRL                  
                

См. также:  CONTAINING.

Последовательности сортировки для UTF-8

Ниже приведена таблица возможных последовательностей сортировки для набора символов UTF8.

Таблица 2.4. Последовательности сортировки для UTF8

COLLATION Комментарии
UCS_BASIC Сортировка работает в соответствии с положением символа в таблице (бинарная): Пример: A, B, a, b, a...
UNICODE Сортировка работает в соответствии с алгоритмом UCA (Unicode Collation Algorithm) (алфавитная). Пример: a, A, a, b, B...
UTF-8 Сортировка происходит без учёта регистра символа.
UNICODE_CI_AI Сортировка происходит без учёта регистра символа, в алфавитном порядке.


Пример сортировки строк для набора символов UTF8 без учёта регистра символов (эквивалент COLLATE PXW_CYRL)

  
ORDER BY NAME COLLATE UNICODE_CI_AI                 
            

Индексирование символьных типов

При построении индекса по строковым полям необходимо учитывать ограничение на длину ключа индекса. Максимальная используемая длина ключа индекса равна 1/4 размера страницы, т.е. от 1024 до 4096 байтов. Максимальная длина индексируемой строки на 9 байтов меньше, чем максимальная длина ключа. В таблице приведены данные для максимальной длины индексируемой строки (в символах) в зависимости от размера страницы и набора символов, её можно вычислить по следующей формуле:

  
max_char_length = FLOOR((page_size / 4 – 9) / N),                
            

где N — число байтов на представление символа.

Таблица 2.5. Длина индексируемой строки и набор символов

Размер страницы Максимальная длина индексируемой строки для набора символов, байт/символ
1 2 3 4 6
4096 1015 507 338 253 169
8192 2039 1019 679 509 339
16384 4087 2043 1362 1021 682


Примечание

В кодировках, нечувствительных к регистру ("_CI"), один символ в *индексе* будет занимать не 4, а 6 (шесть) байт, поэтому максимальная длина ключа для страницы, скажем, 4096 байт, составит 169 символов.

Последовательность сортировки (COLLATE) тоже может повлиять на максимальную длину индексируемой строки. Полный список доступных наборов символов и нестандартных порядков сортировки доступен в приложении Наборы символов и порядки сортировки.

CHAR

CHAR является типом данных фиксированной длины. Если введённое количество символом меньше объявленной длины, то поле дополнится концевыми пробелами. В общем случае символ заполнитель может и не являться пробелом, он зависит от набора символов, так например, для набора символов OCTETS — это ноль.

Полное название типа данных CHARACTER, но при работе нет необходимости использовать полные наименования; инструменты по работе с базой прекрасно понимают и короткие имена символьных типов данных.

Синтаксис: 

  
CHAR [(length)] [CHARACTER SET <charset>] [COLLATE <collate>]                 
                     

В случае если не указана длина, то считается, что она равна единице.

Данный тип символьных данных можно использовать для хранения в справочниках кодов, длина которых стандартна и определённой «ширины». Примером такого может служить почтовый индекс в России – 6 символов.

VARCHAR

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

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

Полное название CHARACTER VARYING. Имеется и сокращённый вариант записи CHAR VARYING.

Синтаксис: 

  
VARCHAR (length) [CHARACTER SET <charset>] [COLLATE <collate>]                 
                     

NCHAR

Представляет собой символьный тип данных фиксированной длины с предопределённым набором символов ISO8859_1.

Синтаксис: 

  
NCHAR [(length)]                 
                     

Синонимом является написание NATIONAL CHAR.

Аналогичный тип данных доступен для строкового типа переменной длины: NATIONAL CHARACTER VARYING.

Бинарные типы данных

BLOB

BLOB (Binary Large Objects, большие двоичные объекты) представляют собой сложные структуры, предназначенные для хранения текстовых и двоичных данных неопределённой длины, зачастую очень большого объёма.

Синтаксис: 

  
BLOB [SUB_TYPE <subtype>]
[SEGMENT SIZE <seg_length>]
[CHARACTER SET <charset>]                 
                

Сокращённый синтаксис: 

  
BLOB (<seg_length>)
BLOB (<seg_length>, <subtype>)
BLOB (, <subtype>)                 
                

Размер сегмента: Указание размера сегмента BLOB является некоторым атавизмом, оно идёт с тех времён, когда приложения для работы с данными BLOB писались на C (Embedded SQL) при помощи GPRE. В настоящий момент размер сегмента при работе с данными BLOB определяется клиентской частью, причём размер сегмента может превышать размер страницы данных.

Подтипы BLOB

Подтип BLOB отражает природу данных, записанную в столбце. Firebird предоставляет два предопределённых подтипа для сохранения пользовательских данных:

  • Подтип 0 (BINARY): Если подтип не указан, то данные считаются нетипизированными и значение подтипа принимается равным 0. Псевдоним подтипа 0 — BINARY. Этот подтип указывает, что данные имеют форму бинарного файла или потока (изображение, звук, видео, файлы текстового процессора, PDF и т.д.).

  • Подтип 1 (TEXT): Подтип 1 имеет псевдоним TEXT, который может быть использован вместо указания номера подтипа. Например, BLOB SUBTYPE TEXT. Это специализированный подтип, который используется для хранения текстовых данных большого объёма. Для текстового подтипа BLOB может быть указан набор символов и порядок сортировки COLLATE, аналогично символьному полю.

Кроме того, существует возможность добавления пользовательских подтипов данных, для них зарезервирован интервал от -1 до -32768. Пользовательские подтипы с положительными числами не поддерживаются, поскольку Firebird использует числа больше 2 для внутренних подтипов метаданных.

Особенности BLOB

Максимальный размер поля BLOB ограничен 4Гб и не зависит от варианта сервера, 32 битный или 64 битный (во внутренних структурах, связанных с BLOB присутствуют 4-х байтные счётчики). Для размера страницы 4096 максимальный размер BLOB поля несколько ниже 2 Гб.

Текстовые BLOB любой длины и с любым набором символов (включая multi-byte) могут быть использованы практически c любыми встроенными функциями и операторами:

  • Полная поддержка для операторов:

    = (присвоение);
    =, <>, <, <=, >, >= (сравнение);
    || (конкатенация);
    BETWEEN, IS [NOT] DISTINCT FROM, IN, ANY|SOME и ALL;

  • Частичная поддержка для STARTING [WITH], LIKE и CONTAINING. (возникает ошибка, в случае если второй аргумент больше или равен 32 Кб);

  • SELECT DISTINCT, ORDER BY и GROUP BY в своей работе используют BLOB ID, а не содержимое самого поля. Это одновременно и хорошо и плохо, кроме того, SELECT DISTINCT ошибочно выдаёт несколько значений NULL, если они присутствуют. GROUP BY ведёт себя странно в том, что он объединяет одинаковые строки, если они находятся рядом, но не делает этого, если они располагаются вдали друг от друга.

Хранение BLOB: 

  • По умолчанию, для каждого BLOB создаётся обычная запись, хранящаяся на какой-то выделенной для этого странице данных (data page). Если весь BLOB на эту страницу поместится, его называют BLOB уровня 0. Номер этой специальной записи хранится в записи таблицы и занимает 8 байт.

  • Если BLOB не помещается на одну страницу данных (data page), то его содержимое размещается на отдельных страницах, целиком выделенных для него (blob page), а в записи о BLOB помещают номера этих страниц. Это BLOB уровня 1.

  • Если массив номеров страниц с данными BLOB не помещается на страницу данных (data page), то его (массив) размещают на отдельных страницах (blob page), а в запись о BLOB помещают уже номера этих страниц. Это BLOB уровня 2.

  • Уровни выше 2 не поддерживаются.

См. также:  FILTER, DECLARE FILTER.

Массивы

Поддержка массивов в СУБД Firebird является расширением традиционной реляционной модели. Поддержка в СУБД такого инструмента позволяет проще решать некоторые задачи по обработке однотипных данных. Массивы в Firebird реализованы на базе полей типа BLOB. Массивы могут быть одномерными и многомерными.

  
CREATE TABLE SAMPLE_ARR (
  ID INTEGER NOT NULL PRIMARY KEY,
  ARR_INT INTEGER [4]);                
                

Так будет создана таблица с полем типа массива из четырёх целых. Индексы данного массива от 1 до 4. Для определения верхней и нижней границы значений индекса следует воспользоваться следующим синтаксисом:

  
[<нижняя>:<верхняя>]                
            

Добавление новой размерности в синтаксисе идёт через запятую. Пример создания таблицы с массивом размерности два, в котором нижняя граница значений начинается с нуля:

  
CREATE TABLE SAMPLE_ARR2 (
  ID INTEGER NOT NULL PRIMARY KEY,
  ARR_INT INTEGER [0:3, 0:3]);             
            

СУБД не предоставляет большого набора инструментов для работы с содержимым массивов. База данных employee.fdb, которая находится в дистрибутиве Firebird, содержит пример хранимой процедуры, показывающей возможности работы с массивами. Ниже приведён её текст:

  
CREATE OR ALTER PROCEDURE SHOW_LANGS (
  CODE VARCHAR(5),
  GRADE SMALLINT,
  CTY VARCHAR(15))
RETURNS (
  LANGUAGES VARCHAR(15))
AS
  DECLARE VARIABLE I INTEGER;
BEGIN
  I = 1;
  WHILE (I <= 5) DO
  BEGIN
    SELECT LANGUAGE_REQ[:I]
    FROM JOB
    WHERE (JOB_CODE = :CODE)
      AND (JOB_GRADE = :GRADE)
      AND (JOB_COUNTRY = :CTY)
      AND (LANGUAGE_REQ IS NOT NULL))
    INTO :LANGUAGES;
    
    IF (:LANGUAGES = '') THEN
      /* PRINTS 'NULL' INSTEAD OF BLANKS */
      LANGUAGES = 'NULL';
    I = I +1;
    SUSPEND;
  END
END
            

Если приведённых выше возможностей достаточно для ваших задач, то вы вполне можете применять массивы для своих проектов. В настоящее время совершенствования механизмов обработки массивов средствами СУБД не производится.

Специальные типы данных

Тип данных SQL_NULL

Данный тип данных содержит не данные, а только состояние: NULL или NOT NULL. Также, этот тип данных не может быть использован при объявлении полей таблицы, переменных PSQL, использован в описании параметров. Этот тип данных добавлен для улучшения поддержки нетипизированных параметров в предикате IS NULL. Такая проблема возникает при использовании «отключаемых фильтров» при написании запросов следующего типа:

  
WHERE col1 = :param1 OR :param1 IS NULL           
                

после обработки, на уровне API запрос будет выглядеть как

  
WHERE col1 = ? OR ? IS NULL           
                

В данном случае получается ситуация, когда разработчик при написании SQL запрос рассматривает :param1 как одну переменную, которую использует два раза, а на уровне API запрос содержит два отдельных и независимых параметра. Вдобавок к этому, сервер не может определить тип второго параметра, поскольку он идёт в паре с IS NULL.

Именно для решения проблемы «? IS NULL» и был добавлен этот специальный тип данных SQL_NULL.

После введения данного специального типа данных при передаче запроса и его параметров на сервер будет работать такая схема: приложение передаёт параметризованные запросы на сервер в виде «?». Это делает невозможным слияние пары «одинаковых» параметров в один. Так, например, для двух фильтров (двух именованных параметров) необходимо передать четыре позиционных параметра (далее предполагается, что читатель имеет некоторое знакомство с Firebird API):

  
SELECT
  SH.SIZE, SH.COLOUR, SH.PRICE
FROM SHIRTS SH
WHERE (SH.SIZE = ? OR ? IS NULL)
  AND (SH.COLOUR = ? OR ? IS NULL)          
                

После выполнения isc_dsql_describe_bind() sqltype 2-го и 4-го параметров устанавливается в SQL_NULL. Как уже говорилось выше, сервер Firebird не имеет никакой информации об их связи с 1-м и 3-м параметрами — это полностью прерогатива программиста. Как только значения для 1-го и 3-го параметров были установлены (или заданы как NULL) и запрос подготовлен, каждая пара XSQLVARs должна быть заполнена следующим образом:

Пользователь задал параметры: 

  • Первый параметр (сравнение значений): set *sqldata в переданное значение и *sqlind в 0 (для NOT NULL);

  • Второй параметр (проверка на NULL): set *sqldata в NULL (не SQL_NULL) и *sqlind в 0 (для NOT NULL).

Пользователь задал параметры: 

  • Оба параметра (проверка на NULL): set *sqldata в NULL (не SQL_NULL) и *sqlind в -1 (индикация NULL).

Другими словами: значение параметра сравнения всегда устанавливается как обычно. SQL_NULL параметр устанавливается также, за исключением случая, когда sqldata передаётся как NULL.

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

При написании выражения или при задании, например, условий сравнения, нужно стараться использовать совместимые типы данных. В случае необходимости использования смешанных данных различных типов, желательно первоначально выполнить преобразования типов, а уже потом выполнять операции.

При рассмотрении вопроса преобразования типов в Firebird большое внимание стоит уделить тому, в каком диалекте база данных.

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

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

Синтаксис: 

  
CAST (<value> | NULL AS <data_type>)

<data_type> ::= 
     sql_datatype
  | [TYPE OF] domain
  | TYPE OF COLUMN relname.colname                
                   

Преобразование к домену

При преобразовании к домену учитываются объявленные для него ограничения, например, NOT NULL или описанные в CHECK и если <value> не пройдёт проверку, то преобразование не удастся. В случае если дополнительно указывается TYPE OF (преобразование к базовому типу), при преобразовании игнорируются любые ограничения домена. При использовании TYPE OF с типом (VAR)CHAR набор символов и сортировка сохраняются.

Преобразование к типу столбца

При преобразовании к типу столбца допускается использовать указание столбца таблицы или представления. Используется только сам тип столбца; в случае строковых типов это также включает набор символов, но не сортировку. Ограничения и значения по умолчанию исходного столбца не применяются.

Примеры:

  
CREATE TABLE TTT (
  S VARCHAR (40)
  CHARACTER SET UTF8 COLLATE UNICODE_CI_AI);
COMMIT;

/* У меня много друзей (шведский)*/
SELECT
  CAST ('Jag har manga vanner' AS TYPE OF COLUMN TTT.S)
FROM RDB$DATABASE;               
                   

Допустимые преобразования для функции CAST

Таблица 2.6. Допустимые преобразования для функции CAST

Из типа В тип
Числовые типы
Числовые типы
[VAR]CHAR
BLOB
[VAR]CHAR
BLOB
[VAR]CHAR
BLOB
Числовые типы
DATE
TIME
TIMESTAMP
DATE
TIME
[VAR]CHAR
BLOB
TIMESTAMP
TIMESTAMP
[VAR]CHAR
BLOB
TIME
DATE


Важно

При преобразовании типов следует помнить о возможной частичной потери данных, например, при преобразовании типа данных TIMESTAMP в DATE.

Преобразование литералов дат и времени

Для преобразования строковых типов данных в типы DATE, TIME или TIMESTAMP необходимо чтобы строковый аргумент был либо одним из предопределённых литералов даты и времени, либо строковое представление даты в одном из разрешённых форматов.

<datetime_literal> ::= {     
  [YYYY<p>]MM<p>DD[<p>HH[<p>mm[<p>SS[<p>NNNN]]]] |     
  MM<p>DD[<p>YYYY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |     
  DD<p>MM[<p>YYYY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |     
  MM<p>DD[<p>YY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |     
  DD<p>MM[<p>YY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |     
  NOW |     
  TODAY |     
  TOMORROW |     
  YERSTERDAY   
}       

<date_literal> ::= {     
  [YYYY<p>]MM<p>DD |     
  MM<p>DD[<p>YYYY] |     
  DD<p>MM[<p>YYYY] |     
  MM<p>DD[<p>YY] |     
  DD<p>MM[<p>YY] |     
  TODAY |     
  TOMORROW |     
  YERSTERDAY   
}       

<time_literal> := HH[<p>mm[<p>SS[<p>NNNN]]]       

<p> ::= whitespace | . | : | , | - | /                    
            

Таблица 2.7. Описание формата даты и времени

Аргумент Описание
datetime_literal Литерал даты-времени.
date_literal Литерал даты.
time_literal Литерал времени.
YYYY Год из четырёх цифр.
YY Последние две цифры года (00-99).
MM Месяц. Может содержать 1 или 2 цифры (1-12 или 01-12). В качестве месяца допустимо также указывать трёхбуквенное сокращение или полное наименование месяца на английском языке, регистр не имеет значение.
DD День. Может содержать 1 или 2 цифры (1-31 или 01-31).
HH Час. Может содержать 1 или 2 цифры (0-23 или 00-23).
mm Минуты. Может содержать 1 или 2 цифры (0-59 или 00-59).
SS Секунды. Может содержать 1 или 2 цифры (0-59 или 00-59).
NNNN Десятитысячные доли секунды. Может содержать от 1 до 4 цифр (0-9999).
p Разделитель, любой из разрешённых символов, лидирующие и завершающие пробелы игнорируются


Таблица 2.8. Литералы с предопределёнными значениями даты и времени

Литерал Значение Тип данных для диалекта 1 Тип данных для диалекта 3
'NOW' Текущая дата и время DATE TIMESTAMP
'TODAY' Текущая дата DATE (c нулевым временем) DATE (только дата)
'TOMORROW' Завтрашняя дата DATE (c нулевым временем) DATE (только дата)
'YESTERDAY' Вчерашняя дата DATE (c нулевым временем) DATE (только дата)


Правила:

  • В формате Год-Месяц-День, год обязательно должен содержать 4 цифры;

  • Для дат в формате с завершающим годом, если в качестве разделителя дат используется точка «.», то дата интерпретируется в форме День-Месяц-Год, для остальных разделителей она интерпретируется в форме Месяц-День-Год;

  • Если год не указан, то в качестве года берётся текущий год;

  • Если указаны только две цифры года, то для получения столетия Firebird использует алгоритм скользящего окна. Задача заключается в интерпретации двухсимвольного значения года как ближайшего к текущему году в интервале предшествующих и последующих 50 лет;

  • Если не указан один из элементов времени, то оно принимается равным 0.

Подсказка

Настоятельно рекомендуем в литералах дат использовать только формы с полным указанием года в виде 4 цифр во избежание путаницы.

Примеры интерпретации литералов дат и времени: 

SELECT
  CAST('04.12.2014' AS DATE) AS d1, -- DD.MM.YYYY
  CAST('04 12 2014' AS DATE) AS d2, -- MM DD YYYY
  CAST('4-12-2014' AS DATE) AS d3,  -- MM-DD-YYYY
  CAST('04/12/2014' AS DATE) AS d4, -- MM/DD/YYYY
  CAST('04,12,2014' AS DATE) AS d5, -- MM,DD,YYYY
  CAST('04.12.14' AS DATE) AS d6,   -- DD.MM.YY
  -- DD.MM в качестве года берётся текущий
  CAST('04.12' AS DATE) AS d7,
  -- MM/DD в качестве года берётся текущий      
  CAST('04/12' AS DATE) AS d8,      
  CAST('2014/12/04' AS DATE) AS d9, -- YYYY/MM/DD
  CAST('2014 12 04' AS DATE) AS d10, -- YYYY MM DD
  CAST('2014.12.04' AS DATE) AS d11, -- YYYY.MM.DD
  CAST('2014-12-04' AS DATE) AS d12, -- YYYY-MM-DD
  CAST('4 Jan 2014' AS DATE) AS d13, -- DD MM YYYY
  CAST('2014 Jan 4' AS DATE) AS dt14, -- YYYY MM DD
  CAST('Jan 4, 2014' AS DATE) AS dt15, -- MM DD, YYYY
  CAST('11:37' AS TIME) AS t1, -- HH:mm
  CAST('11:37:12' AS TIME) AS t2, -- HH:mm:ss
  CAST('11:31:12.1234' AS TIME) AS t3, -- HH:mm:ss.nnnn
  CAST('11.37.12' AS TIME) AS t4, -- HH.mm.ss
  -- DD.MM.YYYY HH:mm
  CAST('04.12.2014 11:37' AS TIMESTAMP) AS dt1, 
  -- MM/DD/YYYY HH:mm:ss
  CAST('04/12/2014 11:37:12' AS TIMESTAMP) AS dt2,
  -- DD.MM.YYYY HH:mm:ss.nnnn 
  CAST('04.12.2014 11:31:12.1234' AS TIMESTAMP) AS dt3, 
  -- MM/DD/YYYY HH.mm.ss
  CAST('04/12/2014 11.37.12' AS TIMESTAMP) AS dt4 
FROM rdb$database                    
            

См. также:  CAST.

Сокращённое приведение типов даты и времени (datetime)

При преобразовании строки в тип DATE, TIME или TIMESTAMP, Firebird позволяет использовать сокращённое "C-style" приведение типов.

Синтаксис: 

  
datatype 'date/time string'             
                   

Примеры: 

  
UPDATE PEOPLE
SET AGECAT = 'Old'
WHERE BIRTHDATE < DATE '1-Jan-1943';

INSERT INTO APPOINTMENTS
(EMPLOYEE_ID, CLIENT_ID, APP_DATE, APP_TIME)
VALUES (973, 8804, DATE 'today' + 2, TIME '16:00');

NEW.LASTMOD = TIMESTAMP 'now';            
                   

Обратите внимание, что эти сокращённые выражения вычисляются сразу же во время синтаксического анализа, то есть, как будто оператор уже подготовлен к выполнению. Таким образом, даже если запрос выполняется несколько раз, значение, например, для TIMESTAMP 'NOW' не изменится, независимо от того, сколько времени проходит. Если вам нужно получать нарастающее значение времени (т.е. оно должно быть оценено при каждом вызове), используйте полный синтаксис CAST. Пример использования в триггере такого выражения:

  
NEW.CHANGE_DATE = CAST('now' AS TIMESTANP);          
               

См. также:  Явное преобразование типов данных.

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

В 3-м диалекте невозможно неявное преобразование данных, здесь требуется указывать функцию CAST для явной трансляции одного типа в другой. Однако это не относится к операции конкатенации, при которой все другие типы данных будут неявно преобразованы к символьному.

При использовании 1-го диалекта во многих выражениях выполняется неявное преобразование одних типов в другой без применение функции CAST. Например, в выражении отбора в диалекте 1 можно записать:

 
WHERE DOC_DATE < '31.08.2014'                   
               

и преобразование строки в дату произойдёт неявно.

В 1-м диалекте можно смешивать целые данные и числовые строки, строки неявно преобразуются в целое, если это будет возможно, например:

2 + '1'                   
               

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

2 + CAST('1' AS SMALLINT)                   
               

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

При конкатенации множества элементов разных типов, все не строковые данные будут неявно преобразованы к строке, если это возможно.

Примеры: 

 
SELECT 30||' days hath September, April, June and November' CONCAT$
FROM RDB$DATABASE                 
               

CONCAT$
------------------------------------------------
30 days hath September, April, June and November                   
                

Пользовательские типы данных — домены

Домены в СУБД Firebird реализуют широко известный по многим языкам программирования инструмент «типы данных, определённые пользователем». Когда несколько таблиц в базе данных содержат поля с характеристиками одинаковыми или практически одинаковыми, то есть целесообразность сделать домен, в котором описать набор свойств поля и использовать такой набор свойств, описанный один раз, в нескольких объектах базы данных. Домены могут использоваться помимо описания полей таблиц и представлений (VIEW) и при объявлении входных и выходных параметров, а также при объявлении переменных в коде PSQL.

Атрибуты домена

Определение домена содержит обязательные и необязательные атрибуты. К обязательному атрибуту относится тип данных. К необязательным относятся:

  • значение по умолчанию;

  • возможности использования NULL для домена;

  • ограничения CHECK для данных домена;

  • набор символов (для символьных типов данных и BLOB полей);

  • порядок сортировки (для символьных типов данных).

Пример создания домена: 

  
CREATE DOMAIN BOOL3 AS SMALLINT
CHECK (VALUE IS NULL OR VALUE IN (0, 1));           
                   

См. также:  Явное преобразование типов данных, где описаны отличия работы механизма преобразования данных при указании доменов для опций TYPE OF и TYPE OF COLUMN.

Переопределение свойств доменов

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

Таблица 2.9. Возможности переопределения атрибутов столбцов на базе доменов

Атрибут Переопределяется? Примечания
тип данных нет  
значение по умолчанию да  
текстовый набор символов да также может использоваться, чтобы восстановить для столбца значения по умолчанию для базы данных
текстовый порядок сортировки да  
условия проверки CHECK нет для добавления в проверку новых условий, можно использовать в операторах CREATE и ALTER на уровне таблицы соответствующие предложения CHECK.
NOT NULL нет во многих случаях лучше оставить при описании домена возможность значения NULL, а контроль его допустимости осуществлять в описании полей на уровне таблицы.


Создание доменов

Создание домена производится оператором CREATE DOMAIN.

Краткий синтаксис: 

  
CREATE DOMAIN <name> [AS] <type>
[DEFAULT {<const> | <literal> | NULL | <context_var>}]
[NOT NULL] [CHECK (<condition>)]
[COLLATE collation];        
                   

См. также:  CREATE DOMAIN.

Изменение доменов

Для редактирования свойств домена используют оператор ALTER DOMAIN языка определения данных (DDL).

При редактировании домена можно:

  • переименовать домен;

  • изменить тип данных;

  • удалить текущее значение по умолчанию;

  • установить новое значение по умолчанию;

  • удалить текущее ограничение CHECK;

  • добавить новое ограничение CHECK.

Краткий синтаксис: 


ALTER DOMAIN name
  [{TO new_name}]
  [{SET DEFAULT {literal | NULL | <context_var>} | 
    DROP DEFAULT}]
  [{ADD [CONSTRAINT] CHECK (<dom_condition>) | 
    DROP CONSTRAINT}]
  [{TYPE <datatype>}];  
                          

Пример: 

  
ALTER DOMAIN STORE_GRP SET DEFAULT -1;   
                   

При изменении доменов следует учитывать и его зависимости: имеются ли столбцы таблиц; находятся ли в коде PSQL объявления переменных, входных и/или выходных параметров с типом этого домена. При поспешном редактировании без внимательной проверки можно сделать данный код неработоспособным!

Важно

При смене в домене типа данных не допустимы преобразования, которые могут привести к потере данных. Также, например, при преобразовании VARCHAR в INTEGER проверьте, все ли данные, что используют данных домен, смогут пройти преобразование.

См. также:  ALTER DOMAIN.

Удаление доменов

Оператор DROP DOMAIN удаляет из базы данных домена при условии, что он не используется в каком либо из объектов базы данных.

Синтаксис: 

  
DROP DOMAIN name;      
                   

Пример: 

  
DROP DOMAIN Test_Domain;  
                   

См. также:  DROP DOMAIN.

Глава 3. Общие элементы языка

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

Выражения

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

Таблица 3.1. Описание элементов языка

Элемент Описание
Имя столбца Идентификаторы столбцов из указанных таблиц, используемые в вычислениях, или сравнениях, или в качестве условия поиска. Столбец типа массив не может быть элементом выражения, если только он не проверяется на IS [NOT] NULL.
Элементы массива В выражении может содержаться ссылка на элемент массива.
Арифметические операторы Символы +, -, *, / используемые для вычисления значений.
Оператор конкатенации Оператор || используется для соединения символьных строк.
Логические операторы Зарезервированные слова NOT, AND и OR используются при комбинировании простых условий поиска для создания сложных утверждений.
Операторы сравнения Символы =, <>, !=, ~=, ^=, <, <=, >, >=, !<, ~<, ^<, !>, ~> и ^>.
Предикаты сравнения LIKE, STARTING WITH, CONTAINING, SIMILAR TO, BETWEEN, IS [NOT] NULL и  IS [NOT] DISTINCT FROM
Предикаты существования Предикаты, используемые для проверки существования значений в наборе. Предикат IN может быть использован как с наборами констант, так и со скалярными подзапросами. Предикаты EXISTS, SINGULAR, ALL ANY, SOME могут быть использованы только с подзапросами.
Константы Числа, заключённые в апострофы строковые литералы, псевдозначение NULL.
Литералы дат Выражения, подобные строковым литералам, заключённые в апострофах, которые могут быть интерпретированы как значения даты, времени или даты-времени. Литералами дат могут быть предварительно объявленные литералы ('TODAY', 'NOW' и т.д.) или строки из символов и чисел, такие как '25.12.2016 15:30:35', которые могут быть преобразованы в дату, время или дату с временем.
Контекстные переменные Встроенные контекстные переменные.
Локальные переменные Локальные переменные, входные или выходные параметры PSQL модулей (хранимых процедур, триггеров, анонимных блоков PSQL).
Позиционные параметры В DSQL в качестве параметров запроса могут быть использованы только позиционные параметры. Позиционные параметры представляют собой знаки вопроса (?) внутри DSQL оператора. Доступ к таким параметрам осуществляется по его номеру (позиции в запросе относительно предыдущего позиционного параметра) поэтому они называются позиционными. Обычно компоненты доступа позволяют работать с именованными параметрами, которые они сами преобразовывают в позиционные.
Подзапросы Оператор SELECT заключённый в круглые скобки, который возвращает одно единственное (скалярное) значение или множество значений (при использовании в предикатах существования).
Идентификаторы функций Идентификаторы встроенных или внешних функций в функциональных выражениях.
Приведения типа

Выражение явного преобразования одного типа данных в другой

CAST(<value> AS <datatype>) 
                                

или сокращённое (для даты/времени) преобразование типа

<datatype> <value>
                                     

например DATE '25.12.2016'

Условные выражения Выражение CASE и встроенные функции COALESCE, NULLIF.
Круглые скобки Используются для группировки выражений. Операции внутри скобок выполняются перед операциями вне скобок. При использовании вложенных скобок, сначала вычисляются значения самых внутренних выражений, а затем вычисления перемещаются наверх по уровням вложенности.
Предложение COLLATE Может быть применено к значениям типа CHAR и VARCHAR, чтобы использовать строковые сравнения в указанной последовательности сортировки.
NEXT VALUE FOR sequence Конструкция NEXT VALUE FOR позволяет получить следующее значение последовательности, то же самое делает встроенная функция GEN_ID().


Операторы SQL

Выражения SQL включают операторы для сравнения и вычисления значений.

Приоритет операторов

Приоритет определяет порядок, в котором операторы и получаемые с помощью них значения вычисляются в выражении.

Все операторы разбиты на 4 типа. Каждый тип оператора имеет свой приоритет. Чем выше приоритет типа оператора, тем раньше он будет вычислен. Внутри одного типа операторы имеют собственный приоритет, который также определяет порядок их вычисления в выражении. Операторы с одинаковым приоритетом вычисляются слева направо. Для изменения порядка вычислений операции могут быть сгруппированы с помощью круглых скобок.

Таблица 3.2. Приоритеты типов операторов

Тип оператора Приоритет Пояснение
Конкатенация 1 Строки объединяются до выполнения любых других операций.
Арифметический 2 Арифметические операции выполняются после конкатенации строк, но перед выполнением операторов сравнения и логических операций.
Сравнение 3 Операции сравнения вычисляются после конкатенации строк и выполнения арифметических операций, но до логических операций.
Логический 4 Логические операторы выполняются после всех других типов операторов.


Оператор конкатенации

Оператор конкатенации (||) соединяет две символьные строки и создаёт одну строку. Символьные стоки могут быть константами или значениями, полученными из столбцов или других выражений.

Пример: 

 
SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME
FROM EMPLOYEE                   
                

Арифметические операторы

Таблица 3.3. Приоритет арифметических операторов

Оператор Назначение Приоритет
* Умножение 1
/ Деление 2
+ Сложение 3
- Вычитание 4


Пример: 

 
UPDATE T
SET A = 4 + 1/(B-C)*D                  
                

Операторы сравнения

Таблица 3.4. Приоритет операторов сравнения

Оператор Назначение Приоритет
= Равно, идентично 1
<>, !=, ~=, ^= Не равно 2
> Больше 3
< Меньше 4
>= Больше или равно 5
<= Меньше или равно 6
!>, ~>, ^> Не больше 7
!<, ~<, ^< Не меньше 8


В эту же группу входят предикаты сравнения BETWEEN, LIKE, CONTAINING, SIMILAR TO и другие.

Пример: 

 
IF (SALARY > 1400) THEN   
...
                

См. также:  Другие предикаты сравнения.

Логические операторы

Таблица 3.5. Приоритет логических операторов

Оператор Назначение Приоритет
NOT Отрицание условия поиска. 1
AND Объединяет два предиката и более, каждый из которых должен быть истинным, чтобы истинным был и весь предикат. 2
OR Объединяет два предиката и более, из которых должен быть истинным хотя бы один предикат, чтобы истинным был и весь предикат. 3


Пример: 

 
IF (A > B OR (A > C AND A > D) AND NOT (C = D)) THEN
...
                

NEXT VALUE FOR

Доступно в: DSQL, PSQL.

Синтаксис: 

NEXT VALUE FOR sequence-name
                

Возвращает следующее значение в последовательности (SEQUENCE). SEQUENCE является SQL совместимым термином генератора в InterBase и Firebird. Оператор NEXT VALUE FOR полностью эквивалентен функции GEN_ID (seq, n) и является рекомендуемым синтаксисом.

Пример: 

 
NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;
                

Примечание

NEXT VALUE FOR не поддерживает значение приращения, отличное от 1. Если требуется другое значение шага, то используйте старую функцию GEN_ID.

См. также:  SEQUENCE (GENERATOR), GEN_ID.

Условные выражения

Условное выражение — это выражение, которое возвращает различные значения в зависимости от истинности некоторого условия или условий. В данном разделе описано лишь одно условное выражение CASE. Остальные условные выражения являются производными встроенными функциями и описаны в разделе Скалярные функции.

CASE

Доступно в: DSQL, ESQL.

Оператор CASE возвращает только одно значение из нескольких возможных. Есть два синтаксических варианта:

  • Простой CASE, сравнимый с Pascal case или C switch;

  • Поисковый CASE, который работает как серия операторов "if ... else if ... else if".

Простой CASE

Синтаксис: 

CASE <test-expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END
                

При использовании этого варианта <test-expr> сравнивается с <expr> 1, <expr> 2 и т.д. до тех пор, пока не будет найдено совпадение, и тогда возвращается соответствующий результат. Если совпадений не найдено, то возвращается defaultresult из ветви ELSE. Если нет совпадений, и ветвь ELSE отсутствует, возвращается значение NULL.

Совпадение эквивалентно оператору «=», т.е. если <test-expr> имеет значение NULL, то он не соответствует ни одному из <expr>, даже тем, которые имеют значение NULL.

Результаты не обязательно должны быть литеральными значением, они также могут быть именами полей, переменными, сложными выражениями или NULL литералами.

Сокращённый вид простого оператора CASE используется в функции DECODE.

Пример: 

 
SELECT
  NAME,
  AGE,
  CASE UPPER(SEX)
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Unknown'
  END AS SEXNAME,
  RELIGION
FROM PEOPLE
                

Поисковый CASE

Синтаксис: 

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> …]
  [ELSE <defaultresult>]
END
                

Здесь <bool_expr> выражение, которое даёт тройной логический результат: TRUE, FALSE или NULL. Первое выражение, возвращающее TRUE, определяет результат. Если нет выражений, возвращающих TRUE, то в качестве результата берётся defaultresult из ветви ELSE. Если нет выражений, возвращающих TRUE, и ветвь ELSE отсутствует, результатом будет NULL.

Как и в простом операторе CASE, результаты не должны быть буквальным значением: они могут быть полями или именами переменных, сложными выражениями, или иметь значение NULL.

Пример: 

 
CANVOTE = CASE
  WHEN AGE >= 18 THEN 'Yes'
  WHEN AGE < 18 THEN 'No'
  ELSE 'Unsure'
END;
                

NULL в выражениях

В SQL NULL не является значением — это состояние, указывающее, что значение элемента неизвестно или не существует. Это не ноль, не пустота, не «пустая строка», и оно не ведёт себя как какое-то из этих значений.

При использовании NULL в числовых, строковых выражениях или в выражениях, содержащих дату/время, в результате вы всегда получите NULL. При использовании NULL в логических (булевых) выражениях результат будет зависеть от типа операции и других вовлечённых значений. При сравнении значения с NULL результат будет неопределённым (UNKNOWN).

Важно

Неопределённый логический результат (UNKNOWN) тоже представлен псевдо-значением NULL.

Выражения возвращающие NULL

Выражения в этом списке всегда возвратят NULL:

1 + 2 + 3 + NULL
'Home ' || 'sweet ' || NULL
MyField = NULL
MyField <> NULL
NULL = NULL
not (NULL)
          

Если вам трудно понять, почему, вспомните, что NULL – значит «неизвестно».

NULL в логических выражениях

Мы уже рассмотрели, что not (NULL) даёт в результате NULL. Для операторов and (логическое И) и or (логическое ИЛИ) взаимодействие несколько сложнее:

NULL or false = NULL
NULL or true = true
NULL or NULL = NULL
NULL and false = false
NULL and true = NULL
NULL and NULL = NULL
          

В Firebird 2.5 не существует логического (булева) типа данных (такой тип введён в Firebird 3.0), тем не менее, существуют логические выражения (предикаты), которые могут возвращать истину, ложь или неизвестно.

Примеры: 

 
  (1 = NULL) OR (1 <> 1) -- возвратит NULL   
  (1 = NULL) OR (1 = 1) -- возвратит TRUE   
  (1 = NULL) OR (1 = NULL) -- возвратит NULL   
  (1 = NULL) AND (1 <> 1) -- возвратит FALSE   
  (1 = NULL) AND (1 = 1) -- возвратит NULL   
  (1 = NULL) AND (1 = NULL) -- возвратит NULL    
                

Подзапросы

Подзапрос — это специальный вид выражения, которое фактически является запросом SELECT к другой таблице, включённый в спецификацию основного запроса. Подзапросы пишутся как обычные SELECT запросы, но должны быть заключены в круглые скобки. Выражения подзапроса используется следующими способами:

  • Для задания выходного столбца выходного столбца в списке выбора SELECT;

  • Для получения значений или условий для предикатов поиска (предложения WHERE, HAVING).

Коррелированные подзапросы

Подзапрос может быть коррелированным (соотнесённым). Запрос называется соотнесённым, когда оба, и внутренний, и внешний, запросы взаимозависимы. Это означает, что для обработки каждой записи внутреннего запроса, должна быть получена также запись внешнего запроса, т.е. внутренний запрос всецело зависит от внешнего.

Пример коррелированного подзапроса: 

 
SELECT *
FROM Customers C
WHERE EXISTS
      (SELECT *
       FROM Orders O
       WHERE C.cnum = O.cnum
         AND O.adate = DATE '10.03.1990');
  
                

При использовании подзапросов для получения значений выходного столбца в списке выбора SELECT, подзапрос должен возвращать скалярный результат.

Подзапросы возвращающие скалярный результат (Singletons)

Подзапросы, используемые в предикатах поиска, кроме предикатов существования и количественных предикатов, должны возвращать скалярный результат, то есть не более чем один столбец из одной отобранной строки или одно агрегированное значение, в противном случае, произойдёт ошибка времени выполнения ("Multiple rows in a singleton select...").

Примеры: 

Пример 3.1. Подзапрос в качестве выходного столбца в списке выбора

  
SELECT
    e.first_name,
    e.last_name,
    (SELECT
         sh.new_salary
     FROM
         salary_history sh
     WHERE
         sh.emp_no = e.emp_no
     ORDER BY sh.change_date DESC ROWS 1) AS last_salary
FROM
    employee e       


Пример 3.2. Подзапрос в предложении WHERE для получения значения максимальной зарплаты сотрудника и фильтрации по нему

  
SELECT
    e.first_name,
    e.last_name,
    e.salary
FROM
    employee e
WHERE
    e.salary = (SELECT
                    MAX(ie.salary)
                FROM
                    employee ie)       


Предикаты

Предикат — это простое выражение, утверждающее некоторый факт. Предикат может быть истинным (TRUE), ложным (FALSE) и неопределённым (UNKNOWN). В SQL ложный и неопределённый результаты трактуются как ложь.

В SQL предикаты проверяют в ограничении CHECK, предложении WHERE, выражении CASE, условии соединения во фразе ON для предложений JOIN, а также в предложении HAVING. В PSQL операторы управления потоком выполнения проверяют предикаты в предложениях IF, WHILE и WHEN.

Утверждения

Проверяемые условия не всегда являются простыми предикатами. Они могут быть группой предикатов, каждый из которых при вычислении делает вклад в вычислении общей истинности. Такие сложные условия называются утверждениями. Утверждения могут состоять из одного или нескольких предикатов, связанных логическими операторами AND, OR и NOT.

Каждый из предикатов может содержать вложенные предикаты. Результат вычисления истинности утверждения получается в результате вычисления всех предикатов по направлению от внутренних к внешним. Каждый «уровень» вычисляется в порядке приоритета, до тех пор, пока невозможно будет получить окончательное утверждение.

Предикаты сравнения

Предикат сравнения представляет собой два выражения, соединяемых оператором сравнения. Имеется шесть традиционных операторов сравнения:


=><>=<=<>
        

(Полный список операторов сравнения см. Операторы сравнения).

Если в одной из частей (левой или правой) предиката сравнения встречается NULL, то значение предиката будет неопределённым (UNKNOWN).

Примеры: 

Получить информацию о компьютерах, имеющих частоту процессора не менее 500 МГц и цену ниже $800

  
SELECT * 
FROM Pc 
WHERE speed >= 500 AND price < 800;      

Получить информацию обо всех принтерах, которые являются матричными и стоят меньше $300

  
SELECT * 
FROM Printer 
WHERE type = 'matrix' AND price < 300;    

Следующий запрос не вернёт ни одной записи, поскольку сравнение происходит с псевдо-значением NULL, даже если существуют принтеры с неуказанным типом.

  
SELECT * 
FROM Printer 
WHERE type = NULL AND price < 300;  

Другие предикаты сравнения

Другие предикаты сравнения состоят из ключевых слов.

BETWEEN

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

 
<value> [NOT] BETWEEN <value_1> AND <value_2>                                      
                

Оператор BETWEEN проверяет, попадает (или не попадает при использовании NOT) ли значение во включающий диапазон значений.

Оператор BETWEEN использует два аргумента совместимых типов. В отличие от некоторых других СУБД в Firebird оператор BETWEEN не является симметричным. Меньшее значение должно быть первым аргументом, иначе предикат BETWEEN всегда будет ложным. Поиск является включающим. Таким образом, предикат BETWEEN можно переписать следующим образом:

 
<value> >= <value_1> AND <value> <= <value_2>                               

При использовании предиката BETWEEN в поисковых условиях DML запросов, оптимизатор Firebird может использовать индекс по искомому столбцу, если таковой доступен.

  
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN date '01.01.1992' AND CURRENT_DATE

LIKE

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

<match value> [NOT] LIKE <pattern>
  [ESCAPE <escape character>]                                   
                

Таблица 3.6. Параметры предиката LIKE

Параметр Описание
match value

Выражение символьного типа.

pattern

Шаблон поиска.

escape character

Символ экранирования.


Предикат LIKE сравнивает выражение символьного типа с шаблоном, определённым во втором выражении. Сравнение с шаблоном является чувствительным к регистру (за исключением случаев, когда само поле определено с сортировкой (COLLATION) нечувствительной к регистру).

Трафаретные символы

В шаблоне, разрешается использование двух трафаретных символов:

  • символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении;

  • символ подчёркивания (_), который можно применять вместо любого единичного символа в проверяемом значении.

Если проверяемое значение соответствует образцу с учётом трафаретных символов, то предикат истинен.

Использование управляющего символа в предложении ESCAPE

Если искомая строка содержит трафаретный символ, то следует задать управляющий символ в предложении ESCAPE. Этот управляющий символ должен использоваться в образце перед трафаретным символом, сообщая о том, что последний следует трактовать как обычный символ.

Найти номера отделов, названия которых начинаются со слова «Software»:

  
SELECT DEPT_NO
FROM DEPT
WHERE DEPT_NAME LIKE 'Software%';

В данном запросе может быть использован индекс, если он построен на поле DEPT_NAME.

Оптимизация LIKE

В общем случае предикат LIKE не использует индекс. Однако если предикат принимает вид LIKE 'строка%', то он будет преобразован в предикат STARTING WITH, который будет использовать индекс. Если вам необходимо выполнить поиск с начала строки, то вместо предиката LIKE рекомендуется использовать предикат STARTING WITH.

Поиск сотрудников, имена которых состоят из 5 букв, начинающихся с букв «Sm» и заканчивающихся на «th». В данном случае предикат будет истинен для имен «Smith» и «Smyth».

  
SELECT
    first_name
FROM
    employee
WHERE first_name LIKE 'Sm_th'

Поиск всех заказчиков, в адресе которых содержится строка «Ростов».

  
SELECT *
FROM CUSTOMER
WHERE ADDRESS LIKE '%Ростов%'

Подсказка

Если вам необходимо выполнить поиск внутри строки, то вместо предиката LIKE рекомендуется использовать предикат CONTAINING.

Поиск таблиц, содержащих в имени знак подчёркивания. В данном случае в качестве управляющего символа задан символ «#».

  
SELECT
  RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME LIKE '%#_%' ESCAPE '#'

См. также:  STARTING WITH, CONTAINING, SIMILAR TO.

STARTING WITH

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

<value> [NOT] STARTING WITH <value>                                       
                

Оператор STARTING WITH ищет строку или тип, подобный строке, которая начинается с символов в его аргументе. Поиск STARTING WITH чувствителен к регистру.

При использовании предиката STARTING WITH в поисковых условиях DML запросов, оптимизатор Firebird может использовать индекс по искомому столбцу, если он определён.

Поиск сотрудников, фамилия которых начинается с «Jo».

  
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME STARTING WITH 'Jo'

См. также:  LIKE.

CONTAINING

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

<value> [NOT] CONTAINING <value>                                          
                

Оператор CONTAINING ищет строку или тип, подобный строке, отыскивая последовательность символов, которая соответствует его аргументу. Он может быть использован для алфавитно-цифрового (подобного строковому) поиска в числах и датах. Поиск CONTAINING не чувствителен к регистру.

При использовании предиката CONTAINING в поисковых условиях DML запросов, оптимизатор Firebird не может использовать индекс по искомому столбцу.

Поиск проектов в именах, которых присутствует подстрока «Map»:

  
SELECT *
FROM PROJECT
WHERE PROJ_NAME CONTAINING 'map';

В данном случае будет возвращены две строки с именами «AutoMap» и «MapBrowser port».

Поиск записей об изменении зарплат с датой содержащей число 84 (в данном случае изменения, которые произошли в 1984 году):

  
SELECT *
FROM SALARY_HISTORY
WHERE CHANGE_DATE CONTAINING 84;

См. также:  LIKE.

SIMILAR TO

Доступно в: DSQL, PSQL.

Синтаксис: 

<match value> [NOT] SIMILAR TO <pattern> [ESCAPE <escape character>]                                           
                

Таблица 3.7. Параметры предиката SIMILAR TO

Параметр Описание
match value

Выражение символьного типа.

pattern

Регулярное выражение SQL.

escape character

Символ экранирования.


Оператор SIMILAR TO проверяет соответствие строки с шаблоном регулярного выражения SQL. В отличие от некоторых других языков для успешного выполнения шаблон должен соответствовать всей строке — соответствие подстроки не достаточно. Если один из операндов имеет значение NULL, то и результат будет NULL. В противном случае результат является TRUE или FALSE.

Синтаксис регулярных выражений SQL

Следующий синтаксис определяет формат регулярного выражения SQL. Это полное и корректное его определение. Он является весьма формальным и довольно длинным и, вероятно, озадачивает тех, кто не имеет опыта работы с регулярными выражениями. Не стесняйтесь пропустить его и начать читать следующий раздел, Создание регулярных выражений, использующий подход от простого к сложному.

<regular expression> ::= <regular term> ['|' <regular term> ...]       

<regular term> ::= <regular factor> ...
       
<regular factor> ::= <regular primary> [<quantifier>]       

<quantifier> ::= ?                  
               | *                  
               | +                  
               | '{' <m> [,[<n>]] '}'       

<m>, <n> ::= целое положительное число с <m> <= <n> если оба присутствуют           

<regular primary> ::= <character>                       
                    | <character class>                       
                    | %                       
                    | (<regular expression>)       

<character> ::= <escaped character>                 
              | <non-escaped character>       

<escaped character> ::= <escape-char> <special character>                         
                      | <escape-char> <escape-char>       

<special character> ::= любой из символов []()|^-+*%_?{       

<non-escaped character> ::= любой символ за исключением <special character> 
                            и не эквивалентный <escape-char> (если задан)       

<character class> ::= '_'                       
                    | '[' <member> ... ']'                       
                    | '[^' <non-member> ... ']'                       
                    | '[' <member> ... '^' <non-member> ... ']'       

<member>, <non-member> ::= <character>   
                         | <range>   
                         | <predefined class>   

<range> ::= <character>-<character>   

<predefined class> ::= '[:' <predefined class name> ':]'   

<predefined class name> ::= ALPHA | UPPER | LOWER | DIGIT 
                         |  ALNUM  |  SPACE  |  WHITESPACE                                             
                
Создание регулярных выражений
Символы

В регулярных выражениях большинство символов представляет сами себя. Единственное исключение — специальные символы (special character):

[ ] ( ) | ^ - + * % _ ? { }                       

и управляющие символы, если они заданы.

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

'Apple' SIMILAR TO 'Apple' -- TRUE
'Apples' SIMILAR TO 'Apple' -- FALSE
'Apple' SIMILAR TO 'Apples' -- FALSE
'APPLE' SIMILAR TO 'Apple' -- в зависимости от сортировки                            
                        
Шаблоны

Известным SQL шаблонам _ и % соответствует любой единственный символ и строка любой длины, соответственно:

'Birne' SIMILAR TO 'B_rne' -- TRUE
'Birne' SIMILAR TO 'B_ne' -- FALSE
'Birne' SIMILAR TO 'B%ne' -- TRUE
'Birne' SIMILAR TO 'Bir%ne%' -- TRUE
'Birne' SIMILAR TO 'Birr%ne' -- FALSE                            
                        

Обратите внимание, что шаблон % также соответствует пустой строке.

Классы символов

Набор символов, заключённый в квадратные скобки определяют класс символов. Символ в строке соответствует классу в шаблоне, если символ является элементом класса:

'Citroen' SIMILAR TO 'Cit[arju]oen' -- TRUE
'Citroen' SIMILAR TO 'Ci[tr]oen' -- FALSE
'Citroen' SIMILAR TO 'Ci[tr][tr]oen' -- TRUE                            
                            

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

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

'Datte' SIMILAR TO 'Dat[q-u]e' -- TRUE
'Datte' SIMILAR TO 'Dat[abq-uy]e' -- TRUE
'Datte' SIMILAR TO 'Dat[bcg-km-pwz]e' -- FALSE                           
                            
[:ALPHA:]

Латинские буквы a...z и A...Z. Этот класс также включает символы с диакритическими знаками при нечувствительных к акцентам сортировках.

[:DIGIT:]

Десятичные цифры 0...9.

[:ALNUM:]

Объединение [:ALPHA:] и [:DIGIT:].

[:UPPER:]

Прописные (в верхнем регистре) латинские буквы A...Z. Также включает в себя символы в нижнем регистре при нечувствительных к регистру сортировках и символы с диакритическими знаками при нечувствительных к акцентам сортировках.

[:LOWER:]

Строчные (в нижнем регистре) латинские буквы a...z. Также включает в себя символы в верхнем регистре при нечувствительных к регистру сортировках и символы с диакритическими знаками при нечувствительных к акцентам сортировках.

[:SPACE:]

Символ пробела (ASCII 32).

[:WHITESPACE:]

Горизонтальная табуляция (ASCII 9), перевод строки (ASCII 10), вертикальная табуляция (ASCII 11), разрыв страницы (ASCII 12), возврат каретки (ASCII 13) и пробел (ASCII 32).

Включение в оператор SIMILAR TO предопределённого класса имеет тот же эффект, как и включение всех его элементов. Использование предопределённых классов допускается только в пределах определения класса. Если Вам нужно сопоставление только с предопределённым классом и ничего больше, то поместите дополнительную пару скобок вокруг него.

'Erdbeere' SIMILAR TO 'Erd[[:ALNUM:]]eere' -- TRUE
'Erdbeere' SIMILAR TO 'Erd[[:DIGIT:]]eere' -- FALSE
'Erdbeere' SIMILAR TO 'Erd[a[:SPACE:]b]eere' -- TRUE
'Erdbeere' SIMILAR TO '[[:ALPHA:]]' -- FALSE
'E' SIMILAR TO '[[:ALPHA:]]' -- TRUE                            
                        

Если определение класса запускается со знаком вставки (^), то все, что следует за ним, исключается из класса. Все остальные символы проверяются.

'Framboise' SIMILAR TO 'Fra[^ck-p]boise' -- FALSE
'Framboise' SIMILAR TO 'Fr[^a][^a]boise' -- FALSE
'Framboise' SIMILAR TO 'Fra[^[:DIGIT:]]boise' -- TRUE                           
                        

Если знак вставки (^) находится не в начале последовательности, то класс включает в себя все символы до него и исключает символы после него.

'Grapefruit' SIMILAR TO 'Grap[a-m^f-i]fruit' -- TRUE
'Grapefruit' SIMILAR TO 'Grap[abc^xyz]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abc^de]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abe^de]fruit' -- FALSE
'3' SIMILAR TO '[[:DIGIT:]^4-8]' -- TRUE
'6' SIMILAR TO '[[:DIGIT:]^4-8]' -- FALSE                          
                        

Наконец, уже упомянутый подстановочный знак "_" является собственным классом символов, соответствуя любому единственному символу.

Кванторы

Вопросительный знак сразу после символа или класса указывает на то, что для соответствия предыдущий элемент может произойти 0 или 1 раз:

'Hallon' SIMILAR TO 'Hal?on' -- FALSE
'Hallon' SIMILAR TO 'Hal?lon' -- TRUE
'Hallon' SIMILAR TO 'Halll?on' -- TRUE
'Hallon' SIMILAR TO 'Hallll?on' -- FALSE
'Hallon' SIMILAR TO 'Halx?lon' -- TRUE
'Hallon' SIMILAR TO 'H[a-c]?llon[x-z]?' -- TRUE                                
                        

Звёздочка (*) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 0 или более раз:

'Icaque' SIMILAR TO 'Ica*que' -- TRUE
'Icaque' SIMILAR TO 'Icar*que' -- TRUE
'Icaque' SIMILAR TO 'I[a-c]*que' -- TRUE
'Icaque' SIMILAR TO '_*' -- TRUE
'Icaque' SIMILAR TO '[[:ALPHA:]]*' -- TRUE
'Icaque' SIMILAR TO 'Ica[xyz]*e' -- FALSE                        
                            

Знак плюс (+) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 1 или более раз:

'Jujube' SIMILAR TO 'Ju_+' -- TRUE
'Jujube' SIMILAR TO 'Ju+jube' -- TRUE
'Jujube' SIMILAR TO 'Jujuber+' -- FALSE
'Jujube' SIMILAR TO 'J[jux]+be' -- TRUE
'Jujube' SIMILAR TO 'J[[:DIGIT:]]+ujube' -- FALSE                        
                            

Если символ или класс сопровождаются числом, заключённым в фигурные скобки, то для соответствия необходимо повторение элемента точно это число раз:

'Kiwi' SIMILAR TO 'Ki{2}wi' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{2}i' -- TRUE
'Kiwi' SIMILAR TO 'K[ipw]{2}' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{3}' -- TRUE                                
                        

Если число сопровождается запятой, то для соответствия необходимо повторение элемента как минимум это число раз:

'Limone' SIMILAR TO 'Li{2,}mone' –- FALSE
'Limone' SIMILAR TO 'Li{1,}mone' -- TRUE
'Limone' SIMILAR TOto 'Li[nezom]{2,}' -- TRUE                        
                            

Если фигурные скобки содержат два числа (m и n), разделённые запятой, и второе число больше первого, то для соответствия элемент должен быть повторен, как минимум, m раз и не больше n раз:

'Mandarijn' SIMILAR TO 'M[a-p]{2,5}rijn' -- TRUE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}rijn' -- FALSE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}arijn' -- TRUE                        
                            

Кванторы ?, * и + сокращение для {0,1}, {0,} и {1,}, соответственно.

Термин ИЛИ

В условиях регулярных выражений можно использовать оператор ИЛИ (|). Соответствие произошло, если строка параметра соответствует, по крайней мере, одному из условий:

'Nektarin' SIMILAR TO 'Nek|tarin' -- FALSE
'Nektarin' SIMILAR TO 'Nektarin|Persika' -- TRUE
'Nektarin' SIMILAR TO 'M_+|N_+|P_+' – TRUE                                
                        
Подвыражения

Одна или более частей регулярного выражения могут быть сгруппированы в подвыражения (также называемые подмасками). Для этого их нужно заключить в круглые скобки:

'Orange' SIMILAR TO 'O(ra|ri|ro)nge' -- TRUE
'Orange' SIMILAR TO 'O(r[a-e])+nge' -- TRUE
'Orange' SIMILAR TO 'O(ra){2,4}nge' -- FALSE
'Orange' SIMILAR TO 'O(r(an|in)g|rong)?e' – TRUE                               
                        
Экранирование специальных символов

Для исключения из процесса сопоставления специальных символов (которые часто встречаются в регулярных выражениях) их надо экранировать. Специальных символов экранирования по умолчанию нет — их при необходимости определяет пользователь:

'Peer (Poire)' SIMILAR TO 'P[^ ]+ \(P[^ ]+\)' ESCAPE '\' -- TRUE
'Pera [Pear]' SIMILAR TO 'P[^ ]+ #[P[^ ]+#]' ESCAPE '#' -- TRUE
'Paron-Appledryck' SIMILAR TO 'P%$-A%' ESCAPE '$' -- TRUE
'Parondryck' SIMILAR TO 'P%--A%' ESCAPE '-' -- FALSE                             
                        

IS DISTINCT FROM

Доступно в: DSQL, PSQL.

Синтаксис: 

 
op1 IS [NOT] DISTINCT FROM op2                                          
                

Два операнда считают различными (DISTINCT), если они имеют различные значения, или если одно из них — NULL, и другое нет. Они считаются NOT DISTINCT (равными), если имеют одинаковые значения или оба имеют значение NULL.

IS [NOT] DISTINCT FROM всегда возвращает TRUE или FALSE и никогда UNKNOWN (NULL) (неизвестное значение). Операторы «=» и «<>», наоборот, вернут UNKNOWN (NULL), если один или оба операнда имеют значение NULL.

Таблица 3.8. Результаты выполнения различных операторов сравнения

Характеристики операнда Результаты различных операторов
= IS NOT DISTINCT FROM <> IS DISTINCT FROM
Одинаковые значения TRUE TRUE FALSE FALSE
Различные значения FALSE FALSE TRUE TRUE
Оба NULL UNKNOWN TRUE UNKNOWN FALSE
Одно NULL UNKNOWN FALSE UNKNOWN TRUE


Примеры: 

SELECT ID, NAME, TEACHER
FROM COURSES
WHERE START_DAY IS NOT DISTINCT FROM END_DAY

IF (NEW.JOB IS DISTINCT FROM OLD.JOB)
THEN POST_EVENT 'JOB_CHANGED';                        
                    

См. также:  IS NULL.

IS NULL

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

 
<value> IS [NOT] NULL                                          
                

Поскольку NULL не является значением, эти операторы не являются операторами сравнения. Предикат IS [NOT] NULL проверяет утверждение, что выражение в левой части имеет значение (IS NOT NULL) или не имеет значения (IS NULL).

Пример 3.3. Предикат IS NULL

Поиск записей о продажах, для которых не установлена дата отгрузки:

  
SELECT *
FROM SALES
WHERE SHIP_DATE IS NULL;


Предикаты существования

В эту группу предикатов включены предикаты, которые используют подзапросы для передачи значений для различного вида утверждений в условиях поиска. Предикаты существования называются так потому, что они различными способами проверяют существование значения в левой части предиката в выходных результатах подзапросов.

EXISTS

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

   
[NOT] EXISTS (<select_stmt>)                                          
                

Предикат EXISTS использует подзапрос в качестве аргумента и оценивает его как истинный, если в подзапросе есть выходные данные, в противном случае предикат оценивается как ложный. Результат подзапроса может содержать несколько столбцов, поскольку значения не проверяются, а просто фиксируется факт наличия строк результата. Данный предикат может принимать только два значения: истина (TRUE) и ложь (FALSE).

Примеры: 

Пример 3.4. Предикат EXISTS

Найти тех сотрудников, у которых есть проекты.

  
SELECT *
FROM employee
WHERE EXISTS (SELECT *
              FROM
                employee_project ep
              WHERE
                ep.emp_no = employee.emp_no)


Пример 3.5. Предикат NOT EXISTS

Найти тех сотрудников, у которых нет проектов.

  
SELECT *
FROM employee
WHERE NOT EXISTS (SELECT *
                  FROM
                    employee_project ep
                  WHERE
                    ep.emp_no = employee.emp_no)


IN

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

   
<value> [NOT] IN (<select_stmt> | <value_list>)       

<value_list> ::= <value_1> [, <value_2> ...]                                              
                

Предикат IN проверяет, присутствует ли значение выражения слева в указанном справа наборе значений. Набор значений не может превышать 1500 элементов. Предикат IN может быть переписан в следующей эквивалентной форме:

(<value> = <value_1> [OR <value> = <value_2> ...])                    
                    

При использовании предиката IN в поисковых условиях DML запросов, оптимизатор Firebird может использовать индекс по искомому столбцу, если он определён.

Во второй форме предикат IN проверяет, присутствует (или отсутствует, при использовании NOT IN) ли значение выражения слева в результате выполнения подзапроса справа. Результат подзапроса может содержать только один столбец, иначе будет выдана ошибка "count of column list and variable list do not match".

Запросы с использованием предиката IN с подзапросом, можно переписать на аналогичный запрос с использованием предиката EXISTS. Например, следующий запрос:

SELECT
  model, speed, hd
FROM PC
WHERE
  model IN (SELECT model
            FROM product
            WHERE maker = 'A');                
                    

Можно переписать на аналогичный запрос с использованием предиката EXISTS:

SELECT
  model, speed, hd
FROM PC
WHERE
  EXISTS (SELECT *
          FROM product
          WHERE maker = 'A'
            AND product.model = PC.model);                
                    

Однако, запрос с использованием NOT IN не всегда даст тот же результат, что запрос NOT EXISTS. Причина заключается в том, что предикат EXISTS всегда возвращает TRUE или FALSE, тогда как предикат IN может вернуть NULL в следующих случаях:

  1. Когда проверяемое значений равно NULL и список в IN не пуст.

  2. Когда проверяемое значение не имеет совпадений в списке IN и одно из значений является NULL.

В этих двух случаях предикат IN вернёт NULL, в то время как соответствующий предикат EXISTS вернёт FALSE. В поисковых условиях или операторе IF оба результата обозначают "провал" и обрабатываются одинаково.

Однако на тех же данных NOT IN вернёт NULL, в то время как EXISTS вернёт TRUE, что приведёт к противоположному результату.

Это можно продемонстрировать следующим примером.

Предположим у вас есть такой запрос:

-- Ищем людей, которые не родились в тот же день, что
-- известные жители Нью-Йорка
SELECT P1.name AS NAME
FROM Personnel P1 
WHERE P1.birthday NOT IN (SELECT C1.birthday
                          FROM Celebrities C1 
                          WHERE С1.birthcity = 'New York');                
                

Можно предположить, что аналогичный результат даст запрос с использованием предиката NOT EXISTS:

-- Ищем людей, которые не родились в тот же день, что
-- известные жители Нью-Йорка
SELECT P1.name AS NAME
FROM Personnel P1
WHERE NOT EXISTS (SELECT *
                  FROM Celebrities C1
                  WHERE C1.birthcity = 'New York'
                    AND C1.birthday = P1.birthday);              
                    

Допустим, что в Нью-Йорке всего один известный житель, и его дата рождения неизвестна. При использовании предиката EXISTS подзапрос внутри него не выдаст результатов, так как при сравнении дат рождения с NULL результатом будет UNKNOWN. Это приведёт к тому, что результат предиката NOT EXISTS будет истинен для каждой строки основного запроса. В то время как результатом предиката NOT IN будет UNKNOWN и ни одна строка не будет выведена.

Примеры: 

Пример 3.6. Предикат IN

Найти сотрудников с именами «Pete», «Ann» и «Roger»:

  
SELECT *
FROM EMPLOYEE
WHERE FIRST_NAME IN ('Pete', 'Ann', 'Roger');


Пример 3.7. Поисковый предикат IN

Найти все компьютеры, для которых существуют модели, производитель которых начинается на букву «A»:

  
SELECT
  model, speed, hd
FROM PC
WHERE
  model IN (SELECT model
            FROM product
            WHERE maker STARTING WITH 'A');


См. также:  EXISTS.

SINGULAR

Доступно в: DSQL, PSQL, ESQL.

Синтаксис: 

[NOT] SINGULAR (<select_stmt>)                                          
                

Предикат SINGULAR использует подзапрос в качестве аргумента и оценивает его как истинный, если подзапрос возвращает одну и только одну строку результата, в противном случае предикат оценивается как ложный. Результат подзапроса может содержать несколько столбцов, поскольку значения не проверяются. Данный предикат может принимать только два значения: истина (TRUE) и ложь (FALSE).

Пример 3.8. Предикат SINGULAR

Найти тех сотрудников, у которых есть только один проект.

  
SELECT *
FROM employee
WHERE SINGULAR (SELECT *
                FROM
                  employee_project ep
                WHERE
                  ep.emp_no = employee.emp_no)


Количественные предикаты подзапросов

Квантором называется логический оператор, задающий количество объектов, для которых данное утверждение истинно. Это логическое количество, а не числовое; оно связывает утверждение с полным множеством возможных объектов. Такие предикаты основаны на формальных логических квантификаторах общности и существования, которые в формальной логике записываются как и .

В выражениях подзапросов количественные предикаты позволяют сравнивать отдельные значения с результатами подзапросов; их общая форма:

  
<value expression> <comp op> <quantifier> <subquery>

ALL

Доступно в: DSQL, PSQL.

Синтаксис: 

<value> <op> ALL (<select_stmt>)                                          
                

При использовании квантора ALL, предикат является истинным, если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса. Если подзапрос не возвращает ни одной строки, то предикат автоматически считается верным.

Пример 3.9. Квантор ALL

Вывести только тех заказчиков, чьи оценки выше, чем у каждого заказчика в Париже.

  
SELECT * 
FROM Customers 
WHERE rating > ALL 
      (SELECT rating 
       FROM Customers 
       WHERE city = 'Paris')


Важно

Если подзапрос возвращает пустое множество, то предикат будет истинен для каждого левостороннего значения, независимо от оператора. Это может показаться странным и противоречивым, потому что в этом случае каждое левостороннее значение рассматривается как одновременно больше, меньше, равное и неравное любому значению из правого потока.

Тем не менее, это нормально согласуется с формальной логикой: если множество пусто, то предикат верен 0 раз, т.е. для каждой строки в множестве.

ANY и SOME

Доступно в: DSQL, PSQL.

Синтаксис: 

<value> <op> {ANY | SOME} (<select_stmt>)                                          
                

Эти два квантора идентичны по поведению. Очевидно, оба представлены в стандарте SQL для взаимозаменяемого использования с целью улучшения читаемости операторов. При использовании квантора ANY или SOME, предикат является истинным, если любое из значений выбранное подзапросом удовлетворяет условию в предикате внешнего запроса. Если подзапрос не возвращает ни одной строки, то предикат автоматически считается ложным.

Пример 3.10. Квантор ANY

Вывести только тех заказчиков, чьи оценки выше, чем у любого заказчика в Риме.

  
SELECT * 
FROM Customers 
WHERE rating > ANY 
      (SELECT rating 
       FROM Customers 
       WHERE city = 'Rome')


Глава 4. Операторы DDL

Data Definition Language (DDL) — язык описания данных. С помощью этого подмножества языка создаются, модифицируются и удаляются объекты базы данных (т.е. Метаданные).

DATABASE

В данном разделе описываются вопросы создания базы данных, подключения к существующей базе данных, изменения структуры файлов, перевод базы данных в состояние, необходимое для безопасного резервного копирования, и обратно и удаления базы данных.

CREATE DATABASE

Назначение: Создание новой базы данных.

Доступно в: DSQL, ESQL.

Синтаксис: 

                     
CREATE {DATABASE | SCHEMA} '<filespec>'
  [USER 'username' [PASSWORD 'password']
  [PAGE_SIZE [=] size]
  [LENGTH [=] num [PAGE[S]]
  [SET NAMES 'charset']
  [DEFAULT CHARACTER SET default_charset
  [COLLATION collation]]
  [<sec_file> [<sec_file> ...]]
  [DIFFERENCE FILE 'diff_file'];

<filespec> ::= [<server_spec>]{filepath | db_alias}

<server_spec> ::= servername[/{port | service}]: | \\servername\

<sec_file> ::= FILE 'filepath'
  [LENGTH [=] num [PAGE[S]] [STARTING [AT [PAGE]] pagenum]              
                  

Таблица 4.1. Параметры оператора CREATE DATABASE

Параметр Описание
filespec

Спецификация первичного файла базы данных.

server_spec

Спецификация удалённого сервера. Включает в себя имя сервера и протокол. Необходима, если база данных создаётся на удалённом сервере.

filepath

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

db_alias

Псевдоним (alias) базы данных, присутствующий в файле aliases.conf

servername

Имя сервера или IP адрес, на котором создаётся база данных.

username

Имя пользователя-владельца базы данных. Не чувствительно к регистру.

password

Пароль пользователя-владельца базы данных. Может содержать до 31 символа, но только первые 8 имеют значение. Чувствительно к регистру.

size

Размер страницы для базы данных. Допустимые значения 4096 (по умолчанию), 8192, 16384.

num

Максимальный размер первичного или вторичного файла в страницах.

charset

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

default_charset

Задаёт набор символов по умолчанию для строковых типов данных.

collation

Сортировка для набора символов по умолчанию.

sec_file

Спецификация вторичного файла.

pagenum

Номер страницы, с которой начинается вторичный файл базы данных.

diff_file

Путь и имя дельта файла.


Оператор CREATE DATABASE создаёт новую базу данных. Вы можете использовать CREATE DATABASE или CREATE SCHEMA. Это синонимы.

База данных может состоять из одного или нескольких файлов. Первый, основной, файл называется первичным, остальные файлы — вторичными.

Примечание

В настоящее время многофайловые базы данных являются атавизмом. Многофайловые базы данных имеет смысл использовать на старых файловых системах, в которых существует ограничение на размер любого файла. Например, в FAT32 нельзя создать файл больше 4х гигабайт.

Спецификация первичного файла — имя файла базы данных и его расширение с указанием к нему полного пути в соответствии с правилами используемой операционной системы. При создании базы данных файл базы данных должен отсутствовать. В противном случае будет выдано сообщение об ошибке и база данных не будет создана. Если полный путь к базе данных не указан, то база данных будет создана в одном из системных каталогов. В каком именно зависит от операционной системы.

Использование псевдонимов БД

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

  
alias = filepath                    
                

Создание удалённых БД

При создании базы данных на удалённом сервере необходимо указать спецификацию удалённого сервера. Спецификация удалённого сервера зависит от используемого протокола. Если вы при создании базы данных используете протокол TCP/IP, то спецификация первичного файла должна выглядеть следующим образом:

  
servername:{filepath | db_alias}                   
                

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

  
\\servername\{filepath | db_alias}                   
                

Необязательные параметры CREATE DATABASE

Необязательные предложения USER и PASSWORD задают, соответственно, имя и пароль пользователя присутствующего в базе данных безопасности (security2.fdb). Пользователя и пароль можно не указывать, если установлены переменные окружения ISC_USER и ISC_PASSWORD. Создать базу данных может любой авторизованный пользователь. Пользователь, указанный при создании базы данных, будет её владельцем.

Необязательное предложение PAGE_SIZE задаёт размер страницы базы данных. Этот размер будет установлен для первичного файла и всех вторичных файлов базы данных. При вводе размера страницы БД меньшего, чем 4096, он будет автоматически изменён на 4096. Другие числа (не равные 4096, 8192 или 16384) будут изменены на ближайшее меньшее из поддерживаемых значений. Если размер страницы базы данных не указан, то по умолчанию принимается значение 4096.

Необязательное предложение LENGTH задаёт максимальный размер первичного или вторичного файла базы данных в страницах. При создании базы данных её первичный или вторичный файл будут занимать минимально необходимое количество страниц для хранения системных данных, не зависимо от величины, установленной в предложении LENGHT. Для единственного или последнего (в многофайловой базе данных) файла значение LENGTH никак не влияет на его размер. Файл будет автоматически увеличивать свой размер по мере необходимости.

Необязательное предложение SET NAMES задаёт набор символов подключения, доступного после успешного создания базы данных. По умолчанию используется набор символов NONE.

Необязательное предложение DEFAULT CHARACTER SET задаёт набор символов по умолчанию для строковых типов данных. Наборы символов применяются для типов CHAR, VARCHAR и BLOB. По умолчанию используется набор символов NONE. Для набора символов по умолчанию можно также указать сортировку по умолчанию (COLLATION). В этом случае сортировка станет умалчиваемой для набора символов по умолчанию (т.е. для всей БД за исключением случаев использования других наборов символов).

Предложение STARTING AT задаёт номер страницы базы данных, с которой должен начинаться следующий файл базы данных. Когда предыдущий файл будет полностью заполнен данными в соответствии с заданным номером страницы, система начнёт помещать вновь добавляемые данные в следующий файл базы данных.

Необязательное предложение DIFFERENCE FILE задаёт путь и имя дельта файла, в который будут записываться изменения, внесённые в БД после перевода её в режим «безопасного копирования» («copy-safe») путём выполнения команды ALTER DATABASE BEGIN BACKUP. Полное описание данного параметра см. в ALTER DATABASE.

Для того чтобы база данных была создана в нужном вам диалекте SQL, следует перед выполнением оператора создания базы данных задать нужный диалект, выполнив оператор SET SQL DIALECT. По умолчанию база данных создаётся в 3 диалекте.

Примеры

Пример 4.1. Создание базы данных в операционной системе Windows

Создание базы данных в операционной системе Windows расположенной на диске D с размером страницы 8192. Владельцем базы данных будет пользователь wizard. База данных будет в 1 диалекте, и использовать набор символов по умолчанию WIN1251.

  
SET SQL DIALECT 1;
CREATE DATABASE 'D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192 DEFAULT CHARACTER SET WIN1251;                


Пример 4.2. Создание базы данных в операционной системе Linux

Создание базы данных в операционной системе Linux с размером страницы 4096. Владельцем базы данных будет пользователь wizard. База данных будет в 3 диалекте, и использовать набор символов по умолчанию UTF8 с умалчиваемой сортировкой UNICODE_CI_AI.

  
CREATE DATABASE '/home/firebird/test.fdb'
USER 'wizard' PASSWORD 'player'
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;                


Пример 4.3. Создание базы данных на удалённом сервере

Создание базы данных на удалённом сервере baseserver расположенном по пути, на который ссылается псевдоним test, описанный в файле aliases.conf. Используется протокол TCP. Владельцем базы данных будет пользователь wizard. База данных будет в 3 диалекте, и использовать набор символов по умолчанию UTF8.

  
CREATE DATABASE 'baseserver:test'
USER 'wizard' PASSWORD 'player'
DEFAULT CHARACTER SET UTF8;                 


Пример 4.4. Создание многофайловой базы данных

Создание базы данных в 3 диалекте с набором символов по умолчанию UTF8. Первичный файл будет содержать 10000 страниц с размером страницы 8192. Как только в процессе работы с базой данных первичный файл будет заполнен, СУБД будет помещать новые данные во вторичный файл test.fdb2. Аналогичные действия будут происходить и со вторым вторичным файлом. Размер последнего файла будет увеличиваться до тех пор, пока это позволяет используемая операционная система или пока не будет исчерпана память на внешнем носителе.

  
SET SQL DIALECT 3;
CREATE DATABASE 'baseserver:D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192
DEFAULT CHARACTER SET UTF8
FILE 'D:\test.fdb2'
STARTING AT PAGE 10001
FILE 'D:\test.fdb3'
STARTING AT PAGE 20001;                  


Пример 4.5. Создание многофайловой базы данных 2

Создание базы данных в 3 диалекте с набором символов по умолчанию UTF8. Первичный файл будет содержать 10000 страниц с размером страницы 8192. Как только в процессе работы с базой данных первичный файл будет заполнен, СУБД будет помещать новые данные во вторичный файл test.fdb2. Аналогичные действия будут происходить и со вторым вторичным файлом.

  
SET SQL DIALECT 3;
CREATE DATABASE 'baseserver:D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192
LENGTH 10000 PAGES
DEFAULT CHARACTER SET UTF8
FILE 'D:\test.fdb2'
FILE 'D:\test.fdb3'
STARTING AT PAGE 20001;                 


См. также:  ALTER DATABASE, DROP DATABASE.

ALTER DATABASE

Назначение: Изменение структуры файлов базы данных или переключение её в состояние "безопасное для копирования".

Доступно в: DSQL, ESQL.

Синтаксис: 

 
ALTER {DATABASE | SCHEMA}
    {<add_sec_clause> [<add_sec_clausee> ...]}
  | {ADD DIFFERENCE FILE 'diff_file' | DROP DIFFERENCE FILE}
  | {{BEGIN | END} BACKUP}

<add_sec_clause> ::= ADD FILE <sec_file> 

<sec_file> ::= 'filepath'
  [STARTING [AT [PAGE]] pagenum]
  [LENGTH [=] num [PAGE[S]]

Таблица 4.2. Параметры оператора ALTER DATABASE

Параметр Описание
add_sec_clause

Инструкция для добавления вторичного файла базы данных.

sec_file

Спецификация вторичного файла.

filepath

Полный путь и имя дельта файла или вторичного файла базы данных.

pagenum

Номер страницы, с которой начинается вторичный файл базы данных.

num

Максимальный размер вторичного файла в страницах.

diff_file

Путь и имя дельта файла.


Оператор ALTER DATABASE изменяет структуру файлов базы данных или переключает её в состояние «безопасное для копирования».

Только владелец базы данных и администраторы имеют привилегии на использование ALTER DATABASE.

Предложение ADD FILE добавляет к базе данных вторичный файл. Для вторичного файла необходимо указывать полный путь к файлу и имя вторичного файла. Описание вторичного файла аналогично тому, что описано в операторе CREATE DATABASE.

Предложение ADD DIFFERENCE FILE задаёт путь и имя дельта файла, в который будут записываться изменения, внесённые в базу данных после перевода её в режим «безопасного копирования» («copy-safe»). Этот оператор в действительности не добавляет файла. Он просто переопределяет умалчиваемые имя и путь файла дельты. Для изменения существующих установок необходимо сначала удалить ранее указанное описание файла дельты с помощью оператора DROP DIFFERENCE FILE, а затем задать новое описание файла дельты. Если не переопределять путь и имя файла дельты, то он будет иметь тот же путь и имя, что и БД, но с расширением .delta.

Примечание

При задании относительного пути или только имени файла дельты он будет создаваться в текущем каталоге сервера. Для операционных систем Windows это системный каталог.

Предложение DROP DIFFERENCE FILE удаляет описание (путь и имя) файла дельты, заданное ранее командой ADD DIFFERENCE FILE. На самом деле при выполнении этого оператора файл не удаляется. Он удаляет путь и имя файла дельты и при последующем переводе БД в режим «безопасного копирования» будут использованы значения по умолчанию (т.е. тот же путь и имя что и у файла БД, но с расширением .delta).

Предложение BEGIN BACKUP предназначено для перевода базы данных в режим «безопасного копирования» («copy-safe»). Этот оператор «замораживает» основной файл базы данных, что позволяет безопасно делать резервную копию средствами файловой системы, даже если пользователи подключены и выполняют операции с данными. При этом все изменения, вносимые пользователями в базу данных, будут записаны в отдельный файл, так называемый дельта файл (delta file).

Примечание

Оператор BEGIN BACKUP, не смотря на синтаксис, не начинает резервное копирование базы данных, а лишь создаёт условия для его осуществления.

Предложение END BACKUP предназначено для перевода базы данных из режима «безопасного копирования» («copy-safe») в режим нормального функционирования. Этот оператор объединяет файл дельты с основным файлом базы данных и восстанавливает нормальное состояние работы, таким образом, закрывая возможность создания безопасных резервных копий средствами файловой системы. (При этом безопасное резервное копирование с помощью утилиты gbak остаётся доступным).

Примеры

Пример 4.6. Добавление вторичного файла в базу данных

Как только в предыдущем первичном или вторичных файлах будет заполнено 30000 страниц, СУБД будет помещать данные во вторичный файл test4.fdb.

  
ALTER DATABASE 
ADD FILE 'D:\test.fdb4'
STARTING PAGE 30001;                


Пример 4.7. Установка пути и имени файла дельты

  
ALTER DATABASE 
ADD DIFFERENCE FILE 'D:\test.diff';                


Пример 4.8. Удаление описание файла дельты

  
ALTER DATABASE 
DROP DIFFERENCE FILE;              


Пример 4.9. Перевод базы данных в режим «безопасного копирования»

  
ALTER DATABASE 
BEGIN BACKUP;           


Пример 4.10. Возвращение базы данных в режим нормального функционирования из режима «безопасного копирования»

  
ALTER DATABASE 
END BACKUP;         


См. также:  CREATE DATABASE, DROP DATABASE.

DROP DATABASE

Назначение: Удаление базы данных к которой вы сейчас подключены.

Доступно в: DSQL, ESQL.

Синтаксис: 

 
DROP DATABASE;
                    

Оператор DROP DATABASE удаляет текущую базу данных. Перед удалением базы данных, к ней необходимо присоединиться. Оператор удаляет первичный, все вторичные файлы и все файлы теневых копий.

Только владелец базы данных и администраторы имеют привилегии на использование DROP DATABASE.

Примеры

Пример 4.11. Удаление базы данных

Удаление базы данных, к которой подключен клиент.

  
DROP DATABASE;


См. также:  CREATE DATABASE, ALTER DATABASE.

SHADOW

Теневая копия (shadow — дословно тень) является точной страничной копией базы данных. После создания теневой копии все изменения, сделанные в базе данных, сразу же отражаются и в теневой копии. Если по каким либо причинам первичный файл базы данных станет недоступным, то СУБД переключится на теневую копию.

В данном разделе рассматриваются вопросы создания и удаления теневых копий.

Примечание

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

CREATE SHADOW

Назначение: Создание теневой копии.

Доступно в: DSQL, ESQL.

Синтаксис: 

  
CREATE SHADOW sh_num [AUTO | MANUAL] [CONDITIONAL]
  'filepath' [LENGTH [=] num [PAGE[S]]]
  [<secondary_file>];

<secondary_file> ::= 
  FILE 'filepath'
  LENGTH [=] num [PAGE[S]] | STARTING [AT [PAGE]] pagenum
                    
                

Таблица 4.3. Параметры оператора CREATE SHADOW

Параметр Описание
sh_num

Номер теневой копии — положительное число, идентифицирующее набор файлов теневой копии.

filepath

Имя файла и путь к нему в соответствии с требованиями ОС.

num

Максимальный размер теневой копии в страницах.

secondary_file

Спецификация вторичного файла.

page_num

Номер страницы, с которой должен начинаться вторичный файл копии.


Оператор CREATE SHADOW создаёт новую теневую копию. Теневая копия начинает дублировать базу данных сразу в момент создания этой копии.

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

Для файлов теневой копии размер страницы устанавливается равным размеру страницы базы данных и не может быть изменён.

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

Режимы AUTO и MANUAL

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

  • Если выбран режим AUTO (значение по умолчанию), то в случае, когда теневая копия становится недоступной, автоматически прекращается использование этой копии и из базы данных удаляются все ссылки на нее. Работа с базой данных продолжается обычным образом без осуществления копирования в данную теневую копию.

    Если указано ключевое слово CONDITIONAL, то система будет пытаться создать новую теневую копию. чтобы заменить потерянную. Это не всегда возможно, тогда вам потребуется создать новую тень вручную.

  • Если выбран режим MANUAL, то в случае, когда теневая копия становится недоступной, все попытки соединения с базой данных и обращения к ней будут вызывать сообщение об ошибке до тех пор, пока теневая копия не станет доступной или пока не будет удалена администратором БД с помощью оператора DROP SHADOW.

Необязательные параметры CREATE SHADOW

Необязательное предложение LENGTH задаёт максимальный размер первичного или вторичного файла теневой копии в страницах. Для единственного или последнего файла теневой копии значение LENGTH никак не влияет на его размер. Файл будет автоматически увеличивать свой размер по мере необходимости.

Предложение STARTING AT задаёт номер страницы теневой копии, с которой должен начинаться следующий файл теневой копии. Когда предыдущий файл будет полностью заполнен данными в соответствии с заданным номером страницы, система начнёт помещать вновь добавляемые данные в следующий файл теневой копии.

Только владелец базы данных и администраторы имеют привилегии на использование CREATE SHADOW.

Примеры

Пример 4.12. Создание теневую копию базы данных с номером 1

  
CREATE SHADOW 1 'g:\data\test.shd';


Пример 4.13. Создание многофайловой теневой копии

  
CREATE SHADOW 2 'g:\data\test.sh1' 
LENGTH 8000 PAGES
FILE 'g:\data\test.sh2';


См. также:  CREATE DATABASE, ALTER DATABASE, DROP SHADOW.

DROP SHADOW

Назначение: Удаление теневой копии.

Доступно в: DSQL, ESQL.

Синтаксис: 

DROP SHADOW sh_num;
                    

Таблица 4.4. Параметры оператора DROP SHADOW

Параметр Описание
sh_num

Номер теневой копии — положительное число, идентифицирующее набор файлов теневой копии.


Оператор DROP SHADOW удаляет указанную теневую копию из базы данных, с которой установлено текущее соединение. При удалении теневой копии удаляются все связанные с ней файлы, и прекращается процесс дублирования данных в эту теневой копии.

Только владелец базы данных и администраторы имеют привилегии на использование DROP SHADOW.

Примеры

Пример 4.14. Удаление теневой копии с номером 1

  
DROP SHADOW 1;


См. также:  CREATE SHADOW.

DOMAIN

Домен (Domain) — один из объектов реляционной базы данных, при создании которого можно задать некоторые характеристики, а затем использовать ссылку на домен при определении столбцов таблиц, объявлении локальных переменных, входных и выходных аргументов в модулях PSQL.

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

CREATE DOMAIN

Назначение: Создание нового домена.

Доступно в: DSQL, ESQL.

Синтаксис: 

CREATE DOMAIN name [AS] <datatype>
  [DEFAULT {literal | NULL | <context_var>}]
  [NOT NULL] [CHECK (<dom_condition>)]
  [COLLATE collation_name];

<datatype> ::= 
    {SMALLINT | INTEGER | BIGINT} [<array_dim>]
  | {FLOAT | DOUBLE PRECISION} [<array_dim>]
  | {DATE | TIME | TIMESTAMP} [<array_dim>]
  | {DECIMAL | NUMERIC} [(precision [, scale])] [<array_dim>]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [<array_dim>] [CHARACTER SET charset_name]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] 
    [(size)] [<array_dim>]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}] 
    [SEGMENT SIZE seglen] [CHARACTER SET charset_name]
  | BLOB [(seglen [, subtype_num])] 

<array_dim> ::= [[m:]n [,[m:]n ...]]

<dom_condition> ::= {
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT <val>
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<dom_condition>)
  | NOT <dom_condition>
  | <dom_condition> OR <dom_condition>
  | <dom_condition> AND <dom_condition>
}

<operator> ::= {= | < | > | <= | >= | !< | !> | <> | !=}

<val> ::= {
    VALUE
  | literal
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <datatype>)
  | (<select_one>)
  | func(<val> [, <val> ...])
}

Таблица 4.5. Параметры оператора CREATE DOMAIN

Параметр Описание
name

Имя домена. Может содержать до 31 байта.

datatype

Тип данных SQL.

literal

Литерал.

context_var

Любая контекстная переменная, тип которой совместим с типом данных  домена.

dom_condition

Условие домена.

collation_name

Имя порядка сортировки. Необходимо указывать если вы хотите чтобы порядок сортировки для домена отличался от порядка сортировки для набора символов по умолчанию этого домена.

array_dim

Размерность массива.

m

Начальный номер элемента в массиве, положительное целое число.

n

Последний номер элемента в массиве, положительное целое число.

precision

Точность. От 1 до 18.

scale

Масштаб. От 0 до 18, должно быть меньше или равно precision.

size

Максимальный размер строки в символах.

charset_name

Набор символов.

subtype_num

Номер подтипа BLOB. См. Подтипы BLOB.

subtype_name

Мнемоника подтипа BLOB. См. Подтипы BLOB.

seglen

Размер сегмента, не может превышать 65535.

select_one

Оператор SELECT выбирающий один столбец и возвращающий только одну строку.

select_list

Оператор SELECT выбирающий один столбец и возвращающий ноль и более строк.

select_expr

Оператор SELECT выбирающий несколько столбцов и возвращающий ноль и более строк.

experssion

Выражение.

genname

Имя последовательности (генератора).

func

Скалярная функция или UDF.


Оператор CREATE DOMAIN создаёт новый домен.

В качестве типа домена можно использовать любой тип данных SQL. Для любого типа данных кроме BLOB можно указать размерность массива, если домен должен быть массивом. Размерность массива указывается в квадратных скобках. Чтобы не перепутать их с символами, означающими необязательные элементы, они выделены жирным шрифтом. При указании размерности массива указываются два числа через двоеточие. Первое число означает начальный номер элемента массива, второе – конечный. Если указано только одно число, то оно означает последний номер в элементе массива, а первым номером считается 1. Если у массива два и более измерения, то они перечисляются через запятую.

Для типов CHAR, VARCHAR и BLOB с подтипом TEXT можно указать набор символов в предложении CHARACTER SET. Если набор символов не указан, то по умолчанию принимается тот набор символов, который был указан при создании базы данных. Если же при создании базы данных не был указан набор символов, то при создании домена по умолчанию принимается набор символов NONE. В этом случае данные хранятся и извлекаются, так как они были поданы. В столбец, основанный на таком домене, можно загружать данные в любой кодировке, но невозможно загрузить эти данные в столбец с другой кодировкой. Транслитерация не выполняется между исходными и конечными кодировками, что может приводить к ошибкам.

Необязательное предложение DEFAULT позволяет указать значение по умолчанию для домена. Это значение будет помещено в столбец таблицы, который ссылает на данный домен, при выполнении оператора INSERT, если значение не будет указано для этого столбца. Локальные переменные и аргументы PSQL модулей, которые ссылаются на этот домен, будут инициализированы значением по умолчанию. В качестве значения по умолчанию может быть литерал совместимый по типу, неизвестное значение NULL и контекстная переменная, тип которой совместим с типом домена.

Предложение NOT NULL запрещает столбцам и переменным, основанным на домене, присваивать значение NULL.

Необязательное предложение CHECK задаёт ограничение домена. Ограничение домена задаёт условия, которому должны удовл