Oracle 存储过程 使用示例

因为工作的需要，最近一直在写存储过程。工作了3年，一直都是做管理，也没有正儿八经的去写过存储过程，这次正好可以好好练习一下。

在这里说一条使用存储过程很重要的理由：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

Oracle存储过程定义和优点与函数区别

http://blog.csdn.net/xujinyang/article/details/6830565

Oracle查看表存储过程触发器函数等对象定义语句的方法

http://blog.csdn.net/xujinyang/article/details/6829361

1.存储过程格式

/* Formatted on 2011/1/17 13:20:44 (QP5 v5.115.810.9015) */

CREATEORREPLACEprocedureproc_trade(

v_tradeidinnumber,--交易id

v_third_ipinvarchar2,--第三方ip

v_third_timeindate,--第三方完成时间

v_thire_stateinnumber,--第三方状态

o_resultoutnumber,--返回值

o_detailoutvarchar2--详细描述

)

as

--定义变量

v_errorvarchar2(500);

begin

--对变量赋值

o_result:=0;

o_detail:='验证失败';

--业务逻辑处理

ifv_tradeid>100then

insertintotable_name(...)values(...);

commit;

elsifv_tradeid<100andv_tradeid>50then

insertintotable_name(...)values(...);

commit;

else

gotolog;

endif;

--跳转标志符，名称自己指定

<<log>>

o_result:=1;

--捕获异常

exception

whenno_data_found

then

result:=2;

whendup_val_on_index

then

result:=3;

whenothers

then

result:=-1;

endproc_trade;

在上面这个存储过程中使用了输入参数，并返回输出参数，这里的参数类型是我们自己指定的。这种写法可行，但是最好使用%type来获取参数的类型(table_name.column_name%TYPE)。这样就不会出现参数类型的错误。

如：

CREATEORREPLACEPROCEDUREspdispsms(

aempidINotherinfo.empid%TYPE,

amsgINotherinfo.msg%TYPE,

abillnoINotherinfo.billno%TYPE,

ainfotypeINotherinfo.infotype%TYPE,

aopidINotherinfo.OPERATOR%TYPE,

ainfoidOUTotherinfo.infoid%TYPE,

RESULTOUTINTEGER

)

2.存储过程中的循环

存储过程写的是业务逻辑，循环是常用的处理方法之一。

2.1for ... in ... loop循环

2.1.1：循环遍历游标

示例1：

CREATEORREPLACEPROCEDUREproc_test

AS

CURSORc1

IS

SELECT*FROMdat_trade;

BEGIN

FORxINc1

LOOP

DBMS_OUTPUT.put_line(x.id);

ENDLOOP;

ENDproc_test;

示例2：

CREATEORREPLACEPROCEDUREproc_test

AS

BEGIN

FORxIN(SELECTpower_idFROMsys_power)

LOOP

DBMS_OUTPUT.put_line(x.power_id);

ENDLOOP;

ENDproc_test;

2.1. 2：根据数值进行循环

示例1：

CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)

AS

BEGIN

forxin1..100loop

dbms_output.put_line(x);

endloop;

ENDproc_test;

示例2：在过程里指定输入参数v_num.在调用过程时指定循环次数。

CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)

AS

BEGIN

FORxIN1..v_num

LOOP

DBMS_OUTPUT.put_line(x);

ENDLOOP;

ENDproc_test;

2.2loop循环

LOOP

DELETEFROMorders

WHEREsenddate<TO_CHAR(ADD_MONTHS(SYSDATE,-3),

'yyyy-mm-dd')

ANDROWNUM<1000;

EXITWHENSQL%ROWCOUNT<1;

COMMIT;

ENDLOOP;

这里的SQL%ROWCOUNT是隐士游标。除了这个，还有其他几个：%found，%notfound，%isopen。

2.3while循环

CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)

AS

iNUMBER:=1;

BEGIN

WHILEi<v_num

LOOP

BEGIN

i:=i+1;

DBMS_OUTPUT.put_line(i);

END;

ENDLOOP;

ENDproc_test;

3.存储过程中的判断

判断也是存储过程中最常用的方法之一。

3.1if ... elsif ... else ...判断

CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)

AS

BEGIN

IFv_num<10

THEN

DBMS_OUTPUT.put_line(v_num);

ELSIFv_num>10ANDv_num<50

THEN

DBMS_OUTPUT.put_line(v_num-10);

ELSE

DBMS_OUTPUT.put_line(v_num-50);

ENDIF;

ENDproc_test;

3.2case ... when ... end case判断

CREATEORREPLACEPROCEDUREproc_test(v_numINNUMBER)

AS

BEGIN

casev_num

when1then

DBMS_OUTPUT.put_line(v_num);

when2then

DBMS_OUTPUT.put_line(v_num);

when3then

DBMS_OUTPUT.put_line(v_num);

elsenull;

endcase;

ENDproc_test;

4.游标

存储过程中使用游标也是很常见的。这里的游标分两种：

4.1Cursor型游标(不能用于参数传递)

这种方法具体参考2.1.1：循环遍历游标中的示例。

4.2SYS_REFCURSOR型游标

该游标是Oracle以预先定义的游标，可作出参数进行传递。

注意一点：SYS_REFCURSOR只能通过OPEN方法来打开和赋值

4.2.1我们可以使用这种类似的游标来返回一个结果集：

CREATE OR REPLACE procedureproc_test(

checknum in number,--每次返回的数据量

ref_cursor out sys_refcursor--返回的结果集，游标

)

as

begin

open ref_cursor for select * from (select * from dat_trade where state=41 order by id) where rownum<checknum;

end proc_test;

/

SYS_REFCURSOR中可使用三个状态属性：

（1）.%NOTFOUND(未找到记录信息)

（2）.%FOUND(找到记录信息)

（3）.%ROWCOUNT(然后当前游标所指向的行位置)

CREATEORREPLACEPROCEDUREproc_test(

checknumINNUMBER,--每次返回的数据量

ref_cursorOUTsys_refcursor--返回的结果集，游标

)

AS

t_tmptable_name%ROWTYPE;

BEGIN

OPENref_cursorFOR

SELECT*

FROM(SELECT*

FROMtable_name

WHEREstate=41

ORDERBYid)

WHEREROWNUM<checknum;

--循环游标

LOOP

FETCHref_cursorINTOt_tmp;

EXITWHENref_cursor%NOTFOUND;

--DBMS_OUTPUT.put_line(t_tmp.id);

UPDATEtable_name

SETstate=53

WHEREid=t_tmp.id;

COMMIT;

ENDLOOP;

CLOSEref_cursor;

ENDproc_test;

五.存储过程的调试

如果使用PL/SQL Developer或者TOAD工具的话，调试还是很方便的。如果是在Sqlplus里，我们可以使用：

SQL>show errors

来查看错误。不过在开发中估计也很少有人直接使用sqlplus来写存储过程。效率低，调试又麻烦。还是使用工具方便点。我一直使用的是Toad的。

如果想在某处退出存储过程，直接使用Return;就可以了。与存储过程编写相关的数组和游标，这两块说起来还是有很多东西。在上面的示例中，也简单的举了几个有关游标与存储过程编写的例子。

总之，写代码都是都是费脑子的事，相比之下还是做管理DBA舒服点，虽然压力大很多，至少不用这么费心思去整理业务逻辑。

------------------------------------------------------------------------------