PreparedStatement catHierarchy=conn.prepareStatement("WITH RecCatList(LEVEL,cat_id,par_cat_id,name,description) AS ("+ " select 1,ROOT.cat_id,ROOT.par_cat_id,ROOT.name,ROOT.description FROM category ROOT where ROOT.cat_id=? UNION ALL"+ " select PARENT.LEVEL+1,CHILD.cat_id,CHILD.par_cat_id,CHILD.name,CHILD.description from RecCatList PARENT,category CHILD"+ " where PARENT.cat_id=CHILD.par_cat_id and PARENT.LEVEL>?)"+ " select LEVEL,cat_id,par_cat_id,name,description from RecCatList"); 为什么也不对?!!!
create procedure PreparedStatement @cat_id int , @LEVEL int as set nocount onselect 1 as LEVEL,ROOT.cat_id,ROOT.par_cat_id,ROOT.name,ROOT.description into #t FROM category ROOT where ROOT.cat_id=@cat_iddeclare @i int set @i =1 while exists (select PARENT.LEVEL+1,CHILD.cat_id,CHILD.par_cat_id,CHILD.name,CHILD.description from #t PARENT,category CHILD where PARENT.cat_id=CHILD.par_cat_id and PARENT.LEVEL=@i) begin insert #t select PARENT.LEVEL+1,CHILD.cat_id,CHILD.par_cat_id,CHILD.name,CHILD.description from #t PARENT,category CHILD where PARENT.cat_id=CHILD.par_cat_id and PARENT.LEVEL=@i set @i=@i+1 endselect LEVEL,cat_id,par_cat_id,name,description from #t where LEVEL>@LEVELdrop table #tgo--调用: exec PreparedStatement 1,3--大概是这样吧
" select 1,ROOT.cat_id,ROOT.par_cat_id,ROOT.name,ROOT.description FROM category ROOT where ROOT.cat_id=? UNION ALL"+
" select PARENT.LEVEL+1,CHILD.cat_id,CHILD.par_cat_id,CHILD.name,CHILD.description from RecCatList PARENT,category CHILD"+
" where PARENT.cat_id=CHILD.par_cat_id and PARENT.LEVEL>?)"+
" select LEVEL,cat_id,par_cat_id,name,description from RecCatList");
为什么也不对?!!!
你不能改存储过程吗?
我已经是用存储过程了!!!!!(PreparedStatement )
你的QQ!
我想和你深聊!
我用的是ACCESS
@cat_id int ,
@LEVEL int
as
set nocount onselect 1 as LEVEL,ROOT.cat_id,ROOT.par_cat_id,ROOT.name,ROOT.description
into #t
FROM category ROOT where ROOT.cat_id=@cat_iddeclare @i int
set @i =1
while exists (select PARENT.LEVEL+1,CHILD.cat_id,CHILD.par_cat_id,CHILD.name,CHILD.description from #t PARENT,category CHILD
where PARENT.cat_id=CHILD.par_cat_id
and PARENT.LEVEL=@i)
begin
insert #t
select PARENT.LEVEL+1,CHILD.cat_id,CHILD.par_cat_id,CHILD.name,CHILD.description from #t PARENT,category CHILD
where PARENT.cat_id=CHILD.par_cat_id
and PARENT.LEVEL=@i
set @i=@i+1
endselect LEVEL,cat_id,par_cat_id,name,description from #t
where LEVEL>@LEVELdrop table #tgo--调用:
exec PreparedStatement 1,3--大概是这样吧
还是不能递归!!!
up