java mysql作用是什么意思_java查询mysql数据库的有什么作用

匿名用户

1级

2017-01-03 回答

远标java老师说过MYSQL中没有Oracle类似的CONNECT BY 语句，如果要实现树状查询的话，如果是5.0以上版本可以使用存储过程：

DELIMITER $$

DROP PROCEDURE IF EXISTS `sx_finance`.`pro_channel_class`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_channel_class`(in parentid int)

begin

declare level smallint default 0;

declare cnt int default 0;

create temporary table tt(class_id int,class_name varchar(20),PARENT_ID int,level smallint,sort varchar(1000));

create temporary table tt2(class_id int,class_name varchar(20),PARENT_ID int,level smallint,sort varchar(1000));

insert into tt select class_id,class_name,PARENT_ID,level,cast(class_id as char) from CHANNEL_CLASS where PARENT_ID=parentid;

select row_count() into cnt;

insert into tt2 select * from tt;

while cnt>0 do

set level=level+1;

truncate table tt;

insert into tt select a.class_id,a.class_name, a.PARENT_ID,level,concat(b.sort,a.class_id) from CHANNEL_CLASS a,tt2 b where a.PARENT_ID=b.class_id and b.level=level-1;

select row_count() into cnt;

insert into tt2 select * from tt;

end while;

select class_id, concat(space(a.level*2),'|---',a.class_name) class_name from tt2 a order by sort;

drop table tt;

drop table tt2;

end$$

DELIMITER ;

要不就只能用JAVA程序实现