|
因为工作的需要，最近一直在写存储过程。 工作了3年，一直都是做管理，也没有正儿八经的去写过存储过程， 这次正好可以好好练习一下。
在这里说一条使用存储过程很重要的理由：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
Oracle 存储过程 定义 和 优点 与 函数 区别 http://blog.csdn.net/tianlesoftware/archive/2010/01/27/5261364.aspx
Oracle 查看 表 存储过程 触发器 函数 等对象定义语句的方法 http://blog.csdn.net/tianlesoftware/archive/2010/06/19/5679293.aspx
1. 存储过程格式 /* Formatted on 2011/1/17 13:20:44 (QP5 v5.115.810.9015) */ CREATE OR REPLACE procedure proc_trade( v_tradeid in number, --交易id v_third_ip in varchar2, --第三方ip v_third_time in date , --第三方完成时间 v_thire_state in number , --第三方状态 o_result out number, --返回值 o_detail out varchar2 --详细描述 ) as -- 定义变量 v_error varchar2(500); begin --对变量赋值 o_result:=0; o_detail:='验证失败';
--业务逻辑处理 if v_tradeid >100 then insert into table_name(...) values(...); commit; elsif v_tradeid < 100 and v_tradeid>50 then insert into table_name(...) values(...); commit; else goto log; end if; --跳转标志符，名称自己指定 <<log>> o_result:=1; --捕获异常 exception when no_data_found then result := 2; when dup_val_on_index then result := 3; when others then result := -1; end proc_trade;
在上面这个存储过程中使用了输入参数，并返回输出参数，这里的参数类型是我们自己指定的。 这种写法可行，但是最好使用%type 来获取参数的类型(table_name.column_name%TYPE)。 这样就不会出现参数类型的错误。
如： CREATE OR REPLACE PROCEDURE spdispsms ( aempid IN otherinfo.empid%TYPE, amsg IN otherinfo.msg%TYPE, abillno IN otherinfo.billno%TYPE, ainfotype IN otherinfo.infotype%TYPE, aopid IN otherinfo.OPERATOR%TYPE, ainfoid OUT otherinfo.infoid%TYPE, RESULT OUT INTEGER )
2. 存储过程中的循环 存储过程写的是业务逻辑，循环是常用的处理方法之一。
2.1 for ... in ... loop 循环 2.1.1：循环遍历游标 示例1： CREATE OR REPLACE PROCEDURE proc_test AS CURSOR c1 IS SELECT * FROM dat_trade; BEGIN FOR x IN c1 LOOP DBMS_OUTPUT.put_line (x.id); END LOOP; END proc_test;
示例2： CREATE OR REPLACE PROCEDURE proc_test AS BEGIN FOR x IN (SELECT power_id FROM sys_power) LOOP DBMS_OUTPUT.put_line (x.power_id); END LOOP; END proc_test;
2.1. 2：根据数值进行循环 示例1： CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER) AS BEGIN for x in 1..100 loop dbms_output.put_line(x); end loop; END proc_test;
示例2：在过程里指定输入参数v_num. 在调用过程时指定循环次数。 CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER) AS BEGIN FOR x IN 1 .. v_num LOOP DBMS_OUTPUT.put_line (x); END LOOP; END proc_test;
2.2 loop 循环 LOOP DELETE FROM orders WHERE 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。
2.3 while 循环 CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER) AS i NUMBER := 1; BEGIN WHILE i < v_num LOOP BEGIN i := i + 1; DBMS_OUTPUT.put_line (i); END; END LOOP; END proc_test;
3. 存储过程中的判断 判断也是存储过程中最常用的方法之一。
3.1 if ... elsif ... else ... 判断 CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER) AS BEGIN IF v_num < 10 THEN DBMS_OUTPUT.put_line (v_num); ELSIF v_num > 10 AND v_num < 50 THEN DBMS_OUTPUT.put_line (v_num - 10); ELSE DBMS_OUTPUT.put_line (v_num - 50); END IF; END proc_test;
3.2 case ... when ... end case 判断 CREATE OR REPLACE PROCEDURE proc_test (v_num IN NUMBER) AS BEGIN case v_num when 1 then DBMS_OUTPUT.put_line (v_num); when 2 then DBMS_OUTPUT.put_line (v_num); when 3 then DBMS_OUTPUT.put_line (v_num); else null; end case; END proc_test;
4. 游标 存储过程中使用游标也是很常见的。 这里的游标分两种：
4.1 Cursor型游标(不能用于参数传递) 这种方法具体参考 2.1.1：循环遍历游标 中的示例。
4.2 SYS_REFCURSOR型游标 该游标是Oracle以预先定义的游标，可作出参数进行传递。 注意一点：SYS_REFCURSOR只能通过OPEN方法来打开和赋值
4.2.1 我们可以使用这种类似的游标来返回一个结果集：
CREATE OR REPLACE procedure proc_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(然后当前游标所指向的行位置)
CREATE OR REPLACE PROCEDURE proc_test ( checknum IN NUMBER, --每次返回的数据量 ref_cursor OUT sys_refcursor --返回的结果集，游标 ) AS t_tmp table_name%ROWTYPE; BEGIN OPEN ref_cursor FOR SELECT * FROM ( SELECT * FROM table_name WHERE state = 41 ORDER BY id) WHERE ROWNUM < checknum; --循环游标 LOOP FETCH ref_cursor INTO 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;
五. 存储过程的调试 如果使用PL/SQL Developer 或者TOAD 工具的话，调试还是很方便的。 如果是在Sqlplus里，我们可以使用： SQL>show errors 来查看错误。不过在开发中估计也很少有人直接使用sqlplus来写存储过程。 效率低，调试又麻烦。 还是使用工具方便点。我一直使用的是Toad的。
如果想在某处退出存储过程，直接使用Return;就可以了。 与存储过程编写相关的数组和游标， 这两块说起来还是有很多东西。 在上面的示例中， 也简单的举了几个有关游标与存储过程编写的例子。
总之，写代码都是都是费脑子的事，相比之下还是做管理DBA舒服点，虽然压力大很多，至少不用这么费心思去整理业务逻辑。
------------------------------------------------------------------------------ Blog： http://blog.csdn.net/tianlesoftware 网上资源： http://tianlesoftware.download.csdn.net 相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850 DBA 超级群：63306533; 聊天 群：40132017 --加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请 |
|
|
