semovy

/*drop table categories;
create table categories
(
  id int primary key ,
  pid int ,
  name varchar(64),
  constraint cc_fk foreign key(pid)  references categories(id)  on  delete cascade
);

create or replace procedure ins_cat(v_id int,v_pid int,v_name varchar2) as
begin
 insert into categories values(v_id,v_pid,v_name);
end ins_cat;

*/

--delete from categories;
/*
begin
ins_cat(1,null,'category1');
ins_cat(2,null,'category2');
ins_cat(3,1,'category3');
ins_cat(4,1,'category4');
ins_cat(5,2,'category5');
ins_cat(6,2,'category6');
ins_cat(7,4,'category7');
ins_cat(8,4,'category8');
ins_cat(9,5,'category9');
end;
*/
select * from categories;

--start with .. connect by 遞歸遍歷無限級類別
select * from categories start with id=2
 connect by prior id=pid;