Question

mysql建立存储过程出错

# 建立存储过程p_dir_info
DELIMITER $$

DROP PROCEDURE IF EXISTS disk.p_dir_info$$
CREATE DEFINER=root@localhost PROCEDURE disk.p_dir_info(in user varchar(20), in parent_path varchar(255))
BEGIN
select * from
(select d.user, d.path , d.parent_path, d.dir, d.create_time ,
(case isnull(sum(f.count)) when true then 0 else sum(f.count) end) as count,
(case isnull(sum(f.size)) when true then 0 else sum(f.size) end) as size
from t_directories d left join
(select path, count(file) as count, sum(size) as size from t_files group by path) f on instr(f.path, d.path) = 1
where d.user=user and d.parent_path=parent_path group by path) dir order by create_time;
END $$

DELIMITER ;

在Navicat中运行出现如下错误：
Query OK, 0 rows affected

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE disk.p_dir_info(in user varchar(20), in parent_path varchar(255))
BE' at line 1

请高手帮忙解决下，十分感谢

Answer 1

CREATE PROCEDURE p_dir_info (in user varchar(20), in parent_path varchar(255))
BEGIN
select * from
(select d.user, d.path , d.parent_path, d.dir, d.create_time ,
(case isnull(sum(f.count)) when true then 0 else sum(f.count) end) as count,
(case isnull(sum(f.size)) when true then 0 else sum(f.size) end) as size
from t_directories d left join
(select path, count(file) as count, sum(size) as size from t_files group by path) f on instr(f.path, d.path) = 1
where d.user=user and d.parent_path=parent_path group by path) dir order by create_time;
END;

仅保留这些代码我是实的能用