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)
-> 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)
解决方案 »
- mysql自带数据库mysql中表的作用
- mysql5.5版本如何记录update类型的语句日志
- 奇怪的mysql字符相同的问题...
- 初学sql,在mysql里写sql发现没有代码对齐跟自动补全的功能
- 求助:netbeans连接mysql出错
- 求助Mssql语句转Mysql?
- 我想把linux9.0中的mysql 泄下来,但是老提示这样的错误,大侠门看看是什么原因,谢谢
- mysql_stmt_prepare(),SQL failed Got packets out of order
- mysql 删除重复语句出错,求指导
- MySQL日志有错误级别的概念吗?
- 关于游标循环内的嵌套循环
- sql 语句 非作业题,只是自己太菜了……
翻译成中文,游标已经打开。
-> OPEN CUR_PAYMENT ;
->
-> OPEN CUR_PAYMENT ;你不是打开了两次?
-> BEGIN
-> declare done default 1;
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 set done =0;
->
-> 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 done END REPEAT ;
-> CLOSE CUR_PAYMENT ;
->
-> END;
-> !!感觉这样比较好点,你以前的那个第一次循环后到until,直接给0不久跳出了