#围栏车左下纵坐标
declare longLow DECIMAL;
#围栏车左下横坐标
declare latLow DECIMAL;
#围栏车左上纵坐标
declare longHigh DECIMAL;
#围栏车左上横坐标
declare latHigh DECIMAL;
#外循环计数器
declare isSuccuss int DEFAULT 0;
#内循环计数器
declare isSuccussIn int DEFAULT 0;
#计数器
declare counts int default 0;
DECLARE cur_Enclosure_ResultSet CURSOR FOR select CAST(e.longLow as DECIMAL(3,7)),CAST(e.latLow as decimal(2,7)),CAST(e.longHigh as decimal(3,7)),cast(e.latHigh as decimal(2,7)),e.driveInOut,e.carTag from enclosure as e where e.carCode = carCode; /*carCode 过程的 in 参数*/
#经度
#装换为小数类型
set @longitude_d = cast(longitude as DECIMAL(3,7));
#纬度
#装换为小数类型
set @latitude_d = cast(latitude as decimal(2,7));
OPEN cur_Enclosure_ResultSet;
if cur_Enclosure_ResultSet <> null then
while counts<2 do
FETCH from cur_Enclosure_ResultSet INTO longLow,latLow,longHigh,latHigh,driveInOut,enclosureCarTag;
while isSuccussIn != 0 DO
IF (driverInOut is NOT NULL) AND (driveInOut != 0) then
if (@longitude_d-longLow>0) and (@longitude_d-longHigh<0) and (@latitude_d-latLow>0) and (@latitude_d-latHigh<0) then
set alarmType = '围栏报警';
set alarmCarTag = enclosureCarTag;
insert into alarmHistory values(alarmType,alarmCarTag,carCode,inTime);
set counts = counts + 1;
set isSuccussIn = 0;
end if;
end if;
end while;
while isSuccuss !=0 do
if(driverInOut is NOT NULL) AND (driveInOut = 0)then
if (@longitude_d-longLow<0) or (@longitude_d-longHigh>0) or (@latitude_d-latLow<0) or (@latitude_d-latHigh>0) then
set alarmType = '围栏报警';
set alarmCarTag = enclosureCarTag;
insert into alarmHistory values(alarmType,alarmCarTag,carCode,inTime);
set counts = counts + 1;
set isSuccuss = 0 ;
end if;
end if ;
end while;
end while;
close cur_Enclosure_ResultSet ;
end if ;现在的问题是我call的时候老是报 No data to FETCH 的出错
我单独测试了下面这个查询,有结果的
select CAST(e.longLow as DECIMAL(3,7)),CAST(e.latLow as decimal(2,7)),CAST(e.longHigh as decimal(3,7)),cast(e.latHigh as decimal(2,7)),e.driveInOut,e.carTag from enclosure as e where e.carCode = carCode;
不知道问题出在哪里了,请各位大侠帮忙啦!
还有就是我如果没有查询到数据这样写会不会报错呀？ 是不是要在 Fetch 之前截断这个取值? 语句怎么写
SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
在 FETCH 语句中引用的游标位置处于结果表最后一行之后。if cur_Enclosure_ResultSet <> null then
...
end if ;
改为
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
REPEAT
....
UNTIL done END REPEAT;
DECLARE cur_Enclosure_ResultSet CURSOR FOR select CAST(e.longLow as DECIMAL(3,7)),CAST(e.latLow as decimal(2,7)),CAST(e.longHigh as decimal(3,7)),cast(e.latHigh as decimal(2,7)),e.driveInOut,e.carTag from enclosure as e where e.carCode = carCode;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
#经度
#装换为小数类型
set @longitude_d = cast(longitude as DECIMAL(3,7));
#纬度
#装换为小数类型
set @latitude_d = cast(latitude as decimal(2,7));
OPEN cur_Enclosure_ResultSet;
REPEAT
while counts<2 do
FETCH next from cur_Enclosure_ResultSet INTO longLow,latLow,longHigh,latHigh,driveInOut,enclosureCarTag;
while isSuccussIn != 0 DO
IF (driverInOut is NOT NULL) AND (driveInOut != 0) then
if (@longitude_d-longLow>0) and (@longitude_d-longHigh<0) and (@latitude_d-latLow>0) and (@latitude_d-latHigh<0) then
set alarmType = '围栏报警';
set alarmCarTag = enclosureCarTag;
insert into alarmHistory(alarmType,alarmCarTag,alarmCarCode,alarmTime) values(alarmType,alarmCarTag,carCode,inTime);
set counts = counts + 1;
set isSuccussIn = 0;
end if;
end if;
end while;
while isSuccuss !=0 do
if(driverInOut is NOT NULL) AND (driveInOut = 0)then
if (@longitude_d-longLow<0) or (@longitude_d-longHigh>0) or (@latitude_d-latLow<0) or (@latitude_d-latHigh>0) then
set alarmType = '围栏报警';
set alarmCarTag = enclosureCarTag;
insert into alarmHistory(alarmType,alarmCarTag,alarmCarCode,alarmTime) values(alarmType,alarmCarTag,carCode,inTime);
set counts = counts + 1;
set isSuccuss = 0 ;
end if;
end if ;
end while;
end while;
UNTIL done END REPEAT;
close cur_Enclosure_ResultSet ;
和while isSuccuss !=0 这两个条件去一下,只执行存在情况下的数据,然后对不满足条件一一去除,判断一下出错点是在哪.
^^
没看到有关mysql存储过程的列子啊
http://blog.chinaunix.net/u/29134/
DECLARE cur_Enclosure_ResultSet CURSOR FOR select CAST(e.longLow as DECIMAL(3,7)),CAST(e.latLow as decimal(2,7)),CAST(e.longHigh as decimal(3,7)),cast(e.latHigh as decimal(2,7)),e.driveInOut,e.carTag from enclosure as e where e.carCode = carCode; /*carCode 过程的 in 参数*/
------------------
好久没有编程了，猜测：
可能是在这个声明中，数据进入游标后，游标的指针处于结尾，这个时候，再去FETCH，就得不到任何数据；这个时候游标本身可能是不为空的。可能是在填充游标后，把游标的指针设回到开始。反正我以前在ms-sql下，在FETCH前，都是习惯的把游标指针重设到最开始。
---
游标其实是系统开辟的一块内存map，select into的时候，内存recorder的指针（即游标的指针）也会往下走，而select into结束后，指针却不会自动复位，而是在最后的地方。这个指针不是显式的，在FETCH的时候会自动往下走，因此用游标很可能出错。
---
一种常用的替代的做法是用临时表，在ms-sql中就是select into #tb，然后set 每次取道的recorder数目为1，做loop，自己控制要处理的纪录（相当于游标中的纪录）。
看manual中给的例子，也是只用了open。
我是菜鸟中的菜鸟，迷惑中
或者，
DECLARE cur_Enclosure_ResultSet CURSOR FOR select CAST(e.longLow as DECIMAL(3,7)) as c1， ? 一定要有别名才能放入数据？不会这么傻吧？
CREATE PROCEDURE `justifyGroupNum`() NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '' BEGIN /*how to run:call justifyGroupNum()*/ DECLARE p_group_id int; declare p_num int; declare stopFlag int; DECLARE cursor_name CURSOR FOR select c_group_id,count(*) as num from `t_group_member` where c_valid in (3,4) group by c_group_id; DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; OPEN cursor_name; REPEAT FETCH cursor_name INTO p_group_id,p_num; begin update t_groupinfo set c_member_number=p_num where c_group_id=p_group_id; end; UNTIL stopFlag = 1 END REPEAT; CLOSE cursor_name; END;
如果不会基础参考这个吧 ！
DECLARE cursor_name CURSOR FOR select c_group_id,count(*) as num
谢谢