CREATE PROCEDURE `p_delete_test_teacher`(IN t_id BIGINT,OUT res_code INT)
BEGIN
/*定义游标结束标记*/
DECLARE done INT DEFAULT 0;
/*定义临时存储变量存储遍历右边的id*/
DECLARE tmp_id BIGINT DEFAULT -1;
/*声明游标*/
DECLARE rs CURSOR FOR SELECT tr.ID FROM t_test_result tr WHERE TESTING_ID = t_id;
/*游标遍历结束时给游标结束标记赋值为1*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET res_code = 0;
IF t_id <> -1 THEN
DELETE FROM t_rel_user_testing WHERE TESTING_ID = t_id;
DELETE FROM t_test_item WHERE TESTING_ID = t_id;
/*删除t_test_result_item关联数据*/
/*打开游标*/
OPEN rs;
/* 遍历游标 */
FETCH NEXT FROM rs INTO tmp_id;
REPEAT
IF NOT done THEN
DELETE FROM t_test_result_item WHERE TEST_RESULT_ID = tmp_id;
END IF;
FETCH NEXT FROM rs INTO tmp_id;
UNTIL done
END REPEAT;
/* 关闭游标 */
CLOSE rs;
/*删除t_test_result关联数据*/
DELETE FROM t_test_result WHERE TESTING_ID = t_id;
DELETE FROM t_testing WHERE ID = t_id;
END IF;
SET res_code = 1;
END
mysql存储过程,对游标的操作
最新推荐文章于 2021-11-07 23:22:57 发布