[Oracle] PL/SQL (실행부/ 예외처리부)

PL/SQL(실행부)

실행부에서는 메인 처리를 기술한다. 필수 요소이며 절차적 언어의 특징 및 SQL을 사용하여 비즈니스 로직을 구현한다.

프로그래밍 언어를 알고 있는 사람은 IF문이나 FOR문 등을 사용해 처리를 기술할 수 있을 것이다.

 

■ 처리

기본적인 처리의 기술 방법. 일반적인 프로그래밍 언어 기술 방법과 거의 동일하다.

기본 구성
IF	[IF-THEN] IF 조건 THEN       TRUE일 때의 처리; END IF; [IF-THEN-ELSE] IF 조건 THEN       TRUE일 때의 처리; ELSE       FALSE일 때의 처리; END IF; [IF-THEN-ELSIF-ELSE] IF 조건A THEN       조건A가 TRUE일 때의 처리; ELSIF 조건B THEN       조건B가 TRUE일 때의 처리; ELSE       그 이외일 때의 처리; END IF; ※ ELSEIF가 아니라 ELSIF인 것 주의하기..!
CASE 문	[CASE] CASE 조건       WHEN 값1 THEN          처리1;       WHEN 값2 THEN          처리2;       ELSE          처리3; END CASE;
FOR 문	[FOR] FOR 카운터명 IN [REVERSE] 하한치 .. 상한치 LOOP       처리; END LOOP; [커서 FOR 루프] FOR 카운터명 IN 커서명 LOOP       처리(FETCH한 값은 카운터명.컬러명으로 가져올 수 있다.) END LOOP; ※ 커서의 OPEN, CLOSE는 자동으로 해주기때문에 불필요.
WHILE 문	WHILE 조건 LOOP       처리; END LOOP;
LOOP 문	LOOP       처리; EXIT (WHEN 조건); END LOOP;
CURSOR	OPEN 커서명 LOOP FETCH 커서명 INTO <%ROWTYPE변수명>       EXIT WHEN 커서명%NOTFOUND;       DBMS_OUTPUT.PUT_LINE(변수명.컬럼명); END LOOP; CLOSE 커서명
벌크 바인드(BULK BIND)	FORALL 카운터변수 IN 하수..상수 DML문;

 

■ 커서 속성

커서란 복수행을 취득할 수 있는 SQL을 발행했을 때에 한 줄씩 취득 결과를 꺼내서 처리하고 싶을 때 사용한다.

 

커서를 사용하는 방법에는 2가지가 있는데

1. 명식적으로 커서를 OPEN하고 FETCH, CLOSE 하는 것과 같은 사용방법

2. 선언부에서 커서를 선언하고 커서 FOR루프로 돌리는 방법이 있다.

 

커서 상태를 가져오려면 1번 방법에서는 명시 커서 속성을,

그 이외의 SQL이나 DML 실행시에는 암시 커서 속성을 사용하면 상태를 얻을 수 있다.

명시 커서 속성
CURSOR%FOUND	1건 이상의 데이터를 찾은 경우 TRUE
CURSOR%NOTFOUND	1건도 데이터를 찾을 수 없으면 TRUE
CURSOR%ROWCOUNT	현재까지의 처리건수를 반환
CURSOR%ISOPEN	커서가 OPEN 상태면 TRUE

암시 커서 속성
SQL%FOUND	SQL, DML문의 처리대상이 존재하는 경우는 TRUE
SQL%NOTFOUND	SQL, DML문의 처리대상이 없는 경우는 TRUE
SQL%ROWCOUNT	처리 대상의 행수를 반환
SQL%ISOPEN	항상 FASLE

■ 연관 배열 속성

연관 배열이란 말 그대로 배열을 의미한다. ⇒ PL/SQL(선언부) 게시글의 컬렉션형 TABLE을 참조.

연관 배열은 고유키를 값과 연관시키는 콜렉션 유형이다. 즉, PL/SQL에서의 테이블 = 일차원 배열

배열이기 때문에 x번째 요소를 취득하고자 하는 경우가 있는데, 그런 경우에 연관 배열 속성을 사용한다.

연관 배열 속성
EXISTS(n)	n번째가 있으면 TRUE
COUNT	요소 개수
DELETE	전체 요소를 삭제
FIRST	첫번째 요소의 색인 번호(인덱스)
LAST	마지막 요소의 색인 번호(인덱스)
NEXT(n)	n의 다음 색인 번호(인덱스)
PRIOR(n)	N의 이전 색인 번호(인덱스)

 

■ 그 외

그 외
사용자 정의 예외 호출 1	RAISE 예외명; 선언부에서 정의한 EXCEPTION을 명시적으로 발생시킴. ⇒ PL/SQL(선언부) 게시글의 데이터형(예외형)을 참조
사용자 정의 예외 호출 2	RAISE_APPLICATION_ERROR(에러코드, '메세지'); 에러코드와 에러메시지를 정의한 에러를 명시적으로 발생시킨다. (※ 정의할 수 있는 오류 코드의 범위는 -20000~-20999까지)
PL/SQL 안의 정보를 콘솔에 출력	set serveroutput on을 실핸한 후, DMBS_OUTPUT.PUT_LINE('문자열');을 실행. ※ Oracle 유틸리티 패키지를 사용할 수 있는 환경에서만 가능

 

---

PL/SQL(예외처리부)

예외 처리부의 EXCEPTION 키워드 다음에 기술하는 구문. '이 에러가 발생하면 이 처리를 실행해라'라는 식으로 기술.

