MySQL存储过程使用 REPEAT 来打开游标读取数据

2019-06-19 20:03栏目:技术创新

delimiter $$
CREATE PROCEDURE myProc (in_customer_id INT)
 BEGIN
      DECLARE l_first_name  VARCHAR(30);
      DECLARE l_id          INT;
      DECLARE l_city        VARCHAR(30);
      DECLARE l_department_count INT;
      DECLARE no_more_departments INT;

      DECLARE dept_csr CURSOR FOR
           SELECT id,first_name, city
             FROM employee;

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

      SET no_more_departments=0;
      OPEN dept_csr;
      dept_loop:REPEAT
           FETCH dept_csr INTO l_id,l_first_name,l_city;
               IF no_more_departments=0 THEN
                 SET l_department_count=l_department_count 1;
               END IF;
           select  l_id,l_first_name,l_city;
      UNTIL no_more_departments
      END REPEAT dept_loop;
      CLOSE dept_csr;
      SET no_more_departments=0;

 END$$

版权声明:本文由新浦京娱乐站_新浦京娱乐场官网_新浦京娱乐发布于技术创新,转载请注明出处:MySQL存储过程使用 REPEAT 来打开游标读取数据