Функции в Oracle, возвращающие таблицу

    Те, кто пришел в Oracle из MSSQL, наверняка столкнулись (как и я) с массой неожиданностей.

    create function Foo1 (param1 nvarchar, param2 decimal(18,2))
    return table (
    id number,
    nn nvarchar(50)
    )
    as
    ...


    Знакомо, не правда ли? Если подобная функция прекрасно возвращала ADO Recordset из MS SQL, то в Oracle такой халявы нет. Однако получать наборы данных из функций через ADO просто необходимо, если мы хотим придерживаться грамотной структуры объектной модели.

    Создадим две таблицы — сотрудников и подразделений.

    --создание табличных пространств
    create tablespace ALEX_DATA datafile 'C:\oracle\user_data\tblsp_alexdata.dat'
    size 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
    create tablespace ALEX_INDEX datafile 'C:\oracle\user_data\tblsp_alexix.dat'
    size 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 200M;
    /
    --создание таблиц
    create table ALEX.T_EMPLOYEES(
    id number(5) not null,
    id_department number(5) not null,
    empinfo nvarchar2(50) not null
    ) tablespace ALEX_DATA;
    create table ALEX.T_DEPARTMENTS(
    id number(5) not null,
    depinfo nvarchar2(50) not null
    ) tablespace ALEX_DATA;
    /
    --создание индексов
    create index IXPK_T_EMPLOYEES on ALEX.T_EMPLOYEES(id)
    tablespace ALEX_INDEX;
    create index IXPK_T_DEPARTMENTS on ALEX.T_DEPARTMENTS(id)
    tablespace ALEX_INDEX;
    /
    --создание реляционных связей
    alter table ALEX.T_DEPARTMENTS
    add constraint PK_T_DEPARTMENTS primary key (ID) using index IXPK_T_DEPARTMENTS;
    /
    alter table ALEX.T_EMPLOYEES
    add constraint PK_T_EMPLOYEES primary key (ID) using index IXPK_T_EMPLOYEES
    add constraint FK_T_DEPARTMENTS foreign key (id_department)
    references ALEX.T_DEPARTMENTS(id);
    /
    --демо-данные
    insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
    values (1, 'Отдел кадров');
    insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
    values (2, 'Информационный отдел');
    insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
    values (3, 'Бухгалтерия');

    commit;

    insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
    values(1, 1, 'Иванов');
    insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
    values(2, 1, 'Борисов');
    insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
    values(3, 2, 'Сергеев');
    insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
    values(4, 3, 'Никитин');
    insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
    values(5, 3, 'Александров');

    commit;


    Наша цель — написать функцию, которая бы возвращала список сотрудников отдела, id которого передается в качестве параметра.
    Для начала нам нужно описать тип данных, возвращаемый таблицей.

    --тип данных строки, возвращаемой GetEmployees
    type rowGetEmployees is record(
    l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, --привязка к типу поля empinfo
    l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
    );


    Это тип данных строки. Атрибут TYPE объявляет для переменной тип, идентичный указанному полю. Создаем второй тип:

    type tblGetEmployees is table of rowGetEmployees;

    Это таблица из строк типа rowGetEmployees. Переменную этого типа будет возвращать наша функция:

    function GetEmployees
    (prm_depID number default null)
    return tblGetEmployees
    pipelined;


    Если параметр не передан, будем возвращать список всех сотрудников. Атрибут pipelined означает, что функция является конвейерной, результат возвращается клиенту немедленно при вызове директивы pipe row, поэтому оператор return необязателен. Фактически, по результирующему набору из запроса в теле функции проходит курсор, который при каждой итерации добавляет в рекордсет текущую строку.
    Поместим типы данных и функцию в пакет. На выходе имеем

    create or replace package ALEX.P_MY1 is
    --тип данных строки, возвращаемой GetEmployees
    type rowGetEmployees is record(
    l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, --привязка к типу поля empinfo
    l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
    );
    --тип данных таблицы из строк rowGetEmployees
    type tblGetEmployees is table of rowGetEmployees;
    --
    function GetEmployees
    (prm_depID number default null)
    return tblGetEmployees
    pipelined;

    end P_MY1;

    create or replace package body ALEX.P_MY1 is
    function GetEmployees
    (prm_depID number default null)
    return tblGetEmployees
    pipelined
    is
    begin
    if prm_depID is null then
    for curr in
    (
    select emp.empinfo, dep.depinfo
    from ALEX.T_DEPARTMENTS dep inner join
    ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
    ) loop
    pipe row (curr);
    end loop;
    else
    for curr in
    (
    select emp.empinfo, dep.depinfo
    from ALEX.T_DEPARTMENTS dep inner join
    ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
    where dep.id = prm_depID
    ) loop
    pipe row (curr);
    end loop;
    end if;
    end GetEmployees;

    end P_MY1;


    Осуществляем вызов:

    SQL> select * from TABLE(ALEX.P_MY1.GetEmployees);

    L_EMPINFO L_DEPINFO
    ---------------- --------------------------------
    Иванов ----------------- Отдел кадров
    Борисов ----------------- Отдел кадров
    Сергеев ----------------- Информационный отдел
    Никитин ----------------- Бухгалтерия
    Александров ----------------- Бухгалтерия

    SQL> select * from TABLE(ALEX.P_MY1.GetEmployees(1));

    L_EMPINFO L_DEPINFO
    ---------------------------------------------------
    Иванов ----------------- Отдел кадров
    Борисов ----------------- Отдел кадров
    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 33

      +5
      И это не последнее различие, с которым сталкиваются при переходе из одной СУБД в другую. Главное в этом случае — грамотно использовать возможности конкретной СУБД.
        0
        Капитан, рад что вы с нами! :)
          +1
          Далеко не последнее! Но «дьявол кроется в деталях», порой имея в «хозяйстве» обе СУБД, очень трудно бывает переключаться.

          Статья — отличная. Все очень прозрачно, понятно и по существу!

          0
          сложновато. в Firebird как то понятнее всё:

          CREATE PROCEDURE GET_EMPLOYEES(
          DEP_ID INTEGER = NULL;
          )
          RETURNS( --список возвращаемых полей
          EMPLOYEE VARCHAR(50);
          DEPARTMENT VARCHAR(50);
          ) AS
          BEGIN
          FOR SELECT E.empinfo, D.depinfo
          FROM T_DEPARTMENTS D
          JOIN T_EMPLOYESS E ON E.id_department = D.id
          WHERE :DEP_ID IS NULL OR D.ID = :DEP_ID INTO :EMPLOYEE, :DEPARTMENT DO
          SUSPEND; -- Это волшебное слово - аналог pipe row
          END

          SELECT * FROM GET_EMPLOYEES()

            0
            В MSSQL аналогично
          • UFO just landed and posted this here
              –1
              1) в принципе характерно для SQL всех разновидностей. Очевидно язык планировался для пользовательского уровня, но для рядовых юзеров он все равно сложен, а для специалистов получился слишком многословным
                –1
                Я усматриваю в этом преимущества:
                1) укрепляются мышцы пальцев;
                2) программисту подконтрольны те механизмы, которые в том же MSSQL являются черным ящиком, например sequences.
                  0
                  кстати в MSSQL так и не запилили sequences? Очень нужны. И UPDATE… RETURNING и INSERT… RETURNING

                  Привык ко всем этим ништякам на Firebird
                    0
                    В 2008 не увидел. Не представляю, как без этого можно строить многопользовательские системы, использующие поля с автоинкрементом. Приходится использовать GUID.
                      0
                      там вроде есть какой-то костыль с возвратом id вставленной строки. Впрочем этот костыль почему-то во многих СУБД встречается и считается нормальным решением.

                      В Firebird вообще решительно отказались от автоинкрементных полей в пользу SEQUENCE. По-началу кажется дикостью, а потом понимаешь удобство
                      • UFO just landed and posted this here
                          0
                          А если между insert и select @@SCOPE_IDENTITY проскочит транзакция с insert от другого пользователя?
                            0
                            я полагаю это в контексте транзакции должно работать. Иначе бессмысленно
                            0
                            это только в ХП доступно? или в обычном запросе можно?
                            • UFO just landed and posted this here
                                0
                                я чессгря больше в сторону Postgre смотрю. Привычный и знакомый мне Firebird конечно хорош в малом-среднем сегменте, но на больших проектах уже не справляется
                          0
                          1. Последовательности, наконец, появились. В SQL Server 2008 R2 Service Pack 1 ( CTP) уже точно. sqlserver-training.com/how-to-create-and-use-sequence-in-sql-server/-
                          2. Очень давно есть, OUTPUT Clause называется
                    +2
                    pipelined имеет смысл использовать когда весь результирующий набор вы сами формируете. А если необходимо вернуть результат select, то проще использовать курсор.
                      0
                      А ещё при работе с конвейерными (pipelined) функциями нужно иногда помнить про исключение NO_DATA_NEEDED :)
                      +1
                      Возвратить таблицу можно еще так:
                      CREATE OR REPLACE
                      FUNCTION admin.virtual_date_table(p_num_rows in INTEGER, p_start_date IN DATE, p_end_date IN DATE)
                      RETURN virtual_date_table_type
                      IS
                      l_data virtual_date_table_type := virtual_date_table_type();
                      l_step NUMBER := 1;
                      BEGIN
                      l_step := (p_end_date - p_start_date) / p_num_rows;
                      FOR i IN 1 .. p_num_rows
                      LOOP
                      l_data.extend;
                      l_data(l_data.count) := p_start_date + i * l_step;
                      END LOOP;

                      RETURN l_data;
                      END;
                      /


                      select * from table(virtual_date_table(5, sysdate-5, sysdate))
                        0
                        pipelined экономнее по памяти имхо.
                      • UFO just landed and posted this here
                          0
                          Нет. А что Вас смутило?
                          • UFO just landed and posted this here
                              0
                              У нас речь идет именно о получении набора данных, а не о программном интерфейсе
                              • UFO just landed and posted this here
                                  0
                                  Вы однозначно не уловили изюминки. Описанный шаблон позволяет вызывать функции выборок однотипно, меняя только название и параметры. Отсюда и логичная модель, когда методы работы с данными инкапсулируются в одном объекте. Или, по Вашему, в клиенте лучше наклепать кучу TADOQuery и TADOStoredProc'ов с жестко прописанными параметрами?
                                  • UFO just landed and posted this here
                                      0
                                      Всё верно, на то он и сервер, чтобы выдавать данные по запросу с параметрами. Выборка данных — задача сервера, и sql код должен быть на сервере. Мне кажется это идеологически правильным.
                          +2
                          Кстати, а зачем было в самой функции разделение на два курсора делать?

                          Ведь можно написать условие в курсоре
                          where (dep.id = prm_depID or prm_depID is null)

                          ну, или, развлечения ради,
                          where nvl(to_char(dep.id), '') LIKE nvl(to_char(prm_depID), '%')
                            0
                            Засчитано
                              0
                              where dep.id = NVL(prm_depID, dep.id)

                              Без лайков и to_char

                            Only users with full accounts can post comments. Log in, please.