oracle 在 oracle11g 中创建参数化视图

creating parameterized views in oracle11g

提问by pri_dev

I have a big query with nesting and left join and Ineed to create a view out of it so as not to run it from the application. The issue is I need the date range and some other fields as input parameters since it will vary from the front end for each request. I just looked up and saw some posts referring to using SYS_CONTEXT for parameterized views and need to know exactly how do I create the view for example with 2 parameters - fromdate, todateand how I invoke the view from the application.

我有一个带有嵌套和左连接的大查询，并且 Ineed 从中创建一个视图，以免从应用程序中运行它。问题是我需要日期范围和其他一些字段作为输入参数，因为它会因每个请求的前端而异。我只是抬起头来，看到一些帖子提到将 SYS_CONTEXT 用于参数化视图，并且需要确切地知道如何创建具有 2 个参数fromdate, todate的视图-以及我如何从应用程序调用视图。

Just for info I am using grails/groovy for developing the application. and here is the query I want to create view out of..

仅供参考，我使用 grails/groovy 来开发应用程序。这是我想从中创建视图的查询..

 select 
    d.dateInRange as dateval,
    eventdesc,
    nvl(td.dist_ucnt, 0) as dist_ucnt
from (
    select 
        to_date(fromdate,'dd-mon-yyyy') + rownum - 1 as dateInRange
    from all_objects
    where rownum <= to_date(fromdate,'dd-mon-yyyy') - to_date(todate,'dd-mon-yyyy') + 1
) d
left join (
    select 
        to_char(user_transaction.transdate,'dd-mon-yyyy') as currentdate,
        count(distinct(grauser_id)) as dist_ucnt,
        eventdesc 
    from
        gratransaction, user_transaction 
  where gratransaction.id = user_transaction.trans_id and 
  user_transaction.transdate between to_date(fromdate,'dd-mon-yyyy') and to_date(todate,'dd-mon-yyyy') 
    group by  to_char(user_transaction.transdate, 'dd-mon-yyyy'), eventdesc 
) td on td.currentdate = d.dateInRange order by d.dateInRange asc

回答by Jeffrey Kemp

The context method is described here: http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm

这里描述了上下文方法：http: //docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm

e.g. (example adapted from the above link)

例如（改编自上述链接的示例）

CREATE CONTEXT dates_ctx USING set_dates_ctx_pkg;

CREATE OR REPLACE PACKAGE set_dates_ctx_pkg IS 
  PROCEDURE set(d1 in date, d2 in date); 
END; 
/

CREATE OR REPLACE PACKAGE BODY set_dates_ctx_pkg IS
  PROCEDURE set(d1 in date, d2 in date) IS 
  BEGIN 
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd1', TO_CHAR(d1,'DD-MON-YYYY'));
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd2', TO_CHAR(d2,'DD-MON-YYYY'));
  END;
END;
/

Then, set the dates in your application with:

然后，在您的应用程序中设置日期：

BEGIN set_dates_ctx_pkg.set(mydate1, mydate2); END;
/

Then, query the parameters with:

然后，查询参数：

SELECT bla FROM mytable
WHERE mydate
  BETWEEN TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd1')
          ,'DD-MON-YYYY')
      AND TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd2')
          ,'DD-MON-YYYY');

The advantage of this approach is that it is very query-friendly; it involves no DDL or DML at runtime, and therefore there are no transactions to worry about; and it is very fast because it involves no SQL - PL/SQL context switch.

这种方法的优点是对查询非常友好；它在运行时不涉及 DDL 或 DML，因此无需担心事务；它非常快，因为它不涉及 SQL - PL/SQL 上下文切换。

Alternatively:

或者：

If the context method and John's package variables method are not possible for you, another one is to insert the parameters into a table (e.g. a global temporary table, if you're running the query in the same session), then join to that table from the view. The downside is that you now have to make sure you run some DML to insert the parameters whenever you want to run the query.

如果上下文方法和约翰的包变量方法对你来说是不可能的，另一种是将参数插入到一个表中（例如一个全局临时表，如果你在同一个会话中运行查询），然后加入到该表从观点来看。缺点是您现在必须确保运行一些 DML 以在您想要运行查询时插入参数。

回答by Neco

I have just made a workaround for this annoying Oracle disadvantage. Like this

我刚刚为这个烦人的 Oracle 缺点制定了一个解决方法。像这样

create or replace package pkg_utl
as
  type test_record is record (field1 number, field2 number, ret_prm_date date);
  type test_table is table of test_record;
  function get_test_table(prm_date date) return test_table pipelined;
end;
/

create or replace package body pkg_utl
as
  function get_test_table(prm_date date) return test_table pipelined
  is
  begin
    for item in (
      select 1, 2, prm_date
      from dual
    ) loop
      pipe row (item);
    end loop;
    return;
  end get_test_table;
end;
/

it still requires a package, but at least i can use it in more convinient way:

它仍然需要一个包，但至少我可以以更方便的方式使用它：

select *
from table(pkg_utl.get_test_table(sysdate))

i am not sure about performance...

我不确定性能...

回答by John Doyle

To use parameters in a view one way is to create a package which will set the values of your parameters and have functions that can be called to get those values. For example:

在视图中使用参数的一种方法是创建一个包，该包将设置参数的值并具有可调用以获取这些值的函数。例如：

create or replace package MYVIEW_PKG as
  procedure SET_VALUES(FROMDATE date, TODATE date);

  function GET_FROMDATE
    return date;

  function GET_TODATE
    return date;
end MYVIEW_PKG;

create or replace package body MYVIEW_PKG as
  G_FROM_DATE   date;
  G_TO_DATE     date;

  procedure SET_VALUES(P_FROMDATE date, P_TODATE date) as
  begin
    G_FROM_DATE := P_FROMDATE;
    G_TO_DATE := P_TODATE;
  end;

  function GET_FROMDATE
    return date is
  begin
    return G_FROM_DATE;
  end;

  function GET_TODATE
    return date is
  begin
    return G_TO_DATE;
  end;
end MYVIEW_PKG;

Then your view can be created thus:

那么你的视图可以这样创建：

create or replace view myview as
    select 
        d.dateInRange as dateval,
        eventdesc,
        nvl(td.dist_ucnt, 0) as dist_ucnt
    from (
        select 
            MYVIEW_PKG.GET_FROMDATE + rownum - 1 as dateInRange
        from all_objects
        where rownum <= MYVIEW_PKG.GET_FROMDATE - MYVIEW_PKG.GET_TODATE + 1
    ) d
    left join (
        select 
            to_char(user_transaction.transdate,'dd-mon-yyyy') as currentdate,
            count(distinct(grauser_id)) as dist_ucnt,
            eventdesc 
        from
            gratransaction, user_transaction 
      where gratransaction.id = user_transaction.trans_id and 
      user_transaction.transdate between MYVIEW_PKG.GET_FROMDATE and MYVIEW_PKG.GET_TODATE
        group by  to_char(user_transaction.transdate, 'dd-mon-yyyy'), eventdesc 
    ) td on td.currentdate = d.dateInRange order by d.dateInRange asc;

And to run it you must set the values first:

要运行它，您必须先设置值：

exec MYVIEW_PKG.SET_VALUES(trunc(sysdate)-1,trunc(sysdate));

And then calls to it will use these values:

然后调用它会使用这些值：

select * from myview;