mysql> select*from 入库单表;
+------+---------------------+--------------+----------------+------+------+----
--+------+------+------+
| 单号 | 入库日期 | 品名 | 供货商编号 | 颜色 | 规格 | 数
量 | 单位 | 单价 | 金额 |
+------+---------------------+--------------+----------------+------+------+----
--+------+------+------+
| 001 | 2011-05-16 16:03:02 | CP | | | | 1
2 | | 5 | 60 |
| | 2011-05-23 15:21:29 | 水杯 | | 白 | |
0 | | 0 | 0 |
| | 2011-05-23 15:21:29 | 水杯 | | 白 | |
0 | | 0 | 0 |
| | 2011-05-23 15:21:29 | SQL 入门经典 | 人民邮电出版社 | | |
0 | | 0 | 0 |
+------+---------------------+--------------+----------------+------+------+----
--+------+------+------+
4 rows in set (0.03 sec)mysql> DELIMITER //mysql> drop procedure p//
Query OK, 0 rows affected (0.07 sec)mysql> create procedure p()
-> begin
-> declare t text;
-> DECLARE o CURSOR FOR SELECT 品名 from 入库单表;
-> open o;
-> repeat
-> fetch o into t;
-> select t;
-> end repeat;
-> close o;
-> 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 'end r
epeat;
close o;
end' at line 9一个游标只能指向一个字段吗?书上的例子,在 FOR 后面的 SELECT 中都是一个字段。
+------+---------------------+--------------+----------------+------+------+----
--+------+------+------+
| 单号 | 入库日期 | 品名 | 供货商编号 | 颜色 | 规格 | 数
量 | 单位 | 单价 | 金额 |
+------+---------------------+--------------+----------------+------+------+----
--+------+------+------+
| 001 | 2011-05-16 16:03:02 | CP | | | | 1
2 | | 5 | 60 |
| | 2011-05-23 15:21:29 | 水杯 | | 白 | |
0 | | 0 | 0 |
| | 2011-05-23 15:21:29 | 水杯 | | 白 | |
0 | | 0 | 0 |
| | 2011-05-23 15:21:29 | SQL 入门经典 | 人民邮电出版社 | | |
0 | | 0 | 0 |
+------+---------------------+--------------+----------------+------+------+----
--+------+------+------+
4 rows in set (0.03 sec)mysql> DELIMITER //mysql> drop procedure p//
Query OK, 0 rows affected (0.07 sec)mysql> create procedure p()
-> begin
-> declare t text;
-> DECLARE o CURSOR FOR SELECT 品名 from 入库单表;
-> open o;
-> repeat
-> fetch o into t;
-> select t;
-> end repeat;
-> close o;
-> 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 'end r
epeat;
close o;
end' at line 9一个游标只能指向一个字段吗?书上的例子,在 FOR 后面的 SELECT 中都是一个字段。
-- 声明游标的异常处理,设置一个终止标记
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
-- 打开游标
open cur;
-- 读取一行数据到变量
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;