plsql 实战题目 一：

一：  通过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;