Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
Обучение от Mail.Ru Group.
Онлайн-университет
для программистов с
гарантией трудоустройства.
Набор открыт!
2002 г

Краткое сравнение Oracle SQL и ANSI SQL

Вязовецков Алексей Сергеевич, a.viazovetskov@mrg.gazprom.ru

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

     

Новости мира IT:

Архив новостей

Последние комментарии:

Вышло обновление Firefox 57.0.1 (1)
Среда 06.12, 09:14

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 985 1945361
Пресс-релизы — pr@citforum.ru
Обратная связь
Информация для авторов
Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2015 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...