Oracle stored procedure code example 1

1. CREATEORREPLACEPROCEDUREINSERTAMOUNTTEST (ST_NUMINNUMBER, ED_NUM

1. create or replace procedure insertamounttest (ST_NUM in number, ED_NUM

1. Stored Procedure for inserting a large amount of test data
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;

Run:
SQL> execute INSERTAMOUNTTEST (45000) -- insert pieces of test data at a time

2. return values from stored procedures
Create or replace procedure spaddflowdate
(
VarAppTypeId in varchar2,
VarFlowId in varchar2,
DateLength in number,
ReturnValue out number -- Return Value
)
Is
Begin
Insert into td values (varAppTypeId, varFlowId, DateLength)
Returning 1 into ReturnValue; -- Return Value
Commit;
Exception
When others then
Rollback;
End;

Execution of Stored Procedures
SQL> variable testvalue number;
SQL> execute spaddflowdate ('V', 'V', 2,: testvalue );
SQL> print
The execution result is displayed.

3. Use a package to return a cursor for the stored procedure:
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;
/



Run:

Set serverout on; -- open the output Tool

Variable x refcursor;

Execute test_p.getinfor (: x );

Exec test_p.getinfor (: x );

Print x;

Drop package test_p;

,