Целью данной статьи является выявление различий между реализацией 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 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)

5 UPDATE emp SET comm=NULL WHERE job=управляющий

этот запрос будет аналогичен следующему запросу:

UPDATE (SELECT * FROM emp )SET comm=NULL WHERE job=управляющий

Оператор DELETE

Оператор DELETE в Oracle полностью соответствует требованиям начального уровня ANSI SQL. Однако имеются некоторые дополнительные возможности:

1,2 DELETE emp aaa 
WHERE sal IN (SELECT AVG(sal)
3 FROM emp bbb WHERE aaa.deptno=bbb.deptno)

 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 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

 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 - полное правое объединение

 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 

 START WITH - для идентификации коренных строк
 CONNECT BY - для связи строк-потомков и строк-предков

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