Oracle stored procedure template

// Procedure version

Create Or Replace Procedure Abc (n Number) Is -- input parameter
Type Cursortype Is Ref Cursor; -- defines the Cursor Type.
Resultset Cursortype; -- defines the cursor variable
Deptstate tb1_00department % Rowtype; -- Define a row object
Strsql Varchar2 (500): = 'create or replace view aView as ('; -- variable Initialization
Begin
Open Resultset For -- Open the cursor
-- Query the summary time of all agent statuses within an hour
Select * From Tb000000department t;
Loop -- looping cursor
Fetch Resultset Into Deptstate;
Exit When Resultset % Notfound;
If n> 0 Then
Dbms_Output.Put_Line (Strsql | '+' | Deptstate. Lid | '+' | n );
End If;
End Loop;
-- Close the cursor
Close Resultset;
Exception
When myexception then
Dbms_output.put_line ('have an error! ');
When others then
Begin
If resultset % isopen then
Close resultset;
End if;
End;
End ABC;

 

PL/SQL block edition:

Declare
Type cursortype is ref cursor;
Resultset cursortype;
Deptstate employee % rowtype;
Strsql varchar2 (500): = 'Hello world! ';
LId Number: = 100;
MyException EXCEPTION;
Begin
Open Resultset For Select * From employee t;
Loop
Fetch Resultset Into Deptstate;
Exit When Resultset % Notfound;
If Deptstate. Lid> 50 Then
Dbms_Output.Put_Line (Strsql | '+' | Deptstate. Lid | '+' | lId );
End If;
End Loop;
Close Resultset;
Exception
WHEN myException THEN
Dbms_output.put_line ('have an error! ');
When others then
Begin
If resultset % isopen then
Close resultset;
End if;
End;
End;
/