Oracel对树的复制操作

--创建表
CREATE TABLE SCOTT_TREE
(
  ID        VARCHAR2(32),
  NAME      VARCHAR2(100),
  PARENT_ID VARCHAR2(32)
)


--查询
SELECT ST.*
  FROM SCOTT_TREE ST
 START WITH ST.NAME = 'ROOT'
CONNECT BY PRIOR ST.ID = ST.PARENT_ID
 ORDER BY LEVEL ASC;


--使用存储过程处理树的复制粘贴
CREATE OR REPLACE PROCEDURE COPY_TREE AS
   TYPE LOOKUP IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(32);  --定义存放id的PL/SQL表
   TYPE TREE_TABLE IS TABLE OF SCOTT_TREE%ROWTYPE;              --定义存放表信息的PL/SQL表
   LOOK      LOOKUP;
   RECORDS   TREE_TABLE;
   V_ID      SCOTT_TREE.ID%TYPE;
BEGIN
   SELECT ST.* BULK COLLECT
    INTO RECORDS
    FROM SCOTT_TREE ST
    START WITH ST.NAME = 'ROOT'
    CONNECT BY PRIOR ST.ID = ST.PARENT_ID;


    FOR I IN RECORDS.FIRST() .. RECORDS.LAST() LOOP
      V_ID := SYS_GUID();
      LOOK(RECORDS(I).ID) := V_ID;      --将新生成的id暂存到记录表里


      RECORDS(I).ID := V_ID;
      IF RECORDS(I).NAME <> 'ROOT' THEN
        RECORDS(I).PARENT_ID := LOOK(RECORDS(I).PARENT_ID);
      END IF;
    END LOOP;
 --批量想表中插入数据
  FORALL J IN RECORDS.FIRST() .. RECORDS.LAST()
   INSERT INTO SCOTT_TREE VALUES RECORDS (J);
END COPY_TREE;