在oracle11g中创建参数化视图

我有一个带有嵌套和左连接的大查询，我需要从中创建一个视图，以免从应用程序中运行它。问题是我需要日期范围和其他一些字段作为输入参数，因为每个请求的前端都会有所不同。 我刚刚查了一下，看到一些帖子提到使用 SYS_CONTEXT 进行参数化视图，并且需要确切地知道如何创建视图，例如使用 2 个参数 -fromdate, todate以及我如何从应用程序调用视图。

仅供参考，我正在使用 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

上下文方法描述如下：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

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

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;
/

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

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

然后，使用以下命令查询参数：

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');

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

或者：

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