当前位置:首页 > Oracle > 正文内容

在Oracle中查询视图参数化的实现方法

管理员2年前 (2018-08-15)Oracle913

有这样的一个需求,在查询视时,能否和执行存储过程一样,传入一些参数,根据不同的参数,返回出不同的结果集,我们知道,视图并没有这样的功能,也不存在这样的入口,这个问题的解决思路有两个,一是使用管道函数直接返回结果集,二是在查询视图之前,把参数存储在其他位置(内存或存储),相当于会话级变量,视图的查询语句直接使引用他们,这里我们暂不关注管道函数方法,只使用提前存储参数的思路。

1、 使用上下文(CONTEXT)对象来存储变量

上下文是ORACLE特有的对象,和高级语言中的Map或Hashtable功能类似,只可以存储键值对,创建这个对象很简单,通过CREATE CONTEXT 就可以,所有上下文对象的所者都是SYS,而不是当前用户,所以通过USER_OBJECTS查询不到这个对象,只能通过DBA_OBJECTS 或 DBA_CONTEXTS查询。

为CONTEXT赋值,可以通过dbms_session.set_context 方法,并且这个方法必须放在一个存储过程中才可以赋值,获取数值的方法也很简单SYS_CONTEXT(namespace, attrib) 就可以取到,各个会话之间的值是隔离的,互不影响。

先提供一个示例代码,创建一个context对象和一个赋值的存储过程,在创建context时要使用using子句指定一个存储过程的名称,以后用它给context赋值,这个存储过程可以不存在,后续创建即可。存储过程的文本也很简单,只是一个set_context操作,第一个参数是context的名字,不区分大小写,第二个参数是属性的名称,第三个是属性的值,标准的key-value结构。

SQL> create context ctx_views using sp_set_ctx_value;
Context created
SQL> 
SQL> create procedure sp_set_ctx_value(
2    p_attrib varchar2,
3    p_value varchar2) as
  5  begin
  5    dbms_session.set_context('ctx_views', p_attrib, p_value);
  6  end;
  7  /
Procedure created
SQL>

下一步,创建一个视图,从dba_objects 中查询一些数据出来,返回2列不超过5行的一个结果集。这其中使用了两个context的属性的内容:OWNER 和TYPE,就是按对象的所有者和对象类型,查询出这些数据。

SQL> create view v1
  2  as
  3  select object_name, created
  4    from dba_objects
  5   where owner = sys_context('ctx_views', 'OWNER')
  6     and object_type = sys_context('ctx_views', 'TYPE')
  7     and rownum <= 5;
View created

SQL>

我们在一个会话刚刚建立时,去查询这个视图,返回的是一个空结果集,所以要在使用之前,首先初始化这两个参数,先看一下效果。

SQL> begin
  2     sp_set_ctx_value('OWNER', 'SYSTEM');
  3     sp_set_ctx_value('TYPE', 'TABLE');
  4  end;
  5  /
PL/SQL procedure successfully completed
SQL> select * from v1;
OBJECT_NAME                    CREATED
------------------------------ -----------
SQLPLUS_PRODUCT_PROFILE        2010-03-30
REPCAT$_USER_PARM_VALUES       2010-03-30
REPCAT$_USER_AUTHORIZATIONS    2010-03-30
REPCAT$_TEMPLATE_TYPES         2010-03-30
REPCAT$_TEMPLATE_TARGETS       2010-03-30

重新赋值再查询一次,可以看到已经达到了我们的预期的效果了。

SQL> begin
  2      sp_set_ctx_value('OWNER', 'SYS');
  3      sp_set_ctx_value('TYPE', 'TABLE');
  4  end;
  5  /
PL/SQL procedure successfully completed
SQL> select * from v1;
OBJECT_NAME                    CREATED
------------------------------ -----------
XSTREAM$_SYSGEN_OBJS           2010-03-30
XSTREAM$_SUBSET_RULES          2010-03-30
XSTREAM$_SERVER                2010-03-30
XS$VERIFIERS                   2010-03-30
XS$SESSION_ROLES               2010-03-30

2、 使用程序包的变量的功能

程序包中可以定义变量、存储过程、函数、类型等等诸多类型的对象,变量的应用范围也是当前会话效,这里也有一个包的示例,包头的结构,包含了2个变量,两个函数用于获取变量的值。这里有一个知识点,包中公共变量可以被PLSQL直接访问,但是不被SQL直接使用,只能通过包内的其他的函数或存储过程去访问它们。

SQL> create or replace package pkg_cfg is
  2      g_owner varchar2(30);
  3      g_type  varchar2(30);
  4      function get_owner return varchar2;
  5      function get_type return varchar2;
  6  end;
  7  /
Package created

下一步,在包体中,实现这两个函数。

SQL> create or replace package body pkg_cfg is
  2      function get_owner return varchar2 is
  3      begin
  4          return g_owner;
  5      end;
  6      function get_type return varchar2 is
  7      begin
  8          return g_type;
  9      end;
 10  end;
 11  /
Package body created

再来创建访问视图,查询条件中,直接使用的是这两个函数,它可以返回变量的值。

SQL> create view v1
  2  as
  3  select object_name, created
  4    from dba_objects
  5   where owner = pkg_cfg.get_owner()
  6     and object_type = pkg_cfg.get_type()
  7     and rownum <= 5;
View created

查询一下看看效果,这里赋值要使用PLSQL的匿名块。

SQL> begin
  2      pkg_cfg.g_owner := 'SYS';
  3      pkg_cfg.g_type := 'TABLE';
  4  end;
  5  /
PL/SQL procedure successfully completed
SQL> select * from v1;
OBJECT_NAME                    CREATED
------------------------------ -----------
XSTREAM$_SYSGEN_OBJS           2010-03-30
XSTREAM$_SUBSET_RULES          2010-03-30
XSTREAM$_SERVER                2010-03-30
XS$VERIFIERS                   2010-03-30
XS$SESSION_ROLES               2010-03-30

3、 使用临时表

使用临时表,要比前两个方法要简单一些,首先定义一个临时表。

SQL> create global temporary table tb_param(
  2      p_owner varchar2(30),
  3      p_type varchar2(30)
  4  );
Table created

视图的定义改成这样的

SQL> create or replace view v1 as
  2  select o.object_name, o.created
  3    from dba_objects o, tb_param p
  4   where o.owner = p.p_owner
  5     and o.object_type = p.p_type
  6     and rownum <= 5;
View created

调用一次

SQL> begin
  2      delete tb_param;
  3      insert into tb_param(p_owner, p_type)
  4      values('SYS', 'TABLE');
  5  end;
  6  /
PL/SQL procedure successfully completed
SQL> select * from v1;
OBJECT_NAME                    CREATED
------------------------------ -----------
ICOL$                          2010-03-30
CON$                           2010-03-30
UNDO$                          2010-03-30
PROXY_ROLE_DATA$               2010-03-30
FILE$                          2010-03-30

4、 总结

前面所述的三种方法,都可以实现会话级变量的使用功能,一般来讲,建议使用第一种,即定义一个上下文的方法,该方法如果使用多个变量时,语法简练、数据存放于内存中,性能上有优势,使用二个方法时,如果变量的个数增加,则函数的个数也要增加,无形中增加了开发的工作量,第三种在使用前要注意先清理临时表中的数据,以避免多次插入数据,造成数据返回的不准确。

打赏 支付宝打赏 微信打赏
    扫描二维码至手机访问

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/oracle/oracle_view_use_variable.html

    分享给朋友:

    相关文章

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。