create or replace PROCEDURE SP_CR_MOVETABLE_EXLISTELEC IS
CURSOR DATA_CUR IS
select EnStation,EnLane,EnTime,EnVehClass,EnVehPlate,ExStation,ExLane,ExTime,ExVehClass,ExVehPlate,RecordNo,AxisName,TotalWeight,TotalExceedWeight,TransFlag,InsertTime,UpdateTime
from EXLISTELEC where transflag=1 and rownum<10001;
TYPE R_CUR IS REF CURSOR;
tmonth CHAR(6);
R_D_CUR R_CUR;
vs_sql char(3000);
BEGIN
FOR R_D_CUR IN DATA_CUR LOOP
tmonth := to_char(R_D_CUR.extime,'yyyyMM');
EXECUTE IMMEDIATE 'insert into exlistelec'||tmonth||' VALUES('|| R_D_CUR.EnStation ||','||R_D_CUR.EnLane ||',
'|| TO_DATE(to_char(R_D_CUR.EnTime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss') ||',
'|| R_D_CUR.EnVehClass ||','''|| R_D_CUR.EnVehPlate||''','||R_D_CUR.ExStation ||','||R_D_CUR.ExLane ||',
'|| TO_DATE(to_char(R_D_CUR.ExTime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss') ||',
'|| R_D_CUR.ExVehClass ||','''||R_D_CUR.ExVehPlate ||''','||R_D_CUR.RecordNo||','||R_D_CUR.AxisName ||',
'|| R_D_CUR.TotalWeight||','|| R_D_CUR.TotalExceedWeight ||',1,
'|| TO_DATE(to_char(R_D_CUR.InsertTime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss') ||',
'|| TO_DATE(to_char(R_D_CUR.UpdateTime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss')||')
where not exists (select 1 from exlistelec'||tmonth||' where exlistelec'||tmonth||'.exstation='||R_D_CUR.ExStation||' and
exlistelec'||tmonth||'.exlane='|| R_D_CUR.ExLane ||' and exlistelec'||tmonth||'.extime=
'||TO_DATE(to_char(R_D_CUR.ExTime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss') ||'
and exlistelec'||tmonth||'.recordno='||R_D_CUR.RecordNo ||' and exlistelec'||tmonth||'.InsertTime='||TO_DATE(to_char(R_D_CUR.InsertTime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss')||')';
COMMIT;
EXECUTE IMMEDIATE 'DELETE from exlistelec where exstation='|| R_D_CUR.exstation ||' and exlane='||R_D_CUR.exlane||' and
extime='|| TO_DATE(to_char(R_D_CUR.extime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss') ||' and recordno='||R_D_CUR.recordno||'
and InsertTime='|| TO_DATE(to_char(R_D_CUR.InsertTime,'yyyy-mm-dd hh24:mi:ss') ,'dd-mm-yy hh24-mi-ss')||'';
COMMIT;
END LOOP;
END;
连接到数据库 gat。
ORA-00933: SQL command not properly ended
ORA-06512: at "PCS_GGJR_GZ.SP_CR_MOVETABLE_EXLISTELEC", line 12
ORA-06512: at line 2
进程已退出。
从数据库 gat 断开连接。
我看我的拼接 没啥问题啊，，，
------解决思路----------------------
你的字段是字符型还是日期型？如果是日期型的话，统一把时间改为下面这样
to_date('2014-12-04 23:54:10','yyyy-mm-dd hh24:mi:ss')