//包规范
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;