ORACLE 创建带参数视图-实践

PS:最近在做一个需求,移交A人多个业务表(7个)的数据给另几个人(就是查询出A的业务数据,把相应字段改成其他人),想到的办法就是创建一个视图union all 所有业务表。根据A编号去查询业务数据,所以考虑通过每个业务表加上客户编号去过滤数据,提高查询性能。但是这个客户编号是动态的,使用普通视图就不能在每个业务表过滤了,在网上查询,视图是可以带参数的,所以实践下,并记录。

下面是使用步骤:

1.创建一个package,包中定义两个方法,一个set值的方法,一个get值的方法

 --定义包
create or replace package view_move_user is
  --set值方法
  function set_moveuser(moveuser varchar2) return varchar2;
  --get值方法
  function get_moveuser return varchar2;
end view_move_user;

2.创建package body,实现package中的两个方法

--包方法实现
create or replace package body view_move_user is
  paramValue varchar2(32);
  -- 给paramValue赋值
  function set_moveuser(moveuser varchar2) return varchar2 is
  begin
    paramValue := moveuser;
    return moveuser;
  end;
 --返回paramValue 的值
  function get_moveuser return varchar2 is
  begin
    return paramValue;
  end;

end view_move_user;

3.创建带参数视图view_move_user.get_moveuser()是从方法中获取传入的参数

Create View view_move1 As select busi_segment,busi_id,move_user from (
select '005' as busi_segment,
       archive_id as busi_id,
       loan_com_id as move_user
  from Doc_Archive
 where archive_type = '1'
   and is_temp is null
   and loan_com_id=view_move_user.get_moveuser()
   union all
   select '004' as busi_segment,
       staging_id as busi_id,
       loan_com_id as move_user
  from Doc_Staging
 where status <> '5'
  and loan_com_id=view_move_user.get_moveuser()
 union all 
 select '003' as busi_segment,
       loan_input_id  as busi_id,
       confirmer_id as move_user
  from Pvp_Loan_Input
 where input_status <> '30'
 and confirmer_id=view_move_user.get_moveuser()
 union all 
 select '002' as busi_segment,
       loan_no   as busi_id,
       loan_com_id as move_user
  from Pvp_Loan_Apply
 where approve_status not in( '000','990','998')
  and loan_com_id=view_move_user.get_moveuser()
 union all 
 select '001' as busi_segment,
       cont_no    as busi_id,
       loan_com_id as move_user
  from Ctr_Cont_Apply
 where approve_status not in( '000')
   and loan_com_id=view_move_user.get_moveuser()
 union all 
 select '006' as busi_segment,
       task_id      as busi_id,
       loan_com_id as move_user
  from Pvp_Repay_Fee_Make
 where approve_status  ='000'
       and loan_com_id=view_move_user.get_moveuser()
 union all 
 select '007' as busi_segment,
       sign_input_id     as busi_id,
       cont_sign_user as move_user
  from Ctr_Sign
 where input_status ='10'
   and cont_sign_user=view_move_user.get_moveuser()

);


4.查询视图语句view_move_user.set_moveuser('20805') ='20805' 通过set方法,传入20805

select * from view_move1 where view_move_user.set_moveuser('20805') ='20805' ;

5.根据PLSQL查看执行效率(取本次最多数据量的数据5775,分别执行5次,单位:s)



PLSQL执行计划

无参数视图:



带参数视图未设置索引:



带参数视图-设置索引:

6.总结

