mysql oracle存储过程实例_Oracle存储过程代码实例一则

1、用来插入大量测试数据的存储过程CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST(ST_NUM IN NUMBER,ED_NUM

1、用来插入大量测试数据的存储过程

CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST

(

ST_NUM IN NUMBER,

ED_NUM IN NUMBER

)

IS

BEGIN

declare

i number;

begin

FOR i IN ST_NUM..ED_NUM LOOP

INSERT 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 --返回值

)

is

begin

insert into td values(varAppTypeId,varFlowId,DateLength)

returning 1 into ReturnValue; --返回值

commit;

exception

when others then

rollback;

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;

，

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网