有关光标的有关问题 ！

有关光标的问题 ！求助！！！
mysql> create table payment(staff_id int not null ,
  -> amount int not null); //建表
Query OK, 0 rows affected (1.68 sec)
mysql> INSERT INTO PAYMENT VALUES(1,213); //插值
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO PAYMENT VALUES(2,213.2312);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO PAYMENT VALUES(3,12);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO PAYMENT VALUES(4,11);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO PAYMENT VALUES(5,21);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO PAYMENT VALUES(6,31);
Query OK, 1 row affected (0.03 sec)

mysql> DELIMITER !! //建立过程PAYMENT_STAT
mysql> 
mysql> //使不同的staff_id值的amount累加mysql> CREATE PROCEDURE PAYMENT_STAT()
  -> BEGIN
  -> DECLARE I_STAFF_ID INT;
  -> DECLARE D_AMOUNT DECIMAL(5,2);
  -> DECLARE CUR_PAYMENT CURSOR FOR SELECT STAFF_ID,AMOUNT FROM PAYMEN
  -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE CUR_PAYMENT;
  ->
  -> SET @X1=0;
  -> SET @X2=0;
  ->
  -> OPEN CUR_PAYMENT ;
  ->
  -> OPEN CUR_PAYMENT ;
  ->
  -> REPEAT
  -> FETCH CUR_PAYMENT INTO I_STAFF_ID ,D_AMOUNT ;
  -> IF I_STAFF_ID =2 THEN
  -> SET @X1=@X1+D_AMOUNT ;
  -> ELSE
  -> SET @X2=@X2+D_AMOUNT ;
  -> END IF;
  -> UNTIL 0 END REPEAT ;
  -> CLOSE CUR_PAYMENT ;
  ->
  -> END;
  -> !!
Query OK, 0 rows affected (0.42 sec)

mysql> DELIMITER ;
mysql> CALL PAYMENT_STAT();
ERROR 1325 (24000): Cursor is already open 、//为什么会这样呢

mysql> SELECT @X1,@X2; 也没有结果输出+——+——+
| @X1 | @X2 |
+——+——+
| 0 | 0 |
+——+——+
1 row in set (0.08 sec)

——解决方案——————–
RROR 1325 (24000): Cursor is already open 、//为什么会这样呢
翻译成中文，游标已经打开。

-> OPEN CUR_PAYMENT ;
->
-> OPEN CUR_PAYMENT ;

你不是打开了两次？
——解决方案——————–
是的，你打开了两次游标。

喜欢 (0)赏

[搞代码]

分享 (0)