|
CREATE PROCEDURE iterator()
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE v_dept CHAR(3);
DECLARE v_deptname VARCHAR(29);
DECLARE v_admdept CHAR(3);
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR
SELECT deptno, deptname, admrdept
FROM department
ORDER BY deptno;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
OPEN c1;
ins_loop:
LOOP
FETCH c1 INTO v_dept, v_deptname, v_admdept;
IF at_end = 1 THEN
LEAVE ins_loop;
ELSEIF v_dept = 'D11' THEN
ITERATE ins_loop;
END IF;
INSERT INTO department (deptno, deptname, admrdept)
VALUES ('NEW', v_deptname, v_admdept);
END LOOP;
CLOSE c1;
END @
not_found 관련 흐름에 대해 설명부탁드립니다.
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
ins_loop:
LOOP
FETCH c1 INTO v_dept, v_deptname, v_admdept;
IF at_end = 1 THEN
LEAVE ins_loop;
ELSEIF v_dept = 'D11' THEN
ITERATE ins_loop;
END IF;
위의 코드에 대한 역할에 대해 구체적으로 알고 싶습니다.
|
