oracle-recursion
CREATE TABLE BBS
(
PARENT_ID NUMBER(9, 0) NOT NULL,
ID NUMBER(9, 0) NOT NULL,
NAME VARCHAR2(200) NOT NULL
);
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,0,'论坛首页');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,1,'数据库开发');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,11,'DB2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,111,'DB2 文章1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1111,'DB2 文章1 的评论1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1112,'DB2 文章1 的评论2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,112,'DB2 文章2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,12,'Oracle');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,2,'Java 技术');
SELECT
LEVEL,
CONNECT_BY_ISLEAF,
CONNECT_BY_ROOT NAME,
SYS_CONNECT_BY_PATH(NAME, '/')
FROM
BBS
START WITH NAME = 'DB2'
CONNECT BY PRIOR ID = PARENT_ID
ORDER SIBLINGS BY NAME
SELECT
*
FROM
bbs;
update bbs set parent_id=1112,id=111
where name = 'DB2 文章2';
SELECT
CONNECT_BY_ISCYCLE,
NAME
FROM
BBS
CONNECT BY NOCYCLE PRIOR ID = PARENT_ID;
UPDATE bbs
SET parent_id = 11,
id = 112
WHERE
name = 'DB2 文章2';
WITH TEMP(PARENT_ID,ID,NAME) AS
(
SELECT PARENT_ID,ID,NAME FROM BBS WHERE NAME='DB2'
UNION ALL
SELECT B.PARENT_ID,B.ID,B.NAME FROM BBS B, TEMP T WHERE B.PARENT_ID=T.ID
)
SELECT NAME FROM TEMP;
SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100;