Oracle数据库学习笔记之异常处理，存储过程

异常（例外）:是plsql程序设计语言提供的一种功能，用来增强程序的健壮性和容错性
1.系统例外：
 No_data_found --没有找到数据
 Too_many_rows --select...into语句匹配多个行
 Zero_Divide   --被零除
 Value_error   --算数或转换错误
 Timeout_on_resource  --在等待资源时发生超时


例
declare 
 empname emp.ename%type;
begin
 select ename into empname from emp where empno=1234;
 exception 
 when No_data_found then 
  dbms_output.put_line('没有找到数据');
 when others then
  dbms_output.put_line('其他例外');
end;


2.自定义例外（当成一个变量来定义，类型是exception）
--查询50号部门的员工姓名(定义一个没有找到emp中的数据的例外)
declare  
 cursor cemp is select ename from emp where deptno=50;
 empname emp.ename%type;
 no_emp_found exception;
begin
 open cemp;
  fetch cemp into empname;
  if(cemp%notfound) then raise no_emp_found;
  end if;
  exception 
   when no_emp_found then 
   dbms_output.put_line('没有找到员工');
   when others then
   dbms_output.put_line('其他例外');
--关闭光标，oracle自动启动pmom(process monitor 进程监视器)来关闭光标
 close cemp;
end;


存储过程和函数定义：以命名的形式存放在数据库中
好处：方便查找和使用，可以重复利用，可以传递参数，使用灵活，安全性高
存储过程分类：1.无参数存储过程 2.有参数存储过程
创建无参数存储过程
create [or replace] procedure 存储过程名 is/as 声明部分 
begin
操作;
end;
[or replace]:存在存储过程，重新创建覆盖之前的  


调用存储过程
begin
  存储过程名;
end;


例子：
编写显示员工信息的emp_list(empno,ename,sal)
create or replace procedure emp_list
as 
 cursor cur_info is select empno,ename,sal from emp;
begin
 for emp_info in cur_info  loop
 dbms_output.put_line(emp_info.empno||emp_info.ename||emp_info.sal);
 end loop;
end;
begin
  emp_list;
end;


或者
create or replace procedure emp_list 
as
 cursor cemp is select empno,ename,sal from emp;
 cempno emp.empno%type;
 cename emp.ename%type;
 csal   emp.sal%type;
begin
 open cemp;
  loop
   fetch cemp into cempno,cename,csal;
   exit when cemp%notfound;
   dbms_output.put_line(cempno||','||cename||','||csal);
  end loop;
 close cemp;
end;
 
begin 
 emp_list;
end;


2.创建输入参数存储过程--带参方法
create or replace procedure 名(参数名 [in] 数据类型,....) as 声明部分;...;
begin 
操作
end;
调用存过程
begin
名(实参);
名(形式参数名=>实际参数)
end;
注：in 定义输入参数时不写表示默认
给存储过程传入参数时，实参与形参个数，顺序，类型一致
形式参数可以有default默认值,不传入实际参数 形式参数的值=默认值;


例子：
create or replace procedure change_sal(u_no in number default 7788,u_sal in number default 500)
as
 u_name varchar2(20);
 u_upsal number;
begin
 select ename,sal into u_name,u_upsal from emp where empno=u_no;
 update emp set sal=u_upsal+u_sal where empno=u_no;
 dbms_output.put_line('员工'||u_name||'增长后的工资'||to_char(u_upsal+u_sal));
end; 
begin
  change_sal();
  --change_sal(u_no=>7788,u_sal=>1000);
end;


输出参数的存储过程--方法返回值
create or replace procedure(参数名 out 数据类型,...) as 声明;
调用输出参数存储过程
begin 
存储过程名(变量);
--变量可以接收存储过程的结果
end;
例
create or replace procedure test_paramout(v_name OUT VARCHAR2 )
as  
begin
 select name into v_name from TESTTABLE where id1='1';
 DBMS_OUTPUT.put_line('name：'||v_name);
end;
----执行存储过程
DECLARE  
 v_name VARCHAR2(32);   
BEGIN  
 test_paramout(v_name);
 DBMS_OUTPUT.PUT_LINE('name：'||v_name);   
END;


输入输出参数的存储过程
create or replace procedure 名(变量 in out 数据类型) as;
 in out:即可以当作输入参数也可以当作输出参数接收结果;

例子：
create or replace procedure change_num(numb in out varchar2)
as
begin
  numb:='024-'||numb;
end;


declare
  numb varchar2(20):='123456';
begin
   change_num(numb);
   dbms_output.put_line(numb);
end;


删除存储过程
drop procedure 存储过程名;