Oracle之异常/存储过程/函数/触发器

2017-10-19 10:18:40来源:oschina作者:wf78728381人点击

分享
1.异常 1.预定义异常:oracle定义了上千个异常,但只有21个最常见的异常,这些异常都规定好了名字,这些有名字的异常叫预定义异常。 语法: exception when 异常名 then ... when 异常名 then ... when others then .... sqlcode:返回当前遇到 的oracle错误号 sqlerrm:返回当前遇到的oracle错误号对应的错误信息 no_data_found:没有返回行数据 zero_divide:除数为0 too_many_rows:返回了多行数据 access_into_null:为一个未初始化的对象的属性赋值 示例: declare v_ename varchar(20); begin select ename into v_ename from emp where empno=8790; dbms_output.put_line('........'); exception when no_data_found then dbms_output.put_line('没查到数据'); dbms_output.put_line(sqlcode||'---'||sqlerrm); when others then dbms_output.put_line('未知错误'); end; 2.非预定义异常:只有错误编码号和错误描述,但该异常没名字。 exception when others then if sqlcode=-1843 then ... end if; 使用步骤: 1.定义异常:异常名 exception; 2.将定义好的异常名与错误号关联起来:pragma exception_init(异常名,错误码); 示例:declare my_exc exception; pragma exception_init(my_exc,-2292);begin delete class; exception when my_exc thendbms_output.put_line('学生表引用了class表'); dbms_output.put_line(sqlcode||'--'||sqlerrm); end; 3.自定义异常:不是oracle的标准错误,是业务逻辑错误。 使用步骤: 1.定义异常:异常名 exception; 2.在适当时候抛出异常:raise 异常名; 3.在exception中对抛出的异常进行处理 示例:修改指定员式的工资 declare no_result exception; v_empno emp.empno%type:=&no; begin update emp set sal=sal+500 where empno=v_empno; if sql%notfound thenraise no_result; end if; exception when no_result thendbms_output.put_line('没有更新');dbms_output.put_line(sqlcode||'--'||sqlerrm); end; 自定义异常的另一种用法: raise_application_error(错误号,错误信息);允许使用的错误号范围:(-20000)---(-20999) declare v_empno emp.empno%type:=&no; begin update emp set sal=sal+500 where empno=v_empno; if sql%notfound thenraise_application_error(-20000,'没有成功更新数据'); end if; exception when others thenif sqlcode=-20000 thendbms_output.put_line('xxxxx'); end if; end; 2.存储过程(重点) 概念:是大型数据库系统中,一组为了完成特定功能的sql语句集,将其存储在数据库中,经过第一次编译后可多次调用。 怎么使用?用户通过指定存储过程的名字并给出参数()来执行。 1.最简单的存储过程:

create or replace procedure p3 is--or replace可选,表示如果p3存在就替换


创建存储过程:


begininsert into emp (empno,ename)values(007,'007a'), end; 执行存储过程: exec p3;或call p3(); 查询存储过程的错误: show error; 创建存储过程语法:

create [or replace] procedure


过程名 (arg1 [in|out|in out] datatype,...) is|as


变量的定义...


