Mysql存储过程中使用游标示例

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

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存储过程中使用游标示例