摘自TCBS,略有刪修

//包規範

CREATE OR REPLACE PACKAGE "PACK_GETBANKCALCVARS" AS

----定義索引表型別

TYPE array_inout_CALCVARCD IS TABLE OF CalcTypVarValue.CalcVarCd%TYPE

INDEX BY BINARY_INTEGER;

TYPE array_inout_CALCVARVALUE IS TABLE OF CalcTypVarValue.CalcVarValue%TYPE

INDEX BY BINARY_INTEGER;

---索引表型別的變數

inout_CALCVARCD array_inout_CALCVARCD;

inout_CALCVARVALUE array_inout_CALCVARVALUE;

PROCEDURE proc_GETBANKCALCVARS( in_EFFDATE IN CHAR,

in_CALCTYPCD IN CalcTyp.CalcTypCd%TYPE,

inout_CALCVARCD IN OUT pack_GETBANKCALCVARS.array_inout_CALCVARCD,

inout_CALCVARVALUE IN OUT pack_GETBANKCALCVARS.array_inout_CALCVARVALUE,

in_DEBUGYN IN CHAR,

out_CALCVARARRAYSIZE OUT INTEGER,

out_ERRORNBR OUT INTEGER,

out_ERRORMSG OUT VARCHAR2,

out_ORAERRORMSG OUT VARCHAR2 );

END pack_GETBANKCALCVARS;

包體，這才是真正作事的地兒，哈哈

CREATE OR REPLACE PACKAGE BODY "PACK_GETBANKCALCVARS" AS

//注意下述的array相關的引數，這就是索引表型別的引數

PROCEDURE proc_GETBANKCALCVARS( in_EFFDATE IN CHAR,

in_CALCTYPCD IN CalcTyp.CalcTypCd%TYPE,

inout_CALCVARCD IN OUT pack_GETBANKCALCVARS.array_inout_CALCVARCD,

inout_CALCVARVALUE IN OUT pack_GETBANKCALCVARS.array_inout_CALCVARVALUE,

in_DEBUGYN IN CHAR,

out_CALCVARARRAYSIZE OUT INTEGER,

out_ERRORNBR OUT INTEGER,

out_ERRORMSG OUT VARCHAR2,

out_ORAERRORMSG OUT VARCHAR2 ) AS

CURSOR curs_GetCalcTypVarInfo ( cpsCalcTypCd CalcTypVarValue.CalcTypCd%TYPE, cpdEffDate CalcTypVarValue.InactiveDate%TYPE ) IS

SELECT A.CalcVarCd, A.CalcVarValue

FROM CalcTypVarValue A

WHERE A.CalcTypCd = cpsCalcTypCd

AND A.ValueEffDate = ( SELECT MAX( ValueEffDate )

FROM CalcTypVarValue

WHERE CalcTypCd = cpsCalcTypCd

AND CalcVarCd = A.CalcVarCd

AND ( InactiveDate > cpdEffDate OR InactiveDate IS NULL )

AND ValueEffDate <= cpdEffDate )

ORDER BY A.CalcVarCd;

/************************************************************************************/

/***** GENERAL ERROR PROCESSING VARIABLES *****/

/************************************************************************************/

OSI_GENERAL_ERROR EXCEPTION;

lvsActvMsg VARCHAR2(100);

lvsORACLEMsg VARCHAR2(100);

lvnSubProcErrorNbr INTEGER;

lvnErrorNbr INTEGER;

lvnUserPersNbr INTEGER;

/************************************************************************************/

/***** PROCEDURE VARIABLES *****/

/************************************************************************************/

lvdEffDate DATE;

lvsCalcTypCd CalcTyp.CalcTypCd%TYPE;

lvnCalcTypCdCnt INTEGER;

lvsCalcVarCd CalcVar.CalcVarCd%TYPE;

lvsCalcVarValue CalcTypVarValue.CalcVarValue%TYPE;

lvnLoopCtr INTEGER;

/************************************************************************************/

