Oracle（四）权限，procedure，块，function，package

权限

       权限：1.系统权限。 2.对象权限

      

       角色：1.预定义角色。2.自定义角色

              角色本质是多个权限集合，用处是简化权限的管理。角色可以包含系统权限，也可以包含对象权限。

 

系统权限

       指执行特定类型sql命令的权利，常用的有：

       Createsession  连接数据库      create table 建表      create view  建视图

       Createpublic synonym  建同义词       create procedure  建过程，函数，包

       Createtrigger  建触发器        create cluster  建族

使用语法：

grant 权限名 to 用户名;--------------grant create session to scott;

       若最后面with admin option 表示ken 可以把它得到这两个权限，继续向别的用户转发

       grantcreate session to scott with admin option;

注：系统权限在10g中有166个。

 

对象权限

       指访问其他方案对象的权利，比如smith用户要访问scott.emp表

       常用的权限：alter   delete   select  insert   update   index  references(引用)

       Execute(执行)

       例子：希望monkey可以查询scott.emp的表数据

       Grantselect on scott.emp to monkey;

       如果把select替换成all，是所有权限都赋给。

注：对象权限10g中提供了17个

 

角色

（1）    预定义角色：oracle提供了33个预定义角色，

常用connect  resource   dba.

例：把角色分别赋给用户jack

Grant connect tojack;

Grant resourceto jack;

Grant dba tojack;

 

(2) 自定义角色： role

有两种方式，带验证和不带验证

不带验证（常用）：create role 角色名 notidentified;

带验证：create role 角色名 identified by 密码；

例：假定用户需要链接数据库，在scott.emp表上select,insert,update

Create role myrole not identified;

  Grant create sessionto myrole;

  Grant select onscott.emp to myrole;

  Grant update onscott.emp to myrole;

  Grant insert onscott.emp to myrole;

最后把创建好的角色myrole赋给用户

  Grant myrole tojack;

 

Oracle的pl/sql编程

       Pl/sql是在标准sql语句基础上扩展的一种对oracle数据库进行编程的语句

 

    存储过程

           Procedure

              创建过程语法：

                     Createprocedure 过程名（参数1...）

                     Is

                     Begin

                            执行语句；

                     End;

                     /

       注：可不带参数，若带参格式为---（参数名 类型）--（in_empno  number）

      

       创建好过程，需要调用：

              Exec过程名(参数1…);

 

    块

              存储过程，函数，触发器，包。他们的基本编程单元是块。

              块的结构：

              Declare   定义部分---定义常量，变量，游标，例外，复杂数据类型

              Begin     执行部分---要执行的pl/sql语句和sql语句

              Exception  例外处理部分---处理运行的各种错误

              End;

       注：Declare和exception部分是可选，并不是必须的。

 

       编写规范

              单行注释–

              多行注释/*  */

1.    当定义变量时，建议用v_作为前缀v_sal

2.    当定义常量时，建议用c_作为前缀c_rate

3.    当定义游标时，建议用_cursor作为后缀emp_cursor

4.    当定义例外时，建议用e_作为前缀e_error

 

Dbms_output是oracle所提供的包，该包包含一些过程，put_line就是dbms_output包的一个过程。

 

1.    只包含执行部分的案例：

输出’hello’

Begin

Dbms_output.put_line(‘hello,world’);

End;

说明：在默认情况下，‘hello world’不输出，需要setserveroutput on;

2.

       案例：根据用户输入的雇员编号，显示该雇员的名字

       说明：&表示要接收从控制台输入的变量

               || 表示把两个串拼接

             

       Declare

             --定义变量的格式是：变量名称 变量的类型

             v_enamevarchar2(8);

      begin

              --into 变量名   把查询的ename值放入v_ename变量

             selectename into v_ename form emp where empno=&empno;

             --输出v_ename

             Dbms_output.put_line(‘雇员名是’||v_name);

       End;

       /

把上面的块，改为procedure

       Createprocedure pro1(in_empno number) is

       v_enamevarchar2(8);

       begin

       selectename into v_ename from emp where empno=in_empno;

       Dbms_output.put_line(‘雇员名是’||v_name);

       End;

       /

      

       执行：exec pro1(7369);

 

 

包含定义部分，执行部分和例外处理部分的块

1.    比如在实例2中，如果输入了不存在的雇员好，应当做例外处理

如果我们输入的用户编号不存在，则系统会提示异常，为了更加明确的指出错误，oracle提供了exception处理机制

快速入门：

Declare

v_ename varchar2(36)

begin

select ename into v_ename from emp where empno=&empno;

dbms_output.put_line(‘雇员名’||v_ename);

exception

when no_data_found then

dbms_output.put_line(‘你输入的编号有误！’);

end;

 

