Oracle游标

游标处理数据范例（在原示例上有删减）：

CREATE OR REPLACE PROCEDURE ATS_GUEST.GATETOSERVER
(
  HOSPITAL_DOMAIN             IN VARCHAR2,
  START_DATE                  IN VARCHAR2,
  END_DATE                    IN VARCHAR2
)
AS
PARM_SQL                      VARCHAR2(4000);
PARM_BODY_PART                VARCHAR2(256);
PARM_METHOD_CODE              VARCHAR2(256);
PARM_REPEAT_NUMBER            VARCHAR2(256);
PARM_MACHINE_NAME             VARCHAR2(256);
PARM_DEVICE_NAME              VARCHAR2(256);
PARM_STUDY_UID                VARCHAR2(256);
PARM_UNIQUE_ID                VARCHAR2(256);
PARM_DOC_ROOT                 VARCHAR2(256);

PARM_CURSOR                   SYS_REFCURSOR;

BEGIN

    IF (HOSPITAL_DOMAIN ='2.16.840.1.113883.4.487.1.4.1') THEN

    PARM_SQL:= 'SELECT CDA_UNIQUE_ID,DOC_AUTHORITY_ROOT,DICOM_BODY_PART,DIAGNOSIS_METHOD_CODE,REPEAT_NUMBER,MACHINE_ROOM_NAME,DEVICE_NAME,DICOM_STUDY_UID FROM ADGATE.REPORTDOC@GATETOSERVER WHERE DOC_AUTHORITY_ROOT=''' || HOSPITAL_DOMAIN || '''';

    END IF;

    PARM_SQL:= PARM_SQL || ' AND EFFECTIVE_TIME > TO_DATE(''' || START_DATE || ''',''yyyymmddhh24miss'') AND EFFECTIVE_TIME < TO_DATE(''' || END_DATE || ''',''yyyymmddhh24miss'')';

    DBMS_OUTPUT.PUT_LINE(PARM_SQL);

    OPEN PARM_CURSOR FOR PARM_SQL;

    LOOP

    FETCH PARM_CURSOR INTO PARM_UNIQUE_ID,PARM_DOC_ROOT,PARM_BODY_PART,PARM_METHOD_CODE,PARM_REPEAT_NUMBER,PARM_MACHINE_NAME,PARM_DEVICE_NAME,PARM_STUDY_UID;

    PARM_SQL:= 'UPDATE AXDS.DOCUMENTSCATTER SET BODY_PART = ''' || PARM_BODY_PART || ''',';

    PARM_SQL:= PARM_SQL || 'DIAGNOSIS_METHOD_CODE = ''' || PARM_METHOD_CODE || ''',';

    PARM_SQL:= PARM_SQL || 'REPEAT_NUMBER = ''' || PARM_REPEAT_NUMBER || ''',';

    PARM_SQL:= PARM_SQL || 'MACHINE_ROOM_NAME = ''' || PARM_MACHINE_NAME || ''',';

    PARM_SQL:= PARM_SQL || 'DEVICE_NAME = ''' || PARM_DEVICE_NAME || ''',';

    PARM_SQL:= PARM_SQL || 'STUDY_UID = ''' || PARM_STUDY_UID || ''',';

    PARM_SQL:= PARM_SQL || 'CUSTOM1 = ''TEST''';

    PARM_SQL:= PARM_SQL || ' WHERE AXDS.DOCUMENTSCATTER.CDA_UNIQUE_ID = ''' || PARM_UNIQUE_ID || ''' AND AXDS.DOCUMENTSCATTER.DOC_AUTHORITY_ROOT = ''' || PARM_DOC_ROOT || '''';

    --next line will break this PROCEDURE 
    --DBMS_OUTPUT.PUT_LINE(PARM_SQL);

    EXECUTE IMMEDIATE PARM_SQL;

    COMMIT;

    IF (HOSPITAL_DOMAIN ='2.16.840.1.113883.4.487.1.4.1') THEN

    PARM_SQL:= 'UPDATE ADGATE.REPORTDOC@GATETOSERVER SET CUSTOM_5 = ''TEST'' WHERE ADGATE.REPORTDOC.CDA_UNIQUE_ID = ''' || PARM_UNIQUE_ID || ''' AND ADGATE.REPORTDOC.DOC_AUTHORITY_ROOT = ''' || PARM_DOC_ROOT || '''';

    EXECUTE IMMEDIATE PARM_SQL;

    COMMIT;
 
    END IF;

    EXIT WHEN PARM_CURSOR%NOTFOUND;

    END LOOP;

    CLOSE PARM_CURSOR;

    EXCEPTION

    WHEN OTHERS THEN

    NULL;

END;