10 视图
10.1 视图的介绍
在SQL中调用函数
A DML语句中可以调用函数但是不能够调用SP
B 不能DDL语句中调用函数。创建视图除外
C 不一定有对函数的操作权限才能调用
D FUN是视图定义的一部分，只需要有对这个视图的查询权限。
视图的定义：
oracle的一种对象，是相关的查询集合结果的查询窗口。视图中查到的数据不是新生成的数据，视图是不存储数据的，查询视图的时候，根据视图的定义，立即、临时去执行创建视图中的select语句查询数据。
10.2 视图的创建
创建视图需要有create view权限，resource权限并不包含create view的权限
SQL> create or replace view emp_x as select * from emp;
create or replace view emp_x as select * from emp
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant create view to scott;
Grant succeeded.
SQL> conn scott/scott;
Connected.
SQL> create or replace view emp_x as select * from emp;
View created.
SQL> create table emp_x as select * from emp;
Table created.
SQL> create table dept_no as select * from dept;
Table created.
(1) 创建单表类型的视图
create or replace view v_emp as select ename,empno,sal from emp_x; --单表
select * from v_emp; --查询这个视图
(2) 创建有关联关系的视图
create or replace view v_emp_dept as
select a.ename,a.empno,a.sal,b.dname
from emp_x a,dept_no b
where b.deptno=a.deptno; --有关联关系的视图
select ename,empno from v_emp_dept;
(3) 需要注意视图字段的别名
视图中的字段如果是表达式或者是聚合函数组成的，必须写别名 as xxxx 否则报错：ORA-00998
create or replace view v_emp_dept_1
as select b.dname,count(a.empno),sum(a.sal),round(avg(a.sal))
from emp_x a,dept_no b
where a.deptno=b.deptno
group by b.dname;
as select b.dname,count(a.empno),sum(a.sal),round(avg(a.sal))
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
create or replace view v_emp_dept_1
as select b.dname,count(a.empno) as c1,sum(a.sal) as c2,round(avg(a.sal)) as c3
from emp_x a,dept_no b
where a.deptno=b.deptno
group by b.dname;
(4) 视图失效的处理
SQL> alter table dept_no rename to dept_x; --修改表的名字
Table altered.
SQL> select * from v_emp_dept;
select * from v_emp_dept
*
ERROR at line 1:
ORA-04063: view "SCOTT.V_EMP_DEPT" has errors --有错
SQL> select object_name,object_type,status from user_objects where object_name=‘V_EMP_DEPT‘;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
V_EMP_DEPT VIEW INVALID
发现视图无效，需要重新创建视图，将表名改为dept_x即可
(5) 在数据字典中查视图
SQL> select object_name,object_type,status from user_objects where object_name=‘V_EMP_DEPT‘;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
V_EMP_DEPT VIEW INVALID
select * from user_views --text字段存储视图的定义
SQL> select view_name,text from user_views;
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
V_EMP select ename,empno,sal from emp_x
V_EMP_DEPT select a.ename,a.empno,a.sal,b.dname
from emp_x a,dept_x b
where b.deptno=a.deptno
V_EMP_DEPT_1 select b.dname,count(a.empno) as c1,sum(a.sal) as c2,round(avg(a.sal)) as c3
from emp_x a,dept_x b
where a.deptno=b.deptno
group by b.dname
(6) 需要注意的三个问题
A 是否可以对视图做DML操作 --可以
B 对视图DML，执行成功，表的数据是否跟着变化 --会
update v_emp set sal=sal*1.2 where empno=7499;
commit;
select * from emp_x where empno=7499;
C 对有 聚合、分组操作、关联操作 的视图的DML操作分析 --都是不能够进行DML操作的
update v_emp_dept set sal=sal*1.2 where empno=7499; --有关联操作的视图是不能DML操作的
commit;
update v_emp_dept_1 set C3=2000 where dname=‘SALES‘; --有聚合操作的视图不能DML
commit;
(7) 在视图中用函数
创建一个视图，SCOTT下，显示姓名，员工号，薪资，部门名称，薪资等级
要求：薪资等级用函数返回，函数要求传入工资，返回等级
创建一个函数
create or replace function get_sal_grade(v_sal in number) return number is
v number;
begin
select s.grade
into v
from salgrade s
where v_sal between s.losal and s.hisal;
return v;
exception when others then
return 0;
end;
创建一个视图
create or replace view v_emp_salgrd as
select a.ename,a.empno,a.sal,b.dname,get_sal_grade(a.sal) as salgrade
from emp a,dept b
where a.deptno=b.deptno;
确认：select * from v_emp_salgrd;
##########################################################################################
10.2 跨用户用视图
(1) 到HR用户下查询视图v_emp_salgrd
SQL> conn hr/hr
Connected.
SQL> select * from scott.v_emp_salgrd;
select * from scott.v_emp_salgrd
*
ERROR at line 1:
ORA-00942: table or view does not exist --没有查询权限
(2) 授权
SQL> conn scott/scott;
Connected.
SQL> grant select on v_emp_salgrd to hr;
Grant succeeded.
(3)再次查询
SQL> conn hr/hr
Connected.
SQL> select * from scott.v_emp_salgrd; --跨用户查视图、
ENAME EMPNO SAL DNAME SALGRADE
---------- ---------- ---------- -------------- ----------
CLARK 7782 2450 ACCOUNTING 4
KING 7839 5000 ACCOUNTING 5
MILLER 7934 1300 ACCOUNTING 2
JONES 7566 2975 RESEARCH 4
FORD 7902 3000 RESEARCH 4
ADAMS 7876 1100 RESEARCH 1
SMITH 7369 800 RESEARCH 1
SCOTT 7788 3000 RESEARCH 4
WARD 7521 1250 SALES 2
TURNER 7844 1500 SALES 3
ALLEN 7499 1600 SALES 3
JAMES 7900 950 SALES 1
BLAKE 7698 2850 SALES 4
MARTIN 7654 1250 SALES 2
14 rows selected.
思考一个问题
上面的视图里面有函数，我们能够利用视图对函数有查的权限，此时是否对SCOTT用户下函数有执行的权限？？
SQL> select scott.get_sal_grade(3500) from dual;
select scott.get_sal_grade(3500) from dual
*
ERROR at line 1:
ORA-00904: : invalid identifier --没有使用函数的权限
跨用户查询视图，发起者是HR，执行者是SCOTT用户，而不是HR在执行
SQL> conn scott/scott
Connected.
SQL> grant execute on get_sal_grade to hr; --跨用户执行SP、FUN、PKG，必须授予execute on xxxx to yyy
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select scott.get_sal_grade(3500) from dual;
SCOTT.GET_SAL_GRADE(3500)
-------------------------
5
总结：
查询的发起者是HR，但实际执行的用户是SCOTT，因为这个对象源于SCOTT用户
当视图定义中有使用函数的时候，不需要将函数的执行权限授予其他用户，只是将视图使用的权限授予其他用户即可
##########################################################################################
10.2 跨用户截断表--使用借刀杀人解决权限问题
需求：
HR用户要truncate SCOTT下的临时表emp_x,但是仅仅让HR用户处理这个表，SCOTT用户下其他的表是不允许HR去truncate
truncate表的权限：必须要授予 drop any table，但是这个权限太大，不允许这样做的。
drop table -- 仅仅可以删除本用户下的表
drop any table -- 可以删除数据库上所有的表
现象：
SQL> conn hr/hr
Connected.
SQL> truncate table scott.emp_x;
truncate table scott.emp_x
*
ERROR at line 1:
ORA-00942: table or view does not exist --没法进行操作
SQL> conn /as sysdba
Connected.
SQL> grant drop any table to hr; --授权，但是权限太大
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> truncate table scott.emp_x; --这时候可以truncate
Table truncated.
此时：
SQL> conn system/orcl
Connected.
SQL> create table xxx as select * from dba_objects; --为了试验在system系统用户下创建一临时表xxx
Table created.
SQL> conn hr/hr
Connected.
SQL> truncate table system.xxx; --系统用户下的表都可以截断
Table truncated.
所以授权不能满足这要求的
SQL> conn /as sysdba
Connected.
SQL> revoke drop any table from hr; --取消权限
Revoke succeeded.
SQL> truncate table scott.emp_x;
truncate table scott.emp_x
*
ERROR at line 1:
ORA-00942: table or view does not exist
解决方案：
在SCOTT下创建一个存储过程
create or replace procedure truncate_tab is
v_tab varchar2(100);
begin
v_tab:=‘emp_x‘;
execute immediate ‘truncate table scott.‘||v_tab; --在存储过程中调用DDL必须用execute immediate
exception when others then
dbms_output.put_line(sqlerrm);
end;
SQL> conn scott/scott;
Connected.
SQL> set serveroutput on 在sqlplus中执行PL SQL的命名块，存储过程必须把这一项打开
SQL> insert into emp_x select * from emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> exec truncate_tab;
PL/SQL procedure successfully completed.
SQL> select count(1) from emp_x;
COUNT(1)
----------
0
SQL> insert into emp_x select * from emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> grant execute on truncate_tab to hr;
Grant succeeded.
SQL> conn hr/hr;
Connected.
SQL> exec scott.truncate_tab;
PL/SQL procedure successfully completed.
SQL> conn scott/scott;
Connected.
SQL> select count(1) from emp_x;
COUNT(1)
----------
0
借用SCOTT用户下的SP(封装了truncate的功能)，SP的发起者是HR，执行者是SCOTT --借刀杀人
########################################################################################
10.4 通过引用来传递
传递参数有两种方式：
A 传递值
B 传递地址(引用调用，传递的是值在内存中的物理地址) 使用nocopy关键字
案例：
create or replace package Nocopypkg is
type arr is varray(1000000) of hrc_tab%rowtype; --创建一个数组，类型为行变量
procedure p1(ip1 in out arr);
procedure p2(ip2 in out nocopy arr);
function get_time return number;
end Nocopypkg;
T1--dbms_utility.get_time
T2--dbms_utility.get_time
两个时间点的间隔的秒数=(T2-T1)/100;
create or replace package body Nocopypkg is
procedure p1(ip1 in out arr) is
begin
null;
end;
procedure p2(ip2 in out nocopy arr) is
begin
null;
end;
function get_time return number is
begin
return(dbms_utility.get_time);
end;
end Nocopypkg;
调用，检测两种传递方式的效率。
declare
arr1 nocopypkg.arr:=nocopypkg.arr(null);
cur_t1 number;
cur_t2 number;
cur_t3 number;
begin
select * into arr1(1) from hrc_tab where hrc_code=1;
arr1.extend(999999,1); --在数组末处添加999999个索引为1处的元素
cur_t1:=nocopypkg.get_time;
nocopypkg.p1(arr1);
cur_t2:=nocopypkg.get_time;
nocopypkg.p2(arr1);
cur_t3:=nocopypkg.get_time;
dbms_output.put_line(‘without nocopy ‘||to_char((cur_t2-cur_t1)/100));
dbms_output.put_line(‘with nocopy ‘||to_char((cur_t3-cur_t2)/100));
end;
without nocopy .52
with nocopy 0 --传递地址几乎是不需要时间的
不管数据有多大，引用调用的方式几乎是不需要时间的，就是把数组的第一个元素的物理地址，通过指针的方式将物理地址复制到SP的形参上
使用nocopy限制：
A 实参不能是index-by的元素，只能是数组中的记录或者是嵌套表中的记录
B 参数不能是记录，但是可以使数组或嵌套表中存储记录传递
C 传递实参变量不能在前面限制精度、数值范围或者定义为not null也不行
原文：http://fengsonglin.blog.51cto.com/9860507/1615257