plsql视图

ewrQ

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也不行