In practice zipper table, the incoming character of time (2019-07-25), after only four years, finally the results, if remove the middle horizontal line - can be normal, according to is this?? !

 
The create table ls_emp as select * from emp; 

The create table sc_emp as select empno, ename, sal, 
To_char (sysdate, 'yyyy - mm - dd) the 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); 
The begin 
V_sql:='create table vt_new as select * from sc_emp where 1=2'. 
The execute immediate v_sql; 
V_sql:="insert into vt_new select empno, ename, sal, '| | cr_date | |', ' '2999-12-31', '
The from ls_emp '; 
The execute immediate v_sql; 
commit; 
V_sql:='create table vt_int as select * from vt_new where 1=2'. 
The execute immediate v_sql; 
V_sql:='insert into vt_int (empno, ename, sal, the start_date, stop_date) 
The select empno, ename, sal, the start_date, stop_date 
The from vt_new 
The where (empno, ename, sal) not in (
The select empno, ename, sal from sc_emp 
Where stop_date='2999-12-31') '; 
The execute immediate v_sql; 
commit; 

V_sql:='update sc_emp set stop_date=' | | cr_date | | 'where empno in (select empno from vt_int)'; 
The execute immediate v_sql; 
commit; 

V_sql:="insert into sc_emp select * from vt_new '; 
The execute immediate v_sql; 
commit; 
V_sql:='drop table vt_new'; 
The execute immediate v_sql; 
V_sql:='drop table vt_int'; 
The execute immediate v_sql; 
The end; 

Results:

But if remove the incoming parameters - the horizontal line inside it would have a normal

 declare 
Cr_date varchar2 (10) :='20190725'. 
V_sql varchar2 (1000); 
The begin 
V_sql:='create table vt_new as select * from sc_emp where 1=2'. 
The execute immediate v_sql; 
V_sql:="insert into vt_new select empno, ename, sal, '| | cr_date | |', ' '29991231', '
The from ls_emp '; 
The execute immediate v_sql; 
commit; 
V_sql:='create table vt_int as select * from vt_new where 1=2'. 
The execute immediate v_sql; 
V_sql:='insert into vt_int (empno, ename, sal, the start_date, stop_date) 
The select empno, ename, sal, the start_date, stop_date 
The from vt_new 
The where (empno, ename, sal) not in (
The select empno, ename, sal from sc_emp 
Where stop_date='29991231') '; 
The execute immediate v_sql; 
commit; 

V_sql:='update sc_emp set stop_date=' | | cr_date | | 'where empno in (select empno from vt_int)'; 
The execute immediate v_sql; 
commit; 

V_sql:="insert into sc_emp select * from vt_new '; 
The execute immediate v_sql; 
commit; 
V_sql:='drop table vt_new'; 
The execute immediate v_sql; 
V_sql:='drop table vt_int'; 
The execute immediate v_sql; 
The end; 

Results:

This is because the system automatic conversion of data types?
If it's not a stored procedure, use the SQL statement, there is no this kind of phenomenon

CodePudding user response:

1, directly to the '2019-07-25 s' substitution variables, do not use variable, 2, the variable - rung, is feasible, and can solve the above two methods, that could be problem because variable mass participation in Oracle converts this variable to the inside, so how to limit internal transformation, or how to hold the type of the variable??????? !

CodePudding user response:

To the great god!