有一张表的一条信息有24段一样的数据,现在想遍历循环这24段,想使用字段变量,但一直报错,不知道能否实现,请高手赐教,在线等,谢谢了!! 程序如下:
create or replace procedure test_09 as
TYPE CURSOR_TYPE IS REF CURSOR;
C_T_ANP_AMWKPL09 CURSOR_TYPE;
R_T_ANP_AMWKPL09 T_ANP_AMWKPL09%ROWTYPE;
V_SQLSTR VARCHAR2(300);
V_BL_FRST_DUE_DATE CHAR(10);--最初到期日
V_BL_STOP_DATE CHAR(10);--停止日期
V_BL_NUM_PMTS NUMBER(12);--还款次数
V_BL_FREQ CHAR(1);--还款频率
V_BL_INCR NUMBER(12);--还款增量
V_NUM INT;--第几段还款计划(共24段)
V_INDX VARCHAR2(100);
V_FLAG CHAR(1);--更新标志
V_DATE CHAR(10);--当前业务日期
begin
SELECT substr(curr_date,1,4)||'-'||substr(curr_date,5,2)||'-'||substr(curr_date,7,2) INTO V_DATE FROM SYSTEM_INFO S;
V_SQLSTR := 'SELECT * FROM T_ANP_AMWKPL09 where rownum=1';
--打开游标
OPEN C_T_ANP_AMWKPL09 FOR V_SQLSTR;
LOOP
FETCH C_T_ANP_AMWKPL09 INTO R_T_ANP_AMWKPL09;
EXIT WHEN C_T_ANP_AMWKPL09%NOTFOUND;
V_INDX := R_T_ANP_AMWKPL09.TR_CTL1||R_T_ANP_AMWKPL09.TR_CTL2||R_T_ANP_AMWKPL09.TR_CTL3||R_T_ANP_AMWKPL09.TR_CTL4||R_T_ANP_AMWKPL09.RT_ACCT_NUM;
V_FLAG := 0;
for i in 1..24 loop
V_BL_FRST_DUE_DATE := 'BL_FRST_DUE_DATE'||i;
V_BL_STOP_DATE := 'BL_STOP_DATE'||i;
V_BL_NUM_PMTS := 'BL_NUM_PMTS'||i;
V_BL_FREQ := 'BL_FREQ'||i;
V_BL_INCR := 'BL_INCR'||i;
DBMS_OUTPUT.put_line(R_T_ANP_AMWKPL09.'||V_BL_FREQ||');--这个报错
end loop;
END LOOP;
CLOSE C_T_ANP_AMWKPL09;
end test_09;
OracleCursor
create or replace procedure test_09 as
TYPE CURSOR_TYPE IS REF CURSOR;
C_T_ANP_AMWKPL09 CURSOR_TYPE;
R_T_ANP_AMWKPL09 T_ANP_AMWKPL09%ROWTYPE;
V_SQLSTR VARCHAR2(300);
V_BL_FRST_DUE_DATE CHAR(10);--最初到期日
V_BL_STOP_DATE CHAR(10);--停止日期
V_BL_NUM_PMTS NUMBER(12);--还款次数
V_BL_FREQ CHAR(1);--还款频率
V_BL_INCR NUMBER(12);--还款增量
V_NUM INT;--第几段还款计划(共24段)
V_INDX VARCHAR2(100);
V_FLAG CHAR(1);--更新标志
V_DATE CHAR(10);--当前业务日期
begin
SELECT substr(curr_date,1,4)||'-'||substr(curr_date,5,2)||'-'||substr(curr_date,7,2) INTO V_DATE FROM SYSTEM_INFO S;
V_SQLSTR := 'SELECT * FROM T_ANP_AMWKPL09 where rownum=1';
--打开游标
OPEN C_T_ANP_AMWKPL09 FOR V_SQLSTR;
LOOP
FETCH C_T_ANP_AMWKPL09 INTO R_T_ANP_AMWKPL09;
EXIT WHEN C_T_ANP_AMWKPL09%NOTFOUND;
V_INDX := R_T_ANP_AMWKPL09.TR_CTL1||R_T_ANP_AMWKPL09.TR_CTL2||R_T_ANP_AMWKPL09.TR_CTL3||R_T_ANP_AMWKPL09.TR_CTL4||R_T_ANP_AMWKPL09.RT_ACCT_NUM;
V_FLAG := 0;
for i in 1..24 loop
V_BL_FRST_DUE_DATE := 'BL_FRST_DUE_DATE'||i;
V_BL_STOP_DATE := 'BL_STOP_DATE'||i;
V_BL_NUM_PMTS := 'BL_NUM_PMTS'||i;
V_BL_FREQ := 'BL_FREQ'||i;
V_BL_INCR := 'BL_INCR'||i;
DBMS_OUTPUT.put_line(R_T_ANP_AMWKPL09.'||V_BL_FREQ||');--这个报错
end loop;
END LOOP;
CLOSE C_T_ANP_AMWKPL09;
end test_09;
OracleCursor
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货