在练习拉链表,传入字符型的时间后(2019-07-25),最后得出的结果只有前四位的年,如果把中间的横线-去掉的话,就能够正常,这是why??!create table ls_emp as select *from emp;create table sc_emp as select empno,ename,sal,
to_char(sysdate,'yyyy-mm-dd') start_date,
to_char(sysdate,'yyyy-mm-dd') stop_date from ls_emp;
declare
cr_date varchar2(10) :='2019-07-25';
v_sql varchar2(1000);
begin
v_sql:='create table vt_new as select * from sc_emp where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_new select empno,ename,sal,'||cr_date||',''2999-12-31''
from ls_emp';
execute immediate v_sql;
commit;
v_sql:='create table vt_int as select * from vt_new where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_int(empno,ename,sal,start_date,stop_date)
select empno,ename,sal,start_date,stop_date
from vt_new
where (empno,ename,sal) not in (
select empno,ename,sal from sc_emp
where stop_date =''2999-12-31'')';
execute immediate v_sql;
commit;
v_sql:='update sc_emp set stop_date ='||cr_date||'where empno in (select empno from vt_int)';
execute immediate v_sql;
commit;
v_sql:='insert into sc_emp select * from vt_new';
execute immediate v_sql;
commit;
v_sql:='drop table vt_new';
execute immediate v_sql;
v_sql:='drop table vt_int';
execute immediate v_sql;
end;
结果:但是如果把传入的参数里面的-横线去掉的话就正常了
declare
cr_date varchar2(10) :='20190725';
v_sql varchar2(1000);
begin
v_sql:='create table vt_new as select * from sc_emp where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_new select empno,ename,sal,'||cr_date||',''29991231''
from ls_emp';
execute immediate v_sql;
commit;
v_sql:='create table vt_int as select * from vt_new where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_int(empno,ename,sal,start_date,stop_date)
select empno,ename,sal,start_date,stop_date
from vt_new
where (empno,ename,sal) not in (
select empno,ename,sal from sc_emp
where stop_date =''29991231'')';
execute immediate v_sql;
commit;
v_sql:='update sc_emp set stop_date ='||cr_date||'where empno in (select empno from vt_int)';
execute immediate v_sql;
commit;
v_sql:='insert into sc_emp select * from vt_new';
execute immediate v_sql;
commit;
v_sql:='drop table vt_new';
execute immediate v_sql;
v_sql:='drop table vt_int';
execute immediate v_sql;
end;
结果:这是因为系统内数据类型的自动转换么?
如果不用存储过程,使用sql语句的话就没有这种现象
to_char(sysdate,'yyyy-mm-dd') start_date,
to_char(sysdate,'yyyy-mm-dd') stop_date from ls_emp;
declare
cr_date varchar2(10) :='2019-07-25';
v_sql varchar2(1000);
begin
v_sql:='create table vt_new as select * from sc_emp where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_new select empno,ename,sal,'||cr_date||',''2999-12-31''
from ls_emp';
execute immediate v_sql;
commit;
v_sql:='create table vt_int as select * from vt_new where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_int(empno,ename,sal,start_date,stop_date)
select empno,ename,sal,start_date,stop_date
from vt_new
where (empno,ename,sal) not in (
select empno,ename,sal from sc_emp
where stop_date =''2999-12-31'')';
execute immediate v_sql;
commit;
v_sql:='update sc_emp set stop_date ='||cr_date||'where empno in (select empno from vt_int)';
execute immediate v_sql;
commit;
v_sql:='insert into sc_emp select * from vt_new';
execute immediate v_sql;
commit;
v_sql:='drop table vt_new';
execute immediate v_sql;
v_sql:='drop table vt_int';
execute immediate v_sql;
end;
结果:但是如果把传入的参数里面的-横线去掉的话就正常了
declare
cr_date varchar2(10) :='20190725';
v_sql varchar2(1000);
begin
v_sql:='create table vt_new as select * from sc_emp where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_new select empno,ename,sal,'||cr_date||',''29991231''
from ls_emp';
execute immediate v_sql;
commit;
v_sql:='create table vt_int as select * from vt_new where 1=2';
execute immediate v_sql;
v_sql:='insert into vt_int(empno,ename,sal,start_date,stop_date)
select empno,ename,sal,start_date,stop_date
from vt_new
where (empno,ename,sal) not in (
select empno,ename,sal from sc_emp
where stop_date =''29991231'')';
execute immediate v_sql;
commit;
v_sql:='update sc_emp set stop_date ='||cr_date||'where empno in (select empno from vt_int)';
execute immediate v_sql;
commit;
v_sql:='insert into sc_emp select * from vt_new';
execute immediate v_sql;
commit;
v_sql:='drop table vt_new';
execute immediate v_sql;
v_sql:='drop table vt_int';
execute immediate v_sql;
end;
结果:这是因为系统内数据类型的自动转换么?
如果不用存储过程,使用sql语句的话就没有这种现象
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货