MySQL資料庫的存儲過程declare語句報錯遊標示例

MySQL的存儲過程declare語句報錯遊標示例

DELIMITER $$   
CREATE DEFINER=`root`@`localhost` 
PROCEDURE
`stu_ave_cursor_procedure`()
BEGIN
declare aveno char(11);
declare avename char(10);
declare avegrade float;
drop table if exists fail_student;
create table fail_student (aveno char(11),
avename char(10), avegrade float);
open stuave;
while not flag do
fetch stuave into aveno,avename,avegrade;
declare avegrade float;
declare flag int;
declare stuave cursor for
select student.sno,sname,avg(grade)
from student,sc
where student.sno=sc.sno
group by student.sno;
DECLARE CONTINUE HANDLER FOR
NOT FOUND SET flag=1;
set flag=0;
fetch stuave into aveno,avename,avegrade;
if (not flag) then
if (avegrade<60) then
insert into fail_student()
values(aveno,avename,avegrade);
end if;
end if;
end while;
close stuave;
END
DELIMITER ;
DELIMITER $$ 
USE `student-course` $$ 
CREATE DEFINER=`root`@`localhost` PROCEDURE `cour_ave_cursor_procedure`() 
BEGIN
declare aveno char(11);
declare avename char(10);
declare avegrade float;
declare flag int;

declare courave cursor for
select course.cno,cname,avg(grade)
from course,sc
where course.cno=sc.cno
group by course.cno;

DECLARE CONTINUE HANDLER FOR
NOT FOUND SET flag=1;
set flag=0;

drop table if exists `student-course`.fail_course;
create table fail_course (aveno char(11),avename char(30), avegrade float);

open courave;
while not flag do
fetch courave into aveno,avename,avegrade;
if (not flag) then
if (avegrade<70) then
insert into fail_course()
values(aveno,avename,avegrade);
end if;
end if;
end while;
close courave;

END  $$ 
DELIMITER ;
DELIMITER $$  
DELIMITER ;
不寫則會報錯

不過到現在都沒太明白,為什麼 mysql 工具一定要個 delimiter

語法上是不需要 delimiter 就可以判斷得出語句分割的,程式用 ODBC 提交多語

句的時候,這種東東就完全沒有問題的,所以應該客戶端工具稍微改進一下就可以>不要 delimiter 瞭吧

You May Also Like