ORACLE 프로시저

1. 프로시저란?

오라클에서의 프로시저는 PL/SQL을 통해 만들어진다. 자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율을 늘릴 수 있다. 오라클 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램이다.( 따라서 프로시저는 함수처럼 SELECT절에는 사용할 수 없다.)

또한, 프로시저란 application의 특정 business logic을 캡슐화하여 재사용 가능하게한 단위이며 Oracle Database에 Schema Object로 저장된 명명 Block이며 Module 단위 Programming이다.(DB 단에서 특정 비즈니스 로직을 지정해 놓고 호출을 통해 언제든 재사용하는 비즈니스로직 단위 함수인 것) 프로시저는 최초 실행시 Compile되고, 이후 재 실행시는 Compile되지 않는다.

 

PL/SQL Procedure vs Stored Procedure 는 각각 저장위치와 호출방법이 다르다.

저장위치 : 프로시저 또는 익명 블록내 저장(PL/SQL Procedure), DB의 모든 Schema에 저장(Stored Procedure)

호출방법 : EXECUTE procedure_name( arguments );(PL/SQL Procedure), {schema}.{procedure}@database(Stored Procedure)

 

https://velog.io/@gillog/Oracle-PLSQL-Procedure

[Oracle] PL/SQL Procedure

2. 프로시저 구조

## 기본 틀
오라클 프로시저는 여러 개의 쿼리를 한 번에 수행하는 특징을 갖는다. 

1) 프로시저 선언 : CREATE OR REPLACE PROCEDURE 프로시저명
2) 파라미터 선언 : ()SELECT 해서 선언한 변수에 넣을 경우는 INTO를 사용해서 선언한 변수에 넣어줘야 한다.
3) 변수 선언 IS
4) 수행할 쿼리 BEGIN
5) 예외 처리 EXCEPTION 필수는 아니지만 처리하는 게 좋다.(ex 필수 값 넣지 않거나, 칼럼의 크기를 넘어서거나, 중복값 넣거나 하는 등)
6) 프로시저 종료 : END 프러시저명

CREATE OR REPLACE PROCEDURE [프로시저명]
 // 1) IN은 입력, OUT은 출력, IN OUT은 입출력 디폴트는 IN, 디폴트 값은 IN 에만 설정할 수 있다.
 // 2) OUT 매개변수는 프로시저 내에서 로직처리 후 해당 매개변수에 값을 할당 해 프로시저 호출부분에서 이 값을 참조할 수 있다.
 // 3) OUT 매개변수를 변수 형태로 전달하고 프로시저 실행부(BEGIN)에서 이 매개변수에 특정 값을 할당한다. 실행 끝나고 참조할 수 있음.
 // 4) IN OUT 매개변수는 입력과 동시에 출력용으로 사용할 수 있다.OUT 매개변수는 프로시저가
 // 5) 성공적으로 실행을 완료할 때까지 값이 할당되지 않는다.따라서
 // 6) 매개변수에 값을 전달해서 사용한 다음 다시 이 매개변수에 값을 받아와 참조하고 싶으면 IN OUT 매개변수를 사용해야함.
 // 7) 프로시저에서 RETURN문은 이후 로직 수행하지 않고 빠져나감.
	(매개변수명1 [IN | OUT | IN OUT] 데이터타입 [:=디폴트값]
    ,매개변수명1 [IN | OUT | IN OUT] 데이터타입 [:=디폴트값] 
    ..)
IS[AS]
	변수, 상수 등 선언
BEGIN
	실행부
[EXCEPTION 
	예외처리부]
    
END [프로시저명];

######################################################################################################################
CREATE OR REPLACE PROCEDURE PROCE_EX
(
   P_DEPARTMENT IN VARCHAR2,
   P_STUDENT_CNT IN NUMBER
)
IS
P_UNIVERSITY VARCHAR2(100)  := '한국대학교';

BEGIN

INSERT INTO UNIVERSITY1 (UNIVERSITY, DEPARTMENT, STUDENT_CNT)
VALUES (P_UNIVERSITY, P_DEPARTMENT, P_STUDENT_CNT);
COMMIT;

END PROCE_EX;

 

프로시저 실행 : EXEC PROCE_EX('컴퓨터공학과', 100);

 

2-1. 프로시저 예시 IN, OUT, IN OUT, RETURN

https://logical-code.tistory.com/48

[PL/SQL] 프로시저(PROCEDURE)

2-2. 프로시저 예외처리

## 기본 형식 ##
EXCEPTION WHEN 예외명 THEN 예외처리구문1
		  WHEN 예외명 THEN 예외처리구문2
          ...
          WHEN OTHERS THEN 예외처리구문N;

 

프로시저에서 예외처리를 한경우 : 프로시저가 정상적으로 종료되고 다음 로직이 수행된다.

프로시저에서 예외처리를 하지 않은경우 : 프로시저에서 강제적으로 종료되고 오류 메시지가 출력된다.

