Коротке порівняння Oracle SQL і ANSI SQL

Метою даної статті є виявлення відмінностей між реалізацією SQL в СУБД Oracle 8 і ANSI SQL92. Зокрема робиться аналіз мови обробки даних (DML) і не розглядається мова визначення даних (DDL), також не розглядається об'єктне розширення мови SQL, призначеного для роботи з об'єктними таблицями Oracle і відсутнє в стандарті ANSI. Мова SQL СУБД Oracle 8 (далі Oracle SQL), по заявою фірми-виробника, відповідає початковому рівню ANSI SQL (entry level), однак деякі особливості реалізації його перевершують, а деякі відрізняються. Стаття робить спробу описати відмінності та доповнення і буде корисна для написання додатків більш легко переносите з СУБД Oracle на іншу СУБД, що задовольняє стандарту ANSI. Хоча й існує думка що не можна перенести додаток з одного СУБД на іншу без зміни коду, інформація даної статті допоможе це зробити у разі необхідності більш легко.


NULL значення


Почнемо порівняння з реалізації NULL-значень в ANSI SQL і Oracle SQL. Згідно ANSI всі типи даних повинні підтримувати невизначені або NULL значення. Oracle в повній мірі підтримує це правило для всіх типів, за винятком символьних. Для будь-яких символьних даних порожній рядок інтерпретується як NULL, наприклад два оператори Oracle SQL:

 INSERT INTO TEST (COL1) VALUES (NULL) і
   INSERT INTO TEST(COL1) VALUES(“”) 

повністю ідентичні і покладуть в таблицю значення NULL, а не порожні рядки.


У Oracle взагалі не можна вставити порожній рядок, так як вона буде розглядатися як NULL. Це відхилення особливо актуально при порівнянні рядків, наприклад нехай є наступна таблиця:

TEST
  COL1  COL2
1 “”   “Str1”
2 “a”  “Str2”

тоді оператор SELECT * FROM TEST WHERE COL1 = "" в Oracle буде інтерпретуватися як SELECT * FROM TEST WHERE COL = NULL і не поверне ниодного рядки, в той же час в ANSI SQL даний оператор поверне першу рядок.


Оператор SELECT * FROM TEST WHERE COL1 <> "" в Oracle буде інтерпретуватися як SELECT * FROM TEST WHERE COL <> NULL і також не поверне ниодного рядки, в ANSI SQL даний оператор поверне друга рядок.


Щоб оператори відпрацював коректно його слід замінити на:

 SELECT * FROM TEST WHERE COL1 IS NULL і
SELECT * FROM TEST WHERE COL1 IS NOT NULL.

Таким чином при порівнянні величини з порожнім рядком в Oracle слід користуватися пропозиціями IS NULL і IS NOT NULL.


Оператор UPDATE


Оператор UPDATE в Oracle повністю відповідає вимогам початкового рівня ANSI SQL. Проте є деякі додаткові можливості. Якщо відкинути можливості призначені для роботи з об'єктними таблицями ось вони:



  1. використання табличних аліасів для посилань на оновлювану таблицю в підзапитах
  2. вкладені запити в правій частині пропозиції SET на відміну від тільки виразів в ANSI SQL
  3. список оновлюваних колонок у лівій частині пропозиції SET, на відміну від однієї колонки в ANSI SQL
  4. вкладені запити в пропозиції SET або WHERE можуть посилатися на оновлювану таблицю
  5. Оператор UPDATE підтримує оновлення підзапитів

Проілюструємо ці можливості на прикладі:

1  UPDATE emp aaa 
 2 SET deptno = (SELECT deptno FROM dept WHERE loc = "Москва"),
3   SET (sal,comm)=(SELECT 1.1*AVG(sal),1.5*AVG(comm)
 4 FROM emp bbb WHERE aaa.deptno = bbb.deptno)

– 1. таблиці emp присвоюється аліас aaa для подальшої посилання на оновлювану таблицю в підзапитах


– 2. значення стовпця deptno береться з вкладені запити, що повертає одне значення


– 3. значення стовпців sal і col, береться з вкладені запити, що повертає два значення


– 4. робиться вибірка з таблиці emp, яка оновлюється цим самим оператором

 5 UPDATE emp SET comm = NULL WHERE job = "керуючий"

цей запит буде аналогічний наступного запиту:

 UPDATE (SELECT * FROM emp) SET comm = NULL WHERE job = "керуючий"

– 5. в даному прикладі Oracle буде оновлювати часове представлення SELECT * FROM emp. Після пропозиції UPDATE в круглих дужках може слідувати будь-який оператор SELECT. На основі цього оператора будується часове представлення. Якщо це подання задовольняє умовам на оновлювані подання Oracle виконає запит.


Оператор DELETE


Оператор DELETE в Oracle повністю відповідає вимогам початкового рівня ANSI SQL. Проте є деякі додаткові можливості:



  1. Ключове слово FROM не обов'язково
  2. використання табличних аліасів для посилань на оновлювану таблицю в підзапитах
  3. подзапросы в предложении WHERE могут ссылаться на обновляемую таблицу
  4. Оператор DELETE підтримує вилучення з підзапитів
1,2   DELETE emp aaa 
WHERE sal IN (SELECT AVG(sal)
3           FROM emp bbb WHERE aaa.deptno=bbb.deptno)

– 1. в пропозиції DELETE відсутня ключове слово FROM


– 2. таблиці emp присвоюється аліас aaa для подальшої посилання на оновлювану таблицю в підзапитах


– 3. робиться вибірка з таблиці emp, з якої робиться видалення цим самим оператором


– 4. Оператор:

 DELETE FROM emp WHERE job = "керуючий"

аналогічний оператору:

 DELETE FROM (SELECT * FROM emp) WHERE job = "керуючий"

