PL/SQL 知识整理（下）

declare
--定义一个自己的异常
myexc exception;
m number(10):=&m;
n number(10):=&n;
begin
  if n=0 then
    dbms_output.put_line('除数不能为0');
    raise myexc;--抛出异常  需要处理异常
   end if;
   exception
     when myexc then
      dbms_output.put_line('出错啦！'); 
end;

结果是：

（5）引发应用程序错误

RAISE_APPLICATION_ERROR 过程，错误编号必须在 - 20000和 -20999 之间。

引发应用程序的语法：

RAISE_APPLICATION_ERROR(error_number,error_message);

六、存储过程和函数

存储过程和函数的唯一区别就是函数总向调用者返回数据，而过程则不返回数据。

1、存储过程

（1）创建语法：

create [or replace] procedure p1[(参数 in|out|in out 参数数据类型...)]

is | as

begin

plsql语句；

exception

异常处理;

end;

输入参数用in，输出参数用out，既作输入又作输出参数用 in out 。没有参数就不写括号了。

（2）调用

1、在plsql块中调用

declare

begin

end;

2、call 命令调用 call p1();

（3）删除存储过程

drop procedure p1;

（4）参数的三种模式

out

in out :用于接收传进来的参数值，并返回更新的值。

注：将过程的执行权限授予其他用户

---grant execute on 存储过程名 to 用户名;

如： grant execute on p1 to scott;

2、函数

（1）创建语法：

create [or replace] function f1[(形参参数类型，......)]

return 返回值类型

声明变量;

begin

plsql代码块;

return 返回值;

end;

（2）调用函数的方式

（1）使用sql语句

select 函数名字() from dual;

（2）使用plsql块

七、触发器

在事件发生时隐式地自动执行的pl/sql 块，不能接受参数，不能被显式调用。

1、触发器类型

（1）DML 触发器：其中包含语句级（表级）触发器、行级触发器，instead of （替换）触发器。

（2）DDL（模式触发器）：在模式中执行DDL语句时执行。

（3）数据库级触发器：在发生打开、关闭、登录和退出数据库等操作时执行。

2、创建触发器语法

create [or replace] trigger 触发器名字

before | after 触发器事件 on 表名

[for each row] --行级触发器要写上这行，表级则不用

[when trigger_condition] --添加的触发条件

trigger_body ：触发体，是标准的PL/SQL语句块，即declare begin end;

3、表级触发器（语句级触发器）

创建时没有 for each row这行，（：new，：old 仅限于使用 for each row时使用）

updating 如果触发这个触发器的是update语句，它的值就是true

deleting inserting 都是这样

4、行级触发器

对DML语句修改的每个行都会执行一次（如果没有限制条件的话），有 for each row 语句。在 begin 代码中可以使用 :new 和 :old 。

 如：update dept set dname='财务部' where empno=7369;

这里的 财务部 就是 :new.dname 7369 就是 :old.empno。

5、替换触发器

只能使用在视图上，而且是行级的触发器。

6、模式触发器

在模式中执行DDL语句时执行

DDL：create,truncate（有表结构），drop（没有表结构），alter

7、数据库级触发器

在发生打开、关闭、登录和登出数据库等系统事件时才会执行。

startup 打开 shutdown 关闭 logon 登录 logoff 退出

启用、禁用和删除触发器

禁用 alter trigger 触发器名字 disable;

启用 alter trigger 触发器名字 enable;

删除 drop trigger 触发器名字;

八、程序包

程序包是对相关过程、函数、变量、游标和异常对象的封装，程序包由包头（规范）和包体（主体）两部分组成。

1、创建程序包的语法

包头和包体名字一致

包头语法：

create [or replace] package 名字 is | as

[public item declaration] 公共声明部分

[subprogram specification] 声明 PL/SQL 子程序

end [package_name];

create or replace package pack1 is

end;

包体语法：

create or replace package body 名字 is | as

[private item declarations] 私有声明部分

[subprogram bodies] 子程序体

[begin initialization]

end [包名];

create or replace package body pack1 is

end;

--定义包头(全是声明的部分，具体实现都在包体里)
create or replace package pack1 
is
aa number:=9;
procedure insert_dept(v_dept in dept%rowtype);
function fun(v1 number,v2 number) return number;
end;

--包体
create or replace package body pack1 
is
bb number:=10;
--存储过程的实现部分
procedure insert_dept(v_dept in dept%rowtype)
is
begin
insert into dept values(v_dept.deptno,v_dept.dname,v_dept.loc);
end;
--函数的实现部分
function fun(v1 number,v2 number)
return number
is
begin
return v1+v2;
end;

调用：包名.元素名

declare
n number;
begin
n:=pack1.fun(3,4);
dbms_output.put_line(n);
end;

2、程序包中的游标

程序包里的游标的定义分为游标规范和游标主体两部分

声明游标规范时必须使用 return 子句指定游标的返回类型（return 数据类型）

return 子句指定的数据类型可以是：%rowtype , 程序员定义的记录类型 record ，不可以是 number ， varchar2 ，%type 等类型。

--显式游标(包头)
create or replace package pack2 is
cursor mycursor return emp%rowtype;
procedure mycursor_use;
end;
--（包体）
create or replace package body pack2 is
cursor mycursor return emp%rowtype is select * from emp;
procedure mycursor_use
is
  v_emp emp%rowtype;
    begin
        open mycursor;
        fetch mycursor into v_emp;
        while mycursor%found loop
            dbms_out.put_line(v_emp.ename);
            fetch mycursor into v_emp;
        end loop;
        close mycursor;
    end;
end;

--ref游标  这个游标是用在存储过程里的
--包头
create or replace package pack3 is
  type refcur is ref cursor; --作为公共部分声明在这里，ref不需要写返回值，因为它是动态的
  procedure mycursor_use;
end;
--包体
create or replace package body pack3 is
procedure mycursor_use is
  mycursor refcur;
  v_emp emp%rowtype;
 begin
  open mycursor for select * from emp;  --关键词for
  fetch mycursor into v_emp; 
  while mycursor%found loop 
    dbms_output.put_line(v_emp.ename); 
    fetch mycursor into v_emp;
  end loop;
  close mycursor;
 end;
end;