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;