# 예외처리 하지 않은 프로시저 선언

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE
IS
	VI_NUM NUMBER :=0;
BEGIN
	VI_NUM :=10/0;
    //로그  찍기
    DBMS_OUTPUT.PUT_LINE('SUCCESS');
END;

# 예외처리한 프로시저 선언

CREATE OR REPLACE PROCEDURE EXCEPTION_PROCEDURE
IS
	VI_NUM NUMBER :=0;
BEGIN
	VI_NUM := 10/0;
    //로그 찍기
    DBMS_OUTPUT.PUT_LINE('SUCCESS');
   
EXCEPTION
	WHEN OTHERS THEN
    		DBMS_OUTPUT.PUT_LINE('예외발생!!');
END;


# 예외처리 하지 않은 프로시저 실행
DECLARE
	VI_NUMBER NUMBER := 0;
BEGIN 
	TEST_PROCEDURE;
    DBMS_OUTPUT.PUT_LINE('SUCCESS');
END;
## 실행하다가 멈춤

# 예외처리 한 프로시저 실행
DECLARE
	VI_NUMBER NUMBER :=0;
BEGIN
	EXCEPTION_PROCEDURE;
    DBMS_OUTPUT.PUT_LINE('SUCCESS');
END;

결과
예외발생!!
SUCCESS

https://logical-code.tistory.com/49

[PL/SQL] 예외처리 (1) : 예외처리란?

1) 시스템예외 : OTHERS외에 사용할 수 있는 시스템 예외명이 존재하는데, ORACEL 내부에 미리 정의된 예외라고한다.(OTHERS를 사용하면 오라클 시스템에서 PL/SQL 코드상에서 발생한 런타임 예외를 자동으로잡아줌)

 

2) 사용자 정의 예외 : 사용자가 직접 예외를 정해서 사용

 1. 예외 정의 : 사용자정의 예외명 EXCEPTION;

 2. 예외 발생 : RAISE 사용자 정의 예외명;

 3. 발생된 예외 처리 :  EXCEPTION WHEN 사용자 정의 예외명 THEN

// 예외를 발생시키면 자동으로 제어권이 EXCEPTION으로 넘어오므로 시스템 예외와 동일한 방식으로 처리하면된다.

 4. 사용자 정의 예외명과 시스템 예외코드 연결 https://logical-code.tistory.com/50

 PRAGMA EXCEPTION_INIT (사용자 정의 예외명, 시스템 예외 코드);

CREATE OR REPLACE PROCEDURE HELLO_PROCEDURE
( P_EMP_NAME EMPLOYEES.EMP_NAME%TYPE,
  P_DEPARTMENT DEPARTMENTS.DEPARTMENT%TYPE,
  P_WORKINT_YEAR VARCHAR2)
IS
 ...
 EX_INVALID_MONTH EXCEPTION;
 EX_INCALID_YEAR EXCEPTION;
 PRAGMA EXCEPTION_INIT (EX_INVALID_MONTH, -1855);
 ...
 
 
 
 EXCEPTION
 	WHEN EX_INVALID_MONTH THEN
    DBMS_OUTPUT.PUT_LINE('ㅇㅇㅇㅇㅇㅇ');
    WHEN EX_INVALID_YEAR THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE);
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    DBMS_OUTPU.PUT_LINE('1~12월 범위를 벗어났습니다.');
    WHEN OTHERS THEN
    	DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;

2-3. 효율적으로 예외처리하기

가. 시스템 예외인 경우 OTHERS를 사용

  - 모든 시스템의 예외를 잡아주는 OTHERS와 예외의 정보를 알려주는 SQLCODE, SQLERRM를 사용

나. 예외처리 루틴을 공통 모듈화 하고, 발생된 예외 로그 남기기

  - 발생한 예외에 대한 로그 테이블을 만들어 예외가 발생할 때 마다 로그 테이블에 기록 

다. 사용자 정의 예외도 별도의 테이블을 만들어 로그 관리

 -  https://logical-code.tistory.com/51 (참고)

[PL/SQL] 예외처리(3) : 효율적인 예외처리

2-4. 프로시저 호출

EXEC [프로시저명]; 
또는
CALL [프로시저명];

3. 오라클 조회 프로시저

https://wakestand.tistory.com/132

오라클 프로시저(Procedure) 작성부터 실행, 조회까지

4. 프로시저 디버깅

https://wakestand.tistory.com/376

DBeaver 오라클 펑션/프로시저 디버깅 방법

5. 프로시저 문법

https://studyingazae.tistory.com/59

[ORACLE] 프로시저(Procedure) 생성 및 실행 (PL/SQL)

6. mybatis, spring procedure 

https://kkh0977.tistory.com/1204

28. (spring/스프링) mybatis 오라클 프로시저 호출 및 다중 아웃 out 메시지, 커서 cursor 데이터 확인 실