令人比较费解的问题

[cjcwqp]

这是本人写的一个过程其中一部分,原来的:
create or replace procedure sp_testsqlfor1(cMsisdn in varchar2,cCalQj in varchar2, feesum out number) is
.....
if i<>28 then
cLSql:=' select MSISDN,(Fee1+Fee2+Fee3+Fee4+Fee5) FEESUM from bill.mb_detailgp_'||cYear||cMonth||cDay||'@bill'||chr(13)||chr(10)
||' where msisdn=:cMsisdn'||chr(13)||chr(10)
||' union all'||chr(13)||chr(10);
dbms_output.put_line('csql'||to_char(i,'00')||chr(13)||chr(10)||clsql);
else
cLSql:=' select MSISDN,(Fee1+Fee2+Fee3+Fee4+Fee5) FEESUM from bill.mb_detailgp_'||cYear||cMonth||cDay||'@bill'||chr(13)||chr(10)
||' where msisdn=:cMsisdn'||chr(13)||chr(10);
dbms_output.put_line('csql'||to_char(i,'00')||chr(13)||chr(10)||clsql);
end if;
cSql:=cSql||cLSql;
end loop;

end if;
......
cSql:='SELECT SUM(feesum) feesum from ('||chr(13)||chr(10)
||cSql||chr(13)||chr(10)
||') '||chr(13)||chr(10)
||'GROUP BY MSISDN';
--dbms_output.put_line(csql);
--'MERGE INTO USING
execute immediate cSql into feesum using cmsisdn,cCalQj ;
end sp_testsqlfor1;
编译成功,测试提示:ORA-01008:并非所有变量都已关联

修改后的:
create or replace procedure sp_testsqlfor1(cMsisdn in varchar2,cCalQj in varchar2, feesum out number) is
.....
if i<>28 then
cLSql:=' select MSISDN,(Fee1+Fee2+Fee3+Fee4+Fee5) FEESUM from bill.mb_detailgp_'||cYear||cMonth||cDay||'@bill'||chr(13)||chr(10)
||' where msisdn=:cMsisdn'||chr(13)||chr(10)
||' union all'||chr(13)||chr(10);
dbms_output.put_line('csql'||to_char(i,'00')||chr(13)||chr(10)||clsql);
else
cLSql:=' select MSISDN,(Fee1+Fee2+Fee3+Fee4+Fee5) FEESUM from bill.mb_detailgp_'||cYear||cMonth||cDay||'@bill'||chr(13)||chr(10)
||' where msisdn=:cMsisdn'||chr(13)||chr(10);
dbms_output.put_line('csql'||to_char(i,'00')||chr(13)||chr(10)||clsql);
end if;
cSql:=cSql||cLSql;
end loop;

cSql:='SELECT SUM(feesum) feesum from ('||chr(13)||chr(10)
||cSql||chr(13)||chr(10)
||') '||chr(13)||chr(10)
||'GROUP BY MSISDN';
--dbms_output.put_line(csql);
--'MERGE INTO USING
execute immediate cSql into feesum using cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,
cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn,cmsisdn ;
end if;
....
end sp_testsqlfor1;
编译成功,测试成功.
使用oracle9i,oracle怎么会这样呢?重复的同名变量要全列出来,真是奇怪!

[cjcwqp]

请高手指点!

[bobfang]

因为execute immediate ... using ...是按位置绑定参数，而不是按名字的。