1、用来插入大量测试数据的存储过程
CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST(ST_NUM IN NUMBER,ED_NUM IN NUMBER)ISBEGINdeclare i number;beginFOR i IN ST_NUM..ED_NUM LOOPINSERT INTO tb values(i,i,'3','3','3',100,'0');END LOOP;end;END;运行:
sql>execute INSERTAMOUNTTEST(1,45000) -- 一次插入45000条测试数据2、从存储过程中返回值
create or replace procedure spaddflowdate(varAppTypeId in varchar2, varFlowId in varchar2,DateLength in number,ReturnValue out number --返回值)isbegininsert into td values(varAppTypeId,varFlowId,DateLength)returning 1 into ReturnValue; --返回值commit;exceptionwhen others thenrollback;end;存储过程的执行
sql>variable testvalue number;sql>execute spaddflowdate('v','v',2,:testvalue);sql>print就可以看到执行结果
3、用包实现存储过程返回游标:
create or replace package test_p as type outList is ref cursor; PROCEDURE getinfor(taxpayerList out outList); end test_p; /create or replace package body test_p as PROCEDURE getinfor(taxpayerList out outList) is begin
OPEN taxpayerList FOR select * from
td where tag='0'; end getinfor; end test_p; / 运行: set serverout on; --将输出工具打开variable x refcursor; execute test_p.getinfor(:x);exec test_p.getinfor(:x);
print x;drop package test_p;