create or replace procedure p_update_bgy is Type TCursor is Ref Cursor ;
v_Cursor TCursor ;
vs_bh_bgy VARCHAR2(3);
vs_bh_zzlb VARCHAR2(3);
vs_bh_lb VARCHAR2(12);
vi_jb INTEGER;
BEGIN
Open v_Cursor For
SELECT bh_bgy,bh_zzlb,bh_lb,jb FROM ea_temp ;
LOOP
FETCH v_cursor INTO vs_bh_bgy,vs_bh_zzlb,vs_bh_lb,vi_jb;
EXIT WHEN v_Cursor%NotFound;
vs_bh_lb:=substr(vs_bh_lb,1,(vi_jb - 1)*2);
UPDATE ea_zcxx SET bh_bgy = vs_bh_bgy,bh_zzlb = vs_bh_zzlb WHERE bh_lb like vs_bh_lb + '%';--line 16
UPDATE ea_zcxx_in SET bh_bgy=vs_bh_bgy,
bh_zzlb=vs_bh_zzlb
WHERE bh_lb like vs_bh_lb + '%';
END LOOP;
CLOSE v_cursor;
end p_update_bgy;过程我这样写,报错如下
ORA-01722: 无效数字
ORA-06512: 在"EAMIS.P_UPDATE_BGY", line 16
ORA-06512: 在line 1但我看不出是哪里错误
v_Cursor TCursor ;
vs_bh_bgy VARCHAR2(3);
vs_bh_zzlb VARCHAR2(3);
vs_bh_lb VARCHAR2(12);
vi_jb INTEGER;
BEGIN
Open v_Cursor For
SELECT bh_bgy,bh_zzlb,bh_lb,jb FROM ea_temp ;
LOOP
FETCH v_cursor INTO vs_bh_bgy,vs_bh_zzlb,vs_bh_lb,vi_jb;
EXIT WHEN v_Cursor%NotFound;
vs_bh_lb:=substr(vs_bh_lb,1,(vi_jb - 1)*2);
UPDATE ea_zcxx SET bh_bgy = vs_bh_bgy,bh_zzlb = vs_bh_zzlb WHERE bh_lb like vs_bh_lb + '%';--line 16
UPDATE ea_zcxx_in SET bh_bgy=vs_bh_bgy,
bh_zzlb=vs_bh_zzlb
WHERE bh_lb like vs_bh_lb + '%';
END LOOP;
CLOSE v_cursor;
end p_update_bgy;过程我这样写,报错如下
ORA-01722: 无效数字
ORA-06512: 在"EAMIS.P_UPDATE_BGY", line 16
ORA-06512: 在line 1但我看不出是哪里错误
应该是vs_bh_lb||'%';
oracle 下 ,字符窜连接用||
其中bh_bgy,bh_zzlb,bh_lb相当于我问题中的a,b,c
用程序中用+习惯了,竟然忘记了
PL/SQL编译的时候也没给我提示错误
再次谢谢,现在运行成功了