在oracle11g中创建参数化视图 [英] creating parameterized views in oracle11g

问题描述

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

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, todate and how I invoke the view from the application.

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

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

 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

推荐答案

此处描述了上下文方法:

The context method is described here: 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');

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

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.

或者:

如果无法使用context方法和John的package变量方法，另一种方法是将参数插入表(例如，全局临时表，如果您在同一会话中运行查询)，然后加入从视图转到该表.缺点是您现在必须确保在运行查询时运行一些DML来插入参数.

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.

这篇关于在oracle11g中创建参数化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！