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

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 then
  dbms_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 then
  raise no_result;
 end if;
exception
 when no_result then
  dbms_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 then
  raise_application_error(-20000,'没有成功更新数据');
 end if;
exception
 when others then
  if sqlcode=-20000 then
  dbms_output.put_line('xxxxx');
 end if;
end;
2.存储过程（重点）
概念：是大型数据库系统中，一组为了完成特定功能的sql语句集，将其存储在数据库中，经过第一次编译后可多次调用。
怎么使用？用户通过指定存储过程的名字并给出参数（）来执行。
1.最简单的存储过程：

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

创建存储过程：

 begin
  insert 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 then
  raise e_null;
 elsif spno<0 then
  raise e_error;
 else
  select nvl2(comm,sal+comm,sal)*12 into v_sumsal from emp where empno=spno;
  return v_sumsal;
 end if;
exception
 when e_null then
  dbms_output.put_line('员工的工号不能为空!');
  return 0;
 when e_error then
  dbms_output.put_line('员工的工号不能为负数!');
  return -1;
 when no_data_found then
  dbms_output.put_line('员工的工号不存在!');
  return -2;
 when others then
  dbms_output.put_line(sqlcode||'----'||sqlerrm);
  return -3;
end;

declare
 v_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 then
  dbms_output.put_line('insert 了emp表');
 elsif updating then
  dbms_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 then
  dbms_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('星期六','星期日') or
  to_char(sysdate,'hh24') not between '09' and '12' then
  raise_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 then
  raise_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;