特定代码段的 MySql 处理程序

Question

我正在开发一个存储过程来将数据从表传输到另一个表。规范化现有数据库后。我正在将工作分配中的所有数据读入游标，并在用列关系 id 替换几列后插入到表事件中。此操作的代码如下。

    DROP procedure IF EXISTS `transfer_work_assignment`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer_work_assignment`()
BEGIN

DECLARE val_emp_id,val_emp_name,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_sub_project_name VARCHAR(100);
DECLARE val_insert_date DATETIME;
DECLARE val_remarks TEXT;
DECLARE val_user_id,val_sub_task_id,val_sub_project_id,b,cnt INTEGER;
DECLARE curs1 CURSOR FOR SELECT emp_id,task_name,sub_task_name,efforts,deliverable,insert_date,sub_project_name,remarks FROM work_assignment limit 0,100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b=1;

OPEN curs1;
SET b=0;
SET cnt=0;

WHILE b=0 DO 

    FETCH curs1 INTO val_emp_id,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_insert_date,val_sub_project_name,val_remarks;
    BEGIN
        SELECT a.sub_task_id,a.sub_task_name,val_sub_task_name,b.task_name from pmtool_db.tbl_sub_task a,pmtool_db.tbl_task_details b where a.sub_task_name=val_sub_task_name and b.task_name=val_task_name;
    END;
    SET cnt=cnt+1;

END WHILE;


CLOSE curs1;

    SELECT cnt;

END$$
DELIMITER ;

问题是我想在游标中的所有值完成获取后退出程序。但我想继续执行 while 循环内的 select 部分，即使它在某些选择中没有值。

Answer 1

我认为您的存储过程需要使用继续处理程序或 session 变量。这可以帮助你——通过使用 CONTINUE HANDLER

DROP procedure IF EXISTS `transfer_work_assignment`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer_work_assignment`()
BEGIN

DECLARE nomore BOOLEAN DEFAULT FALSE;
DECLARE val_emp_id,val_emp_name,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_sub_project_name VARCHAR(100);
DECLARE val_insert_date DATETIME;
DECLARE val_remarks TEXT;
DECLARE val_user_id,val_sub_task_id,val_sub_project_id,b,cnt INTEGER;
DECLARE curs1 CURSOR FOR SELECT emp_id,task_name,sub_task_name,efforts,deliverable,insert_date,sub_project_name,remarks FROM work_assignment limit 0,100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET nomore = TRUE;

OPEN curs1;
SET b=0;
SET cnt=0;

myloop: LOOP 

    FETCH curs1 INTO val_emp_id,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_insert_date,val_sub_project_name,val_remarks;
    IF NOT nomore THEN   
        SELECT a.sub_task_id,a.sub_task_name,val_sub_task_name,b.task_name from pmtool_db.tbl_sub_task a,pmtool_db.tbl_task_details b where a.sub_task_name=val_sub_task_name and b.task_name=val_task_name;
        SET nomore = FALSE; 
    END IF;  
    SET cnt=cnt+1;
    IF nomore THEN
            LEAVE myloop;
    END IF;
END LOOP myloop;


CLOSE curs1;

SELECT cnt;

END$$
DELIMITER ;

这是我的例子-

drop procedure if exists cur;
delimiter $$
create procedure cur()
BEGIN 
     DECLARE rowcount INT default 0;
     DECLARE code_var VARCHAR(320); 
     DECLARE name_var VARCHAR(320);
     DECLARE myvar VARCHAR(320); 
     DECLARE nomore BOOLEAN DEFAULT FALSE;
     DECLARE c cursor FOR
       SELECT code,name FROM Country WHERE continent='Africa';
     open c;
     begin

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET nomore = TRUE;
        myloop: loop
              fetch c into code_var,name_var; 
                IF NOT nomore THEN  
                   select name into myvar from City where countrycode='ggg' limit 1;    
                   select myvar; 
                   SET nomore = FALSE;      
                END IF;    
              select code_var, name_var;
          set rowcount=rowcount+1;
         IF nomore THEN
            LEAVE myloop;
         END IF;
         end loop myloop;
     end;  
     close c;
     select rowcount as 'no of country rows fetched';
     select rowcount;
END $$
delimiter ;
call cur();

通过使用 session 变量

DROP procedure IF EXISTS `transfer_work_assignment`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer_work_assignment`()
BEGIN

DECLARE val_emp_id,val_emp_name,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_sub_project_name VARCHAR(100);
DECLARE val_insert_date DATETIME;
DECLARE val_remarks TEXT;
DECLARE val_user_id,val_sub_task_id,val_sub_project_id,b,cnt INTEGER;
DECLARE curs1 CURSOR FOR SELECT emp_id,task_name,sub_task_name,efforts,deliverable,insert_date,sub_project_name,remarks FROM work_assignment limit 0,100;

OPEN curs1;
SET cnt=0;

BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
    LOOP
          FETCH curs1 INTO val_emp_id,val_task_name,val_sub_task_name,val_efforts,val_deliverables,val_insert_date,val_sub_project_name,val_remarks;
          SELECT @var1 := a.sub_task_id, @var2 := a.sub_task_name, @var3 := val_sub_task_name, @var4 := b.task_name from pmtool_db.tbl_sub_task a,pmtool_db.tbl_task_details b where a.sub_task_name=val_sub_task_name and b.task_name=val_task_name;
          SET cnt=cnt+1;
    END LOOP;
END;


CLOSE curs1;

SELECT cnt;

END$$
DELIMITER ;

我没有你的 table 。但下面是我的代码，运行良好。它将运行，直到未获取光标的所有记录，即使 select with in 循环没有选择记录，它也不会停止。首先使用 in 循环进行选择不会选择任何记录，并将 @myvar 设置为 null。它不会终止循环。第二个 select 和 in 循环总是选择记录，如果选择失败将终止循环。所以问题是使用 @variable

drop procedure if exists cur;
delimiter $$
create procedure cur()
BEGIN 
     DECLARE rowcount INT default 0;
     DECLARE code_var VARCHAR(320); 
     DECLARE name_var VARCHAR(320);
     DECLARE myvar VARCHAR(320); 
     DECLARE c cursor FOR
       SELECT code,name FROM Country WHERE continent='Africa';
     open c;
     begin
        declare EXIT HANDLER for SQLSTATE '02000' begin end;    
         loop
              fetch c into code_var,name_var;
              select @myvar := name from City where countrycode='ggg';
              select name into myvar from City where countrycode=code_var limit 1;         
              select code_var, name_var;
              select myvar, @myvar;
          set rowcount=rowcount+1;
        end loop ;
     end;  
     close c;
     select rowcount as 'no of country rows fetched';
     select rowcount;
END $$
delimiter ;
call cur();