一。异常

1.定义

pl/sql在执行过程中所产生的标识符。

2.怎么产生的

出现Oracle错误 使用raise语句，来明确产生

3.产生异常怎么办

使用处理句柄来捕捉他 向调用时的环境传递，会污染内存空间

4.异常类型

Oracle预定义异常 非Oracle预定义异常 用户定义异常

①Oracle预定义异常：

no_data_found too_many_rows invalid cursor zero divide dup_val_on_index

例子：追踪错误代码-2292.

declare e_emps_remaining exception; pragma exception_init( e_emps_remaining , -2292); v_deptno dept.deptno%type :=&p_deptno; begin delete from dept where deptno = v_deptno; commit; exception when e_emps_remaining then dbms_output.put_line( '不能删除' || v_deptno); end;

②用户自定义异常

declare e_invalid_product exception; begin update product set descrip ='&product_descripion' where prodid = &product_number; if sql%notfound then raise e_invalid_product ; end if; commit; exception when e_invalid_product then dbms_output.put_line('无效的prodid！'); end;

5.追踪异常的函数

-sqlcode 返回错误代码的数字值。 -sqlerrm 返回与错误代码相联系的错误信息。

例子：

declare v_error_code number(3); v_error_message varchar2(10); begin ... exception ... when others then rollback; v_error_code :=sqlcode; v_error_message :=sqlerrm; insert into error values(v_error_code , v_error_message ); end;

6.调用环境和异常处理方式

调用环境处理方式SQL plus在屏幕上显示错误代码和错误信息procedure builder在屏幕上显示错误代码和错误信息Oracle developer forms在一个触发器中，使用函数error_code和error_text来存取错误代码和信息预编译的应用程序使用sqlca数据结构来存取异常的号码嵌套的pl/sql块在嵌套块的异常处理程序中处理异常

7.raise_application_error过程

①语法： raise_application_error( error_number , message [,{true | false}]);

②说明 这是一个很有用的过程，可以使用户在存储子过程中使用这个过程，以处理用户自定义错误。 只能从一个可执行的存储子过程中调用。

③在哪里使用： 可执行代码部分 异常处理部分

8练习

①写一个pl/sql块，让用户输入一个工资，并做如下异常处理： a.如果返回的信息多于一行，则引发异常。将异常信息“查询记录超过一行了！”打印在屏幕上。 b.如果返回的信息为0行，则引发异常。将异常信息“没有信息查询出来！”打印在屏幕上。 c.如果信息为一行，则打印出雇员的名字。

declare e_more_one exception; e_zero exception; v_name varchar2(10); v_sal number; v_count number; begin v_sal :=&emplyee_salary; select count(*) into v_count from emp where sal=v_sal; if v_count =0 then raise e_zero; elsif v_count >1 then raise e_more_one ; elsif v_count = 1 then select ename into v_name from emp where sal=v_sal; dbms_output.put_line( ' 雇员的姓名：' || v_name ); end if; exception when e_zero then dbms_output.put_line('没有信息查询出来！'); when e_more_one then dbms_output.put_line('查询记录超过一行了！'); end;

②用raise_application_error过程，重新定义异常错误消息。

create or replace function get_salary (p_deptno number) return number as v_sal number; begin if p_deptno is null then raise_application_error( -20991 , ' 部门号是空！' ); elsif p_deptno < 0 then raise_application_error( -20992, '部门号不存在！ ' ); else select sum(sal) into v_sal from emp where deptno=p_deptno; return v_sal; end if; end;

二。存储过程

1.几个概念：

①子程序：pl/sql的过程和函数统称为子程序。 ②匿名块：以declare和begin开始，每次提交都被编译； 匿名块不在数据库中存储并且不能直接从其他pl/sql块中调用。 ③命名块：除匿名块的其它快。包括过程、函数、触发器和包。 可以在数据库中存储，并在适当的时候运行。

2.创建：

①记录用户登录信息：

create or replace procedure loginInfo is begin insert into login_table(loginId, logdate) values (user, sysdate); end; exec loginInfo;

②根据删除表中一条记录。

create or replace procedure DelEmp( v_empno in emp.empno%type ) as no_result exception; begin delete from emp where empno = v_empno; if sql%notfound then raise no_result; end if; dbms_output.put_line( v_empno || '被删除！'); exception when no_result then dbms_output.put_line('没找到结果！'); when others then dbms_oupt.put_line('删除失败！'); end DelEmp;

三。函数

1.创建

create or replace function get_salary( dept_no number,v_num out number) return number is v_sum number; begin select sum(sal), count(*) into v_sum, v_num from emp where deptno=dept_no; exception when no_data_found then dbms_output.put_line('你需要的数据不存在！'); when too_many_rows then dbms_output.put_line('程序运行错误，请使用游标！'); when others then dbms_output.put_line('发生其他错误！'); end get_salary;

四。触发器

1.说明

