mysql - 需要帮助编写 case 语句 - MySQL 游标

Question

我在学生表中为我的数据库创建了两列。我现在需要为学生表创建一个游标。我需要利用起始学生 ID 和结束学生 ID 的输入参数来过滤游标查询中的结果。我还需要打开创建的光标并检查学生是否分配了电子邮件。如果未分配电子邮件，我需要更新电子邮件列以将电子邮件分配给学生。

最后我为什么需要帮助:我不知道如何为该游标编写 case 语句。我试图想出一个好的方法来做到这一点，但没有成功。请帮忙!!

DELIMITER $

DROP PROCEDURE IF EXISTS CURSOR_DEMO$

CREATE PROCEDURE CURSOR_DEMO(start_student_id   INT
                            ,end_student_id     INT
                            ) 
BEGIN

DECLARE l_table_name    VARCHAR(50);
DECLARE iam_done    INT DEFAULT 0;
DECLARE l_sql_stmt  VARCHAR(5000);


 SET @l_sql_stmt='ALTER TABLE STUDENT ADD EMAIL VARCHAR';
    SELECT @l_sql_stmt;
    prepare stmt from @l_sql_stmt;
    execute stmt;


 SET @l_sql_stmt='ALTER TABLE STUDENT ADD PHONE int(10)';
    SELECT @l_sql_stmt;
    prepare stmt from @l_sql_stmt;
    execute stmt;

 DECLARE TBL_CUR CURSOR FOR
    SELECT EMAIL FROM STUDENT.TABLES WHERE TABLE_SCHEMA='MYSQLDB';



BEGIN

DECLARE CONTINUE HANDLER FOR NOT FOUND SET IAM_DONE=1;



    OPEN TBL_CUR;



        tbl_loop:LOOP
            FETCH tbl_cur INTO l_table_name;        

                IF IAM_DONE = 1 THEN

                    LEAVE tbl_loop;

                END IF;


                CASE WHEN l_table_name = 'STUDENT' THEN



                ELSE BEGIN END;                     


                END CASE;                       



        END LOOP tbl_loop;

    CLOSE TBL_CUR;

END;

END$

DELIMITER ;

Answer 1

这是您需要的东西吗？

/*
drop table student;
delimiter $$
CREATE TABLE `student` (
  `id` int(11) NOT NULL ,
  `name` char(1) NOT NULL,
  `email` varchar(1),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8$$

drop table email;
delimiter $$

CREATE TABLE `email` (
  `id` int(11) NOT NULL ,
  `type` varchar(8) NOT NULL,
  `person_id` int,
  `email` varchar(1),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8$$
;
*/
DELIMITER $
DROP PROCEDURE IF EXISTS CURSOR_DEMO$
CREATE PROCEDURE CURSOR_DEMO(start_student_id   INT
                            ,end_student_id     INT)
BEGIN

declare email_person_id int;
declare email_address   varchar(50);
DECLARE done    INT DEFAULT 0;
DECLARE CUR CURSOR FOR
    SELECT person_id,email FROM sandbox.email where type = 'student';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    OPEN CUR;
        cur_loop:LOOP
                IF done = 1 THEN
                    LEAVE cur_loop;
                END IF;
                FETCH CUR INTO email_person_id,email_address;   
                update  student
                set     email = email_address
                where   id = email_person_id
                    and email is null
                ;
        END LOOP cur_loop;
    CLOSE CUR;
    commit;
END$
DELIMITER ;
truncate table student;
insert into student
values
(1,'A','1'),
(2,'B','1'),
(3,'C',null),
(4,'D',null),
(5,'E',null),
(6,'G',null),
(7,'F',null)
;
truncate table email;
insert into email
values
(1,'student',1,'a'),
(2,'student',2,'b'),
(3,'faculty',7,'z'),
(4,'student',3,'c')
;

select * from student;

call cursor_demo(1,10);

select * from student;