Как можно реализовать представление получающее параметр?

Question

Надо сделать представление, которое вернёт должность и отдел сотрудников на заданую дату, например, конец 2007.

Если так:

create or replace view jobsOnDate as
    select h.employee_id, job_id, department_id
    from hr.job_history h
    where date'2007-12-31' between start_date and end_date;

То запрос из jobsOnDate конечно работает, но константную дату нужно передать представлению как параметр.

То есть, в идеале создание предстовления и запрос выглядели бы так:

create or replace view jobsOnDate (effDate date) as 
    select [... where effDate between ...]
/
select * from jobsOnDate (date'2007-12-31')
/

Но создать так передставление нельзя. Можно конечно добавить столбцы start_date, end_date в сет результата предтавления, но это только пример, реальные данные и запрос намного сложнее, это будет не так просто.

Какие есть пути решения?

Answer 1

Mожно решить через переменную пакета. Перед вызовом представления ее устанавливать, а в представлении использовать функцию pkgViewSet.getJobsOnDate.

Создаем пакет:

create or replace package pkgViewSet is
  function getJobsOnDate return date;
  procedure setJobsOnDate(inDate date);
end pkgViewSet;
/
create or replace package body pkgViewSet is
  cDateSet date;

  function getJobsOnDate return date as
  begin
    return(cDateSet);
  end;

  procedure setJobsOnDate(inDate date) as
  begin
    cDateSet := inDate;
  end;
end pkgViewSet;
/

Создаем представление:

create or replace view jobsOnDate as
    select h.employee_id, job_id, department_id
    from hr.job_history h
    where pkgViewSet.getJobsOnDate between start_date and end_date;

Использование:

pkgViewSet.setJobsOnDate(date'2007-12-31');

select * from jobsOnDate

++ Простота реализации.
-- Инициализация пакета должна происходить в каждой сессии.

Никогда так не делал, но всегда очень хотелось.

Answer 2

Можно решить через конвеерные функции.

Создаем пользовательские типы данных, они заменят представление:

create or replace type t_job_history force as object (
  employee_id integer,
  job_id varchar2(10),
  department_id integer)
/
create or replace type t_job_history_tbl as table of t_job_history
/
select * from table(t_job_history_tbl(t_job_history(999, 'UNEMPLOYED', 99)));

EMPLOYEE_ID JOB_ID     DEPARTMENT_ID
----------- ---------- -------------
        999 UNEMPLOYED            99

Сама функция:

create or replace function getJobHistory(inDate date) return t_job_history_tbl pipelined as
begin
  for f_rec in (
    select 
      jh.employee_id, jh.job_id, jh.department_id
    from hr.job_history jh 
    where inDate between jh.start_date and jh.end_date) loop
    pipe row (t_job_history(f_rec.employee_id, f_rec.job_id, f_rec.department_id)); 
  end loop;
  return;
end getJobHistory;
/

Вызов очень похож на приведенный в вопросе:

select *
from table (getJobHistory (date'2007-12-31'))

++ Относительная простота реализации.
-- Затруднительная оптимизация при использовании функции в сложных запросах.

Answer 3

Один из известных способов, установить параметры в контексте выполнения, а представление создать с учётом этих параметров из контекста. Рабочий пример:

create or replace context myctx using myCtxApi;

create or replace package myCtxApi as
    procedure set (d date, what varchar2 := null);
    function get (what varchar2, forceNull boolean := false) return varchar2; 
end myCtxApi;
/
create or replace package body myCtxApi as
    context constant varchar2 (32) := 'migctx'; 
    procedure set (d date, what varchar2 := null) is
    begin 
        dbms_session.set_context (upper ('myctx'), coalesce (what, 'onDate'), d);
    end set;
    function get (what varchar2, forceNull boolean := false) return varchar2 is 
        ret varchar2 (4000);
    begin
        ret := sys_context (upper ('myCtx'), what);
        if ret is null and not forceNull then 
            raise_application_error (-20000, 'not set in the context '||context||'('||what||')'); 
        end if;
        return ret;
    end get;
end myCtxApi;
/
create or replace view jobsOnDate as
    select h.employee_id, job_id, department_id
    from hr.job_history h
    where myCtxApi.get ('onDate') between start_date and end_date;

Перед вызовом запроса необходимо будет установить контехт:

exec myCtxApi.set (date'2007-12-31')

select * from jobsOnDate;

EMPLOYEE_ID JOB_ID     DEPARTMENT_ID
----------- ---------- -------------
        114 ST_CLERK              50
        122 ST_CLERK              50
        176 SA_MAN                80

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

Answer 4

Начиная с версии 20c задачу можно решить с SQL Macros. В SQL контексте функция с ключевым словом SQL_MACRO будет вызвана на этапе парсинга. В тексте возвращаемого ею запроса параметры будут замещены соответствующими литералами. То есть, функция выполнит роль макроса, а её результат и есть тот запрос, который будет эффективно выполнен.

create or replace function jobHistView (onDate date) return varchar2 SQL_MACRO is
begin
    return q'[
        select h.employee_id, job_id, department_id
        from hr.job_history h
        where onDate between start_date and end_date]';
end;
/

Запрос будет выглядеть так:

select * 
from jobHistView (date'2007-12-31');

БЛОГ НА HUSL

Как можно реализовать представление получающее параметр?

Ответы (4 шт):