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