在 oracle11g 中创建参数化视图

2021-12-06 00:00:00 oracle11g oracle views

我有一个带有嵌套和左连接的大查询，并且 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

推荐答案

context 方法描述在这里:http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm

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

然后，查询参数:

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的包变量方法对你来说不可行，另一种是把参数插入到一个表中(比如一个全局临时表，如果你在同一个会话中运行查询)，然后加入从视图到那个表.缺点是您现在必须确保运行一些 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.

相关文章