mysql数据库游标的使用

 

Mysql在5.0版本支持在存储过程中使用游标。

游标声明必须出现在处理程序声明变量和条件的声明后。

游标的使用如下：

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

 上面的例子是从官方提供的文档上找到的.

CREATE TABLE test(
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
product_type INT
);

INSERT INTO test(product_name,product_type)VALUES('mobile',1);
INSERT INTO test(product_name,product_type)VALUES('computer',1);
INSERT INTO test(product_name,product_type)VALUES('apple',2);
INSERT INTO test(product_name,product_type)VALUES('Orange',2);

CREATE TABLE test_temp(
id INT,
product_name VARCHAR(50)
);

  

下面的是有游标的存储过程

DELIMITER $$
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _id INT;
  DECLARE _product_name VARCHAR(50);
  DECLARE cur CURSOR FOR SELECT id,product_name FROM test WHERE product_type=1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO _id,_product_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO test_temp (id,product_name)VALUES(_id,_product_name);
  END LOOP;
  CLOSE cur;
END$$
DELIMITER;

 调用存储过程

CALL curdemo;

 查看运行结果

SELECT * FROM test_temp;

 运行结果为:

id	product_name
1	mobile
2	computer

 此存储过程是可以运行的,结果也是正确的,下面稍微修改一下存储过程.

DELIMITER $$
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _id INT;
  DECLARE _product_name VARCHAR(50);
  DECLARE cur CURSOR FOR SELECT id,product_name FROM test WHERE product_type=1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO _id,_product_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT product_type INTO @product_type FROM test WHERE product_type = 3;
    INSERT INTO test_temp (id,product_name)VALUES(_id,_product_name);
  END LOOP;
  CLOSE cur;
END$$
DELIMITER;

 清空test_temp表数据

TRUNCATE TABLE test_temp;

 再次调用存储过程后,

CALL curdemo;

SELECT * FROM test_temp;

 运行结果为:

id	product_name
1	mobile

 为什么少了一行数据,不就是多加了一条select 语句嘛

原因很简单,就是SELECT product_type INTO @product_type FROM test WHERE product_type = 3;

抛出了一个not found的异常,从而使得游标的循环终止了.

很多时候在游标循环的过程体中,调用了其他存储过程,但是我们不知道其他存储过程会不会抛出not found,我们的目的是循环完游标,怎么解决?

一种解决方法如下:

DELIMITER $$
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _id INT;
  DECLARE _product_name VARCHAR(50);
  DECLARE cur CURSOR FOR SELECT id,product_name FROM test WHERE product_type=1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    SET done = FALSE; 
    FETCH cur INTO _id,_product_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT product_type INTO @product_type FROM test WHERE product_type = 3;
    INSERT INTO test_temp (id,product_name)VALUES(_id,_product_name);
  END LOOP;
  CLOSE cur;
END$$
DELIMITER;

 运行结果正常.