有表TREE,根据某ID查询其所有子节点,其中某个子节点可能在不同的父节点下出现,求SQL下面附上表和部分数据
drop table TREE cascade constraints;
/*==============================================================*/
/* Table: TREE */
/*==============================================================*/
create table TREE (
ID VARCHAR2(32) not null,
PID VARCHAR2(32),
constraint PK_TREE primary key (ID)
);ID PID
————————————————
001
1001 !!001!!
1002 !!001!!
1003 !!001!!,!!001001!!
1004 !!001!!,!!001002!!
1005 !!001001!!,!!001002!!
1006 !!001!!,!!001002!!
1007 !!001001!!,!!001002!!
drop table TREE cascade constraints;
/*==============================================================*/
/* Table: TREE */
/*==============================================================*/
create table TREE (
ID VARCHAR2(32) not null,
PID VARCHAR2(32),
constraint PK_TREE primary key (ID)
);ID PID
————————————————
001
1001 !!001!!
1002 !!001!!
1003 !!001!!,!!001001!!
1004 !!001!!,!!001002!!
1005 !!001001!!,!!001002!!
1006 !!001!!,!!001002!!
1007 !!001001!!,!!001002!!
ID是子节点么,P_ID是父节点?
SELECT T.ID
FROM TREE T
WHERE INSTR(T.PID,
CHR(33) || CHR(33) || '00' || '&ID' || CHR(33) || CHR(33),
1) > 0;
SQL> SELECT T.ID
2 FROM TREE T
3 WHERE INSTR(T.PID,
4 CHR(33) || CHR(33) || '00' || '&ID' || CHR(33) || CHR(33),
5 1) > 0;ID
--------------------------------
1003
1005
1007
CHILD_ID VARCHAR2(20);
CURSOR CUR_ID IS
SELECT T.ID
FROM TREE T
WHERE INSTR(T.PID,
CHR(33) || CHR(33) || '00' || IN_ID || CHR(33) || CHR(33),
1) > 0;
BEGIN
FOR I IN CUR_ID LOOP
CHILD_ID := I.ID;
DBMS_OUTPUT.PUT_LINE(CHILD_ID);
END LOOP;END GET_IDS;
------------------------
--pl/sql语句块中调用存储过程
BEGIN
GET_IDS('1002');
END;