对该案例的细节说明：

这里涉及到底异常处理：

异常的基本语法：

 

Exception

When 异常的名称 then

//对异常进行处理的代码

//对异常进行处理的代码

              When异常的名称2 then

              //对这种异常处理

              end;

             

              注：then 后面都属于处理，可以有添加删除等等操作。

 

              oracle提供的异常（参考pl/sql官方文档）

             

              异常处理作用：

1.    可以给出捕获异常，并给出明确提示。

2.    有时可以利用异常，来进行业务处理

 

Declare

v_ename varchar2(36)

begin

select ename into v_ename from emp whereempno=&empno;

dbms_output.put_line(‘雇员名’||v_ename);

exception

when no_data_found then

dbms_output.put_line(‘你输入的编号有误！我帮你加一条’);

insert into emp (empno,ename) values(1.’马大哈’)；

end;

       过程的进一步讲解：

              Oracle过程，可以指定参数是输入的参数，还是输出的参数。

              语法：create procedure 过程名(参数名 in 参数类型……参数名 out 参数类型……)is

              //定义变量

              Begin

              //执行的语句

              End;

       注：变量可以有多个，默认是in

 

       当编写过程时，可以输入show error 来显示具体的错误

 

函数

    Function

函数和过程的区别:

函数必须有返回值(return)，而过程可以没有。

 

       建立函数时，在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。可以使用create function 来建立函数。

       语法：

       Createfunction 函数名(参数1…)

       Return返回的数据类型 is

       --定义变量

       Begin

       --执行语句

       Return变量名                   //把变量返回

       End;

注：观察和过程的不同，在参数后面多一句return。

 

       快速入门：

       案例：请编写一个函数，可以接收用户名并返回该用户的年薪。

       Createor replace function fun1(in_v_ename varchar2)

       Returnnumber is

       --定义一个变量，来接收年薪

       V_annual_salnumber;

       Begin

       Select(sal+nvl(comm,0))*13 into v_annual_sal from emp where ename=in_v_ename;

       Returnv_annual_sal;

       End;

       /

 

    调用函数

       Select函数名(实际参数) from dual;  

注：因为函数体里已经定义了查询哪个表，所以from后面写dual虚表就可以了。

若写emp,相当于查询很多次，会返回很多次结果。

       Selectfun1(‘SMITH’) from emp;           //函数方法的使用就和MAX,MIN一样了。

 

包

       Package

       包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。（像是java中的接口）

       包体用于实现包规范中的过程和函数。

      

包规范

基本语法：

       Createpackage 包名 is

              --声明过程

              Procedure过程名(参数名 参数类型...);

              --声明函数

              Function函数名(参数名 参数类型..) return 数据类型；

       End;

 

注：可以把包规范想象为定义接口，而包体是实现接口

       包的快速入门：

       案例：编写一个包，该包有一个过程，该过程可以接收用户名和新的薪水，（将来用于通过用户名去更新薪水）还有一个函数，该函数可以接收一个用户名，（将来要实现得到用户的年薪是多少）。

       Createor replace package mypackage1 is

       --声明第一个过程

       Procedurepro1(v_in_ename varchar2,v_in_newsal number);

       --声明一个函数

       Functionfun1(v_in_ename varchar2) return number;

       End;

 

包体  create packagebody

       Create or replace package body 包名 is

       --实现过程

       Procedure 过程名(参数列表…) is

       --定义变量

       Begin

       --执行语句

       End;

      

       --实现过程

       Create function 函数名(参数列表…) return 数据类型 is

       --定义变量

       Begin

       --执行

       End;

       End;

 

       注：每个end是各自匹配的。

 

       快速入门：

       把前面包中的声明的函数和过程实现。

       Create ot replace package body mypackeageis

       --实现过程

       Procedure pro1(v_in_enamevarchar2,v_in_newsal number) is

       Begin

       Update emp set sal=v_in_newsal whereename=v_in_ename;

       End;

       --实现函数

       Function fun1(v_in_ename varchar2) returnnumber is

       V_annual_sal number;

       Begin

       Select (sal+nvl(comm,0))*13 intov_annual_sal from emp where ename=v_in_ename;

       Return v_annual_sal;

       End;

       End;

 

注：包体中要实现的方法或者过程，应当先在包中声明才可以

调用包的过程或函数：

exec 方案名.包名.过程名或函数名(参数…)

       当调用包的过程或者函数时，在过程和函数前需要带包名，如果要访问其它方案的包，需要在包名前加方案名。

       例： Exec scott.mypackage.pro1(参数…);或exec scott.mypackage.fun1(参数…);

 

 

初识触发器

       触发器是一个隐含执行的过程。它不是由程序员或者dba来显示调用，而是因为某个操作引发执行的。