触发器在数据库中以独立的对象存储。 与存储过程不同的是，存储过程通过其他程序启动运行或直接启动运行。而触发器是由一个事件来启动运行。 即触发器是当某个事件发生时自动的隐式运行，而且触发器不能接收参数。

补充： Oracle事件：指的是对数据库的表进行insert、update、delete，或对视图进行的类似操作。

2.触发器类型

①DML触发器 Oracle可以在DML语句执行前、执行时、执行后进行触发，而且可以对每个行或语句上进行触发。 ②替代触发器 由于在Oracle里，不能直接对由两个以上的表建立的视图进行操作。所以替代触发器就是专门为视图设计的。 ③系统触发器 可以在Oracle数据库系统的事件中进行触发，如Oracle系统的启动和关闭。

3.触发器的组成

触发事件：即在何种情况下触发trigger；例如，insert、update、delete 触发时间：即该trigger是在触发事件之前触发还是在触发事件之后触发。 触发器本身：即该trigger被触发后的目的和意图，例如pl/sql块。 触发类型：说明触发器内定义的动作被执行的次数。 即语句级(statement)触发器和行级(row)触发器。 语句级触发器：是指当某触发事件发生时，该触发器只执行一次。 行级触发器：当某触发事件触发时，对受到该操作影响的每一行数据，触发器都单独执行一次。

4.例子

①建立一个触发器，当职工表emp被删除一条记录时，把被删除的记录写入到删除日志表中。

create or replace trigger del_emp before delete or update on lee.emp for each row begin insert into emp_his( empno, ename, job, mgr, hiredate, sal, comm, deptno) values (:old.empno, :old.ename, :old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm, :old.deptno); end;

注意：rollback后emp表被删除的数据会恢复，emp_his表被增加的数据也会消失。

②创建替代触发器执行delete操作

先创建一个视图：

create or replace view emp_view as select deptno, count(*) "总人数" ,sum(sal) "总工资" from emp group by deptno;

创建触发器：

create or replace trigger emp_view_delete instead of delete on emp_view for each row begin delete from emp where deptno=:old.deptno; end emp_view_delete;

测试：

delete from emp_view where deptno=10;

③创建系统触发器： login事件触发器：

create or replace trigger login_his alter login on database begin insert into log_table values( user , sysdate); end;

五。包

1.定义：

包是由存储在一起的相关对象组成的pl/sql结构。 包只能被存储，而且不能是局部。

2.组成：

包由两个独立的部分：说明和包体。这两个部分独立的存储在数据字典中。

包的说明：也叫包头。包含了有关包的内容的信息，该部门不包含任何子程序。

3.创建：

包头：

create or replace package demo_pack is DeptRec dept%rowtype; V_sqlcode number; V_sqlerr varchar2(2048); function add_dept(dept_no number, dept_name varchar2, location varchar2) return number; function remove_dept(dept_no number) return number; procedure query_dept(dept_no in number); end demo_pack;

包体：

create or replace package body demo_pack is flag integer; function check_dept( dept_no number) return integer; function add_dept( dept_no number, dept_name varchar2, location varchar2 ) return number is begin if check_dept(dept_no)=0 then insert into dept values(dept_no, dept_name, location); return 1; else return 0; end if; exception when others then V_sqlcode :=sqlcode; V_sqlerr :=sqlerrm; return -1; end add_dept; function remove_dept(dept_no number) return number is begin V_sqlcode:=0; V_sqlerr:=null; if check_dept(dept_no)=1 then delete from dept where deptno=dept_no; return 1; else return 0; end if; exception when others then V_sqlcode:=sqlcode; V_sqlerr:=sqlerrm; return -1; end remove_dept; procedure query_dept( dept_no in number) is begin if check_dept(dept_no)=1 then select * into DeptRec from dept where deptno=dept_no; dbms_output.put_line( demo_pack.DeptRec.deptno || '-----' || demo_pack.DeptRec.dname || '-----' || demo_pack.DeptRec.loc ); else dbms_output.put_line('部门没找到！'); end if; end query_dept; function check_dept(dept_no number) return integer is begin select count(*) into flag from dept where deptno = dept_no; if flag>0 then flag:=1; end if; return flag; end check_dept; begin V_sqlcode:=null; V_sqlerr:=null; end demo_pack;

测试：

declare Var number; begin Var := demo_pack.add_dept( 90, 'Administration', 'BeiJing'); if Var=-1 then dbms_output.put_line(demo_pack.V_sqlerr); elsif Var=0 then dbms_output.put_line('记录已存在！'); else dbms_output.put_line('记录添加成功！'); demo_pack.query_dept(90); Var :=demo_pack.remove_dept(90); if Var = -1 then dbms_output.put_line(demo_pack.V_sqlerr); else dbms_output.put_line('删除成功！'); end if; end if; end;

4.查看用户有哪些包？

select distinct name, type from user_source;

5.查看包的内容：

select text from user_source where name='DEMO_PACK';