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

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;