请教， ls_sql := 'select a.yhdabh,a.jlbbh, b.zlx, b.bjzsw,b.zhbl,
nvl(b.scygzds,0) , nvl(b.scygjds,0), nvl(b.scygfds,0),
nvl(b.scyggds,0) , nvl(b.scygpds,0), nvl(b.scwgds,0) , nvl(b.scfxwgds,0) ,
nvl(a.ygzds,0) , nvl(a.ygjds,0 ), nvl(a.ygfds,0) ,
nvl(a.yggds,0) , nvl(a.ygpds,0 ), nvl(a.wgds,0) , nvl(a.fxwgds,0)
from '||ls_table||' a, ls_cbsj b
where a.fgsbh = '||as_fgsbh||' and
a.fgsbh = b.fgsbh and
b.cbpcbh = '||ls_cbpcbh||' and
b.cblxbh = '||ls_cblxbh||' and
to_char(a.cjsj,''yyyymmdd'') = '||ls_cjsj||' and
a.yhdabh = b.yhdabh and
a.jlbbh = b.jlbbh and
b.cbbz = 0 ';
这是我用游标取值的SQL，直接检索没有问题
但一加上第10行 “ b.cblxbh = '||ls_cblxbh||' and “这个条件，语句执行就报错：
ORA-00904 无效的标识符，然后错误指向ls_cblxbh这个变量的值
很郁闷啊，谁能帮我解决这个问题
nvl(b.scygzds,0) , nvl(b.scygjds,0), nvl(b.scygfds,0),
nvl(b.scyggds,0) , nvl(b.scygpds,0), nvl(b.scwgds,0) , nvl(b.scfxwgds,0) ,
nvl(a.ygzds,0) , nvl(a.ygjds,0 ), nvl(a.ygfds,0) ,
nvl(a.yggds,0) , nvl(a.ygpds,0 ), nvl(a.wgds,0) , nvl(a.fxwgds,0)
from '||ls_table||' a, ls_cbsj b
where a.fgsbh = '||as_fgsbh||' and
a.fgsbh = b.fgsbh and
b.cbpcbh = '||ls_cbpcbh||' and
b.cblxbh = '||ls_cblxbh||' and
to_char(a.cjsj,''yyyymmdd'') = '||ls_cjsj||' and
a.yhdabh = b.yhdabh and
a.jlbbh = b.jlbbh and
b.cbbz = 0 ';
这是我用游标取值的SQL，直接检索没有问题
但一加上第10行 “ b.cblxbh = '||ls_cblxbh||' and “这个条件，语句执行就报错：
ORA-00904 无效的标识符，然后错误指向ls_cblxbh这个变量的值
很郁闷啊，谁能帮我解决这个问题
(as_fgsbh varchar2,
as_cbpcbh varchar2,
as_cblxbh varchar2,
as_cblr varchar2)
is
LS_FGSBH VARCHAR2(8);
LS_CBLXBH VARCHAR2(10);
ls_cbpcbh varchar2(10);
ls_cjsj varchar2(10);
ls_sql varchar2(5000);
ls_table varchar2(30);v_ErrorCode number;
v_ErrorStack varchar2(2000);
v_CallStack varchar2(2000);
ll_count number(2) ; -------------------------
ls_yhdabh varchar(40);
ls_jlbbh varchar(40);
ls_zlx varchar(10);
li_bjzsw number(2);
li_zhbl number(10);
-------------------------
ldec_scygzds number(15,4);
ldec_ygzds number(15,4);
ldec_scygjds number(15,4);
ldec_ygjds number(15,4);
ldec_scygfds number(15,4);
ldec_ygfds number(15,4);
ldec_scyggds number(15,4);
ldec_yggds number(15,4);
ldec_scygpds number(15,4);
ldec_ygpds number(15,4);
ldec_scwgds number(15,4);
ldec_wgds number(15,4);
ldec_scfxwgds number(15,4);
ldec_fxwgds number(15,4); --------------------------
ldec_zdl number(10) ;
ldec_jdl number(10) ;
ldec_fdl number(10) ;
ldec_gdl number(10) ;
ldec_pdl number(10) ;
ldec_wgdl number(10) ;
ldec_fxwgdl number(10) ; type curtype is ref cursor;
cur_ww_cbsj curtype;
no_rows exception;begin
ls_fgsbh := as_fgsbh;
ls_cbpcbh := as_cbpcbh ;
ls_cjsj := substr(as_cbpcbh,1,6)||as_cblr;
ls_cblxbh := as_cblxbh ;
ls_table := 'ww_cbsj_js_'||substr(ls_cbpcbh,5,2)||'@ww.com.cn' ;
ls_sql := 'select a.yhdabh,a.jlbbh, b.zlx, b.bjzsw,b.zhbl,
nvl(b.scygzds,0) , nvl(b.scygjds,0), nvl(b.scygfds,0),
nvl(b.scyggds,0) , nvl(b.scygpds,0), nvl(b.scwgds,0) , nvl(b.scfxwgds,0) ,
nvl(a.ygzds,0) , nvl(a.ygjds,0 ), nvl(a.ygfds,0) ,
nvl(a.yggds,0) , nvl(a.ygpds,0 ), nvl(a.wgds,0) , nvl(a.fxwgds,0)
from '||ls_table||' a, ls_cbsj b
where a.fgsbh = '||as_fgsbh||' and
a.fgsbh = b.fgsbh and
b.cbpcbh = '||ls_cbpcbh||' and
b.cblxbh = '||ls_cblxbh||' and
to_char(a.cjsj,''yyyymmdd'') = '||ls_cjsj||' and
a.yhdabh = b.yhdabh and
a.jlbbh = b.jlbbh and
b.cbbz = 0 '; open cur_ww_cbsj for ls_sql ;
loop fetch cur_ww_cbsj
into ls_yhdabh , ls_jlbbh, ls_zlx , li_bjzsw , li_zhbl ,
ldec_scygzds, ldec_scygjds,ldec_scygfds,
ldec_scyggds, ldec_scygpds,ldec_scwgds,ldec_scfxwgds,
ldec_ygzds , ldec_ygjds , ldec_ygfds,
ldec_yggds , ldec_ygpds , ldec_wgds , ldec_fxwgds;
if cur_ww_cbsj%rowcount = 0 then
raise no_rows;
end if;
exit when cur_ww_cbsj%notfound ;
---------判断整数位---------
if li_bjzsw = 0 or li_bjzsw is null then li_bjzsw := 4 ; end if;
---有功总
if ldec_ygzds < 0 then ldec_ygzds :=ldec_scygzds; end if;
if ldec_scygzds is null then ldec_scygzds := 0 ; end if;
ldec_zdl := f_calculate_dl(ldec_scygzds,ldec_ygzds,li_bjzsw,li_zhbl);
---有功尖
if ldec_ygjds < 0 then ldec_ygjds :=ldec_scygjds; end if;
if ldec_scygjds is null then ldec_scygjds := 0 ; end if;
ldec_jdl := f_calculate_dl(ldec_scygjds,ldec_ygjds,li_bjzsw,li_zhbl);
---有功峰
if ldec_ygfds < 0 then ldec_ygfds :=ldec_scygfds; end if;
if ldec_scygfds is null then ldec_scygfds := 0 ; end if;
ldec_fdl := f_calculate_dl(ldec_scygfds,ldec_ygfds,li_bjzsw,li_zhbl);
---有功谷
if ldec_yggds < 0 then ldec_yggds :=ldec_scyggds; end if;
if ldec_scyggds is null then ldec_scyggds := 0 ; end if;
ldec_gdl := f_calculate_dl(ldec_scyggds,ldec_yggds,li_bjzsw,li_zhbl);
---无功正向
if ldec_wgds < 0 then ldec_wgds :=ldec_scwgds; end if;
if ldec_scwgds is null then ldec_scwgds := 0 ; end if;
ldec_wgdl := f_calculate_dl(ldec_scwgds,ldec_wgds,li_bjzsw,li_zhbl);
---反向无功
if ldec_fxwgds < 0 then ldec_fxwgds :=ldec_scfxwgds; end if;
if ldec_scfxwgds is null then ldec_scfxwgds := 0 ; end if;
ldec_fxwgdl := f_calculate_dl(ldec_scfxwgds,ldec_fxwgds,li_bjzsw,li_zhbl); -- 有功平读数
ldec_ygpds := ldec_ygzds - ldec_ygjds - ldec_ygfds - ldec_yggds ;
ldec_pdl := ldec_zdl - ldec_jdl - ldec_fdl - ldec_gdl ;
ldec_wgdl := abs(ldec_wgdl) + abs(ldec_fxwgdl) ; update ls_cbsj
set ygzds =ldec_ygzds,ygjds =ldec_ygjds,ygfds = ldec_ygfds, yggds = ldec_yggds,
ygpds =ldec_ygpds,wgds =ldec_wgds, fxwgds= ldec_ygfds,
ygzdl =ldec_zdl , ygjdl =ldec_jdl ,ygfdl =ldec_fdl , yggdl = ldec_gdl,
ygpdl =ldec_pdl , wgdl =ldec_wgdl,cbbz = 1
where fgsbh = ls_fgsbh and
cbpcbh = ls_cbpcbh and
cblxbh = ls_cblxbh and
yhdabh = ls_yhdabh and
jlbbh = ls_jlbbh ; end loop;
close cur_ww_cbsj; commit;
EXCEPTION
WHEN OTHERS THEN
rollback;
v_ErrorCode:=SQLCODE;
v_ErrorStack:=DBMS_UTILITY.FORMAT_ERROR_STACK;
v_CallStack:=DBMS_UTILITY.FORMAT_CALL_STACK;
return;
end OF_UPDATE_WW_CBSJ;
(as_fgsbh varchar2,
as_cbpcbh varchar2,
as_cblxbh varchar2,
as_cblr varchar2)
is
LS_FGSBH VARCHAR2(8);
LS_CBLXBH VARCHAR2(10);
ls_cbpcbh varchar2(10);
ls_cjsj varchar2(10);
ls_sql varchar2(5000);
ls_table varchar2(30);v_ErrorCode number;
v_ErrorStack varchar2(2000);
v_CallStack varchar2(2000);
ll_count number(2) ; -------------------------
ls_yhdabh varchar(40);
ls_jlbbh varchar(40);
ls_zlx varchar(10);
li_bjzsw number(2);
li_zhbl number(10);
-------------------------
ldec_scygzds number(15,4);
ldec_ygzds number(15,4);
ldec_scygjds number(15,4);
ldec_ygjds number(15,4);
ldec_scygfds number(15,4);
ldec_ygfds number(15,4);
ldec_scyggds number(15,4);
ldec_yggds number(15,4);
ldec_scygpds number(15,4);
ldec_ygpds number(15,4);
ldec_scwgds number(15,4);
ldec_wgds number(15,4);
ldec_scfxwgds number(15,4);
ldec_fxwgds number(15,4); --------------------------
ldec_zdl number(10) ;
ldec_jdl number(10) ;
ldec_fdl number(10) ;
ldec_gdl number(10) ;
ldec_pdl number(10) ;
ldec_wgdl number(10) ;
ldec_fxwgdl number(10) ; type curtype is ref cursor;
cur_ww_cbsj curtype;
no_rows exception;begin
ls_fgsbh := as_fgsbh;
ls_cbpcbh := as_cbpcbh ;
ls_cjsj := substr(as_cbpcbh,1,6)||as_cblr;
ls_cblxbh := as_cblxbh ;
ls_table := 'ww_cbsj_js_'||substr(ls_cbpcbh,5,2)||'@ww.com.cn' ;
ls_sql := 'select a.yhdabh,a.jlbbh, b.zlx, b.bjzsw,b.zhbl,
nvl(b.scygzds,0) , nvl(b.scygjds,0), nvl(b.scygfds,0),
nvl(b.scyggds,0) , nvl(b.scygpds,0), nvl(b.scwgds,0) , nvl(b.scfxwgds,0) ,
nvl(a.ygzds,0) , nvl(a.ygjds,0 ), nvl(a.ygfds,0) ,
nvl(a.yggds,0) , nvl(a.ygpds,0 ), nvl(a.wgds,0) , nvl(a.fxwgds,0)
from '||ls_table||' a, ls_cbsj b
where a.fgsbh = '||as_fgsbh||' and
a.fgsbh = b.fgsbh and
b.cbpcbh = '||ls_cbpcbh||' and
b.cblxbh = '||ls_cblxbh||' and
to_char(a.cjsj,''yyyymmdd'') = '||ls_cjsj||' and
a.yhdabh = b.yhdabh and
a.jlbbh = b.jlbbh and
b.cbbz = 0 '; open cur_ww_cbsj for ls_sql ;
loop fetch cur_ww_cbsj
into ls_yhdabh , ls_jlbbh, ls_zlx , li_bjzsw , li_zhbl ,
ldec_scygzds, ldec_scygjds,ldec_scygfds,
ldec_scyggds, ldec_scygpds,ldec_scwgds,ldec_scfxwgds,
ldec_ygzds , ldec_ygjds , ldec_ygfds,
ldec_yggds , ldec_ygpds , ldec_wgds , ldec_fxwgds;
if cur_ww_cbsj%rowcount = 0 then
raise no_rows;
end if;
exit when cur_ww_cbsj%notfound ;
---------判断整数位---------
if li_bjzsw = 0 or li_bjzsw is null then li_bjzsw := 4 ; end if;
---有功总
if ldec_ygzds < 0 then ldec_ygzds :=ldec_scygzds; end if;
if ldec_scygzds is null then ldec_scygzds := 0 ; end if;
ldec_zdl := f_calculate_dl(ldec_scygzds,ldec_ygzds,li_bjzsw,li_zhbl);
---有功尖
if ldec_ygjds < 0 then ldec_ygjds :=ldec_scygjds; end if;
if ldec_scygjds is null then ldec_scygjds := 0 ; end if;
ldec_jdl := f_calculate_dl(ldec_scygjds,ldec_ygjds,li_bjzsw,li_zhbl);
---有功峰
if ldec_ygfds < 0 then ldec_ygfds :=ldec_scygfds; end if;
if ldec_scygfds is null then ldec_scygfds := 0 ; end if;
ldec_fdl := f_calculate_dl(ldec_scygfds,ldec_ygfds,li_bjzsw,li_zhbl);
---有功谷
if ldec_yggds < 0 then ldec_yggds :=ldec_scyggds; end if;
if ldec_scyggds is null then ldec_scyggds := 0 ; end if;
ldec_gdl := f_calculate_dl(ldec_scyggds,ldec_yggds,li_bjzsw,li_zhbl);
---无功正向
if ldec_wgds < 0 then ldec_wgds :=ldec_scwgds; end if;
if ldec_scwgds is null then ldec_scwgds := 0 ; end if;
ldec_wgdl := f_calculate_dl(ldec_scwgds,ldec_wgds,li_bjzsw,li_zhbl);
---反向无功
if ldec_fxwgds < 0 then ldec_fxwgds :=ldec_scfxwgds; end if;
if ldec_scfxwgds is null then ldec_scfxwgds := 0 ; end if;
ldec_fxwgdl := f_calculate_dl(ldec_scfxwgds,ldec_fxwgds,li_bjzsw,li_zhbl); -- 有功平读数
ldec_ygpds := ldec_ygzds - ldec_ygjds - ldec_ygfds - ldec_yggds ;
ldec_pdl := ldec_zdl - ldec_jdl - ldec_fdl - ldec_gdl ;
ldec_wgdl := abs(ldec_wgdl) + abs(ldec_fxwgdl) ; update ls_cbsj
set ygzds =ldec_ygzds,ygjds =ldec_ygjds,ygfds = ldec_ygfds, yggds = ldec_yggds,
ygpds =ldec_ygpds,wgds =ldec_wgds, fxwgds= ldec_ygfds,
ygzdl =ldec_zdl , ygjdl =ldec_jdl ,ygfdl =ldec_fdl , yggdl = ldec_gdl,
ygpdl =ldec_pdl , wgdl =ldec_wgdl,cbbz = 1
where fgsbh = ls_fgsbh and
cbpcbh = ls_cbpcbh and
cblxbh = ls_cblxbh and
yhdabh = ls_yhdabh and
jlbbh = ls_jlbbh ; end loop;
close cur_ww_cbsj; commit;
EXCEPTION
WHEN OTHERS THEN
rollback;
v_ErrorCode:=SQLCODE;
v_ErrorStack:=DBMS_UTILITY.FORMAT_ERROR_STACK;
v_CallStack:=DBMS_UTILITY.FORMAT_CALL_STACK;
return;
end OF_UPDATE_WW_CBSJ;重新贴了下，有些符号变了
需要改成:
b.cblxbh = '''|| ls_cblxbh ||''' and