Question

mysql中我写了一个存储函数 为什么会报错 请指点 着急

表名：total 字段：id，tablenames，time_colum,fix_time

delimiter &&
create function alarm_info()
returns varchar(100) reads sql data
begin
declare counts int(11);
declare n int(11);
declare fixs int(11);
declare times varchar(20);
declare diff int(11);
declare names varchar(20);
declare result varchar(100);
set n=1;
select count(*) into counts from total;
while counts>=n
select tablenames,time_colum,fix_time into names,times,fixs from total where id=n;
execute immediate 'select round(time_to_sec(timediff(sysdate(),(select times from names order by id desc limit 1)/60)' into diff;
if(diff>fix_time) then result=tablenames;
end if;
n=n+1;
end &&;

delimiter;

Answer 1

MySQL 里面, 好像没有
execute immediate 'select round(time_to_sec(timediff(sysdate(),(select times from names order by id desc limit 1)/60)' into diff;

这种写法的。
可以修改为：
select round(time_to_sec(timediff(sysdate(),(select times from names order by id desc limit 1)/60) into diff;

或者
SET diff = round(time_to_sec(timediff(sysdate(),(select times from names order by id desc limit 1)/60)

还有后面的 n=n+1;
修改为
SET n=n+1;

追问

可是 还是继续报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
if(diff>fix_time) then result=tablenames;
end if;
set n=n+1;
end while;
end' at line 15

追答

WHILE 的语法不正确

要
WHILE 条件 DO

END WHILE;

下面是一个例子。

mysql> DELIMITER //
mysql> CREATE PROCEDURE TestWhile()
-> BEGIN
-> DECLARE v_index INT;
->
-> SET v_index = 0;
->
-> WHILE v_index SET v_index = v_index + 1;
-> SELECT v_index;
-> END WHILE;
->
-> END//
Query OK, 0 rows affected (0.00 sec)

追问

弱弱的问下，请问存储函数如果没有没有参数 怎么调用？

追答

直接 SELECT 函数名字 () 就可以了。
下面是一个例子：

mysql> CREATE FUNCTION HelloWorld4()
-> RETURNS VARCHAR(20)
-> BEGIN
-> RETURN 'Hello World!';
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> select HelloWorld4() //
+---------------+
| HelloWorld4() |
+---------------+
| Hello World! |
+---------------+
1 row in set (0.00 sec)

追问

嗯 您说的那些我已经改过了，但是调用的时候还是不行呐。
mysql> select alarm_info();
ERROR 1146 (42S02): Table 'fenda.names' doesn't exist 能不能帮我再看下，names是我定义的变量，好像是循环没被执行，不知道应该改哪里/？

追答

你把代码粘贴出来看看啊。
我现在在这个页面， 查找 fenda .
也就 ERROR 1146 (42S02): Table 这一个地方有
我也不知道你的 函数怎么写的啊。