oracle存储过程基础语句
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_testAS 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_testASBEGIN 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)ASBEGIN 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)ASBEGIN 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)ASBEGIN 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)ASBEGIN 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 --返回的结果集，游标)ASt_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;
?
