存储过程的目的是多次查询一个表,以验证查询的性能。
存储过程如下,但执行时没有反应。drop PROCEDURE if exists SELECT_SUBSCRIBEINFO;CREATE PROCEDURE SELECT_SUBSCRIBEINFO()
BEGIN
DECLARE lsSQL VARCHAR(1024);
DECLARE n int default 1;
DECLARE m varchar(20);
WHILE n < 100 DO
set @lsSQL = concat( "SELECT SUBSCRIBEID INTO ", '@m', " FROM SUBSCRIBEINFO WHERE MBID=", n, " AND NOTICETYPE=", 1, " AND STATUS=" , 1 );
prepare stmt from @lsSQL;
execute stmt;
SET n = n + 1;
END WHILE;
END;call SELECT_SUBSCRIBEINFO;
存储过程如下,但执行时没有反应。drop PROCEDURE if exists SELECT_SUBSCRIBEINFO;CREATE PROCEDURE SELECT_SUBSCRIBEINFO()
BEGIN
DECLARE lsSQL VARCHAR(1024);
DECLARE n int default 1;
DECLARE m varchar(20);
WHILE n < 100 DO
set @lsSQL = concat( "SELECT SUBSCRIBEID INTO ", '@m', " FROM SUBSCRIBEINFO WHERE MBID=", n, " AND NOTICETYPE=", 1, " AND STATUS=" , 1 );
prepare stmt from @lsSQL;
execute stmt;
SET n = n + 1;
END WHILE;
END;call SELECT_SUBSCRIBEINFO;
BEGIN
DECLARE n int default 1;
WHILE n < 100 DO
set @lsSQL = concat( 'SELECT SUBSCRIBEID INTO @m FROM SUBSCRIBEINFO WHERE MBID=', n, ' AND NOTICETYPE=1 AND STATUS=1');
prepare stmt from @lsSQL;
execute stmt;
DEALLOCATE PREPARE stmt;
SET n = n + 1;
END WHILE;
END;
mysql>
mysql> CREATE PROCEDURE SELECT_SUBSCRIBEINFO()
-> BEGIN
-> DECLARE n int default 1;
-> WHILE n < 100 DO
-> set @lsSQL = concat( 'SELECT SUBSCRIBEID INTO @m FROM SUBSCRIBEINFO WHERE MBID=', n, ' AND NOTICETYPE=1 AND STATUS=1');
-> prepare stmt from @lsSQL;
-> execute stmt;
-> DEALLOCATE PREPARE stmt;
-> SET n = n + 1;
-> END WHILE;
-> select "test OK",n;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> call SELECT_SUBSCRIBEINFO();
+---------+------+
| test OK | n |
+---------+------+
| test OK | 100 |
+---------+------+
1 row in set (0.01 sec)Query OK, 0 rows affected, 99 warnings (0.01 sec)mysql>