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

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

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

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

(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, помогает справиться с другими неоднозначностями, возникающими при работе со временем.