oracle存储过程

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 table 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 table 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