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

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

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

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

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、 总结

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

打赏

支付宝打赏

微信打赏

返回列表

上一篇：因函数索引逻辑调整造成查询结果不正确的案例分享

下一篇：数据库中唯一约束与唯一索引的区别

ORACLE 9i 冷备份之异机恢复

1. 本文描述一个冷备份及恢复的过程，就是把现在运行的实例内容完合复制一份出来，运行在另一个实例里（可以是同一台机器，也可是另一台机器），如果您想以现有的环境数据为基础，做一个测试环境的话，这是一个不错的选择。源数据库环境说明：OS：W...

因函数索引逻辑调整造成查询结果不正确的案例分享

收到数据仓库项目组人员提交的一个问题，说是某个查询结果的数据有问题，具体描述是在某一个列做了一个计算，并且作为一个查询条件放在了 where后面，得到了非预期结果，并且有截图（因数据涉密，这里不贴原图了），第一反应是，数据中存在着不可见字符（比如：空格），干扰了正常的结果，得到的回复是没有这样情况，...

卖水果的net

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

相关文章

ORACLE 9i 冷备份之异机恢复

因函数索引逻辑调整造成查询结果不正确的案例分享

发表评论

@2020 www.msgde.net 冀ICP备20003249号

冀公网安备 13060602001226号

Powered By Z-BlogPHP. Theme by TOYEAN.

卖水果的net

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

相关文章

ORACLE 9i 冷备份之异机恢复

因函数索引逻辑调整造成查询结果不正确的案例分享

发表评论取消回复

@2020 www.msgde.net 冀ICP备20003249号冀公网安备 13060602001226号

Powered By Z-BlogPHP. Theme by TOYEAN.

发表评论

@2020 www.msgde.net 冀ICP备20003249号

冀公网安备 13060602001226号