使用游标数据迁移

PL/SQL Developer Test script 3.0
52
declare 
--行类型
  row_emp T_LEDGER%rowtype;
--显式游标
  cursor cur_emp is
  select t.* from T_LEDGER T,t_Coursetypes c where t.course_id=c.id and t.account_no<>c.course_code;
    id varchar2(60);
    accountid varchar2(60);
    accountno varchar2(40);
    floatrate number(8,5);
    EFFECTIVE_DATE date;
    INVALID_DATE date;
    REMARK varchar2(100);
    centerid varchar2(32);
    status number;
    inputuser varchar2(32);
    inputtime varchar2(11);
    inputdate date;      
    --定义SQL语句
    sql_stmt varchar2(10000);
begin
--打开游标
  open cur_emp ;
  --循环游标
  loop
  --游标结束后停止循环
    exit when cur_emp%notfound;
  --取出游标的值 
    fetch cur_emp into row_emp ;
        id :=sys_guid();
        accountid :=row_emp.id;
        accountno :=row_emp.account_no;
        floatrate:=1;
        EFFECTIVE_DATE:=sysdate;
        INVALID_DATE:=null;
        REMARK:=null;
        centerid:='0300002';
        status:=1;
        inputuser:='50293830b23c11e71e22f5b0ecd1d656';
        inputtime:=to_char(sysdate,'HHmmss');
        inputdate:=TRUNC(sysdate);
          --定义SQL语句
          sql_stmt := 'INSERT INTO t_floatrate VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12)';
          --执行SQL语句
          EXECUTE IMMEDIATE sql_stmt USING id, accountid,accountno,floatrate,EFFECTIVE_DATE,INVALID_DATE,REMARK,centerid,status,inputuser,inputtime,inputdate; 
    
    --DBMS_OUTPUT.PUT_LINE(row_emp.ename);
--停止循环 
 end loop;
--关闭游标
  close cur_emp ;
end;
0
0