如题DECLARE
CURSOR cur IS SELECT DISTINCT staff_charged_id FROM xl_charged_info WHERE ROWNUM <=20 ORDER BY staff_charged_id;
temp NUMBER;
str VARCHAR2(500);
BEGIN
FOR a IN cur LOOP
dbms_output.put_line(a.staff_charged_id);
str:='SELECT A.* FROM (SELECT STAFF_CHARGED_ID,CHARGE_ID,SUM(B_AMOUNT) A_AMOUNT,SUM(E_AMOUNT) B_AMOUNT,'||
'SUM(F_AMOUNT) F_AMOUNT,SUM(B_CNT) B_CNT,SUM(E_CNT) E_CNT,SUM(F_CNT) F_CNT,SUM(B_DISC_AMOUNT) B_DISC_AMOUNT,'||
'SUM(E_DISC_AMOUNT) E_DISC_AMOUNT,SUM(F_DISC_AMOUNT) F_DISC_AMOUNT,MAX(STAFF_ID) STAFF_ID FROM XL_CHARGED_INFO'||
' WHERE STAFF_CHARGED_ID = '''||a.staff_charged_id||'''GROUP BY STAFF_CHARGED_ID, CHARGE_ID ORDER BY STAFF_CHARGED_ID) A';
dbms_output.put_line(str);
execute IMMEDIATE str INTO temp;--这个str返回的数据时多行,我想循环这个str执行后的sql,该如何解决?
END LOOP;
END;
怎样才能让一个游标指向 str 所拼出来的sql?
CURSOR cur IS SELECT DISTINCT staff_charged_id FROM xl_charged_info WHERE ROWNUM <=20 ORDER BY staff_charged_id;
temp NUMBER;
str VARCHAR2(500);
BEGIN
FOR a IN cur LOOP
dbms_output.put_line(a.staff_charged_id);
str:='SELECT A.* FROM (SELECT STAFF_CHARGED_ID,CHARGE_ID,SUM(B_AMOUNT) A_AMOUNT,SUM(E_AMOUNT) B_AMOUNT,'||
'SUM(F_AMOUNT) F_AMOUNT,SUM(B_CNT) B_CNT,SUM(E_CNT) E_CNT,SUM(F_CNT) F_CNT,SUM(B_DISC_AMOUNT) B_DISC_AMOUNT,'||
'SUM(E_DISC_AMOUNT) E_DISC_AMOUNT,SUM(F_DISC_AMOUNT) F_DISC_AMOUNT,MAX(STAFF_ID) STAFF_ID FROM XL_CHARGED_INFO'||
' WHERE STAFF_CHARGED_ID = '''||a.staff_charged_id||'''GROUP BY STAFF_CHARGED_ID, CHARGE_ID ORDER BY STAFF_CHARGED_ID) A';
dbms_output.put_line(str);
execute IMMEDIATE str INTO temp;--这个str返回的数据时多行,我想循环这个str执行后的sql,该如何解决?
END LOOP;
END;
怎样才能让一个游标指向 str 所拼出来的sql?
解决方案 »
- 把一台机器上的数据同步到另一台机器不是实时同步如何操作
- 请教个sqlcode的问题,fetch不到数据的时候
- 求一条sql语句!统计的
- 初学oracle9 有问题请教大家
- [急!!!] 关于Oracle占用CPU. 高手进来看看!
- 高分请教!
- 在运行console后填写了管理员和口令,为什么总是出错进不去啊?
- 求助!能把一个字段内容分解的函数?
- 怎么用SQL语句列出所有数据库里面的存储过程的名称?
- MSSQLSERVER数据导入ORALE的问题求赐教
- Oracle Application Framework中,做下拉框的LIST时,写的SQL绑到LIST上和真实查询的结果不一样
- 要将orgbak里面的编号与orgdel编号相同的记录删除??
然后open c for str就可以了
cur sys_refcursor;
2.或自己定义一个游标类型,再用其来声明游标变量
type curtype is ref cursor;
cur curtype;
v_cur sys_refcursor;
里面
open v_cur for v_sql;