本来使用的是NAVICAT SQL工具里写的过程,然后过程在使用的时候也没错,后面部署使用PHPMYADMIN进行导入的时候老说存储过程有错误
#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 6 CREATE PROCEDURE `sp_test`(user_id_input int,category_id_input int,page_size int,current_page_input int)
begin declare latest_ware_id int; declare current_ware_sort int; declare total_record int; declare current_page int; declare limit_subject_start int; declare current_page_mod int; declare current_subject_id int;#判断如果传递了页数的情况 if current_page_input is not null then begin set limit_subject_start = (current_page_input-1)*page_size; select subject_name,img_path,description,current_page_input as current_page,kbl_subject.id from kbl_subject where subject_type = category_id_input order by sort asc limit limit_subject_start,page_size; end; else begin #计算最后一个课程的ID select kbl_ware_record.ware_id into latest_ware_id from kbl_ware_record left join kbl_ware on kbl_ware.id = kbl_ware_record.ware_id left join kbl_waretype on kbl_waretype.id =waretype_id where play_flag =2 and user_id =user_id_input and waretype_id =category_id_input order by kbl_ware_record.addtime desc limit 0,1; if latest_ware_id is null then#如果用户最后一个课件的ID为空,说明用户没有上过课,取第一页的数据 begin select id into current_subject_id from kbl_subject where subject_type = category_id_input order by sort asc limit 0,1;#取得排序最小的主题ID select kbl_subject.id,subject_name,img_path,description,1 as current_page,current_subject_id from kbl_subject where subject_type = category_id_input order by sort asc limit 0,page_size;#取得第一页的数据 end; else begin #计算数据总共条数 #select count(*) as num into total_record from kbl_subject; #计算当前课件所在的主题的排序 select kbl_subject.sort,kbl_subject.id into current_ware_sort,current_subject_id from kbl_subject_ware left join kbl_subject on kbl_subject.id = kbl_subject_ware.subject_id where ware_id = latest_ware_id; #sort为空的情况一定要HLOD住 if current_ware_sort is not null then begin #计算当前课件所在的页数 set current_page_mod = current_ware_sort mod page_size; #取余数 set current_page = current_ware_sort div page_size; #取取整 if current_page_mod <>0 then #余数不为0,页数加一 begin set current_page = current_page+1; end; end if; end; else#如果主题的排序为空,说明没有给课件设置主题 begin select "error"; end; end if; end; #计算当前主题所在页数的主题列表 if current_page is not null then begin set limit_subject_start = ((current_page-1)*page_size);#从当前页数减1乘以页数开始取。 select kbl_subject.id,subject_name,img_path,description,current_page as current_page,current_subject_id from kbl_subject where subject_type = category_id_input order by sort asc limit limit_subject_start,page_size; end; end if; end if; end;end if;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 6 CREATE PROCEDURE `sp_test`(user_id_input int,category_id_input int,page_size int,current_page_input int)
begin declare latest_ware_id int; declare current_ware_sort int; declare total_record int; declare current_page int; declare limit_subject_start int; declare current_page_mod int; declare current_subject_id int;#判断如果传递了页数的情况 if current_page_input is not null then begin set limit_subject_start = (current_page_input-1)*page_size; select subject_name,img_path,description,current_page_input as current_page,kbl_subject.id from kbl_subject where subject_type = category_id_input order by sort asc limit limit_subject_start,page_size; end; else begin #计算最后一个课程的ID select kbl_ware_record.ware_id into latest_ware_id from kbl_ware_record left join kbl_ware on kbl_ware.id = kbl_ware_record.ware_id left join kbl_waretype on kbl_waretype.id =waretype_id where play_flag =2 and user_id =user_id_input and waretype_id =category_id_input order by kbl_ware_record.addtime desc limit 0,1; if latest_ware_id is null then#如果用户最后一个课件的ID为空,说明用户没有上过课,取第一页的数据 begin select id into current_subject_id from kbl_subject where subject_type = category_id_input order by sort asc limit 0,1;#取得排序最小的主题ID select kbl_subject.id,subject_name,img_path,description,1 as current_page,current_subject_id from kbl_subject where subject_type = category_id_input order by sort asc limit 0,page_size;#取得第一页的数据 end; else begin #计算数据总共条数 #select count(*) as num into total_record from kbl_subject; #计算当前课件所在的主题的排序 select kbl_subject.sort,kbl_subject.id into current_ware_sort,current_subject_id from kbl_subject_ware left join kbl_subject on kbl_subject.id = kbl_subject_ware.subject_id where ware_id = latest_ware_id; #sort为空的情况一定要HLOD住 if current_ware_sort is not null then begin #计算当前课件所在的页数 set current_page_mod = current_ware_sort mod page_size; #取余数 set current_page = current_ware_sort div page_size; #取取整 if current_page_mod <>0 then #余数不为0,页数加一 begin set current_page = current_page+1; end; end if; end; else#如果主题的排序为空,说明没有给课件设置主题 begin select "error"; end; end if; end; #计算当前主题所在页数的主题列表 if current_page is not null then begin set limit_subject_start = ((current_page-1)*page_size);#从当前页数减1乘以页数开始取。 select kbl_subject.id,subject_name,img_path,description,current_page as current_page,current_subject_id from kbl_subject where subject_type = category_id_input order by sort asc limit limit_subject_start,page_size; end; end if; end if; end;end if;end;
DELIMITER $$
你的SP语句,在最后的end;后面加上$$
DELIMITER ;在MYSQL命令行 OR 在PHPMYADMIN中PASTE上述语句,看看运行,提示什么
DELIMITER //
DROP PROCEDURE IF EXISTS lp_user_pay //
CREATE PROCEDURE lp_user_pay(p_pay_id INT)
LABEL_PROC:
BEGIN
........
END LABEL_PROC //
DELIMITER ;
BEGIN
foo1;
foo2;
foo3;
END;
在建存储过程的时候,这一整句是单独一句SQL。
在执行xxx()的时候,foo1~foo3是各自独立的3句,要区别这两个不同的分割符(CREATE的结束,和运行时,每句的结束),参考3F。亦即,用//代表CREATE的结束,存储过程运行时每句的结尾,还是使用分号。
CREATE PROCEDURE `sp_getCurrentSubject`(user_id_input int,category_id_input int,page_size int,out current_page_input int,out current_subject_id int)
begin declare latest_ware_id mediumint;
declare current_ware_sort int;
declare total_record int;
declare current_page int;
declare limit_subject_start int;
declare current_page_mod int;
declare current_subject_id int;#判断如果传递了页数的情况
if current_page_input is not null then
begin
set limit_subject_start = current_page_input*page_size; select subject_name,img_path,description from kbl_subject order by sort asc limit limit_subject_start,page_size;
end;
else
begin
#计算最后一个课程的ID
select kbl_ware_record.ware_id into latest_ware_id from kbl_ware_record left join kbl_ware on
kbl_ware.id = kbl_ware_record.ware_id left join kbl_waretype on kbl_waretype.id =waretype_id where play_flag =2
and user_id =user_id_input and waretype_id =category_id_input order by kbl_ware_record.addtime desc limit 0,1; if latest_ware_id is null then#如果用户最后一个课件的ID为空,说明用户没有上过课,取第一页的数据
begin
select subject_name,img_path,description,1 as current_page from kbl_subject order by sort asc limit 0,page_size;#取得第一页的数据
select id into current_subject_id from kbl_subject order by sort asc limit 0,1;#取得排序最小的主题ID
end;
else
begin #计算数据总共条数
#select count(*) as num into total_record from kbl_subject;
#计算当前课件所在的主题的排序
select kbl_subject.sort,kbl_subject.id into current_ware_sort,current_subject_id from kbl_subject_ware left join kbl_subject on kbl_subject.id = kbl_subject_ware.subject_id where ware_id = latest_ware_id; #sort为空的情况一定要HLOD住
if current_ware_sort is not null then
begin
#计算当前课件所在的页数
set current_page_mod = current_ware_sort mod page_size; #取余数
set current_page = current_ware_sort div page_size; #取取整
if current_page_mod <>0 then #余数不为0,页数加一
begin
set current_page = current_page+1;
end;
end if;
end;
else#如果主题的排序为空,说明没有给课件设置主题
begin
select "unset ware subject!";
end;
end if;
end;
#计算当前主题所在页数的主题列表
if current_page is not null then
begin
set limit_subject_start = ((current_page-1)*page_size);#从当前页数减1乘以页数开始取。
select subject_name,img_path,description,current_page as current_page from kbl_subject order by sort asc limit limit_subject_start,page_size;
end;
end if;
end if; end;
end if;
end;;
DELIMITER ;
end;
else
begin select ' at line 17
命令行报这样的错误
现在我换了PHPMYADMIN,
加了DELIMITER $$
之后#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 'limit_subject_start,page_size; end; else begin
修改了,但老提示我这句有问题。。 唉。。
select subject_name,img_path,description from kbl_subject order by sort asc limit limit_subject_start,page_size;
DELIMITER ;;
CREATE PROCEDURE `sp_getCurrentSubject`(user_id_input INT,category_id_input INT,page_size INT,OUT current_page_input INT,OUT current_subject_id INT)
BEGIN DECLARE latest_ware_id MEDIUMINT;
DECLARE current_ware_sort INT;
DECLARE total_record INT;
DECLARE current_page INT;
DECLARE limit_subject_start INT;
DECLARE current_page_mod INT;
DECLARE current_subject_id INT;
-- #判断如果传递了页数的情况
IF current_page_input IS NOT NULL THEN
BEGIN
SET limit_subject_start = current_page_input*page_size; SELECT subject_name,img_path,description FROM kbl_subject ORDER BY sort ASC LIMIT limit_subject_start,page_size;-- 估计要用动态执行 END;
ELSE
BEGIN
-- #计算最后一个课程的ID
SELECT kbl_ware_record.ware_id INTO latest_ware_id FROM kbl_ware_record LEFT JOIN kbl_ware ON
kbl_ware.id = kbl_ware_record.ware_id LEFT JOIN kbl_waretype ON kbl_waretype.id =waretype_id WHERE play_flag =2
AND user_id =user_id_input AND waretype_id =category_id_input ORDER BY kbl_ware_record.addtime DESC LIMIT 0,1; IF latest_ware_id IS NULL THEN#如果用户最后一个课件的ID为空,说明用户没有上过课,取第一页的数据
BEGIN
SELECT subject_name,img_path,description,1 AS current_page FROM kbl_subject ORDER BY sort ASC LIMIT 0,page_size;#取得第一页的数据
SELECT id INTO current_subject_id FROM kbl_subject ORDER BY sort ASC LIMIT 0,1;#取得排序最小的主题ID
END;
ELSE
BEGIN #计算数据总共条数
#select count(*) as num into total_record from kbl_subject;
#计算当前课件所在的主题的排序
SELECT kbl_subject.sort,kbl_subject.id INTO current_ware_sort,current_subject_id FROM kbl_subject_ware LEFT JOIN kbl_subject ON kbl_subject.id = kbl_subject_ware.subject_id WHERE ware_id = latest_ware_id; #sort为空的情况一定要HLOD住
IF current_ware_sort IS NOT NULL THEN
BEGIN
#计算当前课件所在的页数
SET current_page_mod = current_ware_sort MOD page_size; #取余数
SET current_page = current_ware_sort DIV page_size; #取取整
IF current_page_mod <>0 THEN #余数不为0,页数加一
BEGIN
SET current_page = current_page+1;
END;
END IF;
END;
ELSE#如果主题的排序为空,说明没有给课件设置主题
BEGIN
SELECT "unset ware subject!";
END;
END IF;
END;
#计算当前主题所在页数的主题列表
IF current_page IS NOT NULL THEN
BEGIN
SET limit_subject_start = ((current_page-1)*page_size);#从当前页数减1乘以页数开始取。
SELECT subject_name,img_path,description,current_page AS current_page FROM kbl_subject ORDER BY sort ASC LIMIT limit_subject_start,page_size;
END;
END IF;
END IF; END;
END IF;
END;;
DELIMITER ;
prepare stml from @asql;
execute stml;