/***** PROCEDURE CONSTANTS *****/

/************************************************************************************/

lcsDebugProcCd VARCHAR2(4) := 'BCLV';

BEGIN

/********************************************************************************************************/

/***** CLEAN UP STUFF PASSED THRU ROUTER... UNTIL FIXED *****/

/********************************************************************************************************/

lvdEffDate := TO_DATE(in_EFFDATE, 'YYYY-MM-DD');

lvsCalcTypCd := FUNC_CLEANSTR( in_CALCTYPCD );

IF ( in_DEBUGYN = 'Y' ) THEN

INSERT INTO SP_Debug (SeqNbr, DebugDate, DebugTime, PersNbr, ProcName, DebugText)

VALUES ( SP_DebugSeqNbr.nextval, SYSDATE, TO_CHAR( SYSDATE, 'HH24:MI:SS' ), lvnUserPersNbr, 'PROC_GETCALCVARS',

'BEGIN in_CALCTYPCD: ' || in_CALCTYPCD || ', in_EFFDATE: ' || lvdEffDate);

END IF;

lvsActvMsg := 'Attempting to Open curs_GetCalcTypVarInfo.';

//開啟遊標

OPEN curs_GetCalcTypVarInfo( lvsCalcTypCd, lvdEffDate );

//開啟遊標之後，馬上為此引數初始化一個值，這個引數用於上述的索引表型別的元素下標，大家可以理解為C語言的陣列，起一個定位陣列的作用

lvnLoopCtr := 0;

LOOP

lvsActvMsg := 'Prior to Fetch.';

FETCH curs_GetCalcTypVarInfo

INTO lvsCalcVarCd, lvsCalcVarValue;

EXIT WHEN curs_GetCalcTypVarInfo%NOTFOUND;

IF ( lvsCalcVarValue IS NOT NULL ) THEN

IF ( in_DEBUGYN = 'Y' ) THEN

INSERT INTO SP_Debug (SeqNbr, DebugDate, DebugTime, PersNbr, ProcName, DebugText)

VALUES ( SP_DebugSeqNbr.nextval, SYSDATE, TO_CHAR( SYSDATE, 'HH24:MI:SS' ), lvnUserPersNbr,

'PROC_GETCALCVARS', ' inout_CALCVARCD(' || lvnLoopCtr || '): ' || lvsCalcVarCd ||

', inout_CALCVARVALUE(' || lvnLoopCtr || '): ' || lvsCalcVarValue );

END IF;

//上述元素下標變數每次會隨著遊標的值而發生變化，

lvnLoopCtr := lvnLoopCtr + 1;

//這是關鍵的程式碼，透過上述的元素下標變數lvnloopctr與索引表變數

//inout_calcvarcd與遊標變數lvscalcvarcd，這三者就聯絡起來了

inout_CALCVARCD( lvnLoopCtr ) := lvsCalcVarCd;

inout_CALCVARVALUE( lvnLoopCtr ) := lvsCalcVarValue;

END IF;

END LOOP;

CLOSE curs_GetCalcTypVarInfo;

out_CALCVARARRAYSIZE := lvnLoopCtr;

EXCEPTION

WHEN OTHERS THEN

lvsORACLEMsg := SUBSTR(SQLERRM, 1, 100);

out_ORAERRORMSG := lvsORACLEMsg;

out_ERRORNBR := ABS(SQLCODE);

out_ERRORMSG := lvsActvMsg || ' - ' || lcsDebugProcCd;

INSERT INTO SP_Debug (SeqNbr, DebugDate, DebugTime, PersNbr, ProcName, DebugText)

VALUES ( SP_DebugSeqNbr.nextval, SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'), lvnUserPersNbr, 'PROC_GETCALCVARS',

lvsActvMsg || ' *** ' || lvsORACLEMsg );

END proc_GETBANKCALCVARS;

END pack_GETBANKCALCVARS;

tcbs_oracle plsql程式設計之cursor_索引表型別聯合演練

相關文章