DELIMITER $$
DROP PROCEDURE IF EXISTS `testdb`.`code` $$
CREATE PROCEDURE `code`(in sno char(10))
BEGIN
DECLARE done INT DEFAULT 0;
declare inta,seq0,qseq int;
declare flag char(1);
declare curseq cursor for select seq from sto_iproduces where pslipno =sno;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
set inta=0,qseq=1,seq0=0,flag='0';
open curseq;
fetch curseq into seq0;
while done=0 do
set inta=inta+1;
set flag='2';
if seq0<>inta then
set qseq=inta;
set flag='1';
set done=1;
end if;
fetch curseq into seq0;
end while;
close curseq;
if flag='2' then
set qseq=inta+1;
end if;
select qseq;
END $$
DELIMITER ;
注:1.pslipno的參數為sno,可別定義與pslipno相同,或者資料表中的欄位名同名
2.seq為欄位名,其變數為seq0若設為一樣則沒有值
3.此範例應用於:資料表有slipno+seq為主KEY,要找seq所缺號
