mysql循环select_mysql select 结果集循环

drop procedure if exists add_test;

CREATE PROCEDURE add_test()

BEGIN

DECLARE _Done INT DEFAULT 0;

DECLARE _companyId bigint(20);

DECLARE _currentName VARCHAR(50);

/* 声明游标 */

DECLARE rs CURSOR FOR select id AS companyId ,`name` FROM  h_company  WHERE id>65 GROUP BY `name`;

/* 异常处理  异常退出*/

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _Done = 1;

/* 打开游标 */

OPEN rs;

/* 逐个取出当前记录_companyId _currentName字段的值 */

FETCH NEXT FROM rs INTO _companyId, _currentName;

/* 遍历数据表 */

REPEAT

IF NOT _Done THEN

/*查询企业数据插入员工表 */

INSERT INTO h_company_employe(companyId,`name`,phone,updateTime,state)

select id companyId, managerName `name`, managerPhone phone,NOW(),1 from h_company where `name` =_currentName;

END IF;

FETCH NEXT FROM rs INTO _companyId, _currentName;

UNTIL _Done END REPEAT;

/* 关闭游标 */

CLOSE rs;

END

#调用存储函数add_test

CALL add_test()

备忘