Опубликован: 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 >

Операции над типами "момент" и "интервал времени"

Два вида временных типов — для моментов и для интервалов — имеют свою "временную арифметику", основанную на сложении и вычитании и позволяющую строить простые выражения:

Выражение для времени Значение
DATE '1990-08-28' + 3 31 августа 1990 года, 00:00:00
3 + DATE '1990-08-28' 31 августа 1990 года, 00:00:00
DATE '1988-12-04' - 5 29 ноября 1988 года, 00:00:00
SYSDATE + 1 / 24 [сейчас](*) плюс час
SYSTIMESTAMP(9-) - INTERVAL '3' HOUR(9-) [сейчас](*) минус три часа (типа TIMESTAMP(9) WITH TIME ZONE)
DATE '2005-1-1' - SYSDATE число нецелых суток до/после Нового 2005 года (типа NUMBER)
TIMESTAMP '2005-1-1 0:0:0'(9-) - SYSTIMESTAMP(9-) время до/после Нового 2005 года (типа INTERVAL DAY(9) TO SECOND(9))
SYSDATE - INTERVAL '3' HOUR(9-) [сейчас](*) минус три часа (типа DATE, т. е. с неявным преобразованием типа)
SYSTIMESTAMP(9-) - 3 / 24 [сейчас](*) минус три часа (типа DATE, т. е. с неявным преобразованием типа)
DATE '2005-1-1' - SYSTIMESTAMP(9-) время до/после Нового 2005 года (типа INTERVAL DAY(9) TO SECOND(9) , т. е. с неявным преобразованием типа)

(*) время компьютера с СУБД

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

Пример употребления:

SELECT projno FROM proj WHERE bdate > SYSDATE + 1;

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

DATE '2009-01-28' + INTERVAL '1' MONTH
DATE '2009-01-29' + INTERVAL '1' MONTH
DATE '2008-01-29' + INTERVAL '1' MONTH
DATE '2009-01-30' + INTERVAL '1' MONTH

Формат выдачи момента времени можно устанавливать для БД, СУБД и отдельного сеанса. Например, применительно к типу DATE:

SQL> SELECT value FROM nls_session_parameters
  2> WHERE  parameter = 'NLS_DATE_FORMAT';
VALUE
----------------------------------------
DD-MON-RR
SQL> SELECT SYSDATE FROM dual;
SYSDATE
---------
14-SEP-09
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-------------------
2009-09-14 00:36:11
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'Day HH:MI:SS am';
Session altered.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
---------------------
Monday    12:36:11 am

Непосредственно в выражениях формат указывается маской в функциях TO_DATE, TO_TIMESTAMP, TO_CHAR и подобных; обширный перечень способов указать маску приводится в документации по Oracle.

Функции

Средством построения выражений в SQL могут служить функции. В Oracle функции могут быть разных категорий: скалярными, обобщающими (агрегирующими), аналитическими, табличными, общесистемными (встроенными) или написанными пользователями. Далее рассматриваются общесистемные функции, доступные всем пользователям СУБД, разных видов. Полный официальный их перечень из более 200 наименований приведен в документации по Oracle. Часть из них введена в Oracle вслед за стандартом SQL (правда, не всегда с пунктуальным соблюдением правил оформления), а часть — нет.

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

Ниже приводятся примеры некоторых характерных групп функций.

Функции для строк текста

Используются для работы со строками типов VARCHAR2 и CHAR. Примеры функций:

  • LENGTH — вычисление длины строки;
  • LOWER, UPPER — понижение и повышение регистра букв;
  • INITCAP — повышение регистра первых букв в словах и понижение остальных;
  • RTRIM, LTRIM — убирание одинаковых символов в конце либо в начале (по умолчанию пробелов);
  • RPAD, LPAD — дополнение строки текста одинаковыми символами справа либо слева (по умолчанию — пробелами);
  • INSTR, SUBSTR — поиск вхождения подстроки и замена.

Примеры действия функций на строки:

SQL> SELECT ename, LOWER ( ename ), INITCAP ( ename ) FROM emp;
ENAME      LOWER(ENAM INITCAP(EN
---------- ---------- ----------
SMITH      smith      Smith
ALLEN      allen      Allen
...
SQL> SELECT LPAD ( ename, 7, '*' ), RTRIM ( ename, 'ITH' ) FROM emp;
LPAD(EN RTRIM(ENAM
------- ----------
**SMITH SM
**ALLEN ALLEN
***WARD WARD
...

Большей частью все эти функции в Oracle определены в стандарте SQL, а значит, будут доступны и будут исполняться тем же порядком в СУБД ряда прочих типов.

Функции преобразования типов данных

В соответствии со стандартом SQL-92 в Oracle есть общая функция преобразования типов CAST.

Примеры:

SELECT CAST ( '0123' AS NUMBER ( 5 ) ) FROM dual;
SELECT CAST ( SYSDATE AS VARCHAR2 ( 20 ) ) FROM dual;

Она способна выполнять большинство преобразований, имеющих смысл. Однако в Oracle эта функция используется нечасто (за исключением преобразования типов коллекций, объяснение которых см. ниже) ввиду наличия собственных, более развитых ее замен:

TO_CHAR
TO_CLOB
TO_NUMBER
TO_BINARY_FLOAT/DOUBLE 
TO_DATE
TO_TIMESTAMP
TO_YMINTERVAL, TO_DSINTERVAL
NUMTOYMINTERVAL, NUMTODSINTERVAL
других.

Большинство из них имеет имена, начинающиеся с 'TO_', однако Oracle не пунктуальна в соблюдении этого неформального правила.

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

SELECT TO_TIMESTAMP ( '10-APR-56' ) FROM dual;
SELECT
 TO_TIMESTAMP (
   '10-Апрель-56'
 , 'DD-MONTH-RR'
 , 'NLS_DATE_LANGUAGE=RUSSIAN' 
) 
FROM dual;
SELECT TO_CHAR ( SYSDATE, 'Day HH24:MI:SS' ) FROM dual;

Использование маски позволяет в частности поставить под контроль выдачу номера недели. В США, где разрабатывается СУБД Oracle, неделя начинается с воскресения, а недели отсчитываются с первого дня года. По правилам ISO это не так. Правильно выбранная маска способна заставить СУБД выдать желаемое. Вот пояснительная пара запросов со сравнительной выдачей:

COLUMN "Неделя в США"  FORMAT A13
COLUMN "Неделя по ISO" FORMAT A13
COLUMN "Название дня"  FORMAT A13
SELECT
  TO_CHAR ( DATE '2010-1-1', 'ww' )  "Неделя в США"
, TO_CHAR ( DATE '2010-1-1', 'iw' )  "Неделя по ISO"
, TO_CHAR ( DATE '2010-1-1', 'day' ) "Название дня"
FROM dual
;
SELECT
  TO_CHAR ( DATE '2010-1-4', 'ww' )  "Неделя в США"
, TO_CHAR ( DATE '2010-1-4', 'iw' )  "Неделя по ISO"
, TO_CHAR ( DATE '2010-1-5', 'day' ) "Название дня"
FROM dual
;

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

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