oracle调用存储过程[常用指令记录]

037wo31u2i

DECLARE
  DAT_NOW_TIME DATE;
  NUM_STATUS NUMBER;
  NUM_MSG_NO NUMBER;
  VR2_MSG_BODY VARCHAR2(200);
  VR2_DBG_POS VARCHAR2(200);
  VR2_MSG_SEVERITY VARCHAR2(200);
  NUM_ELAPSE NUMBER;
  REF_CURSOR GCMS_ADM.GCMS_TIMEZONE.ref_output;
  --row_ref REF_CURSOR%rowtype
  
  TZH_ID number;
  TZH_START_DATE date;
  TZH_END_DATE date;
  VR2_DESCR varchar2(200);
  TZD_ID number;
  TZ_NAME varchar2(200);
  OFFSET_HR number;
BEGIN
  DAT_NOW_TIME := NULL;
  --select sysdate into DAT_NOW_TIME from dual;

  GCMS_TIMEZONE.TIMEZONE_SEARCH(
    DAT_NOW_TIME => DAT_NOW_TIME,
    NUM_STATUS => NUM_STATUS,
    NUM_MSG_NO => NUM_MSG_NO,
    VR2_MSG_BODY => VR2_MSG_BODY,
    VR2_DBG_POS => VR2_DBG_POS,
    VR2_MSG_SEVERITY => VR2_MSG_SEVERITY,
    NUM_ELAPSE => NUM_ELAPSE,
    REF_CURSOR => REF_CURSOR
  );
  DBMS_OUTPUT.PUT_LINE('NUM_STATUS = ' || NUM_STATUS);
  DBMS_OUTPUT.PUT_LINE('NUM_MSG_NO = ' || NUM_MSG_NO);
  DBMS_OUTPUT.PUT_LINE('VR2_MSG_BODY = ' || VR2_MSG_BODY);
  DBMS_OUTPUT.PUT_LINE('VR2_DBG_POS = ' || VR2_DBG_POS);
  DBMS_OUTPUT.PUT_LINE('VR2_MSG_SEVERITY = ' || VR2_MSG_SEVERITY);
  DBMS_OUTPUT.PUT_LINE('NUM_ELAPSE = ' || NUM_ELAPSE);
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('REF_CURSOR = ' || REF_CURSOR);
  IF REF_CURSOR IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('REF_CURSOR-->not null');
    --FOR REF_CURSOR;
    --open REF_CURSOR;
    LOOP
    FETCH REF_CURSOR INTO TZH_ID,TZH_START_DATE,TZH_END_DATE,VR2_DESCR,TZD_ID,TZ_NAME,OFFSET_HR;
    EXIT WHEN REF_CURSOR%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('TZ_NAME-->'|| TZ_NAME);
      DBMS_OUTPUT.PUT_LINE('TZH_ID-->'|| TZH_ID);
      DBMS_OUTPUT.PUT_LINE('TZD_ID-->'|| TZD_ID);
      DBMS_OUTPUT.PUT_LINE('OFFSET_HR-->'|| OFFSET_HR);
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('REF_CURSOR-->null');
  END IF;
END;