(프로그래밍 언어의 TRY-CATCH문의 CATCH구문과 동일함)

 

■ 예외 처리부 구문

EXCEPTION 구문
모든 오류에 대해 동일한 처리를 하는 경우	WHEN OTHERS TEHN      처리;
특정 오류에 대해서만 처리하는 경우	WHEN 예외명칭 [OR 예외명칭..] THEN      처리;
특정 오류에 대해서만 특별히 처리하는 경우	WHEN 예외명칭 THEN      처리; WHEN OTHERS THEN      처리;

 

■ 예외 명칭, 에러 코드, SQL 메세지코드

예외에는 에러 명칭과 에러 코드가 붙어 있고, 예외 명칭에 따라 EXCEPTION 구문에서는 처리를 배분한다.

예를 들면 데이터를 찾을 수 없다고 하는 예외를 캐치하고 싶은 경우는 「NO_DATA_FOUND」를 지정한다.

 

판정에서 에러코드나 SQL 메시지 코드를 사용할 때도 있다.

예외명칭	예외 메시지	ORACLE 에러	SQL 메시지
ACCESS_INTO_NULL	reference to uninitialized composite. 초기화되지 않은 조합을 참조합니다.	ORA-06530	-6530
COLLECTION_IS_NULL	reference to uninitialized collection. 초기화되지 않은 모음을 참조합니다.	ORA-06531	-6531
CURSOR_ALREADY_OPEN	PL/SQL:cursor already open. PL/SQL:커서가 이미 열려있습니다.	ORA-06511	-6511
DUP_VAL_ON_INDEX	unique constraint. 유일성(무결성) 제약 조건에 위배됩니다.	ORA-00001	-1
INVALID_CURSOR	invalid cursor. 커서가 부적합합니다.	ORA-01001	-1001
INBALID_NUMBER	invalid number. 수치가 잘못되었습니다.	ORA-01722	-1722
LOGIN_DENIED	invalid username/password; logon denied. 사용자명/비밀번호가 부적합, 로그온할 수 없습니다.	ORA-01017	-1017
NO_DATA_FOUND	not data found. 데이터를 찾을 수 없습니다.	ORA-01403	+100
NOT_LOGGED_ON	not logged on. 로그온 되지않았습니다.	ORA-01012	-1012
PROGRAM_ERROR	PL/SQL:program error PL/SQL:프로그램 오류.	ORA-06501	-6501
ROWTYPE_MISMATCH	PL/SQL:Return types of Result Set variables or query do not match. PL/SQL:Result Set 변수 또는 질의의 리턴 유형이 일치하지 않습니다.	ORA-06504	-6504
STORAGE_ERROR	PL/SQL:storage error use sql collection. PL/SQL:저장 장치 오류입니다.	ORA-06500	-6500
SELF_IS_NULL	method dispatch on NULL SELF agrument is disallowed. NULL SELF 인수로 유형의 멤버 메소드를 호출 중입니다.	ORA-30625	-30625
SUBSCRIPT_BEYOND_COUNT	Subscript beyond count. 첨자가 개수를 넘었습니다.	ORA-06533	-6533
SUBSCRIPT_OUTSIDE_LIMIT	Subscript outside of limit. 서브스크립트가 유효 범위를 벗어났습니다.	ORA-06532	-6532
SYS_INVALID_ROWID	invalid ROWID. ROWID가 부적합합니다.	ORA-01410	-1410
TIMEOUT_ON_RESOURCE	timeout occurred while waiting for a resource. 자원 대기 중 시간초과입니다.	ORA-00051	-51
TOO_MANY_ROWS	exact fetch returns more than requested number of rows. 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다.	ORA-01422	-1422
VALUE_ERROR	PL/SQL:numeric or value error: character string buffer too small. PL/SQL:수치 또는 값 오류: 문자를 숫자로 변환하는데 오류입니다.	ORA-06502	-6502
ZERO_DIVIDE	divisor is equal to zero. 제수가 0입니다.	ORA-01476	-1476

※ NO DATA FOUND 에러만 SQL메시지 코드가 +(플러스) 임

 

 

각 EXCEPTION은 아래와 같이 처리할 수 있다.

 

 - EXCEPTION 예문

BEGIN
  --이런 식의 검색을 한다고 가정
  SELECT COL1 INTO col
  FROM TEST_TABLE
  WHERE KEY1 = 999;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    --검색 결과가 0건의 경우인 처리는 여기에 작성

  WHEN OTHERS THEN
    --검색 결과가 0건이 아닌 경우의 EXCEOTION 처리는 여기에 작성

END;

■ 함수

프로시저 내에서 EXCEPTION 발생 후 발생한 EXCEPTION 에러코드나 에러 메시지를 취득하고자 하는 경우

아래 변수명으로 취득할 수 있다.

 

EXCEPTION 관련 변수
SQLCODE	에러 코드를 반환
SQLERRM	에러 메시지를 반환

 

- SQLCODE와 SQLERRM을 반환하는 샘플 코드

CREATE OR REPLACE PROCEDURE TEST(
  o_sqlcode OUT NUMBER
 ,o_sqlerrm OUT VARCHAR2
) IS
  a NUMBER;

BEGIN
  --예외를 발생하게 하기
  a := 10 / 0;

EXCEPTION
  WHEN OTHERS THEN
    o_sqlcode := SQLCODE;
    o_sqlerrm := SQLERRM;

END TEST;