异常、存储过程、触发器、函数、包_procedure delemp 出现错误:-优快云博客

本文围绕Oracle数据库展开，详细介绍了异常的定义、产生原因、处理方式、类型及追踪函数等；阐述了存储过程、函数的创建；说明了触发器的特点、类型、组成并给出实例；还介绍了包的定义、组成、创建及查看方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一。异常

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';