如下，有一个动态的游标语句，其中动态的sql字符串里面的字段是拼接而成，所以不知道具体有几个字段，那么我在获取这个游标之后，怎么动态获取这一行内容的各个字段的内容呢（我是想保存在一个数组里），是不是fetch那一行也写成sql,然后execute immediate sql呀？
- SQL code
ascur sys_refcursor;.....begin.....for i in 1..n_NumKeys loop if i = n_NumKeys then v_SqlTemp := v_SqlTemp||KeyName_Array(i); else v_SqlTemp := v_SqlTemp||KeyName_Array(i)||','; end if; end loop; v_sql := 'select '||v_SqlTemp||' from ' ||b_tablename; open cur for v_sql; fetch cur into ...--?这里不知道怎么接收
------解决方案--------------------
- SQL code
仅供参考
---定义游标部分
cursor c_typeupg is
SELECT a.AD_CLIENT_ID, a.AD_ORG_ID, a.OWNERID, a.MODIFIERID, b.c_vip_id,
a.docno, a.billdate,b.c_viptype_id, b.c_viptype_destid,
nvl(b.minusintegral, 0) minusintegral
FROM c_vip_upgrade a, C_vip_upgradeItem b
where a.id = b.c_vip_upgrade_id and a.id = p_SubmittedSheetID;
r1 c_typeupg%ROWTYPE;
--试用游标部分
OPEN c_typeupg;
LOOP
FETCH c_typeupg
into r1;
exit when c_typeupg%notfound;
IF r1.c_viptype_destid IS NULL THEN
raise_application_error(-20201, '请输入所升级的VIP类型!');
END IF;
IF nvl(r1.minusintegral, 0) <> 0 THEN
--更新VIP积分流水帐
INSERT INTO FA_VIPINTEGRAL_FTP
(ID, AD_CLIENT_ID, AD_ORG_ID, OWNERID, MODIFIERID, CREATIONDATE,
MODIFIEDDATE, ISACTIVE, C_VIP_ID, DOCNO, CHANGDATE,/* INTEGRAL,*/INTEGRAL_UP,
DESCRIPTION,C_FORVIPTYPE_ID,C_BACKVIPTYPE_ID)
VALUES
(GET_SEQUENCES('FA_VIPINTEGRAL_FTP'), r.AD_CLIENT_ID,
r.AD_ORG_ID, r.OWNERID, r.MODIFIERID, SYSDATE, SYSDATE, 'Y',
r.c_vip_id, r.docno, r.billdate, -r.minusintegral,
'由VIP升级：' || r.docno || '提交生成',r.c_viptype_id,r.c_viptype_destid);
END IF;
update c_client_vip t
set t.integral_up = t.integral_up - nvl(r.minusintegral, 0),
/*t.integral = t.integral - nvl(r.minusintegral, 0),*/
t.c_viptype_id = r.c_viptype_destid
where t.id = r.c_vip_id;
end loop;
close c_typeupg;