04.01.2018 08:36
all-servs 27 3800 Источник
В промышленных системах часто требуется выполнить преобразования данных с использованием pl/sql кода с возможностью обращения к этим данным в sql запросе. Для этого в oracle используются табличные функции.

Табличные функции – это функции возвращающие данные в виде коллекции, к которой мы можем обратиться в секции from запроса, как если бы эта коллекция была реляционной таблицей. Преобразование коллекции в реляционный набор данных осуществляется с помощью функции table().

Однако такие функции имеют один недостаток, так как в них сначала полностью наполняется коллекция, а только потом эта коллекция возвращается в вызывающую обработку. Каждая такая коллекция храниться в памяти и в высоконагруженных системах это может стать проблемой. Так же в вызывающей обработке происходит простой на время наполнения коллекции. Решить данный недостаток призваны табличные конвейерные функции.

Конвейерными функциями называются табличные функции, которые возвращают данные в виде коллекции, но делают это асинхронно, то есть получена одна запись коллекции и сразу же эта запись отдается в вызывающий код в котором она сразу же обрабатывается. В этом случае память сохраняется, простой по времени ликвидируется.

Рассмотрим, как создаются такие функции. В данном примере будет использована учебная схема hr и три ее таблицы: employees, departments, locations.

• employees — таблица сотрудников.
• departments — таблица отделов.
• locations — таблица географического местонахождения.

Данная схема и таблицы есть в каждой базовой сборке oracle по умолчанию.

В схеме hr я создам пакет test, в нем будет реализован наш код. Создаваемая функция будет возвращать данные по сотрудникам в конкретном отделе. Для этого в спецификации пакета нужно описать тип возвращаемых данных:

create or replace package hr.test as

type t_employee is record
 (
  employee_id integer,
  first_name varchar2(50),
  last_name varchar2(50), 
  email varchar2(50),   
  phone_number varchar2(12),
  salary number(8,2),
  salary_recom number(8,2),
  department_id integer,
  department_name varchar2(100),
  city varchar2(50)
 );

type t_employees_table is table of t_employee;

end;

• employee_id – ид сотрудника
• first_name – имя
• last_name – фамилия
• email – электронный адрес
• phone_number – телефон
• salary – зарплата
• salary_recom – рекомендуемая зарплата
• department_id – ид отдела
• department_name — наименование отдела
• city – город

Далее опишем саму функцию:

function get_employees_dep(p_department_id integer) return t_employees_table pipelined;

Функция принимает на вход ид отдела и возвращает коллекцию созданного нами типа t_employees_table. Ключевое слово pipelined делает эту функцию конвейерной. В целом спецификация пакета следующая:

create or replace package hr.test as
type t_employee is record
 (
  employee_id integer,
  first_name varchar2(50),
  last_name varchar2(50), 
  email varchar2(50),   
  phone_number varchar2(12),
  salary number(8,2),
  salary_recom number(8,2),
  department_id integer,
  department_name varchar2(100),
  city varchar2(50)
 );

type t_employees_table is table of t_employee;

function get_employees_dep(p_department_id integer) return t_employees_table pipelined;
end;

Рассмотрим тело пакета, в нем описано тело функции get_employees_dep:

create or replace package body hr.test as
function get_employees_dep(p_department_id integer) return t_employees_table pipelined as 
begin
  
  for rec in
  (
    select 
      emps.employee_id,
      emps.first_name,
      emps.last_name, 
      emps.email,   
      emps.phone_number,
      emps.salary,
      0 as salary_recom,
      dep.department_id,
      dep.department_name,
      loc.city
    from 
      hr.employees emps
      join hr.departments dep on emps.department_id = dep.department_id
      join hr.locations loc on dep.location_id = loc.location_id
    where
      dep.department_id = p_department_id  
  )
  loop
   
   if (rec.salary >= 8000) then
     rec.salary_recom := rec.salary;     
   else
     rec.salary_recom := 10000;
   end if;  
   
   pipe row (rec);  
  end loop;
end;
end;

В функции мы получаем набор данных по сотрудникам конкретного отдела, каждую строчку этого набора анализируем на предмет того, что если зарплата сотрудника меньше 8 000, то рекомендуемую зарплату устанавливаем в значение 10 000, дальше каждая строчка не дожидаясь окончания наполнения всей коллекции, отдается в вызывающую обработку. Обратите внимание, что в теле функции отсутствует ключевое слово return и присутствует pipe row (rec).

