oracle 存储过程的基本用法

基本结构

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;