gbase8s存储过程一些隐藏的错误写法

在某项目现场使用存储过程返回结果集时最后一条数据会重复一次

比如应该返回  1,2,3,4,5 实际返回 1,2,3,4,5,5

写法如下

CREATE PROCEDURE p14(v_rq int)
returning   varchar(5000) AS stationname;
define global v1_stationname varchar(50) default null;
define execsql  varchar(1000);
let execsql = "SELECT tabname from systables where tabid<"||v_rq;
prepare stmt FROM execsql;
declare c1 cursor for stmt; 
open c1;
while(SQLCODE != 100)
fetch c1 into v1_stationname; 
return v1_stationname with resume;
end while; 
END PROCEDURE;

问题出现在while 循环中

当游标处于最后一条记录时满足 sqlcode !=100 ,然后还会再次进入 while 循环中 ，导致最后一条数据会重复一次，因为是先return  再退出循环，所以需要在 return 前就退出循环

修改如下

drop PROCEDURE p14;
CREATE PROCEDURE p14(v_rq int)
returning   varchar(5000) AS stationname;
define  v1_stationname varchar(50) ;
define execsql  varchar(1000);
let execsql = "SELECT tabname from systables where tabid<"||v_rq;
prepare stmt FROM execsql;
declare c1 cursor for stmt; 
open c1;
while(1=1)
fetch c1 into v1_stationname; 
if SQLCODE = 100 then exit;
end if;
return v1_stationname with resume;
end while; 
close c1;
END PROCEDURE;