ORACLE 递归删除数据

数据库设计：

CREATE  TABLE  ADVERTISE_CATEGORY
(
       CATEGORY_ID       NUMBER PRIMARY KEY,  --节点ID 
       CATEGORY_NAME     VARCHAR2(500),       --节点名字 
       PARENT_ID         NUMBER                          --父节点ID
)

表数据

删除 节点ID为1的存储过程

create or replace procedure sp_advertise_category_del
(
       v_cate_id  in number
)
 is
 begin
       delete from advertise_category where category_id in
        (select category_id from advertise_category  start with category_id=v_cate_id connect by prior category_id = parent_id);
 end;