oracle 存储过程的基本用法

yuyjrzhtxu

　　基本结构
　　CREATE OR REPLACE PROCEDURE 名称
　　(
　　参数1 IN NUMBER,
　　参数2 OUT NUMBER,
　　参数3 IN OUT NUMBER
　　.....
　　) IS (AS)
　　变量1 VARCHAR2(50);
　　变量2 INTEGER :=0;
　　.....
　　BEGIN
　　dosomething...
　　END 名称;
　　一些用法
　　1.SELECT INTO STATEMENT (返回一条记录赋值给一个或多个变量)
　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
　　例子:
　　BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN 
dosomething... 
　　END;
　　2.IF 判断
　　例子:
　　IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
3.WHILE 循环
　　例子:
　　WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
　　4.FOR 循环
　　例子:
　　FOR I IN 1..100 LOOP
　　do something.
　　END LOOP;
　　5.变量赋值
　　V_TEST :=0;
　　6.使用游标(返回多条记录的结果集)
　　例子1(使用fetch...into...)
　　BEGIN
　　OPEN cur FOR SELECT * FROM EMP;
　　LOOP
　　FETCH cur INTO cur_result ;
　　do something;
　　END LOOP;
　　CLOSE cur;
　　END;
　　例子2(使用for...in...)
　　....
　　IS
　　Cursor cur is select * from emp;
　　v_emp EMP%ROWTYPE;
　　BEGIN
　　 FOR v_emp IN cur loop
　　 do someting;
　　end loop;
　　END;
　　7.有无返回值,看看传入的参数是否有IN,OUT,无OUT无返回值,反之，亦然.
　　8.IS 后面定义的不能加入IN,OUT 关键字
　　9.游标属性,cur%found,cur%notFound,cur%rowcount=>下一行结果集存在，下一行结果集不存在,行数
　　10.类型属性,table.field%type,table%rowType
　　11.异常(内部异常和用户异常)
　　常见的异常:
　　no_data_found(select into 语句没有符合条件的记录返回)
too_many_rows(select into 语句符合条件的记录有多条返回)
dup_val_on_index(对于数据库表中的某一列，该列已经被限制为唯一索引，程序试图存储两个重复的值)
value_error(在转换字符类型，截取或长度受限时，会发生该异常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该异常)
storage_error(内存溢出)
zero_divide(除数为零)
case_not_found(对于选择case语句，没有与之相匹配的条件，同时，也没有else语句捕获其他的条件)
cursor_already_open( 程序试图打开一个已经打开的游标 )
timeout_on_resource( 系统在等待某一资源，时间超时 )
　　others (其他未命名的异常)
　　例子:
　　BEGIN
　　do something
　　EXCEPTION
　　when not_data_found then null;
　　when others then exit;
　　END;
　　12.抛出异常
　　通过PL/SQL运行时引擎 　　
使用RAISE语句
调用RAISE_APPLICATION_ERROR存储过程
　　例子:(使用RAISE语句)
　　BEGIN
IF order_rec.qty>inventory_rec.qty THEN
RAISE inventory_too_low;
END IF ;
EXCEPTION
WHEN inventory_too_low THEN
order_rec.staus:='backordered';
END;
　　 一些对应的例子
　　  -- 存储过程测试1(赋值变量)
  create or replace procedure mytest1(t  in varchar2,
                                      t2 out varchar2,
                                      t3 out varchar2) is
begin
  select ename, job into t2, t3 from emp p where p.empno = t;
  dbms_output.put_line('ok');
exception
  when others then
    rollback;
end mytest1;
　　  -- 存储过程测试2(判断条件)
  create or replace procedure mytest2(x in number, y out number) is
begin
  if (x > 0) then
begin
  y := 10;
end;
end if; if x = 0 then
begin
  y := 5
end;
end if;
end mytest2;
　　  -- 存储过程测试3(while 循环条件)
  create or replace procedure mytest3(i in number, j out number) is
begin
  while i < 10 loop
begin
  i := i + 1; j := i;
end;
end loop;
end mytest3;
　　  -- 存储过程测试4(for 循环条件)
  create or replace procedure mytest4() as
  cur Cursor is
  select ename from emp; myname varchar2(100);
begin
  for myname in cur loop
begin
  dbms_output.put_line(myname);
end;
end loop;
end mytest4;
　　  -- 游标的使用(Cursor型游标,SYS_REFCURSOR型游标)
  create or replace procedure mytest5() is
  cur1 Cursor is
  select ename from emp where empno = ''; --Cursor的使用方式1
  cur2 Cursor;
begin
  select xxxx into cur2 from tablename where xx = ''; --Cursor的使用方式2
end mytest5;
　　  create or replace procedure mytest52(rsCursor out sys_refcursor) as
  cur sys_refcursor; myname varchar(20);
begin
  open cur for select ename from emp where ''; --SYS_REFCURSOR只能通过OPEN方法来打开和赋值
  loop fetch cur into myname
--SYS_REFCURSOR只能通过fetch into来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR中可使用三个状态属性：
  ---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息) ---%ROWCOUNT(然后当前游标所指向的行位置)
  dbms_output.put_line(myname);
end loop; rsCursor := cur;
end mytest52;