一: 通过dblink 迁移900 万数据,到另外一个库:
create or replace procedure p_login as
cursor cur_login is
select * from user_login@px_dblink; ---- 远程的表
type type_login is table of number; ---- 定义数据类型,也可以用OSS_READMAIL_DAY_DETAIL.usernumber%TYPE;
v_user_id type_login;
type type_login1 is table of number;
v_login type_login1;
type type_login2 is table of date;
v_login_time type_login2;
begin
lv_errinfo varchar2(2000);
open cur_login;
loop
fetch cur_login bulk collect
into v_user_id, v_login, v_login_time limit 5000; --- 使用批量游标
forall i in 1 .. v_user_id.count
insert into user_login
values
(v_user_id(i), v_login(i), v_login_time(i));
commit;
insert into log_t(id,createtime) values(seq_id.nextval,sysdate);
commit;
exit when cur_login%notfound or cur_login%notfound is null;
end loop;
close cur_login;
exception when others then
rollback;
lv_errinfo := '错误信息:' || SQLERRM;
insert into t_log(seqid,msg,createtime) values(seq_log,lv_errinfo,sysdate);
commit;
end p_login;
二: 将如下表的结果按照小时进行汇总:
create table fact_login_cn
(statedate number, login_cn int,userid number);
create or replace procedure p_fact as
cursor cur_fact is
select to_char(login_time, 'yyyymmddhh24'), count(1), user_id
from user_login
group by user_id, to_char(login_time, 'yyyymmddhh24');
type type_statedate is table of number;
v_statedate type_statedate;
type type_login_cn is table of int;
v_login_cn type_login_cn;
type type_userid is table of number;
v_userid type_userid;
begin
open cur_fact;
loop
fetch cur_fact bulk collect
into v_statedate, v_login_cn, v_userid limit 5000;
forall i in 1.. v_statedate.count
insert into fact_login_cn
values
(v_statedate(i), v_login_cn(i), v_userid(i));
commit;
exit when cur_fact%notfound or cur_fact%notfound is null;
end loop;
close cur_fact;
end p_fact;