Осталось вызвать созданную функцию в pl/sql блоке:

declare 
  v_department_id integer :=100;
begin   
   for rec in (
     select 
       *  
     from 
       table (hr.test.get_employees_dep(v_department_id)) emps   
   )loop
      -- какой то код
   end loop;  
end;

Вот так вот просто с помощью конвейерных табличных функций мы получаем возможность сделать выборку, наполненную сколько угодно сложной логикой за счет использования pl/sql кода и не просесть в плане производительности, а в ряде случаем даже ее увеличить.

Комментарии (27)


  1. mad_nazgul
    05.01.2018 06:07
    #10600030

    Лучше бы «with» реализовали, как в PostgreSQL, тогда табличные функции особо не нужны были бы. :-)


    1. all-servs Автор
      05.01.2018 08:28
      #10600092

      with это все равно sql запрос, я когда что то делаю стараюсь сначала на sql многое отдать, но бывают случаи когда логика очень сложная и ее проще реализовать за счет процедурного языка, который в oracle очень силен. Некоторые процедуры получаются очень большими, если их отдать на откуп в sql во первых запрос станет очень громоздким и его станет тяжело дописывать во вторых в таких запросах будет много конструкций or из за чего сам запрос начнет тормозить.


      1. mad_nazgul
        05.01.2018 09:03
        #10600124

        Не истины ради, а холивара для…
        По мне использование ХП это плохо, тем более для «сложной логики», для этого есть другие ЯП, где это делать по моему удобнее.
        Конечно PL/SQL это мощный ЯП, но он застрял в 80 годах прошлого века.
        Плюс если этой «логики» много и она размазана по ХП, пакетам, функциям в куче мест, то сопровождать этого «фрянкинпуха» то еще удовольствие.
        «with» как в PostgreSQL по мне это разумный компромисс для создания сложных запросов, без тотального привлечения ХП.


        1. all-servs Автор
          05.01.2018 09:25
          #10600140

          У нас один модуль на работе был, который возвращал выборку для документа, логика в нем была сложная и требования к нему постоянно нарастали как снежный ком. Его сколько могли тянули на sql, в итоге во первых запрос стал очень тяжелый для доработки, во вторых из за того что в нем было миллион конструкций or он начал просто висеть на большом объеме данных. В данном случае его было просто необходимо перенести в функцию, в которой описать именно очень сложную логику процедурным языком было в сто раз проще и скорость работы стала нормальной.


          1. mad_nazgul
            05.01.2018 09:31
            #10600142

            Понятно, что некоторые задачи на императивном ЯП решаются проще, чем на декларативном SQL.
            Я «холиварю» немного про другое.
            Если есть «сложная логика», то ее лучше оформить не на ХП (и иже с ними), а на другом ЯП не зависимым от БД.
            :-)


            1. all-servs Автор
              05.01.2018 09:40
              #10600152

              если в субд не встроен мощный процедурный язык то да, но в oracle pl/sql очень силен и заточен именно под работу с данными, поэтому мне кажется на нем решать такие задачи удобнее, так сказать не отходя от кассы.


              1. mad_nazgul
                05.01.2018 10:56
                #10600216

                Прошу прощения еще раз.
                Я не спорю, что PL/SQL мощный ЯП.
                Но логику реализованную в ХП очень сложно и дорого поддерживать.
                Сталкиваюсь с этой проблемой не в первый раз.


                1. all-servs Автор
                  05.01.2018 11:12
                  #10600246

                  Ну тут уже больше вопрос что отдавать на базу данных. Я сторонник того, что база данных для работы с данными, а не для реализации 85% функциональности приложения включая посылку http запросов и тому подобному. Данный пример был сконцентрирован на случай сложной выборки из базы которую тяжело сделать средствами sql. Если же нужно распарить xml поменять значения для пары полей или провести валидацию, то конечно это нужно делать в языке программирования а не в базе данных


                  1. mad_nazgul
                    08.01.2018 05:53
                    #10604320

                    Согласен с вами.
                    Просто мое мнение, что если запрос сложный, что его приходится выносить в ХП, значит «что-то здесь не так».
                    И стоит присмотреться как структуре данных, так и к бизнес-логике


    1. xtender
      08.01.2018 02:15
      #10604252

      «WITH» есть в Oracle, причем он функционально гораздо шире, чем в PostgreSQL. Там сейчас можно даже процедуры и функции размещать.

      ЗЫ. Не истины ради, а холивара для в Oracle исторически многое очень хорошо оптимизировано, включая PL/SQL, причем настолько много, что мало кто знает о таких оптимизациях и как они работают, что потом удивляются почему «то же самое» на других СУБД выполняется очень медленно.


      1. mad_nazgul
        08.01.2018 05:52
        #10604318

        Есть, то он есть, но вот работать с ним нельзя, т.к. убожество полное.
        Я не могу использовать уже определенные представления в других представлениях.
        Только в результирующем запросе.
        И нафига там размещать процедуры и функции?!

        Насчет PL/SQL — это типичный vendor lock.
        Для Oracle это хорошо, а вот для остальных не очень.


        1. xtender
          08.01.2018 06:32
          #10604334

          И нафига там размещать процедуры и функции?!
          Ни разу не встречал когда нужны функции и сам придумать не можешь?
          Я не могу использовать уже определенные представления в других представлениях.
          Не понял? Почему не можешь?
          SQL> with
  2   a(a) as (select 1 from dual)
  3  ,b(b) as (select * from a)
  4  ,c(c) as (select * from b)
  5  select *
  6* from a,b,c;

         A          B          C
