在練習拉鏈表，傳入字符型的時間后（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陳述句的話就沒有這種現象
uj5u.com熱心網友回復：
1、直接將'2019-07-25'替換變數，不使用變數，2、將變數內的-橫杠去掉，也是可行的，以上兩種方法都可以解決，說明有可能問題是因為變數傳參時Oracle內部將此變數給轉換了，那么該怎么限制內部轉換呢，或者說怎么固定住變數的型別呢？？？！uj5u.com熱心網友回復：
來大神啊
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/36508.html
標籤：基礎和管理
上一篇：帶有UNION ALL的快速重繪物化視圖建立的物化視圖日志問題
下一篇：關于oracle權限查詢