tcbs_oracle plsql编程之cursor_索引表类型联合演练

摘自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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-733766/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-733766/