1, for inserting a large amount of test data stored procedure 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;
Run: SQL> Execute INSERTAMOUNTTEST (1,45000) - Insert 45000 test data at a time
2, the return value from the stored procedure create or replace procedure spaddflowdate (varAppTypeId in varchar2, varFlowId in varchar2, DateLength in number, ReturnValue out number - return value) isbegininsert into td values (varAppTypeId, varFlowId, DateLength) returning 1 into ReturnValue; - Return Value Commit; Exceptionwhen Othershenrollback; End;
The execution of the stored procedure SQL> Variable TestValue Number; SQL> Execute SpaddFlowdate ('V', 'V', 2,: TestValue); SQL> Print can see the execution result
3, use the package to implement the stored procedure return cursor: create or replace package test_P as type outlist is ref cursor; procedure getinfor (taxpayerlist out output); 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; / Run: set serverout on; - the tool to open the output variable X refcursor; execute test_p.getinfor (: x);
Exec Test_p.getinfor (: x); Print X;
Drop package test_p;
