Запрос выборки

Функция

Определение запроса выборки данных.

Спецификация

   
< общее табличное выражение >::=
< имя табличного выражения >::=
< время >::=
целочисленное положительное значение
< приоритет >::=
целочисленное положительное значение

Синтаксические правила

  1. Если конструкция WAIT (NOWAIT) не задана, по умолчанию используется WAIT.

  2. < Имена столбцов > и/или < FROM-спецификация > < select-запроса выборки > или его подзапросов могут содержать ссылки к < общим табличным выражениям >.

    with "Цвета авто" ("Цвет") as (select color from auto) select "Цвет", count(*) as "Количество" from "Цвета авто" group by "Цвет";
    Цвет       Количество
     ----       ----------
    |BLACK     |        262|
    |BLUE      |        108|
    …
  3. < Имена столбцов > и/или < FROM-спецификация > < общего табличного выражения > или его подзапросов могут содержать ссылки к определенным ранее < общим табличным выражениям >.

    WITH t1 AS (select color, count(*) as cnt1 from auto group by color), 
         t2 AS (select cnt1 as cnt2 from t1 where color='BLUE')
    SELECT color
      FROM t1, t2 WHERE cnt1<  >cnt2 and color not like'B%';
    
    COLOR
     -----
    |GREEN     |
    |GREY      |
    |RED       |
    |WHITE     |
    |YELLOW    |
  4. Ссылки к < общим табличным выражениям >, определяемым позже, и рекурсивные ссылки не допускаются.

  5. < Имя табличного выражения > должно отличаться от любого другого < имени табличного выражения >, определенного в том же предложении WITH, но оно может совпадать с именем базовой таблицы, именем представления или синонимом.

  6. Повторение < имен столбцов > в < общем табличном выражении > не допускается.

  7. Количество заданных < имен столбцов > должно совпадать с количеством столбцов в результирующей выборке соответствующего < подзапроса >. Список < имен столбцов > необязателен только в том случае, если все результирующие столбцы в соответствующем < подзапросе > имеют уникальные имена.

  8. < Общее табличное выражение > можно использовать в команде CREATE VIEW, при этом одно < общее табличное выражение > не должно включать в себя другое < общее табличное выражение >.

    create or replace view t(Color_Auto, Count_Auto)
    as with "Цвета авто" ("Цвет")
    as (select color from auto)
    select "Цвет", count(*) as "Количество"  from "Цвета авто" group by "Цвет";
  9. Несколько < общих табличных выражений > могут объединяться в < комбинированный запрос >. При этом < общие табличные выражения > могут ссылаться на самих на себя, а также на < общие табличные выражения >, определенные до этого в том же предложении WITH. Ссылки на определяемые далее < общие табличные выражения > недопустимы. Допустимые схемы < комбинированных запросов > с использованием < общих табличных выражений >:

    а)
      with S1, S2, … SN
    select from S1 …
     union | except | intersect
    select from S2 …
     union | except | intersect
    …
    select from SN …
     union | except | intersect
    б)
      with S1
    select from S1 …
     union | except | intersect
      with S2
    select from S2 …
     union | except | intersect
    …
      with   SN
    select from SN …
    
    

    Например,
      WITH "Список моделей"("Модель") as (select distinct model from auto limit 5),
           "Список производителей"("Производитель") as (select distinct make from auto limit 4),
           "Окраска авто"("Цвет") as (select distinct color from auto limit 3)
    SELECT "Модель", count(*) from "Список моделей" where "Модель" like '2%'
     GROUP BY "Модель"
     UNION
    SELECT "Производитель", count(*) from "Список производителей" group by "Производитель";
     UNION
    SELECT "Цвет", count(*) from "Окраска авто";

  10. < Общие табличные выражения > могут содержать вложенные < общие табличные выражения >:

    with s(model) as
    (with k as (select model from auto where color='RED' group by model)
    select count(*) from k)
    select count(*) from s group by model;
    
    |          1|

  11. Конструкция WITH допускает использование параметров:

    …
    with q as
    (select * from s where status = ?)

  12. Параметрами < скалярной процедуры > может быть SELECT-запрос, возвращающий одно значение.

