DROP FUNCTION IF EXISTS `Func_app_GetClassIdByPid`;DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `Func_app_GetClassIdByPid`
(
p_pid INT,
p_sqlcmd VARCHAR(8000)
)
RETURNS VARCHAR(8000)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_classid INT;
DECLARE v_count INT;
DECLARE cur_class CURSOR FOR
SELECT i_class_id FROM t WHERE i_pid = p_pid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET @@max_sp_recursion_depth = 10;
-- SET max_sp_recursion_depth = 10; OPEN cur_class;
REPEAT
FETCH cur_class INTO v_classid;
SET p_sqlcmd = CONCAT(p_sqlcmd, v_classid, ',');
SET v_count = 0;
SELECT COUNT(*) INTO v_count FROM t
WHERE i_pid = v_classid;
IF v_count > 0 THEN
SELECT Func_app_GetClassIdByPid(v_classid, p_sqlcmd) INTO p_sqlcmd;
END IF;
UNTIL done END REPEAT;
CLOSE cur_class;
RETURN p_sqlcmd;
END$$DELIMITER ;t表的结构为create table t
(
i_class_id int primary key,
i_pid int
);
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货