Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6782 / 673 | Оценка: 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'));

И сколько строк он все таки вернет
Ладисьяно Писеев
Ладисьяно Писеев
Россия