oracle存储过程开发模板,Oracle存储过程模板

// Procedure版

Create Or Replace Procedure Abc( n Number) Is --输入参数

Type Cursortype Is Ref Cursor;                                                       --定义游标类型，好的使用方式

Resultset Cursortype;                                       --定义游标变量

Deptstate Tb000000department%Rowtype;                       --定义行对象

Strsql Varchar2(500) := 'create or replace view aView as('; --变量初始化

Begin

Open Resultset For --打开游标

--查询所有座席在某小时内的各状态的汇总时间

Select * From Tb000000department t;

Loop --循环遍历游标

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 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块版：

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;

/