--创建表
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;
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;