Oracle数据库存储过程
1.用来插入大量测试数据的存储过程
CREATE OR REPLACE PROCEDURE INSERTFLOW
(
ST_NUM IN NUMBER,--输入的参数1
ED_NUM IN NUMBER --输入的参数2
)
IS
BEGIN
declare i number;
begin
FOR i IN ST_NUM..ED_NUM LOOP
INSERT INTO T_AUTH_FLOW values(SQ_AUTHFLOW.NEXTVAL,'103',
'10901',sysdate,'1','测试','99999999','请求处理结果正确',i); --向T_AUTH_FLOW表中每个字段插入值
END LOOP;
end;
END;
执行语句:
execute INSERTFLOW(1,45000);
--一次插入45000条测试数据
正确执行的结果提示:
SQL>
PL/SQL procedure successfully completed
2.从存储过程中返回值
CREATE OR REPLACE PROCEDURE SPADDFLOW
(
AUTH_SYSTYPE IN varchar2,
AUTH_SYSIP IN varchar2,
AUTH_SYSK IN varchar2,
AUTH_SYSSTATUS IN varchar2,
AUTH_ACTTYPE IN varchar2,
ReturnValue out number --返回值
)
IS
BEGIN
insert into T_AUTH_USER values(AUTH_SYSTYPE,AUTH_SYSIP,AUTH_SYSK,AUTH_SYSSTATUS,AUTH_ACTTYPE)
returning 1 into ReturnValue; --返回值
commit;
exception
when others then
rollback;
END;
执行语句:
SQL> variable testvalue number;
SQL> execute SPADDFLOW('v','v','v','v','v',:testvalue);
正确执行的结果:
PL/SQL procedure successfully completed
testvalue
---------
1
注意点:
1.插入数据时,表的所有字段都要有插入的值,不能有缺少。