DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_get_treedata`$$CREATE PROCEDURE `sp_get_treedata`(IN i_First_Introduer varchar(20),
OUT o_Result varchar(2000)
)
BEGIN
declare v_level int;
declare v_flag tinyint;
drop TABLE IF EXISTS tb_get_treeuser_tmp;
CREATE TABLE tb_get_treeuser_tmp
(
id int auto_increment primary key,
Tree_Level int,
igtxtNickNameTxt varchar(20),
igtxtRecommendCardNoTxt varchar(20)
);
Set v_level = 1;
SET v_flag=0;
INSERT into tb_get_treeuser_tmp(Tree_Level,igtxtNickNameTxt,igtxtRecommendCardNoTxt)
SELECT v_level,igtxtNickNameTxt,igtxtRecommendCardNoTxt FROM user WHERE igtxtRecommendCardNoTxt=i_First_Introduer;
REPEAT INSERT into tb_get_treeuser_tmp(Tree_Level,igtxtNickNameTxt,igtxtRecommendCardNoTxt)
SELECT v_level+1,a.igtxtNickNameTxt,a.igtxtRecommendCardNoTxt
FROM user a, tb_get_treeuser_tmp b
WHERE a.igtxtRecommendCardNoTxt=b.igtxtNickNameTxt and b.Tree_Level=v_level;
SET v_flag = FOUND_ROWS();
SET v_level = v_level + 1;
UNTIL (v_flag=0) END REPEAT;
SET @str='';
SELECT @str := concat(@str,igtxtNickNameTxt,',') FROM tb_get_treeuser_tmp;
SET @str=left(@str,char_length(@str)-1);
SET o_Result = @str;
DROP TABLE IF EXISTS tb_get_treeuser_tmp;
END$$DELIMITER ;
==========================================================================================================
java设置输出数据属性是 var么?? 怎么接受呢。。老是报错
String procedure = "{call sp_get_treedata('000005',?) }";
cs = conn.prepareCall(procedure);
cs.registerOutParameter(1, Types.VARCHAR);
rs = cs.executeQuery(); while (rs.next()) {
System.out.print( rs.getString(0));
}
。。老是报错
java.sql.SQLException: Column Index out of range, 0 > 1.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.checkColumnBounds(ResultSet.java:662)
at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5477)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5420)
OUT o_Result varchar(2000)
)
BEGIN
declare v_level int;
declare v_flag tinyint;
drop TABLE IF EXISTS tb_get_treeuser_tmp;
CREATE TABLE tb_get_treeuser_tmp
(
id int auto_increment primary key,
Tree_Level int,
igtxtNickNameTxt varchar(20),
igtxtRecommendCardNoTxt varchar(20)
);
Set v_level = 1;
SET v_flag=0;
INSERT into tb_get_treeuser_tmp(Tree_Level,igtxtNickNameTxt,igtxtRecommendCardNoTxt)
SELECT v_level,igtxtNickNameTxt,igtxtRecommendCardNoTxt FROM user WHERE igtxtRecommendCardNoTxt=i_First_Introduer;
REPEAT INSERT into tb_get_treeuser_tmp(Tree_Level,igtxtNickNameTxt,igtxtRecommendCardNoTxt)
SELECT v_level+1,a.igtxtNickNameTxt,a.igtxtRecommendCardNoTxt
FROM user a, tb_get_treeuser_tmp b
WHERE a.igtxtRecommendCardNoTxt=b.igtxtNickNameTxt and b.Tree_Level=v_level;
SET v_flag = FOUND_ROWS();
SET v_level = v_level + 1;
UNTIL (v_flag=0) END REPEAT;
SET @str='';
SELECT @str := concat(@str,igtxtNickNameTxt,',') FROM tb_get_treeuser_tmp;
SET @str=left(@str,char_length(@str)-1);
SET o_Result = @str;
DROP TABLE IF EXISTS tb_get_treeuser_tmp;
END$$DELIMITER ;
==========================================================================================================
java设置输出数据属性是 var么?? 怎么接受呢。。老是报错
String procedure = "{call sp_get_treedata('000005',?) }";
cs = conn.prepareCall(procedure);
cs.registerOutParameter(1, Types.VARCHAR);
rs = cs.executeQuery(); while (rs.next()) {
System.out.print( rs.getString(0));
}
。。老是报错
java.sql.SQLException: Column Index out of range, 0 > 1.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.checkColumnBounds(ResultSet.java:662)
at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5477)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5420)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货