在10g中可以遍历游标而9i只能走一次或两次。
包create or replace package pkg_mouthtime
as
type mycur_type is ref cursor;
procedure proc_alltime(oldmouth in varchar2,my_cur out mycur_type);
end;
/create or replace package body pkg_mouthtime
as
procedure proc_alltime(oldmouth in varchar2,my_cur out mycur_type)
as
begin
open my_cur for 'select to_char(to_date(to_char(to_date(:1||''-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')+
dbms_random.value(0,to_char(last_day(to_date(:2,''yyyy-mm'')),''dd'')-1),
''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss''),round(dbms_random.value(1,1800))
from dual connect by rownum <=round(dbms_random.value(0,to_char(last_day(to_date(:3,''yyyy-mm'')),''dd'') ))'
using oldmouth,oldmouth,oldmouth;
end;
end;/
调用
--自定义记录类型
type my_row2_type is record(
dd varchar2(20),
ff number
);
--声明自定义row
myrowtype2 my_row2_type;
my_row2 myrowtype2%type;type mycur_type2 is ref cursor;
my_cur2 mycur_type2;
pkg_mouthtime.proc_alltime(my_row.dd,my_cur2); loop
fetch my_cur2 into my_row2;
exit when my_cur2%notfound;
insert into t_charge values(seq_t_charge.nextval,my_row2.dd,my_row2.ff,trunc(my_row2.ff*2/100,2),var_phone);
var_num:=var_num+trunc(my_row2.ff*2/100);
end loop;
包create or replace package pkg_mouthtime
as
type mycur_type is ref cursor;
procedure proc_alltime(oldmouth in varchar2,my_cur out mycur_type);
end;
/create or replace package body pkg_mouthtime
as
procedure proc_alltime(oldmouth in varchar2,my_cur out mycur_type)
as
begin
open my_cur for 'select to_char(to_date(to_char(to_date(:1||''-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')+
dbms_random.value(0,to_char(last_day(to_date(:2,''yyyy-mm'')),''dd'')-1),
''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss''),''yyyy-mm-dd hh24:mi:ss''),round(dbms_random.value(1,1800))
from dual connect by rownum <=round(dbms_random.value(0,to_char(last_day(to_date(:3,''yyyy-mm'')),''dd'') ))'
using oldmouth,oldmouth,oldmouth;
end;
end;/
调用
--自定义记录类型
type my_row2_type is record(
dd varchar2(20),
ff number
);
--声明自定义row
myrowtype2 my_row2_type;
my_row2 myrowtype2%type;type mycur_type2 is ref cursor;
my_cur2 mycur_type2;
pkg_mouthtime.proc_alltime(my_row.dd,my_cur2); loop
fetch my_cur2 into my_row2;
exit when my_cur2%notfound;
insert into t_charge values(seq_t_charge.nextval,my_row2.dd,my_row2.ff,trunc(my_row2.ff*2/100,2),var_phone);
var_num:=var_num+trunc(my_row2.ff*2/100);
end loop;
另外从9I开始,直接使用sys_refcursor类型就行了。不用再先去申明个ref cursor类型。
不过这种情况,如果你两处的表里的“数据”都完全一致,应该不会啊。
你把查询SQL提出来单独在两个数据库中分别执行一下,看有问题吗。