ITPub博客

首页 > Linux操作系统 > Linux操作系统 > tcbs_oracle plsql编程之cursor_索引表类型联合演练

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

原创 Linux操作系统 作者:wisdomone1 时间:2012-06-25 17:00:17 1 删除 编辑
摘自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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2149
  • 访问量
    11892444