求一个转移数据的存储过程（要求用游标）

Question

求一个转移数据的存储过程（要求用游标）实现功能：
数据库内有表A和表B，其结构完全相同。要求把表A内的全部数据逐条移动到表B，并打印每条的基本信息（可能要做一些处理），要求必须用游标。如果移动某条数据失败，需要打印出该条数据的主键内容。伪代码：
SP
begin
  declare cursor cur_a for select * from A for update;
  open cur_a;
  loop
  {
     fetch cur_a into ....;
     print ....
     insert into B ...;
     出错处理
     delete 该条数据 from A ...
     出错处理
  }
end;请哪位帮忙给出oracle的存储过程，谢谢

Answer 1

SQL> desc dept;
Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
DEPTNO NUMBER(2)                              
DNAME  VARCHAR2(14) Y                         
LOC    VARCHAR2(13) Y                         
 
SQL> create table dept1 as select * from dept where 1 > 2;
 
Table created
 
SQL> select count(*) from dept1;
 
  COUNT(*)
----------
         0SQL> create or replace procedure pro is
  2  begin
  3  for cur in(select * from dept) loop
  4  begin
  5  dbms_output.put_line(cur.deptno || ','|| cur.dname || ','|| cur.loc);
  6  insert into dept1 values(cur.deptno ,cur.dname ,cur.loc);
  7  exception
  8  when others then
  9  dbms_output.put_line(cur.deptno);
 10  delete from dept where deptno = cur.deptno;
 11  end;
 12  end loop;
 13  commit;
 14  end;
 15  /
 
Procedure created
 
SQL> exec pro;
 
PL/SQL procedure successfully completed
 
SQL> select * from dept1;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

Answer 2

delete 后面还应该加上异常处理

Answer 3

1.两表的表结构完全一样
2.A表所谓的基本信息为哪些？打印下主键
3.导致失败的原因是什么？比如insert B 失败，或者delete A失败
4.失败的话，在内部打印主键，在SP的外部ROOLBACK，（外部也是一个主SP在调用它）

Answer 4

declare
cursor cur is select * from a for update;
begin
for c1 in cur loop
  begin
  insert into b values(c1.id,c1.a,c1.b);
  exception
    when others then
    dbms_output.put_line('插入异常:'||c1.id);
  end;
  dbms_output.put_line(c1.id);
  begin
  delete from a where current of cur;
  exception
    when others then
    dbms_output.put_line('删除异常:'||c1.id);
  end;
end loop;
end;假设id为主键

Answer 5

还有另一个需求，对于另外一对A'和B'，结构依然完全相同，要实现一样的操作。
但是考虑性能，不用游标，就简单的把A的数据直接移动到B，但是要300条数据一个transaction

Answer 6

  自己创建两张表:名字为T1 和T2 
然后 把T1 插满数据 T2 不需要数据
执行这个  你OK了
DECLARE
  P_COUNT INTEGER;
  CURSOR X_CURSOR IS
    SELECT * FROM T1; --定义游标--
  --定义数据类型--
  T_DATA_TYPE X_CURSOR%ROWTYPE;
BEGIN
  OPEN X_CURSOR; --打开游标--
  --循环游标--
  LOOP
    FETCH X_CURSOR
      INTO T_DATA_TYPE; --将游标查询的数据放入这个类型中--
    EXIT WHEN X_CURSOR%NOTFOUND; --游标找不到数据的时候退出--
    --将数据循环的插入表格t2--
    INSERT INTO T2 VALUES (T_DATA_TYPE.T_ID, T_DATA_TYPE.T_NAME);
    DBMS_OUTPUT.PUT_LINE('用户ID:' || T_DATA_TYPE.T_ID || '    ' || '用户名:' ||
                         T_DATA_TYPE.T_NAME);
  END LOOP;
  --发生异常的时候--
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('你发异常了');
  
END;

Answer 7

还有另一个需求，对于另外一对A'和B'，结构依然完全相同，要实现一样的操作。 
但是考虑性能，不用游标，就简单的把A的数据直接移动(不是拷贝)到B，但是要300条数据一个transaction

Answer 8

create or replace procedure pp_insert
as
begin
  while 1=1 loop
      execute immediate 'insert into ta select * from tb where rownum<=300';
      if SQL%NOTFOUND then
         exit;
      end if;
      commit;
  end loop;
  commit;
  --异常处理
  EXCEPTION WHEN OTHERS THEN
  rollback;
  RETURN;END;

Answer 9

关键我还要把原来表内的数据删掉，这样要求在一个transaction里不但要insert，还要把原来的那300条删除掉

Answer 10

先在B表插数据，execute immediate 'insert into ta select * from tb where rownum<=300';
删除的话，凡是B表里有的主键，就从A删除，用EXISTS

Answer 11

既然有一次300条的限制那就简单多了给你几个例子，拼一下就可以了批量获取数据，并限制行数DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
  l_tab t_bulk_collect_test_tab;
  CURSOR c_data IS
    SELECT * FROM bulk_collect_test;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data BULK COLLECT
      INTO l_tab LIMIT 10000;
    DBMS_OUTPUT.put_line(l_tab.count || ‘ rows’);
    EXIT WHEN c_data%NOTFOUND;
  END LOOP CLOSE c_data;
END;
/
forall带returning的例子，你可以把主键returning回来用来删除原表中的数据DECLARE
  TYPE t_desc_tab IS TABLE OF t1.description%TYPE;
  TYPE t_tab IS TABLE OF t1%ROWTYPE;
  l_desc_tab t_desc_tab := t_desc_tab('FIVE', 'SIX', 'SEVEN');
  l_tab      t_tab;
BEGIN
  FORALL i IN l_desc_tab.first .. l_desc_tab.last
    INSERT INTO t1
    VALUES
      (t1_seq.nextval, l_desc_tab(i))
    RETURNING id, description BULK COLLECT INTO l_tab;
  FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('INSERT ID=' || l_tab(i)
                         .id || ' DESC=' || l_tab(i).description);
  END LOOP;
  COMMIT;
END;
/
批量操作错误处理的例子，可以知道集合里面是第几个元素有问题DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  -- The zeros in this list will cause divide-by-zero errors.
  num_tab NumList := NumList(10, 0, 11, 12, 30, 0, 20, 199, 2, 0, 9, 1);
  errors  NUMBER;
  dml_errors EXCEPTION;
  PRAGMA exception_init(dml_errors, -24381);
BEGIN
  -- SAVE EXCEPTIONS means don't stop if some DELETEs fail.
  FORALL i IN num_tab.FIRST .. num_tab.LAST SAVE EXCEPTIONS
    DELETE FROM emp2 WHERE salary > 500000 / num_tab(i);
  -- If any errors occurred during the FORALL SAVE EXCEPTIONS,
  -- a single exception is raised when the statement completes.
EXCEPTION
  WHEN dml_errors THEN
    -- Now we figure out what failed and why.
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    dbms_output.put_line('Number of DELETE statements that failed: ' ||
                         errors);
    FOR i IN 1 .. errors LOOP
      dbms_output.put_line('Error #' || i || ' occurred during ' ||
                           'iteration #' || SQL%BULK_EXCEPTIONS(i)
                           .ERROR_INDEX);
      dbms_output.put_line('Error message is ' ||
                           SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
END;
/

Answer 12

如果想要出错以后不影响后面的数据转移，可以建临时表把错误的数据转移到里面去，建议每300跳提交时使用匿名事务文档
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems002.htm例子
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php注意批量操作的每次取数一定要大于100，否则直接一条一条处理比较快