---------- ---------- ----------
         1          1          1


          Есть, то он есть, но вот работать с ним нельзя, т.к. убожество полное.
          Забавно слышать, т.к. в PG возможностей еще меньше…


  1. all-servs Автор
    05.01.2018 08:20
    #10600082

    вы с mssql работаете?


    1. all-servs Автор
      05.01.2018 08:27
      #10600090

      with это все равно sql запрос, я когда что то делаю стараюсь сначала на sql многое отдать, но бывают случаи когда логика очень сложная и ее проще реализовать за счет процедурного языка, который в oracle очень силен. Некоторые процедуры получаются очень большими, если их отдать на откуп в sql во первых запрос станет очень громоздким и его станет тяжело дописывать во вторых в таких запросах будет много конструкций or из за чего сам запрос начнет тормозить.


  1. dr_Irbisov
    05.01.2018 08:20
    #10600084

    Подскажите, в данном случае есть какое-то приниципиальное отличие от использования хранимых процедур? Ну кроме простоты переиспользования)


    1. all-servs Автор
      05.01.2018 08:21
      #10600086

      вы с mssql работаете?


      1. dr_Irbisov
        05.01.2018 12:26
        #10600408

        Регулярно не работаю. А при чем тут mssql?


        1. all-servs Автор
          05.01.2018 12:44
          #10600468
          +1

          Просто в mssql хранимая процедура может вернуть выборку, в oracle такого нет. Статья про то как получить выборку средствами pl/sql кода обратиться к ней как к обычной таблице и не просесть в плане производительности. Как это можно сделать с помощью процедуры?


          1. xtender
            08.01.2018 02:19
            #10604256

            С 12.1 может


  1. viktprog
    05.01.2018 08:56
    #10600114

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


    1. all-servs Автор
      05.01.2018 09:33
      #10600144

      Описанный пример да, так как тут отсутствует сложность в логике получения, но я начал с того что иногда просто необходимо задействовать процедурный язык, если запрос очень сложный не всегда все можно получить за счет sql, а если и получиться то запрос будет ужасным и начнет тормозить, именно для этого и нужны такие конструкции как я описал. Там где запрос не очень сложный конечно нужно использовать sql без использования pl/sql кода

      Посчитать среднюю зп пожалуйста я не написал вызов этой функции
      select * from table (hr.test.get_employees_dep(v_department_id)) emps
      основной смысл в том что к результату можно обратиться как к реляционной таблице, табличные функции для этого и созданы.


      1. viktprog
        05.01.2018 10:44
        #10600194

        Если у вас 1000 подразделений вы будете 1000 запросов выполнять? Думаю, будет что-то такое все-таки:

        select dep.department_id, avg(emp.salary_recom) as avg_salary from hr.departments dep cross join table (hr.test.get_employees_dep(dep.department_id)) emp group by dep.department_id

        И как вы считаете, производительность тут не упадет?

        Oracle может очень сложные запросы обрабатывать, если их правильно написать. Хотя, тут от случая к случаю, конечно.


        1. all-servs Автор
          05.01.2018 11:01
          #10600222

          Эта функция рассчитана на получение списка по одному отделу, джонить ее 1000 раз конечно не нужно. Если вам нужно получать информацию по всем отделам, стоит просто доработать функцию, например условиях выборки поменять:

          where
      dep.department_id = p_department_id

          на 
          where
      dep.department_id = p_department_id  or p_department_id   is null

          а если нужно получать информацию не по всем отделам а по нескольким, то строку со списком ид отделов через запятую передавать и брать через like:
          where
      '%p_str%' like ',' || dep.department_id || ','

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


          1. viktprog
            05.01.2018 11:31
            #10600296

            Согласен, что не всегда получается все написать на чистом SQL. Хотя, в общем случае, SQL всегда должен быть быстрее динамики.


            По поводу доработки функции — так вы ее еще бесконечно будете дорабатывать, а со вью такого не было бы. И не используйте like как вы предлагаете, вы так потеряете индекс на department_id и все станет медленным (а еще само условие там некорректное). Лучше разбить строку и делать непосредственный join уже по pk.


            В общем, все что я хотел сказать своим первым комментарием — пример некорректный и кто-то может ошибочно подумать, что pipelined-функции хорошо так использовать.


            1. all-servs Автор
              05.01.2018 12:03
              #10600348
              +1

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

              По поводу like это первое что в голову пришло, можно как вы написали разбить строку и уже делать join по первичному ключу, так что вопрос решаем.

              А дописывать всегда все приходиться, требования растут. У нас была сначала небольшая view, аналитик все дописывал требования, в итоге все это в 1000 строк когда уложилось, когда в функцию перенесли, на view стало дорабатывать ее просто не возможно и огромное кол-во оператора or в запросе делало свое дело в плане торможения.

              Предлагаю сойтись на том, что есть view а есть конвейерные функции и их не нужно сравнивать как одно и тоже. В большинстве случаев нужно использовать view, но при очень сложной логике нужно использовать конвейерные функции.


    1. xtender
      08.01.2018 02:43
      #10604266

      У автора просто пример надуманный… Конечно, практически всегда лучше следовать мантре от Тома Кайта:

      I have a pretty simple mantra when it comes to developing database software, and I have written this many times over the years:
      • You should do it in a single SQL statement if at all possible.
      • If you cannot do it in a single SQL statement, do it in PL/SQL.
      • If you cannot do it in PL/SQL, try a Java stored procedure.
      • If you cannot do it in Java, do it in a C external procedure.
      • If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.


      Но иногда, в крайне редких случаях, по производительности лучше использовать PL/SQL, я писал пару примеров тут: orasql.org/2014/02/28/straight-sql-vs-sql-and-plsql

      И кстати, насчет обычных табличных неконвеерных функций: чуть менее месяца назад, я как раз разбирался с тем, что неконвеерные функции ужасно медленно возвращают результаты в SQL, причем настолько, что даже если у вас уже есть такая функция, то будет быстрее, если просто напишите конвеерную функцию-обертку, в которой будете получать весь результат неконвеерной и возвращать через пайплайн: orasql.org/2017/12/13/collection-iterator-pickler-fetch-pipelined-vs-simple-table-functions


  1. tom_ozi
    05.01.2018 11:34
    #10600300

    Шикарно! Очень познавательная статья.
    Мы использовали подход посложнее:

    CREATE OR REPLACE TYPE "TYPE_ROW" AS OBJECT (COL1 VARCHAR2(10),COL2 VARCHAR2(256));
CREATE OR REPLACE TYPE "TYPE_TAB"  IS TABLE OF TYPE_ROW;
--Заполняем TYPE_ROW в SAME_PKG.SAME_FN;
SELECT * FROM TABLE(CAST(SAME_PKG.SAME_FN(:PARAM) AS N_TABLE)) ;

    Кстати в Oracle 9.2i всё же нужно писать RETURN;