存储过程示例(带游标)

CREATE OR REPLACE PROCEDURE P_ZG_StateTimeStatistics
----------------------------------------------------------------------------------------------
  ---- 创建 2012,04,22                                    --------------------------
  ---- 修改 2012,04,24                                    --------------------------
----------------------------------------------------------------------------------------------
( 
  In_ToolNo IN VARCHAR2,
  In_State IN VARCHAR2,
  Out_StateTime OUT NUMBER
)
AS
  V_StartTime DATE;
  V_EndTime   DATE;
  V_Time      NUMBER;
  V_SumTime   NUMBER :=0;
  CURSOR cur IS 
  SELECT 
  STATESTA_TIME,STATEEND_TIME INTO V_StartTime,V_EndTime 
  FROM T_ZG_Operation 
  WHERE Tool_No=In_ToolNo AND State=In_State AND stateend_time IS NOT NULL;
BEGIN
  OPEN cur; --打开游标
  LOOP        --循环
  FETCH cur INTO V_StartTime,V_EndTime;  --从游标得到一行数据
  EXIT WHEN cur%NOTFOUND;
  SELECT ROUND((V_EndTime - V_StartTime)*24,1) INTO V_Time From dual;
  V_SumTime :=V_SumTime + V_Time;
  END LOOP;
  SELECT V_SumTime INTO Out_StateTime FROM DUAL;
END;