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

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

< Лекция 3 || Лекция 4: 12345 || Лекция 5 >
"Системные переменные" и "псевдостолбцы"

Оба названия в заголовке заключены в кавычки в силу своей условности.

"Системные переменные" по сути представляют собой ряд иногда полезных для употребления системных функций без аргументов. Вот некоторые примеры:

Переменная Тип Описание
USER VARCHAR2 ( 256 ) Имя пользователя, выдавшего предложение SQL
UID NUMBER Номер пользователя, выдавшего предложение SQL
SYSDATE DATE Текущая дата + время суток с точностью до секунды
SYSTIMESTAMP(9-) TIMESTAMP ( 9 ) WITH TIME ZONE(9-) Текущая дата + время суток с точностью до 1/100 секунды
DBTIMEZONE(9-) и SESSIONTIMEZONE(9-) VARCHAR2 ( 6 ) и VARCHAR2 ( 75 ) Зоны времени, определенные для БД и в рамках конкретного сеанса

(9-) начиная с версии 9

Пример использования в выражении:

SELECT object_name, owner FROM all_objects WHERE owner <> USER;

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

Примеры:

Псевдостолбец Тип Описание
ROWNUM NUMBER Последовательный номер строки в результате SELECT
LEVEL NUMBER Номер уровня выдаваемой строки в предложении SELECT с использованием CONNECT BY
CONNECT_BY_ISCYCLE[10-) NUMBER В предложении SELECT с использованием CONNECT BY: 1, если потомок узла является одновременно его предком, иначе 0
CONNECT_BY_ISLEAF[10-) NUMBER В предложении SELECT с использованием CONNECT BY:1, если узел не имеет потомков
ROWID VARCHAR2 ( 256 ) Физический адрес строки или хранимого объекта
XMLDATA[9.2-) CLOB Текст документа объекта типа XMLTYPE
OBJECT_ID[10-) RAW ( 16 ) Идентификатор объекта в таблице первичных или виртуальных объектов (представлений)
OBJECT_VALUE[10-) тип объекта Системное имя для столбца в таблице первичных или виртуальных объектов (в том числе типа XMLTYPE)
ORA_ROWSCN[10-) NUMBER Порядковый номер изменения в БД (SCN), соответствующий строке таблицы или же блоку данных с этой строкой
COLUMN_VALUE[10.2-) тип объекта Тип элемента результата функций TABLE и XMLTABLE
VERSIONS_STARTSCN[10-)
VERSIONS_STARTTIME[10-)
VERSIONS_ENDSCN[10-)
VERSIONS_ENDTIME[10-)
VERSIONS_XID[10-)
VERSIONS_OPERATION[10-
Используются в "быстрых" запросах к прошлым данным (flashback queries)

[9.2-) начиная с версии 9.2

[10-) начиная с версии 10.1

[10.2-) начиная с версии 10.2

Пример использования в выражениях:

SELECT object_name, ROWNUM FROM all_objects WHERE ROWNUM <= 15;
Величины, взятые из полей строк таблицы

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

Составные выражения

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

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

  • операции над числами, строками, моментами и интервалами времени;
  • функции;
  • операторы CASE;
  • скалярные подзапросы.
Арифметические операции и числовые выражения

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

Предположим, что поле COMM в очередной строке имеет значение 25. Тогда справедливы следующие примеры:

Числовое выражение Значение
6 + 4 * 25 106
0.6E1 + 4 * COMM 106.E0
(50 / 10) * 5 25
1 + '25' 26 (неявное преобразование типа)
123d + 123 246 в формате BINARY_DOUBLE
123d + 123f 246 в формате BINARY_DOUBLE
6 + 4 * COMM 106
(6 + 4) * 25 250
NULL * 30 NULL
1f - BINARY_FLOAT_INFINITY BINARY_FLOAT_INFINITY
1d + BINARY_DOUBLE_NAN BINARY_DOUBLE_NAN

Числовая арифметика в SQL несколько отличается от школьной. Вот некоторые особенности.

  • Если значение элемента числового выражения отсутствует (то есть помечено как NULL, в обозначениях SQL), значение выражения тоже отсутствует (NULL). Это не всегда привычно. Например:
    SELECT 1 / 0 FROM dual;
    -- Ошибка !
    SELECT NULL / 0 FROM dual;
    -- OK
    
  • Тип данных результата приводится к наиболее точному из употребленных в выражении.
  • Типы элементов — участников числового выражения должны быть числовыми. Если же встречается строка текста (указанная явно или вычисленная подвыражением), она автоматически приводится к числовому значению, то есть происходит неявное преобразование типа. Фактическое преобразование осуществляется функциями TO_NUMBER, TO_BINARY_FLOAT или TO_BINARY_DOUBLE (в зависимости от контекста), например:
    SELECT '1' / 2 FROM dual;
    -- будет обработано как:
    SELECT TO_NUMBER ( '1' ) / 2 FROM dual;
    
    Соответственно, невозможность такого преобразования в конкретных случаях (строка текста несводима к числу) определяется правилами работы этих функций.

Многие специалисты полагают неправильным использование неявного преобразования типов, считая необходимым все преобразования выписывать явно, хотя бы и в ущерб краткости записи. Явное указание преобразования повышает качество кода и снижает риск возникновения ненамеренных ошибок. Однако даже если бы у разработчиков Oracle возникло желание отменить неявное преобразование типов, сделать это уже было бы невозможно, не нарушив правило обратной совместимости кода. Есть и другая причина: функции в Oracle не поддерживают всего допустимого в БД разнообразия типов. Например, отсутствуют целые функции, так что автоматическое преобразование типов становится неизбежным.

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

Простейшие выражения над строками текста (алфавитно-цифровые, или строковые выражения) можно получить из непосредственно указанных значений и единственной текстовой операцией || ("склейки").

Предположим, что поле ENAME (типа VARCHAR2 ( 10 )) в очередной строке имеет значение 'SMITH'. Тогда справедливы следующие примеры:

Строковое выражение Значение
'Работник' Вася и Петя (типа CHAR ( 8 ))
ENAME SMITH (типа VARCHAR2 ( 10 ))
USER SCOTT (VARCHAR2 ( 30 ))
'зиг' || 'заг' зигзаг
'абв' || n'абв' || 'абв' абвабвабв (в многобайтовой кодировке)
'Работник' || ENAME Работник SMITH (типа VARCHAR2 ( 18 ))
1845 || ' г.' 1845 г.

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

SELECT '->' || 001.00 || '<-' FROM dual;
-- будет обработано как:
SELECT '->' || TO_CHAR ( 001.00 ) || '<-' FROM dual;

Возникающие тонкости неявного приведения к строке (в вышеприведенном примере ведущие и незначащие нули в записи числа) следует уточнять по описанию функции TO_CHAR.

< Лекция 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'));

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