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;
/