Оператор INSERT


У Oracle є наступні додаткові можливості в порівнянні з ANSI SQL:


1. Оператор INSERT підтримує вкладені запити в пропозиції INTO
Оператор:

 INSERT INTO dept VALUES (50, "продукція", "Москва") 

аналогічний оператору:

  INSERT INTO (SELECT deptno, ndept, loc FROM dept)
 VALUES (50, "продукція", "Москва")

Оператор SELECT


У операторі SELECT є наступні додаткові можливості в порівнянні з ANSI SQL:



  1. NULL в списку вибірки
  2. Запит із запиту (SELECT FROM (SELECT ….))
  3. Ліва частина оператора IN може бути списком виразів на відміну від одиночного вираження в ANSI SQL
  4. Не тільки стовпець, а будь-який вираз може бути використано з оператором LIKE
  5. Будь-яке вираження, а не тільки окремий стовпець може бути використаний в операторах порівняння IS NULL і IS NOT NULL
  6. У пропозиції ORDER BY може бути використано будь-який вираз містить будь-які стовпці будь-яких таблиць пропозиції FROM на відміну від тільки імен, аліасів, номерів позицій стовпців списку вибірки
  7. У пропозиції GROUP BY може бути використано будь-який вираз містить будь-які стовпці будь-яких таблиць пропозиції FROM на відміну від тільки імен, аліасів стовпців списку вибірки
  8. Вкладені агрегатні функції MIN (MAX (col1)) (рівень вкладеності не більше 2)
  9. Оператор зовнішнього з'єднання (+)
  10. Деревоподібні запити
1    SELECT ename, job, sal, deptno, NULL
         FROM
2                (SELECT * FROM emp WHERE deptno=30)
3       WHERE  (ename,job) IN (SELECT ename,job FROM …. );
 
       SELECT ename,ename2,sal,sal2
         FROM emp
4       WHERE ename LIKE “%”//ename2//”%” AND
5                      sal+sal2IS NOT NULL 
6        ORDER BY sal+sal2


  1. в списку вибірки присутня NULL-значення
  2. в реченні FROM вказаний підзапит
  3. ліворуч від оператора IN вказаний список з двох стовпців, а праворуч – запит, який повертає два стовпці
  4. з оператором LIKE використано вираз "%"// ename2 //"%", містить посилання на стовпець
  5. З оператором порівняння IS NOT NULL використовується вираз sal + sal2
  6. Сортування здійснюється за значенням вираження sal + sal2

Зовнішні з'єднання


У ANSI SQL зовнішні об'єднання реалізовані за допомогою розширеної форми пропозиції FROM:

 SELECT * FROM tab1 FULL JOIN tab2 ON col1 = col2 – повне зовнішнє об'єднання
 SELECT * FROM tab1 LEFT JOIN tab2 ON col1 = col2 – повне ліве об'єднання
 SELECT * FROM tab1 RIGHT JOIN tab2 ON col1 = col2 – повне праве об'єднання

У Oracle не реалізовано розширену пропозицію FROM для реалізації зовнішніх з'єднань (початковий рівень ANSI SQL цього не вимагає) як це зроблено в ANSI. Однак реалізований свій власний синтаксис для отримання лівих і правих зовнішніх об'єднань. Повні зовнішні об'єднання в Oracle не реалізовані.


Для реалізації лівого зовнішнього об'єднання використовується оператор (+) в реченні WHERE, який ставитися праворуч від стовпця, по якому здійснюється з'єднання, праворуч від знака =. Аналогічно для правого об'єднання оператор (+) ставитися праворуч від стовпця зліва від знаку рівності.

 SELECT * FROM tab1 LEFT JOIN tab2 ON col1 = col2 – аналогічний запиту:
  SELECT * FROM tab,tab2  WHERE col1=col2 (+)
 
 SELECT * FROM tab1 RIGHT JOIN tab2 ON col1 = col2 – аналогічний запиту:
  SELECT * FROM tab,tab2  WHERE col1 (+)=col2 

Деревоподібні запити


У Oracle також реалізовані так звані деревоподібні запити, призначені для роботи з даними, організованими у вигляді дерева. Для реалізації дерева у вигляді таблиці в ній повинно бути додаткових два поля: id вузла і id батьківського вузла. Також повинен бути корінь (коріння). Для реалізації деревовидних запитів є два додаткові пропозиції:

 START WITH – для ідентифікації корінних рядків
 CONNECT BY – для зв'язку рядків-нащадків і рядків-предків

У пропозиції CONNECT BY реалізований також оператор PRIOR який використовується для позначення вираження-батька.


Оператор SELECT, який здійснює деревоподібний запит, може використовувати псевдостолбец LEVEL, що містить рівень вкладеності для кожного рядка. Для корінних записів LEVEL = 1, для нащадків корінних записів LEVEL = 2 і і.дз.

 SELECT LPAD ("", 2 * (LEVEL-1)) / / ename org_chart, empno, mgr, job
FROM emp
START WITH job = “PRESIDENT”
CONNECT BY PRIOR empno = mgr;
ORG_CHART    EMPNO       MGR        JOB
———— ———-  ———- ———
KING         7839                   PRESIDENT
  JONES      7566        7839       MANAGER
    SCOTT    7788        7566       ANALYST
      ADAMS  7876        7788       CLERK
    FORD     7902        7566       ANALYST
      SMITH  7369        7902       CLERK
  BLAKE      7698        7839       MANAGER
    ALLEN    7499        7698       SALESMAN
    WARD     7521        7698       SALESMAN
    MARTIN   7654        7698       SALESMAN
    TURNER   7844        7698       SALESMAN
    JAMES    7900        7698       CLERK
  CLARK      7782        7839       MANAGER
    MILLER   7934        7782       CLERK

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*