Выражения в Oracle SQL

Общие элементы запросов и предложений DML: выражения

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

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

Исходные значения

Исходные значения лежат в основе любого выражения в составе оператора DML или запроса на SQL. Это такие подвыражения, которые при анализе не разложимы далее на другие подвыражения. Исходные значения могут быть сообщены выражению:

• явно,
• через "системные переменные",
• именем поля строки таблицы.

Явно обозначенные величины ("литералы")

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

Нелишне помнить, что одни и те же величины часто могут быть обозначены по-разному, например, 1 и +1 и так далее (в известном "треугольнике Фреге" предмет — обозначение — смысл literal value скорее "обозначение"). Для разных видов данных в выражениях предусмотрены разные способы обозначения.

Числовые величины

Примеры обозначения целых чисел:

38, +12, -3404

Примеры обозначения "десятичных" чисел (decimal), иначе чисел с возможной дробной частью, записанных в десятичной системе счисления:

342.16, 49, -16, 0.83459

Отделение целой части от дробной осуществляется с помощью десятичной точки или же запятой, в зависимости от установок местности ("языковых"). Русский формат записи чисел (в Oracle устанавливается параметром сеанса NLS_NUMERIC_CHARACTERS как ', ') унаследовал исторически французскую традицию употребления в качестве разделителя запятую в отличие от английской точки, унаследованной Северной Америкой как местом разработки СУБД Oracle. Если не обращать внимания на эту мелочь, могут возникать ошибки вывода числовых данных из БД.

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

49, 18.47, -34e2, 0.16e4, 4e-3, 4e-3, 
123f (явное указание BINARY_FLOAT), 
-123.25d (явное указание BINARY_DOUBLE)

Регистр букв, как обычно, не имеет значения.

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

Пример для всех версий:

SQL> SELECT 1000, DUMP ( 1000 ) FROM dual;
      1000 DUMP(1000)          
---------- ------------------- 
      1000 Typ=2 Len=2: 194,11 
Пример для версий 10+:
SQL> SELECT 123f, DUMP ( 123f ), DUMP ( 123d ) FROM dual;
      123F DUMP(123F)                 DUMP(123D)
---------- -------------------------- -----------------------------------
 1.23E+002 Typ=100 Len=4: 194,246,0,0 Typ=101 Len=8: 192,94,192,0,0,0,0,0

В качестве упражнения предлагается выполнить другие проверки, например, при записи числа как 1000.1 и 1.0e+3.

Строки текста

Примеры указания строк:

'Collins', '''tis' (кавычки в кавычках), '!?-@ ', '', '''', '1234'
n'Многобайтовая кодировка; по правилам ANSI можно писать и N, и n', 
n'Тоже многобайтовая кодировка'
q'[Строка без 'искажений'. Возможно начиная с версии 10]',  
q'|ограничивающий символ может быть практически любой|'
u'строка в Unicode начиная с версии 10'

Функция DUMP помогает понять, как воспринимает СУБД по-разному оформленные строки. Последние два предложения SELECT работают начиная с версии 10:

SQL> SELECT 'a''bc', DUMP ( 'a''bc' ) FROM dual;
'A'' DUMP('A''BC')
---- -------------------------
a'bc Typ=96 Len=4: 97,39,98,99
SQL> SELECT 'a''bc', DUMP ( q'wa'bcw' ) FROM dual;
'A'' DUMP(Q'WA'BCW')
---- -------------------------
a'bc Typ=96 Len=4: 97,39,98,99
SQL> SELECT 'a''bc', DUMP ( nq'wa'bcw' ) FROM dual;
'A'' DUMP(NQ'WA'BCW')
---- ---------------------------------
a'bc Typ=96 Len=8: 0,97,0,39,0,98,0,99

Моменты и интервалы времени

Начиная с версии 9 в Oracle поддерживается система указаний моментов и интервалов времени, принятая для SQL комитетами ANSI/ISO по стандартизации SQL. Использованный в примерах ниже формат указания самого значения жестко регламентирован ANSI/ISO. Это касается и типа DATE, для которого Oracle принимает формулировку из стандарта, но по-своему раскрывает ее содержание.

Для обозначения моментов времени используются конструкции DATE, TIME и TIMESTAMP.

Примеры:

DATE '2003-04-14' 
  (14 апреля 2003 00:00:00; имеет тип DATE, а временная компонента обнулена)
TIME '12:30:45' 
  (12.30.45.000000000 пополудни; тип не играет самостоятельной роли в БД и может использоваться только в выражении)
TIMESTAMP '2003-04-14 15:16:17' 
  (14 апреля 2003 15:16:17.000000000; этот и следующий пример имеет тип TIMESTAMP ( 9 ) )
TIMESTAMP '2003-04-14 15:16:17.88' 
  (14 апреля 2003 15:16:17.880000000)
TIMESTAMP '1997-01-31 09:26:56.66 +02:00' 
  (31 января 1997 09:26:56.660000000 во второй временной зоне; имеет тип TIMESTAMP ( 9 ) WITH TIME ZONE)
TIMESTAMP '1997-01-31 09:26:56.66 Europe/Moscow' 
  (31 января 1997 09:26:56.660000000 во временной зоне г. Москвы; имеет тип TIMESTAMP ( 9 ) WITH TIME ZONE)

Для обозначения интервалов времени используются конструкции INTERVAL, допускающие указание подынтервалов "грубого" и "точного" диапазона интервалов и, в дополнение к синтаксису ANSI/ISO, указание точности. Деление на "грубые" и "точные" диапазоны условно. Примеры формулирования "точных" интервалов (диапазон от дней до долей секунд и подынтервалы):

INTERVAL '5 04:03:02.01' DAY TO SECOND 
  (5 дней, 4 часа, 3 минуты, 2,01 секунды; имеет тип INTERVAL DAY ( 2 ) TO SECOND ( 6 )),
INTERVAL '04:03' HOUR TO MINUTE 
  (0 дней, 4 часа, 3 минуты; этот и следующий пример имеет тип INTERVAL DAY ( 2 ) TO SECOND ( 0 )),
INTERVAL '03' MINUTE 
  (0 дней, 0 часов, 3 минуты)

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

Примеры формулирования "грубых" интервалов (диапазон из лет и месяцев и два подынтервала):

INTERVAL '04-5' YEAR TO MONTH 
  (плюс 4 года и 5 месяцев; этот и два следующих примера имеют тип INTERVAL YEAR ( 2 ) TO MONTH),
INTERVAL '-4' YEAR 
  (минус 4 года),
INTERVAL '5' MONTH 
  (плюс 5 месяцев)

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

INTERVAL ' + 4- 0005 ' YEAR TO MONTH