num number(3); ... begin .... [exception] end;示例1:查询指定工号的姓名 create or replace procedure p3(num number) is name varchar(8); begin num:=100; select ename into name from emp where empno=num; dbms_output.put_line('员工的工号:'||name); end; 执行:exec p3(111);或call p3(111); 注意:过程的参数如果没有in,out,in out修饰,默认是in,in相当于java中的final修饰 create or replace procedure p3(num in number,name in varchar2,tmp out number) is begin tmp:=100; insert into emp(empno,ename)values(num,name); dbms_output.put_line('出参值:'||tmp); end; 如果有出参,要放在匿名块或其他存储过程中使用。 declare out_para number(3); begin p3(9,'a9',out_para); dbms_output.put_line('被改变后的值:'||out_para); end; 三种参数的特点: in参数:由调用者传入,并且只能被过程读取,不能被修改; out参数:由过程传出给调用者,在执行过程中该参数将被过程修改 in out:同时具有in和out参数的特点3.函数: 创建函数语法:create [or replace] function 函数名(arg1 {in|out|in out}datetype,....) return datatype; //函数的返回值类型(必须声明) is|as 变量的定义...; bein ... return exception; exception ... end; 示例:根据员工工号,查询出该员工一年的总收入(包括奖金) create or replace function get_incom(spno in number)return number is v_sumsal number(7,2); e_null exception; e_error exception; begin if spno is null thenraise e_null; elsif spno<0 thenraise e_error; elseselect nvl2(comm,sal+comm,sal)*12 into v_sumsal from emp where empno=spno;return v_sumsal; end if; exception when e_null thendbms_output.put_line('员工的工号不能为空!');return 0; when e_error thendbms_output.put_line('员工的工号不能为负数!');return -1; when no_data_found thendbms_output.put_line('员工的工号不存在!');return -2; when others thendbms_output.put_line(sqlcode||'----'||sqlerrm);return -3; end;declarev_sumsal number(7,2); begin v_sumsal:=get_incom(7369); dbms_output.put_line('总收入:'||v_sumsal); end;函数与存储过程区别: 1.过程没有返回值,而且不能通过sql语句直接适用,只能通过exec或call或在块中使用。 2.函数必须有返回值,可作为sql或块的表达式的一部分使用,函数的返回值类型在创建函数时使用。 4.触发器(trigger):在做A(insert,update,delete)这件事时,会自动引起B事件(触发器中定义的事件)的发生。(重点) 触发的对象:table,view,database; 触发的频率:行级触发或语句级触发(对象触发) 触发的时间:在A事件之前触发还是在之后触发 1.创建触发器语法: create[or replace] trigger 触发器名 {before|after} {insert|upadte|delete[ of col1[,col2...]]} on 表名 [for each row] begin ... end; 2.条件谓谓词:inserting,updating,deleting 3.修饰符:new,old;(使用 :new|old.字段名) 示例: create or replace trigger t1 after insert on emp begin dbms_output.put_line('修改了emp表'); end; 练习1:当对emp修改(insert,update,delete)后,都会在dept表中增加一条记录,只增加deptno,dname要用序列插入deptno create or replace trigger t1 after insert or update or delete on emp for each row begin if inserting thendbms_output.put_line('insert 了emp表'); elsif updating thendbms_output.put_line('update 了emp表'); else dbms_output.put_line('delete 了emp表'); end if; insert into dept (deptno,dname)values(s2.nextval,'a'||s2.currval); end; 示例: create or replace trigger t1 before insert on emp for each row begin if inserting thendbms_output.put_line('new value:'||:new.ename||'--工号:'||:new.empno||'--旧值:'||:old.ename); end if; end; 练习2:显示增删改的新旧值。 创建一个触发器,当你删除员工表中的数据时,要求把删除的数据写入到另一张表中(emp_log)create table emp_log as select * from emp where 1=2; create or replace trigger t1 before delete on emp for each row begin insert into emp_log (empno,ename)values(:old.empno,:old.ename); end; 练习3:创建触发器,限制对dept表进行dml操作,具体如下: 只可在09:00~12:00修改表,而且还必须是周一到周五的工作日 create or replace trigger t1 before insert or update or delete on dept begin if to_char(sysdate,'day') in('星期六','星期日') orto_char(sysdate,'hh24') not between '09' and '12' thenraise_application_error(-20001,'不可以在不正确的时间修改数据'); end if; end; 练习4:修改emp表的员工工资,并输出修改前和修改后的工资,同时要确保新工资不能比旧工资低。 create or replace trigger t1 before update of sal on emp for each row begin if :old.sal>:new.sal thenraise_application_error(-20003,'工资 不能比之前低'); end if; end;4.系统触发器 create trigger 名字 { before|after} 系统事件 on database begin ... end; logon after:用户连接事件 logoff before:用户退出事件create table user_log(user_name varchar(20),address varchar2(20),log_date date,logoff_date date); create or replace trigger t1 after logon on database begin insert into user_log(user_name,address,log_date)values(ora_login_user,ora_client_ip_address,systimestamp); end; 5.包:主要用来管理存储过程,或函数等 创建包的语法: 1.包说明 create [or replace] package 包名 is|as <函数或存储过程,变量,游标,异常,数据类型的声明> end; 2.包体 create[or replace] package body 包名 is <函数或存储过程的定义> end; 练习:编写一个包,该包中有一个过程可以接收用户名和新的sal,将来用户可通过该用户名更新工资,有一个函数,该函数可接收一个name,将来要通过name得到该员工的年薪。 create or replace package pack1 is--声明一个过程 procedure pro1(en varchar2,newsal number); function f1(en varchar2) return number; end;create or replace package body pack1 is procedure pro1(en varchar2,newsal number) is begin update emp set sal=newsal where ename=en; end;function f1(en varchar2)return number is sumsal number; begin --select nvl2(comm,sal+comm,sal) select (sal+nvl(comm,0))*12 into sumsal from emp where ename=en; return sumsal; end; end;declare v number; begin v:=pack1.f1('SMITH'); dbms_output.put_line(v); end;

微信扫一扫

第七城市微信公众平台