mysqlストレージ・プロシージャ、カーソルの操作


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