[오라클 SQL] 프로시저 생성/CREATE PROCEDURE / 예외 선언

소스 코드

CREATE OR REPLACE PROCEDURE TEST_PROC
(
	IN_PARAM1 IN VARCHAR2,
    IN_PARAM2 IN VARCHAR2
)
IS
V_DATA1 TABLE_NM.COLUMN_NM%TYPE;
V_CNT NUMBER;

PARAMETER_NULL EXCEPTION;
NO_SELECT EXCEPTION;

BEGIN

IF (IN_PARAM1 IS NULL OR IN_PARAM2 IS NULL) THEN
	RAISE PARAMETER_NULL;
END IF;

	SELECT COUNT(*) INTO V_CNT FROM TABLE_NM WHERE COLUMN_NM = '조건';
    
IF V_CNT <= 0 THEN
	RAISE NO_SELECT;
END IF;

	INSERT INTO TABLE_NM VALUES('1', '2', '3');
    DBMS_OUTPUT.PUT_LINE('INSERT COMPLETE');

EXCEPTION
	WHEN PARAMETER_NULL THEN
    	DBMS_OUTPUT.PUT_LINE('PARAMETER IS NULL');
    	ROLLBACK;
    WHEN NO_SELECT THEN
    	DBMS_OUTPUT.PUT_LINE('데이터가 없습니다.');
    	ROLLBACK;
    WHEN OTHERS THEN
    	DBMS_OUTPUT.PUT_LINE('에러 발생');
    	ROLLBACK;
END;

최근에 만들었던 프로시저를 샘플로 만든 코드입니다.

인서트 프로시저 샘플이고 대략적인 코드 설명하겠습니다.

코드 설명

CREATE OR REPLACE PROCEDURE TEST_PROC
(
	IN_PARAM1 IN VARCHAR2,
    IN_PARAM2 IN VARCHAR2
)

파라미터를 설정하는 부분은 프로시저명 뒤에 괄호()를 추가해서 설정할 수 있습니다. 파라미터가 없다면 괄호를 생략할 수 있습니다.

인풋 파라미터는 파라미터 명, IN/OUT타입, 데이터타입 이렇게 3가지로 설정할 수 있습니다.

프로시저 내의 변수는 IS 다음 선언합니다.

IS
V_DATA1 TABLE_NM.COLUMN_NM%TYPE;
V_CNT NUMBER;

PARAMETER_NULL EXCEPTION;
NO_SELECT EXCEPTION;

변수명 데이터타입; 형식으로 선언하며 실제 테이블의 컬럼 형식을 그대로 적용하고 싶다면 테이블명.컬럼명%TYPE 을 사용하면 됩니다.

예외부를 사용자 정의로 설정할 땐 사용자 정의 예외명을 설정해야 합니다.

예외명 EXCEPTION; 으로 정의하면 됩니다.

BEGIN 뒤로는 프로시저의 로직을 정의하면 됩니다.

이 부분에서 언제 사용자 정의 예외가 실행되야 하는지 정의하시면 됩니다.

SELECT, INSERT 등의 데이터 조작 언어도 이 부분에서 사용됩니다.

IF (IN_PARAM1 IS NULL OR IN_PARAM2 IS NULL) THEN
	RAISE PARAMETER_NULL;
END IF;

RAISE 예외선언 명; 이 코드가 사용자 정의 예외를 실행하는 코드입니다.

SELECT COUNT(*) INTO V_CNT FROM TABLE_NM WHERE COLUMN_NM = '조건';

SELECT INTO는 추출한 데이터를 변수에 저장하는 문법입니다.

DBMS_OUTPUT.PUT_LINE('INSERT COMPLETE');

프로시저가 실행됨에 따라 로그를 볼 땐 DBMS_OUTPUT 기능을 사용하면 로그를 볼 수 있습니다.

EXCEPTION
	WHEN PARAMETER_NULL THEN
    	DBMS_OUTPUT.PUT_LINE('PARAMETER IS NULL');
    	ROLLBACK;
    WHEN NO_SELECT THEN
    	DBMS_OUTPUT.PUT_LINE('데이터가 없습니다.');
    	ROLLBACK;
    WHEN OTHERS THEN
    	DBMS_OUTPUT.PUT_LINE('에러 발생');
    	ROLLBACK;

예외가 발생했을 때의 로직을 정하는 부분입니다.

사용자 정의 예외를 만들지 않았다면 맨 밑의 OTHERS 만 사용해도 예외처리를 할 수 있습니다.