这是本人写的一个过程其中一部分,原来的:
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怎么会这样呢?重复的同名变量要全列出来,真是奇怪!
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怎么会这样呢?重复的同名变量要全列出来,真是奇怪!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货