Oracle SQL, штуки и трюки

Это рассказ о некоторых возможностях и трюках в Oracle SQL, которые привлекли мое внимание за последнее время. Некоторые почти бесполезны, но показались мне любопытными, другие - часто пригождаются в работе.

1. Неявная потеря точности и округление чисел до десятков, сотен и т.д.

SQL> CREATE TABLE ay_numbers (
      n1    NUMBER,
      n2    NUMBER(5,2),
      n3    NUMBER(5),    -- округлять до целых
      n10   NUMBER(5,-1), -- округлять до десятков
      n100  NUMBER(5,-2), -- округлять до сотен
      n1000 NUMBER(5,-3)  -- округлять до тысяч
     );
Table created

SQL> INSERT INTO ay_numbers VALUES (
         12345, 123.4567, 123.4567, 12345, 12345, 12345);
1 row inserted

SQL> INSERT INTO ay_numbers VALUES (
         12345.45, 123, 123, 123.45, 123.45, 123.45);
1 row inserted

SQL> SELECT * FROM ay_numbers;

        N1      N2     N3     N10    N100   N1000
---------- ------- ------ ------- ------- -------
     12345  123.46    123   12350   12300   12000
  12345.45  123.00    123     120     100       0

SQL> DROP TABLE ay_numbers;
Table dropped

2. Дать имя ограничению целостности NOT NULL

SQL> -- Создать inline именованные ограничения

SQL> CREATE TABLE ay123 (
      id NUMBER CONSTRAINT ay123_pk PRIMARY KEY,
      di VARCHAR2(50) CONSTRAINT ay123_uk UNIQUE,
      dd DATE CONSTRAINT ay_dd_nn NOT NULL
     );
Table created

SQL> -- Создать именованное ограничение при модификации столбца

SQL> ALTER TABLE ay123 MODIFY di CONSTRAINT ay123_di_nn NOT NULL;
Table altered

3. Можно переименовать ограничение целостности

SQL> ALTER TABLE ay123 RENAME CONSTRAINT ay_dd_nn TO ay123_dd_nn;
Table altered

Это полезно для переменовывания органичений целостности, которым дала имена система, такие как SYS_C00553386 и SYS_C00557526.

SQL> DROP TABLE ay123;
Table dropped

4. UPDATE нескольких столбцов одним подзапросом

SQL> -- Таблица для эксперимента
SQL> CREATE TABLE my_users AS SELECT * FROM all_users;
Table created

SQL> -- Обновим сразу несколько столбцов данными из таблицы-источника

SQL> UPDATE my_users mu
     SET (username, created) = (
         SELECT INITCAP(username), ADD_MONTHS(created, -6) 
         FROM all_users 
         WHERE user_id = mu.user_id
     );
14 rows updated

SQL> SELECT * FROM my_users WHERE rownum <= 3;

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------
Xs$Null                        2147483638 27.02.2011
Ay                                     50 15.12.2012
Apex_040200                            49 12.08.2012

Обновить несколько столбцов данными из таблицы-источника можно и иначе, используя либо MERGE, либо UPDATE подзапроса, связывающего целевую таблицу и таблицу-источник.

SQL> DROP TABLE my_users;
Table dropped

5. Два уровня агрегирования

Агрегатная функция может быть вложена в другую агрегатную функцию, как максимум, один раз. Что и создает двухуровневое агрегирование.

SQL> -- среднее количество таблиц, принадлежащих пользователям

SQL> SELECT AVG(COUNT(*)) FROM all_tables GROUP BY owner;
AVG(COUNT(*))
-------------
193.454545454

SQL> -- тот же результат можно получить с помощью подзапроса

SQL> SELECT AVG(cnt) FROM (SELECT COUNT(*) cnt FROM all_tables GROUP BY owner);
     AVG(CNT)
-------------
193.454545454

Один уровень агрегирования не требует GROUP BY, но второй - невозможен без GROUP BY:

SQL> SELECT COUNT(*) FROM all_tables;
  COUNT(*)
----------
      2128

SQL> SELECT SUM(COUNT(*)) FROM all_tables;
SELECT SUM(COUNT(*)) FROM all_tables
ORA-00978: nested group function without GROUP BY

6. REGEXP_LIKE вместо нескольких LIKE

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

SQL> SELECT * FROM all_users 
     WHERE username LIKE 'A%' OR username LIKE 'B%' OR username LIKE 'C%';

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------
CTXSYS                                 32 27.08.2011
ANONYMOUS                              35 27.08.2011
APEX_PUBLIC_USER                       45 27.08.2011
APEX_040000                            47 27.08.2011
APEX_040200                            49 12.02.2013
AY                                     50 15.06.2013
 
6 rows selected

Того же результата можно добиться короче:

SQL> SELECT * FROM all_users WHERE REGEXP_LIKE(username, '^(A|B|C)');

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------
CTXSYS                                 32 27.08.2011
ANONYMOUS                              35 27.08.2011
APEX_PUBLIC_USER                       45 27.08.2011
APEX_040000                            47 27.08.2011
APEX_040200                            49 12.02.2013
AY                                     50 15.06.2013
 
6 rows selected

7. Генератор строк

Эта возможность доступна, начиная с версии Oracle 10g.

SQL> SELECT LEVEL FROM dual CONNECT BY LEVEL < 6;

     LEVEL
----------
         1
         2
         3
         4
         5

В запросе отсутствует ограничение START WITH, поскольку в dual всего одна строка. Отсутствие PRIOR в выражении CONNECT BY приводит к тому, что каждая строка уровня n соединяется с каждой строкой уровня n+1. Смотрите:

SQL> SELECT LEVEL FROM (SELECT * from dual UNION ALL SELECT * FROM dual) CONNECT BY LEVEL < 3;

     LEVEL
----------
         1
         2
         2
         1
         2
         2

6 rows selected

Генерировать можно не только числовые последовательности:

SQL> SELECT trunc(sysdate-7) + LEVEL, CHR(64 + LEVEL) FROM dual CONNECT BY LEVEL <= 7;

TRUNC(SYSDATE-7)+LEVEL CHR(64+LEVEL)
---------------------- -------------
12.12.2013             A
13.12.2013             B
14.12.2013             C
15.12.2013             D
16.12.2013             E
17.12.2013             F
18.12.2013             G
 
7 rows selected