带参数视图-设置索引的执行计划明显比其他两个好看吐舌头吐舌头吐舌头

  • 3
    点赞
  • 29
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
视图:是基于一个表或多个表或视图的逻辑表,本身不包含数据,通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表,Oracle的数据库对象分为五种:表,视图,序列,索引和同义词。 视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。 视图的优点: 1.对数据库的访问,因为视图可以有选择性的选取数据库里的一部分。 2.用户通过简单的查询可以从复杂查询中得到结果。 3.维护数据的独立性,试图可从多个表检索数据。 4.对于相同的数据可产生不同的视图视图分为简单视图和复杂视图: 1、简单视图只从单表里获取数据,复杂视图从多表; 2、简单视图不包含函数和数据组,复杂视图包含; 3、简单视图可以实现DML操作,复杂视图不可以。 语法结构:创建视图 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] 语法解析: OR REPLACE :若所创建的试图已经存在,则替换旧视图; FORCE:不管基表是否存在ORACLE都会自动创建视图(即使基表不存在,也可以创建视图,但是该视图不能正常使用,当基表创建成功后,视图才能正常使用); NOFORCE :如果基表不存在,无法创建视图,该项是默认选项(只有基表都存在ORACLE才会创建视图)。 alias:为视图产生的列定义的别名; subquery :一条完整的SELECT语句,可以在该语句中定义别名; WITH CHECK OPTION :插入或修改的数据行必须满足视图定义的约束; WITH READ ONLY :默认可以通过视图对基表执行增删改操作,但是有很多在基表上的限制(比如:基表中某列不能为空,但是该列没有出现在视图中,则不能通过视图执行insert操作),WITH READ ONLY说明视图是只读视图,不能通过该视图进行增删改操作。现实开发中,基本上不通过视图对表中的数据进行增删改操作。 案例3:基于EMP表和DEPT表创建视图 代码演示:视图 SQL> CREATE OR REPLACE VIEW EMPDETAIL 2 AS 3 SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME 4 FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 5 WITH READ ONLY 6 / VIEW CREATED SQL> SELECT * FROM EMPDETAIL; ① EMPNO ENAME JOB HIREDATE DEPTNO DNAME 7369 SMITH CLERK 17-12月-80 20 RESEARCH 7499 ALLEN SALESMAN 20-2月 -81 30 SALES 7521 WARD SALESMAN 22-2月 -81 30 SALES 7566 JONES MANAGER 02-4月 -81 20 RESEARCH 7654 MARTIN SALESMAN 28-9月 -81 30 SALES 7698 BLAKE MANAGER 01-5月 -81 30 SALES 7782 CLARK MANAGER 09-6月 -81 10 ACCOUNTING 7788 SCOTT ANALYST 19-4月 -87 20 RESEARCH 7839 KING PRESIDENT 17-11月-81 10 ACCOUNTING 7844 TURNER SALESMAN 08-9月 -81 30 SALES 7876 ADAMS CLERK 23-5月 -87 20 RESEARCH 7900 JAMES CLERK 03-12月-81 30 SALES 7902 FORD ANALYST 03-12月-81 20 RESEARCH 7934
Oracle 中,你可以创建参数视图参数视图可以接受一个或多个输入参数,并根据这些输入参数动态生成视图的结果集。要创建参数视图,需要使用 PL/SQL 函数来定义视图的查询语句。 以下是创建参数视图的一般步骤: 1. 创建一个 PL/SQL 函数,该函数接受一个或多个输入参数,并返回一个 `SELECT` 查询语句的字符串。 ```sql CREATE OR REPLACE FUNCTION my_view_func(p_param1 IN VARCHAR2, p_param2 IN NUMBER) RETURN VARCHAR2 AS v_query VARCHAR2(4000); BEGIN v_query := 'SELECT col1, col2, col3 FROM my_table WHERE col4 = ''' || p_param1 || ''' AND col5 = ' || p_param2; RETURN v_query; END; ``` 在上面的示例中,我们创建了一个名为 `my_view_func` 的函数,该函数接受两个参数 `p_param1` 和 `p_param2`,并返回一个 `SELECT` 查询语句的字符串。该查询语句查询 `my_table` 表中 `col4` 等于参数 `p_param1`,且 `col5` 等于参数 `p_param2` 的记录。 2. 创建一个视图,该视图调用上述 PL/SQL 函数,并将输入参数传递给该函数。 ```sql CREATE OR REPLACE VIEW my_view (col1, col2, col3) AS SELECT col1, col2, col3 FROM TABLE(DBMS_SQL.PARSE(my_view_func('parameter1', 123), DBMS_SQL.NATIVE)); ``` 在上面的示例中,我们创建了一个名为 `my_view` 的视图,该视图调用了 `my_view_func` 函数,并将参数 `parameter1` 和 `123` 传递给该函数。最终,该视图的结果集会根据传递的参数动态生成。 需要注意的是,在使用参数视图时,需要传递正确的参数类型和值,否则会导致查询失败或产生错误的结果集。希望这能对你有所帮助!
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值