cursor解读

procedure系列Oracle-procedure解读Oracle存储过程和自定义函数

procedure概述

存储过程（ Stored Procedure ）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是由流控制和 SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在 Oracle 中，若干个有联系的过程可以组合在一起构成程序包。

procedure优点

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
当对数据库进行复杂操作时（如对多个表进行 Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
存储过程可以重复使用,可减少数据库开发人员的工作量。
安全性高,可设定只有某用户才具有对指定存储过程的使用权。

和function的区别

procedure例子

CREATE OR REPLACE procedure proc_trade（ v_tradeid in number, --交易id v_third_ipin varchar2, --第三方ip v_third_timein date, --第三方完成时间 v_thire_state in number, --第三方状态 o_resultout number, --返回值 o_detailout varchar2 --详细描述） as-- 定义变量v_error varchar2（500）;begin--对变量赋值o_result := 0;o_detail := "验证失败";--业务逻辑处理if v_tradeid > 100 theninsert into table_name （.. .） values （.. .）;commit;elsif v_tradeid < 100 and v_tradeid > 50 theninsert into table_name （.. .） values （.. .）;commit;elsegoto log;end if;--跳转标志符，名称自己指定<<log>>o_result := 1;--捕获异常exceptionwhen no_data_found thenresult := 2;when dup_val_on_index thenresult := 3;when others thenresult := -1;end proc_trade;

参数类型可以自己指定，这种写法可行，但是最好使用%type 来获取参数的类型（table_name.column_name%TYPE）。这样就不会出现参数类型的错误。

存储过程中的循环

for … in … loop 循环

循环遍历游标

示例1：

CREATE OR REPLACE PROCEDURE proc_test ASCURSOR c1 ISSELECT * FROM dat_trade;BEGINFOR x IN c1 LOOPDBMS_OUTPUT.put_line（x.id）;END LOOP;END proc_test;

示例 2：

CREATE OR REPLACE PROCEDURE proc_test ASBEGINFOR x IN （SELECT power_id FROM sys_power） LOOPDBMS_OUTPUT.put_line（x.power_id）;END LOOP;END proc_test;

根据数值进行循环

栗子一

CREATE OR REPLACE PROCEDURE proc_test（） ASBEGINfor x in 1 .. 100 loopdbms_output.put_line（x）;end loop;END proc_test;

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

CREATE OR REPLACE PROCEDURE proc_test（v_num IN NUMBER） ASBEGINFOR x IN 1 .. v_num LOOPDBMS_OUTPUT.put_line（x）;END LOOP;END proc_test;

loop 循环

LOOPDELETE FROM ordersWHERE senddate < TO_CHAR （ADD_MONTHS （SYSDATE, -3）,"yyyy-mm-dd"）AND ROWNUM < 1000;EXIT WHEN SQL%ROWCOUNT < 1;COMMIT;END LOOP;

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

while 循环

CREATE OR REPLACE PROCEDURE proc_test（v_num IN NUMBER） ASi NUMBER := 1;BEGINWHILE i < v_num LOOPBEGINi := i + 1;DBMS_OUTPUT.put_line（i）;END;END LOOP;END proc_test;

存储过程中的判断

if … elsif … else … 判断

CREATE OR REPLACE PROCEDURE proc_test（v_num IN NUMBER） ASBEGINIF v_num < 10 THENDBMS_OUTPUT.put_line（v_num）;ELSIF v_num > 10 AND v_num < 50 THENDBMS_OUTPUT.put_line（v_num - 10）;ELSEDBMS_OUTPUT.put_line（v_num - 50）;END IF;END proc_test;

case … when … end case 判断

CREATE OR REPLACE PROCEDURE proc_test（v_num IN NUMBER） ASBEGINcase v_numwhen 1 thenDBMS_OUTPUT.put_line（v_num）;when 2 thenDBMS_OUTPUT.put_line（v_num）;when 3 thenDBMS_OUTPUT.put_line（v_num）;elsenull;end case;END proc_test;

游标

之前整理的游标的知识

Cursor 型游标（不能用于参数传递）

CREATE OR REPLACE PROCEDURE proc_test ASCURSOR c1 ISSELECT * FROM dat_trade;BEGINFOR x IN c1 LOOPDBMS_OUTPUT.put_line（x.id）;END LOOP;END proc_test;

SYS_REFCURSOR 型游标

该游标是 Oracle 预先定义的游标，可作出参数进行传递。SYS_REFCURSOR 只能通过 OPEN 方法来打开和赋值我们可以使用这种类似的游标来返回一个结果集：

CREATE OR REPLACE procedure proc_test（checknum in number, --每次返回的数据量ref_cursor out sys_refcursor --返回的结果集，游标） asbeginopen ref_cursor forselect *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（然后当前游标所指向的行位置）
（4）. %ISOPEN（是否打开）

CREATE OR REPLACE PROCEDURE proc_test（checknum IN NUMBER, --每次返回的数据量ref_cursor OUT sys_refcursor --返回的结果集，游标） ASt_tmp table_name%ROWTYPE;BEGINOPEN ref_cursor FORSELECT *FROM （SELECT * FROM table_name WHERE state = 41 ORDER BY id） WHERE ROWNUM < checknum;--循环游标LOOPFETCH ref_cursorINTO t_tmp;EXIT WHEN ref_cursor%NOTFOUND;-- DBMS_OUTPUT.put_line （t_tmp.id）;UPDATE table_name SET state = 53 WHERE id = t_tmp.id;COMMIT;END LOOP;CLOSE ref_cursor;END proc_test;

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址