2019独角兽企业重金招聘Python工程师标准>>>
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()
备忘