CREATE OR REPLACE PROCEDURE TEST4 (
v_starttime varchar2, --开始日期
v_endtime varchar2, --结束日期
)ISv_starttimenew date;
v_endtimenew date;
type cur is ref cursor ;
my_cur cur;
rs TABLE1%ROWTYPE;
sqlstr VARCHAR2(4000); --动态SQL拼接
beginv_starttimenew :=to_date(v_starttime,'yyyymmdd') ;v_endtime :=to_date(v_endtime,'yyyymmdd') ;
--sqlstr:='select T_ID, T_NAME, T_TIME from TABLE1 where T_TIME>=to_date('''||v_starttime||''',''yyyymmdd'') and T_TIME<=to_date('''||v_endtime||''',''yyyymmdd'') '||v_string||'';
sqlstr:='select T_ID, T_NAME, T_TIME from TABLE1 where T_TIME>=' ||v_starttimenew|| 'and T_TIME<='||v_endtimenew;
open my_cur for sqlstr;
loop
fetch my_cur into rs;
exit when my_cur%notfound;
insert into TABLE2( T_ID ,T_NAME ,T_TIME,T_DEPT,INDATE)
values(rs.T_ID, rs.T_NAME,rs.T_TIME,NULL,SYSDATE);
end loop;
close my_cur ;--关闭游标
commit;
end TEST4;表结构如下:
TABLE1(
T_ID NUMBER,
T_NAME VARCHAR2(10),
T_TIME DATE
)
TABLE2(
T_ID NUMBER,
T_NAME VARCHAR2(10),
T_TIME DATE,
T_DEPT VARCHAR2(10),
INDATE DATE
)
v_starttime varchar2, --开始日期
v_endtime varchar2, --结束日期
)ISv_starttimenew date;
v_endtimenew date;
type cur is ref cursor ;
my_cur cur;
rs TABLE1%ROWTYPE;
sqlstr VARCHAR2(4000); --动态SQL拼接
beginv_starttimenew :=to_date(v_starttime,'yyyymmdd') ;v_endtime :=to_date(v_endtime,'yyyymmdd') ;
--sqlstr:='select T_ID, T_NAME, T_TIME from TABLE1 where T_TIME>=to_date('''||v_starttime||''',''yyyymmdd'') and T_TIME<=to_date('''||v_endtime||''',''yyyymmdd'') '||v_string||'';
sqlstr:='select T_ID, T_NAME, T_TIME from TABLE1 where T_TIME>=' ||v_starttimenew|| 'and T_TIME<='||v_endtimenew;
open my_cur for sqlstr;
loop
fetch my_cur into rs;
exit when my_cur%notfound;
insert into TABLE2( T_ID ,T_NAME ,T_TIME,T_DEPT,INDATE)
values(rs.T_ID, rs.T_NAME,rs.T_TIME,NULL,SYSDATE);
end loop;
close my_cur ;--关闭游标
commit;
end TEST4;表结构如下:
TABLE1(
T_ID NUMBER,
T_NAME VARCHAR2(10),
T_TIME DATE
)
TABLE2(
T_ID NUMBER,
T_NAME VARCHAR2(10),
T_TIME DATE,
T_DEPT VARCHAR2(10),
INDATE DATE
)
v_starttime varchar2, --开始日期
v_endtime varchar2, --结束日期
)ISv_starttimenew date;
v_endtimenew date;
rs TABLE1%ROWTYPE;
type cur is ref cursor return rs;
my_cur cur;
sqlstr VARCHAR2(4000); --动态SQL拼接
beginv_starttimenew :=to_date(v_starttime,'yyyymmdd') ;v_endtime :=to_date(v_endtime,'yyyymmdd') ;
--sqlstr:='select T_ID, T_NAME, T_TIME from TABLE1 where T_TIME>=to_date('''||v_starttime||''',''yyyymmdd'') and T_TIME<=to_date('''||v_endtime||''',''yyyymmdd'') '||v_string||'';
sqlstr:='select T_ID, T_NAME, T_TIME from TABLE1 where T_TIME>=' ||v_starttimenew|| 'and T_TIME<='||v_endtimenew;
open my_cur for sqlstr;
loop
fetch my_cur into rs;
exit when my_cur%notfound;
insert into TABLE2( T_ID ,T_NAME ,T_TIME,T_DEPT,INDATE)
values(rs.T_ID, rs.T_NAME,rs.T_TIME,NULL,SYSDATE);
end loop;
close my_cur ;--关闭游标
commit;
end TEST4;表结构如下:
TABLE1(
T_ID NUMBER,
T_NAME VARCHAR2(10),
T_TIME DATE
)
TABLE2(
T_ID NUMBER,
T_NAME VARCHAR2(10),
T_TIME DATE,
T_DEPT VARCHAR2(10),
INDATE DATE
)
CREATE OR REPLACE PROCEDURE TEST4 (
v_starttime varchar2, --开始日期
v_endtime varchar2, --结束日期
)
CREATE OR REPLACE PROCEDURE TEST4 (
v_starttime varchar2, --开始日期
v_endtime varchar2 --结束日期
)IS
.....
.....
beginv_starttimenew :=to_date(v_starttime,'yyyymmdd') ;v_endtimenew :=to_date(v_endtime,'yyyymmdd') ; --估计你这里写错了,应该是v_endtimenew 而不是v_endtime....
....end TEST4;