第一次用存储过程
功能在mem_user_buffer表有相应的sys_user.uid插入随机5条记录,没有的话插入随机3条记录到user_renown_task,
取随机数取到一样的值的话用给定的值在mysql 5.0.46-enterprise-gpl-log中出来一堆的错,晕
[code]
CREATE Procedure Procedure_User_Renown_Task()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE @uid INT;
DECLARE @weimingzhao INT;
DECLARE @tid1 INT;
DECLARE @tid2 INT;
DECLARE @tid3 INT;
DECLARE @tid4 INT;
DECLARE @tid5 INT;
set @tid1=0;
set @tid2=0;
set @tid3=0;
set @tid4=0;
set @tid5=0;
DECLARE User_Cursor CURSOR FOR
SELECT u.uid,ifnull(b.endtime,0)
FROM sys_user AS u LEFT JOIN mem_user_buffer AS b ON u.uid=b.uid AND u.uid>1000 AND buftype=1100000 AND endtime>=unix_timestamp();
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN User_Cursor;
REPEAT
FETCH NEXT FROM User_Cursor INTO @uid, @weimingzhao;
BEGIN
set @tid1 = SELECT floor(rand()*62);
set @tid2 = SELECT floor(rand()*62);
set @tid3 = SELECT floor(rand()*62);
IF (@tid1==@tid2 OR @tid1==@tid3 OR @tid2==@tid3) THEN
set @tid1 = 1101001;
set @tid2 = 1101010;
set @tid3 = 1101019;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid1),(@uid,@tid2),(@uid,@tid3);
IF (@weimingzhao>0) THEN
set @tid4 = SELECT floor(rand()*62);
set @tid5 = SELECT floor(rand()*62);
IF (@tid4==@tid1 OR @tid4==@tid2 OR @tid4==@tid3 OR @tid4==@tid5 OR @tid5==@tid1 OR @tid5==@tid2 OR @tid5==@tid3) THEN
set @tid4 = 1101033;
set @tid5 = 1101052;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid4),(@uid,@tid5);
END IF;
END;
UNTIL stopFlag = 1
END REPEAT;
CLOSE User_Cursor;
END ;
[/code]
功能在mem_user_buffer表有相应的sys_user.uid插入随机5条记录,没有的话插入随机3条记录到user_renown_task,
取随机数取到一样的值的话用给定的值在mysql 5.0.46-enterprise-gpl-log中出来一堆的错,晕
[code]
CREATE Procedure Procedure_User_Renown_Task()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE @uid INT;
DECLARE @weimingzhao INT;
DECLARE @tid1 INT;
DECLARE @tid2 INT;
DECLARE @tid3 INT;
DECLARE @tid4 INT;
DECLARE @tid5 INT;
set @tid1=0;
set @tid2=0;
set @tid3=0;
set @tid4=0;
set @tid5=0;
DECLARE User_Cursor CURSOR FOR
SELECT u.uid,ifnull(b.endtime,0)
FROM sys_user AS u LEFT JOIN mem_user_buffer AS b ON u.uid=b.uid AND u.uid>1000 AND buftype=1100000 AND endtime>=unix_timestamp();
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN User_Cursor;
REPEAT
FETCH NEXT FROM User_Cursor INTO @uid, @weimingzhao;
BEGIN
set @tid1 = SELECT floor(rand()*62);
set @tid2 = SELECT floor(rand()*62);
set @tid3 = SELECT floor(rand()*62);
IF (@tid1==@tid2 OR @tid1==@tid3 OR @tid2==@tid3) THEN
set @tid1 = 1101001;
set @tid2 = 1101010;
set @tid3 = 1101019;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid1),(@uid,@tid2),(@uid,@tid3);
IF (@weimingzhao>0) THEN
set @tid4 = SELECT floor(rand()*62);
set @tid5 = SELECT floor(rand()*62);
IF (@tid4==@tid1 OR @tid4==@tid2 OR @tid4==@tid3 OR @tid4==@tid5 OR @tid5==@tid1 OR @tid5==@tid2 OR @tid5==@tid3) THEN
set @tid4 = 1101033;
set @tid5 = 1101052;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid4),(@uid,@tid5);
END IF;
END;
UNTIL stopFlag = 1
END REPEAT;
CLOSE User_Cursor;
END ;
[/code]
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE @uid INT;
DECLARE @weimingzhao INT;
DECLARE @tid1 INT;
DECLARE @tid2 INT;
DECLARE @tid3 INT;
DECLARE @tid4 INT;
DECLARE @tid5 INT;
set @tid1=0;
set @tid2=0;
set @tid3=0;
set @tid4=0;
set @tid5=0;
DECLARE User_Cursor CURSOR FOR
SELECT u.uid,ifnull(b.endtime,0)
FROM sys_user AS u LEFT JOIN mem_user_buffer AS b ON u.uid=b.uid AND u.uid>1000 AND buftype=1100000 AND endtime>=unix_timestamp();
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN User_Cursor;
REPEAT
FETCH NEXT FROM User_Cursor INTO @uid, @weimingzhao;
BEGIN
set @tid1 = SELECT floor(rand()*62);
set @tid2 = SELECT floor(rand()*62);
set @tid3 = SELECT floor(rand()*62);
IF (@tid1==@tid2 OR @tid1==@tid3 OR @tid2==@tid3) THEN
set @tid1 = 1101001;
set @tid2 = 1101010;
set @tid3 = 1101019;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid1),(@uid,@tid2),(@uid,@tid3);
IF (@weimingzhao>0) THEN
set @tid4 = SELECT floor(rand()*62);
set @tid5 = SELECT floor(rand()*62);
IF (@tid4==@tid1 OR @tid4==@tid2 OR @tid4==@tid3 OR @tid4==@tid5 OR @tid5==@tid1 OR @tid5==@tid2 OR @tid5==@tid3) THEN
set @tid4 = 1101033;
set @tid5 = 1101052;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid4),(@uid,@tid5);
END IF;
END;
UNTIL stopFlag = 1
END REPEAT;
CLOSE User_Cursor;
END ;
CREATE Procedure Procedure_User_Renown_Task()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE uid INT;
DECLARE weimingzhao INT;
DECLARE tid1 INT;
DECLARE tid2 INT;
DECLARE tid3 INT;
DECLARE tid4 INT;
DECLARE tid5 INT;
set @tid1=0;
set @tid2=0;
set @tid3=0;
set @tid4=0;
set @tid5=0;
DECLARE User_Cursor CURSOR FOR
SELECT u.uid,ifnull(b.endtime,0)
FROM sys_user AS u LEFT JOIN mem_user_buffer AS b ON u.uid=b.uid AND u.uid>1000 AND buftype=1100000 AND endtime>=unix_timestamp();
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN User_Cursor;
REPEAT
FETCH NEXT FROM User_Cursor INTO @uid, @weimingzhao;
BEGIN
set @tid1 = SELECT floor(rand()*62);
set @tid2 = SELECT floor(rand()*62);
set @tid3 = SELECT floor(rand()*62);
IF (@tid1==@tid2 OR @tid1==@tid3 OR @tid2==@tid3) THEN
set @tid1 = 1101001;
set @tid2 = 1101010;
set @tid3 = 1101019;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid1),(@uid,@tid2),(@uid,@tid3);
IF (@weimingzhao>0) THEN
set @tid4 = SELECT floor(rand()*62);
set @tid5 = SELECT floor(rand()*62);
IF (@tid4==@tid1 OR @tid4==@tid2 OR @tid4==@tid3 OR @tid4==@tid5 OR @tid5==@tid1 OR @tid5==@tid2 OR @tid5==@tid3) THEN
set @tid4 = 1101033;
set @tid5 = 1101052;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (@uid,@tid4),(@uid,@tid5);
END IF;
END;
UNTIL stopFlag = 1
END REPEAT;
CLOSE User_Cursor;
END ;以后错误
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 '' at line 6
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 'DECLARE weimingzhao INT' at line 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 'DECLARE tid1 INT' at line 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 'DECLARE tid2 INT' at line 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 'DECLARE tid3 INT' at line 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 'DECLARE tid4 INT' at line 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 'DECLARE tid5 INT' at line 1
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)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 'DECLARE User_Cursor CURSOR FOR
SELECT u.uid,ifnull(b.endtime,0)
FROM sys_user' at line 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 'DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1' at line 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 'OPEN User_Cursor' at line 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 'REPEAT
FETCH NEXT FROM User_Cursor INTO @uid, @weimingzhao' at line 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 'set @tid1 = SELECT floor(rand()*62)' at line 2
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 'SELECT floor(rand()*62)' at line 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 'SELECT floor(rand()*62)' at line 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 (@tid1==@tid2 OR @tid1==@tid3 OR @tid2==@tid3) THEN
set @tid1 = 1101001' at line 1
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)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 'END IF' at line 1
ERROR 1146 (42S02): Table 'bloodwar.user_renown_task' doesn't exist
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 (@weimingzhao>0) THEN
set @tid4 = SELECT floor(rand()*62)' at line 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 'SELECT floor(rand()*62)' at line 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 (@tid4==@tid1 OR @tid4==@tid2 OR @tid4==@tid3 OR @tid4==@tid5 OR @tid5==@tid1' at line 1
Query OK, 0 rows affected (0.00 sec)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 'END IF' at line 1
ERROR 1146 (42S02): Table 'bloodwar.user_renown_task' doesn't exist
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 'END IF' at line 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 'END' at line 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 'UNTIL stopFlag = 1
END REPEAT' at line 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 'CLOSE User_Cursor' at line 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 'END' at line 1
set @tid2=0;
set @tid3=0;全部改掉。
Your MySQL connection id is 2
Server version: 5.0.46-enterprise-gpl-log SourceType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> source /root/procedure.sql
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 '' at line 6
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 'DECLARE weimingzhao INT' at line 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 'DECLARE tid1 INT' at line 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 'DECLARE tid2 INT' at line 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 'DECLARE tid3 INT' at line 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 'DECLARE tid4 INT' at line 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 'DECLARE tid5 INT' at line 1
ERROR 1193 (HY000): Unknown system variable 'tid1'
ERROR 1193 (HY000): Unknown system variable 'tid2'
ERROR 1193 (HY000): Unknown system variable 'tid3'
ERROR 1193 (HY000): Unknown system variable 'tid4'
ERROR 1193 (HY000): Unknown system variable 'tid5'
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 'DECLARE User_Cursor CURSOR FOR
SELECT u.uid,ifnull(b.endtime,0)
FROM sys_user' at line 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 'DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1' at line 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 'OPEN User_Cursor' at line 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 'REPEAT
FETCH NEXT FROM User_Cursor INTO uid, weimingzhao' at line 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 'set tid1 = SELECT floor(rand()*62)' at line 2
ERROR 1193 (HY000): Unknown system variable 'tid2'
ERROR 1193 (HY000): Unknown system variable 'tid3'
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 (tid1==tid2 OR tid1==tid3 OR tid2==tid3) THEN
set tid1 = 1101001' at line 1
ERROR 1193 (HY000): Unknown system variable 'tid2'
ERROR 1193 (HY000): Unknown system variable 'tid3'
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 'END IF' at line 1
ERROR 1146 (42S02): Table 'bloodwar.user_renown_task' doesn't exist
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 (weimingzhao>0) THEN
set tid4 = SELECT floor(rand()*62)' at line 1
ERROR 1193 (HY000): Unknown system variable 'tid5'
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 (tid4==tid1 OR tid4==tid2 OR tid4==tid3 OR tid4==tid5 OR tid5==tid1 OR tid5==' at line 1
ERROR 1193 (HY000): Unknown system variable 'tid5'
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 'END IF' at line 1
ERROR 1146 (42S02): Table 'bloodwar.user_renown_task' doesn't exist
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 'END IF' at line 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 'END' at line 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 'UNTIL stopFlag = 1
END REPEAT' at line 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 'CLOSE User_Cursor' at line 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 'END' at line 1
mysql>
其中/root/procedure.sql
CREATE Procedure Procedure_User_Renown_Task()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE uid INT;
DECLARE weimingzhao INT;
DECLARE tid1 INT;
DECLARE tid2 INT;
DECLARE tid3 INT;
DECLARE tid4 INT;
DECLARE tid5 INT;
set tid1=0;
set tid2=0;
set tid3=0;
set tid4=0;
set tid5=0;
DECLARE User_Cursor CURSOR FOR
SELECT u.uid,ifnull(b.endtime,0)
FROM sys_user AS u LEFT JOIN mem_user_buffer AS b ON u.uid=b.uid AND u.uid>1000 AND buftype=1100000 AND endtime>=unix_timestamp();
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN User_Cursor;
REPEAT
FETCH NEXT FROM User_Cursor INTO uid, weimingzhao;
BEGIN
set tid1 = SELECT floor(rand()*62);
set tid2 = SELECT floor(rand()*62);
set tid3 = SELECT floor(rand()*62);
IF (tid1==tid2 OR tid1==tid3 OR tid2==tid3) THEN
set tid1 = 1101001;
set tid2 = 1101010;
set tid3 = 1101019;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (uid,tid1),(uid,tid2),(uid,tid3);
IF (weimingzhao>0) THEN
set tid4 = SELECT floor(rand()*62);
set tid5 = SELECT floor(rand()*62);
IF (tid4==tid1 OR tid4==tid2 OR tid4==tid3 OR tid4==tid5 OR tid5==tid1 OR tid5==tid2 OR tid5==tid3) THEN
set tid4 = 1101033;
set tid5 = 1101052;
END IF;
REPLACE INTO `user_renown_task` (uid,tid) VALUES (uid,tid4),(uid,tid5);
END IF;
END;
UNTIL stopFlag = 1
END REPEAT;
CLOSE User_Cursor;
END ;
mysql 5.0.46问题把所有的@都去了,还是差不多的错啊
-> NOT DETERMINISTIC
-> SQL SECURITY DEFINER
-> COMMENT ''
-> BEGIN
-> DECLARE uid INT;
-> DECLARE weimingzhao INT;
-> DECLARE tid1 INT;
-> DECLARE tid2 INT;
-> DECLARE tid3 INT;
-> DECLARE tid4 INT;
-> DECLARE tid5 INT;
-> declare stopFlag int;
->
-> DECLARE User_Cursor CURSOR FOR SELECT u.uid,ifnull(b.endtime,0) FROM sys_user u LEFT JOIN mem_user_buffer b ON u.uid=b.uid AND u.uid>1000 AND buftype=1100000 AND endtime>=unix_timestamp();
-> DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
->
->
-> set tid1=0;
-> set tid2=0;
-> set tid3=0;
-> set tid4=0;
-> set tid5=0;
->
-> OPEN User_Cursor;
-> REPEAT
-> FETCH next from User_Cursor INTO uid, weimingzhao;
-> BEGIN
-> set tid1 = floor(rand()*62);
-> set tid2 = floor(rand()*62);
-> set tid3 = floor(rand()*62);
-> IF (tid1=tid2 OR tid1=tid3 OR tid2=tid3) THEN
-> set tid1 = 1101001;
-> set tid2 = 1101010;
-> set tid3 = 1101019;
-> END IF;
-> REPLACE INTO `user_renown_task` (uid,tid) VALUES (uid,tid1),(uid,tid2),(uid,tid3);
-> IF (weimingzhao>0) THEN
-> set tid4 =floor(rand()*62);
-> set tid5 =floor(rand()*62);
-> IF (tid4=tid1 OR tid4=tid2 OR tid4=tid3 OR tid4=tid5 OR tid5=tid1 OR tid5=tid2 OR tid5=tid3) THEN
-> set tid4 = 1101033;
-> set tid5 = 1101052;
-> END IF;
-> REPLACE INTO `user_renown_task` (uid,tid) VALUES (uid,tid4),(uid,tid5);
-> END IF;
-> END;
-> UNTIL stopFlag = 1
-> END REPEAT;
-> CLOSE User_Cursor;
-> END ;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> call Procedure_User_Renown_Task//
ERROR 1146 (42S02): Table 'gafisadm.sys_user' doesn't exist
mysql> 我正好也在用这里的知识,目前就是改成这样。你在测试下,看有没有别的问题吧。改动总结:
1.declare位于所有语句之前,所以把你光标的declare语句全部提前了
2. 判断相等的问题,不用“==”,用“=”
3. set tid1 = SELECT floor(rand()*62);去掉select,
基本方法是按照提示的错误,一步步改就好了