Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7046 / 737 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 4:

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

< Лекция 3 || Лекция 4: 12345 || Лекция 5 >
Аннотация: Рассматривается построение выражений для использования в операциях выборки данных SELECT и изменения данных INSERT, UPDATE и DELETE.

Общие элементы запросов и предложений 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
< Лекция 3 || Лекция 4: 12345 || Лекция 5 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001