데이터베이스 질문과 답변
by 쿼리어려워 [PL/SQL] EXECUTE IMMEDIATE EXCEPTION [2022.10.28 10:18:11]
안녕하세요
오라클 프로시저에서 LOOP문을 사용해서 작업하던중 궁금중이 생겨서 질문합니다.
예를 들어 반복문 내부에 동적쿼리를 사용하여
EXECUTE IMMEDIATE V_SQL; 했을 시 (동적쿼리는 DELETE & INSERT 구문입니다.)
성공하거나 오류가 나거나 두가지 결과가 나오게 되는데
성공하거나 오류가 나는 결과값을 가지고 IF문을 태울수 있나요?
만약 그게 안된다면 오류가 났을 때 EXCEPTION으로 떨어질텐데
한번 EXCEPTION으로 떨어졌다가 다시 LOOP문으로 돌아가게 할 수 있는 방법이 있을까요?(EXCEPTION에 떨어진 해당 DELETE& INSERT 쿼리는 COMMIT이 안된 상태로)
(예를 들면 1...10 일 때 5번에서 오류가 났으면 EXCEPTION 떨어졌다가 GO TO 문을 통해 6번부터 다시 LOOP문이 시작된다거나..)
DECLARE
name VARCHAR2(20);
ans1 VARCHAR2(3);
ans2 VARCHAR2(3);
ans3 VARCHAR2(3);
suffix NUMBER := 1;
BEGIN
...
LOOP -- could be FOR i IN 1..10 LOOP to allow ten tries
BEGIN -- sub-block begins
SAVEPOINT start_transaction; -- mark a savepoint
/* Remove rows from a table of survey results. */
DELETE FROM results WHERE answer1 = 'NO';
/* Add a survey respondent's name and answers. */
INSERT INTO results VALUES (name, ans1, ans2, ans3);
-- raises DUP_VAL_ON_INDEX if two respondents have the same name
COMMIT;
EXIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO start_transaction; -- undo changes
suffix := suffix + 1; -- try to fix problem
name := name || TO_CHAR(suffix);
END; -- sub-block ends
END LOOP;
END;
위 예제처럼 exception을 중첩해서 사용하시면 될 것 같습니다.
참조 : https://docs.oracle.com/cd/B10500_01/appdev.920/a96624/07_errs.htm
답변감사합니다 신이만든지기님!!
LOOP문 내부에 BEGIN END 블록하나를 더 만들어서 반복문을 돌다가 오류가 나면 EXCEPTION을 통해 해당건은 ROLLBACK이 되고 다시 큰 BEGIN에 있는 루프를 도는거군요...
궁금한점이 드는게 있는데
1. ROLLBACK을 할때 세이브포인트를 사용안하면 그전에 루프돌기전 맨처음으로 아예 ROLLBACK되는건가요?
2. 내부 BEGIN에서 EXIT;를 쓰는건 DELETE&INSERT가 정상적으로 실행되었으면 큰BEGIN에 있는 LOOP로 나가게 되는것같은데 만약 EXIT를 안쓴다면 내부 BEGIN에서 머물러있게 되는건가요?
1. 세이브 포인트가 없으면 commit 하지 않은 모든 데이터는 롤백됩니다. loop 밖의 데이터도 commit 구문이 없다면 롤백됩니다.
2. 말씀하신 대로 exit가 없으면 반복문을 진행합니다.
감사합니다!! 신이만든지기님 덕분에 견문이 넓어졌습니다!
) 버튼을 클릭하여 작성 하시면 됩니다.