Общие правила

  1. Максимальное число столбцов в < запросе выборки > равно 250.

  2. Каждый из < подзапросов > может возвращать до 250 столбцов. При подсчете числа обработанных столбцов < запроса выборки > столбцы основного запроса и подзапросов не суммируются.

  3. < Общее табличное выражение > можно представить себе как временный результирующий набор, определенный на время выполнения < запроса выборки >. Временные результирующие наборы не сохраняются в БД в виде объектов, время их жизни ограничено продолжительностью обработки запроса.

  4. Конструкции с < общим табличным выражением > предназначена для:

    • замены представлений в тех случаях, когда создание и использование представления не оправдано, то есть тогда, когда нет необходимости сохранять в БД текст запроса представления;

      Эти конструкции эквивалентны:
      Подсчитать количество владельцев автомобилей, фамилия которых начинается на
       букву P, владеющих моделью, название которой начинается с GRAN и выпущенных
       компанией FORD.
      
      WITH t as (select model
                   from auto, person
                  where auto.personid=person.personid  and make='FORD'
                    and firstnam like 'P%')
      SELECT count(*) FROM t WHERE model LIKE 'GRAN%';
      
      select count (*)
        from auto, person
       where auto.personid=person.personid 
         and make='FORD'
         and firstnam like 'P%'
         and model like 'GRAN%';
      
      create or replace view  Ford_P as
      (select model
        from auto, person
       where auto.personid=person.personid 
         and make='FORD'
         and firstnam like 'P%');
      select count(*) from Ford_P where model like 'GRAN%';

    • многократных ссылок на одну и ту же выборку данных в одном и том же запросе.

  5. При обработке < общего табличного выражения > между подсказками, ссылающимися на < общее табличное выражение >, может быть конфликт с другими подсказками, обнаруживаемыми, когда < общие табличные выражения > обращаются к их базовым таблицам так же, как подсказки обращаются к представлениям в запросах.

  6. Опция WAIT заставляет СУБД ЛИНТЕР ожидать разблокирования данных, необходимых для выполнения < запроса выборки >.

  7. Опция NOWAIT заставляет СУБД ЛИНТЕР не обрабатывать < запрос выборки > и выдавать соответствующий код завершения, если необходимые для обработки запроса данные заблокированы. В этом случае после некоторой паузы можно попытаться повторить выполнение запроса. Количество таких попыток определяется пользовательским приложением.

  8. Параметр < время > задает максимально допустимую продолжительность кванта обработки запроса (от 1 до 65535 сек.). Если по каким-либо причинам квант обработки запроса превысил указанное значение, обработка запроса прекращается с выдачей соответствующего кода завершения.

  9. Конструкция WITH PRIORITY < приоритет > устанавливает заданный приоритет выполняемому запросу (в диапазоне от 0 до 255). Если задать приоритет больше текущего приоритета пользователя, от имени которого подается запрос, то выдается код завершения 1022 «Нарушение привилегий».

  10. Конструкция WITH PRIORITY назначает приоритет только тому запросу, в котором она указана. На приоритет любых последующих запросов она не влияет.

  11. Независимо от значения RECORD SIZE LIMIT, максимальная строка записи выборки данных ограничена 65535 байтами.

Примеры

  1. {select count(*) from auto, person
    where auto.personid=person.personid and make='FORD');
  2. Пусть есть две таблицы:
    T1 – данные о поступивших в магазин продуктах;
    product_id – идентификатор продукта;
    part_id – номер (идентификатор) поставленной партии продукта;
    income – дата поступления партии продукта.
     
    T2 – справочник продуктов;
    product_id – идентификатор продукта;
    name – название продукта;
    max_term – максимальный срок реализации продукта.
    
    -- Создаем и заполняем таблицы
    create or replace table T1
    (
      product_id int, -- идентификатор продукта
      part_id int,    -- номер (идентификатор) поставленной партии продукта
      income date     -- дата поступления партии продукта (т.е. записей с одним
                      -- и тем же part_id может быть несколько) 
    );
    create or replace table T2
    (
      product_id int,   -- идентификатор продукта
      name varchar(50), -- название продукта
      max_term int      -- максимальный срок реализации продукта в днях
    );
    
    insert into t1 values (1,1,'05.09.2013');
    insert into t1 values (1,2,'10.09.2013');
    insert into t1 values (1,3,'12.09.2013');
    insert into t1 values (2,1,'15.08.2013');
    insert into t1 values (2,2,'01.09.2013');
    insert into t1 values (3,24,'20.08.2013');
    insert into t1 values (3,35,'02.09.2013');
    insert into t1 values (4,7,'15.08.2013');
    insert into t1 values (4,8,'01.09.2013');
    
    insert into t2 values (1, 'Молоко', 3);
    insert into t2 values (2, 'Колбаса', 14);
    insert into t2 values (3, 'Сыр', 35);
    insert into t2 values (4, 'Рыба', 10);
    
    а)
    -- Получить общее количество партий продуктов, в которых на текущую дату
    -- могут встретиться просроченные продукты
    
    WITH obsolete as
    (select t2.name, t1.part_id
       from t1,t2
      where t1.product_id = t2.product_id and sysdate  > t1.income + t2.max_term)
    select to_char(sysdate,'dd.mm.yyyy'), count(*) from obsolete;
    |13.09.2013|          5|
    
    
    б)
    -- Получить количество разных наименований просроченных продуктов
    
    WITH obsolete as
    (select t2.name, t1.part_id
       from t1,t2
      where t1.product_id = t2.product_id and sysdate  > t1.income + t2.max_term)
    select to_char(sysdate,'dd.mm.yyyy'), count(distinct name) from obsolete;
    |13.09.2013|          3|
    
    в)
    -- Получить названия продуктов с максимальным числом просроченных партий и
    -- количество просроченных партий.
    
    WITH
    obsolete as
    (select t2.name, t1.part_id from t1,t2 where t1.product_id = t2.product_id and sysdate  > income + max_term),
    obs_count as (select to_char(sysdate, 'dd.mm.yyyy'), name, count(*) cnt from obsolete group by name)
    select * from obs_count where cnt = (select max(cnt) from obs_count);
    
                NAME             CNT
      ---       ----             ---
    |13.09.2013|Молоко          |          2|
    |13.09.2013|Рыба            |          2|