先用select语句得出最大的level,然后对level做for i in 1..maxlevel loop DELETE from demo t where t.id in (select id from demo where connect_by_isleaf=1 start with parentid=入参 connect by prior name=parentid); end loop;
我是刚毕业的菜菜,接触不到那个。不过我执行那语句的时候报 ORA-02292: 违反完整约束条件 (ETRAINING.SYS_C008310) - 已找到子记录! 这是SQL: delete from t_chapter c where c.chpt_id in (select chpt_id from t_chapter start with parent = 195301 Connect by prior chpt_id = parent) ;
大概意思就是这样 SQL> CREATE OR REPLACE PROCEDURE proc_del_demo(i_root VARCHAR2) AS 2 v_lvl NUMBER; 3 BEGIN 4 SELECT MAX(LEVEL) 5 INTO v_lvl 6 FROM demo 7 START WITH parentid = i_root 8 CONNECT BY PRIOR NAME = parentid; 9 FOR i IN 1 .. v_lvl LOOP 10 DELETE FROM demo t 11 WHERE t.id IN (SELECT id 12 FROM demo 13 WHERE connect_by_isleaf = 1 14 START WITH parentid = i_root 15 CONNECT BY PRIOR NAME = parentid); 16 END LOOP; 17 END; 18 /
Procedure created
SQL> select * from demo;
ID NAME PARENTID ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 1 A -1 2 B A 3 C B 4 D C 5 E D
SQL> exec proc_del_demo('-1');
PL/SQL procedure successfully completed
SQL> select * from demo;
ID NAME PARENTID ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
where id in(select id from demo start with parentid=-1 connect by name=prior parentid)
DELETE from demo t where t.id in (select id from demo where connect_by_isleaf=1 start with parentid=入参 connect by prior name=parentid);
end loop;
ORA-02292: 违反完整约束条件 (ETRAINING.SYS_C008310) - 已找到子记录!
这是SQL:
delete from t_chapter c where c.chpt_id in (select chpt_id from t_chapter start with parent = 195301 Connect by prior chpt_id = parent) ;
select找出最大LEVEL,如果最大级是个集合,有N个同级的怎么处理啊?
SQL> CREATE OR REPLACE PROCEDURE proc_del_demo(i_root VARCHAR2) AS
2 v_lvl NUMBER;
3 BEGIN
4 SELECT MAX(LEVEL)
5 INTO v_lvl
6 FROM demo
7 START WITH parentid = i_root
8 CONNECT BY PRIOR NAME = parentid;
9 FOR i IN 1 .. v_lvl LOOP
10 DELETE FROM demo t
11 WHERE t.id IN (SELECT id
12 FROM demo
13 WHERE connect_by_isleaf = 1
14 START WITH parentid = i_root
15 CONNECT BY PRIOR NAME = parentid);
16 END LOOP;
17 END;
18 /
Procedure created
SQL> select * from demo;
ID NAME PARENTID
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 A -1
2 B A
3 C B
4 D C
5 E D
SQL> exec proc_del_demo('-1');
PL/SQL procedure successfully completed
SQL> select * from demo;
ID NAME PARENTID
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SQL>
辛苦拉!~~~呵呵。公司用的hibernate,不知道hibernate支持不支持存储呢!~~~
实在不行 用递归去了····玩命了!