游标与存贮过程

Question

游标的使用一定要放在包(Packages,Packages bodies)里使用吗?
能不能只用在存贮过程里,如果单独用在存贮过程里,又如何返回记录集呢?

Answer 1

单独用在存贮过程里和在包(Packages,Packages bodies)里使用是一样的呀

Answer 2

在Packge 中声明一个Type为Cursor的引用在函数中返回该Type类型的变量

Answer 3

同意 ylhyh(US7ASCII与ASP.NET的乱码问题谁解决了？) 。
type Rs is ref cursor;
把Rs放入存储过程的参数里就可以了吧。

Answer 4

(
StartTime IN DATE  DEFAULT NULL,
EndTime IN DATE  DEFAULT NULL,
Duration IN NUMBER DEFAULT NULL,
SSNO_1 IN NUMBER DEFAULT NULL,
SUNO_1 IN VARCHAR2 DEFAULT NULL)AS
StartTime_1 DATE:=StartTime;
EndTime_1 DATE:=EndTime;
Duration_ NUMBER(10,0):=Duration;
SSNO_1_ NUMBER(3,0) :=SSNO_1;
SUNO_1_ VARCHAR2(1000):=SUNO_1;
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
  DECLARE
     v_SC_ID ALARM_RECOVERY_RECORD.SC_ID%TYPE;
     v_SS_NO ALARM_RECOVERY_RECORD.SS_NO%TYPE;
     v_SU_NO ALARM_RECOVERY_RECORD.SU_NO%TYPE;
     v_SO_NO ALARM_RECOVERY_RECORD.SO_NO%TYPE;
     v_SA_NO ALARM_RECOVERY_RECORD.SA_NO%TYPE;
     v_ALARM_ID ALARM_RECOVERY_RECORD.ALARM_ID%TYPE;
     v_ALARM_TIME ALARM_RECOVERY_RECORD.ALARM_TIME%TYPE;
     v_ALARM_DURATION ALARM_RECOVERY_RECORD.ALARM_DURATION%TYPE;
     v_ALARM_LEVEL ALARM_RECOVERY_RECORD.ALARM_LEVEL%TYPE;
     v_CONFIRM_TIME ALARM_RECOVERY_RECORD.CONFIRM_TIME%TYPE;
     v_USERID ALARM_RECOVERY_RECORD.USERID%TYPE;
     v_ALARM_RECOVERY ALARM_RECOVERY_RECORD.ALARM_RECOVERY%TYPE;     CURSOR AlarmRecoveryRecordCursor IS SELECT * FROM ALARM_RECOVERY_RECORD;
     
      
                
     BEGIN
    OPEN AlarmRecoveryRecordCursor;
    StoO_error   := 0;
    StoO_rowcnt  := 0;
    LOOP
      FETCH AlarmRecoveryRecordCursor INTO v_SC_ID,v_SS_NO,v_SU_NO,v_SO_NO,v_SA_NO,v_ALARM_ID,v_ALARM_TIME,v_ALARM_DURATION,v_ALARM_LEVEL,v_CONFIRM_TIME,v_USERID,v_ALARM_RECOVERY;
      EXIT WHEN  AlarmRecoveryRecordCursor%NOTFOUND;
      BEGIN
         StoO_rowcnt := StoO_rowcnt+1;
         INSERT INTO SA.ALARM_RECORD_FOR_REPORT(SC_ID, SS_NO, SU_NO, SO_NO, SA_NO, Alarm_ID, Alarm_Time, Alarm_Duration, Alarm_Level,Confirm_Time,UserID, Alarm_Recovery,Alarm_RV_Time)
            VALUES(v_SC_ID,v_SS_NO,v_SU_NO,v_SO_NO,v_SA_NO,v_ALARM_ID,v_ALARM_TIME,v_ALARM_DURATION,v_ALARM_LEVEL,v_CONFIRM_TIME,v_USERID,v_ALARM_RECOVERY,v_ALARM_TIME+v_ALARM_DURATION/3600/24); 
         EXCEPTION
         WHEN NO_DATA_FOUND OR DUP_VAL_ON_INDEX THEN
         NULL;
         WHEN OTHERS THEN
           CLOSE AlarmRecoveryRecordCursor;
         StoO_error := SQLCODE;
         StoO_errmsg := SQLERRM;
         raise_application_error(SQLCODE, SQLERRM,true); 
      END;
    END LOOP;
    CLOSE AlarmRecoveryRecordCursor;
   END;
END INSERTALARM_RECORD_FOR_R_TEST;

Answer 5

Cursor cur is  
  select fmobiles,fmessages from Tu_TempMobiles; 
   
  Begin 
    
  for r in cur loop 
      DELETE FROM TU_TEMPMOBILES WHERE FMOBILEs=R.FMOBILEs; 
      COMMIT; 
  end loop;