mysql 存储过程创建总是出错
我是在phpmyadmin的SQL下运行下面语句
drop procedure if exists productpricing;
create procedure productpricing()
begin
select danhao from s_jc_tbhead;
end;
总是报错这样的错,#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 '' at line 3
如果我把select danhao from s_jc_tbhead;这行删除,然后再运行就不会报错,而select danhao from s_jc_tbhead这条语句单个运行是正确的,请问哪里出错了呢
补充:谢谢,可能我数据库有点问题,本来是5.0的,应该是支持存储过程的,但一运行你的代码就死机,结束程序后,再运行就会报错,我把数据库升级成5.1了,现在可以正常运行你的代码,谢谢
追问:运行这个,出现#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 'delimiter' at line 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;
仅保留这些代码我是实的能用
其他:delimiter $$
drop procedure if exists productpricing $$
create procedure productpricing()
begin
select danhao from s_jc_tbhead;
end;
$$
delimiter ; select danhao into xxx from s_jc_tbhead;
一定要接into,否则你查询有什么意义呢,不能使用查询结果. 在phpmyadmin中,点击“SQL”
在
[ 语句定界符 ] 在此再次显示此查询
中,填;;(两个;)
然后,再把你的句子改一下子:
drop procedure if exists productpricing;;
create procedure productpricing()
begin
select danhao from s_jc_tbhead;
end;;
一定要注意,上面的两个;;,一个;
相信我,一定能成功。
上一个:mysql:MEDIUMTEXT字段默认值问题
下一个:navicat管理mysql,mysql设置密码之后,navicat使用新密码不能链接空密码却可以