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

오라클에서 프로시저(Procedure)는 

여러 개의 쿼리를 한번에 수행한다는 특징이 있는데

 

같은 PL/SQL인 함수(Function)과 비교해보자면

함수는 '특정 값을 Return' 시키는 것이 중점이지만

오라클 함수(Function) 생성 및 문법, 실행, 조회까지

프로시저는 여러 개의 쿼리를

'한번에 수행'하는 것이 중점이다

 

예제의 쿼리는 PROC 테이블에서

총 ROW 수 + 1한 값을 V_NUMBER에 넣은 뒤

가져온 파라미터와 함께

PROC 테이블에 INSERT 시키는 내용인데

 

이걸 프로시저 없이 자바로만 수행하려면

COUNT(*) + 1을 구하는 메소드를 작성하고

메소드에서 구한 값을 파라미터로 넣어서

타 파라미터들과 함께 INSERT를 수행하는 식인데

 

두개니까 별거 아닌거 같아 보이겠지만

쿼리가 10~20개로 늘어나면

자바로 그걸 어떻게 다 짜나

 

그리고 자바는 오라클에서 처리하는 것에 비해

속도가 매우 느리기 때문에

오라클에서 한번에 처리해서 가져오는 것이 유리하다

 

그래서 프로시저를 사용하게 되는 것이다

 

프로시저의 구성은 다음과 같은데

프로시저 선언

파라미터 선언

변수 선언

수행할 쿼리

예외처리

프로시저 종료

 

침착하게 차례대로 알아보자

먼저 프로시저 선언은 

CREATE OR REPLACE 프로시저명 을 말하는데

대부분의 경우에는 Oracle SQL Developer나 DBeaver 같은

툴을 사용해서 프로시저를 작성하기 때문에

이름은 미리 작성되어 있으므로

본인이 직접 작성할 일은 거의 없을거다

 

다음으로 파라미터는 프로시저 호출 시

가지고 들어올 값들을 말하는데

파라미터명 IN 데이터타입

이렇게 작성해주면 된다

 

다음으로 변수 선언은

해당 프로시저에서 사용할 변수들을 말하는데

SELECT 후 INTO로 값을 변수에 넣어주게 되므로

SELECT한 값을 활용해야 한다면

위와 같이 변수를 만들어 사용해야 한다

 

변수는

 변수명 데이터타입(크기);

형태로 만들어주면 된다

 

수행할 쿼리의 경우에는

여러 쿼리를 한 프로시저 안에 넣을 수 있는데

 

포인트는 SELECT 시에는 반드시 INTO를 사용해서

조회한 내용을 선언한 변수 안에 넣어줘야 한다는 거다

 

SELECT만 했는데 에러가 나요는

INTO를 사용해 변수에 넣어주지 않았기 때문에

에러가 나는거다

 

위 예제에서는 SELECT 문에서

총 ROW 수를 구하고 +1을 한 뒤에 V_NUMBER 변수에 넣어주고

프로시저 호출 시 가져온 파라미터 3개와

함께 INSERT를 시키는 내용이 되겠다

 

다음으로는 예외처리(Exception) 인데

예외처리는 필수가 아니지만 넣는 것이 좋다

 

ID가 필수값인데 ID를 넣지 않았다거나

컬럼의 크기를 넘어가거나, 중복 값을 넣거나 하는 등

각종 에러 상황이 발생할 경우 예외처리가 없다면

그냥 안돼요 이럴 상황을

왜 안되는지 설명시킬 수 있게 된다

 

예제에서의 에러처리는 그냥 에러가 뜨면

에러 발생! 이 뜨게 해 놨는데

에러처리는 약간 내용이 있기 때문에

아래 글을 참조해주면 된다

오라클 PL/SQL 예외처리(Exception) 종류부터 사용방법 정리

마지막으로 프로시저 종료 부분인데

END 프로시저명으로 한 프로시저를 종료하게 된다

 

 

실제 작성한 프로시저를 호출해 보면

ID가 중복되지 않을 경우에는 값이 잘 들어가고

ID가 중복이라거나 컬럼 사이즈를 넘는 값을 파라미터로 넘긴다던가

하는 경우에는 에러 발생!이 뜨는 것이 보인다

 

설명만 보자면 프로시저가 어려워 보일 수 있겠지만

결국 파라미터 넣고 여러 쿼리를 수행하는게 전부다

 

프로시저의 장점은 여러 쿼리를 한번에 수행할 수 있다는 거고

 

단점은 유지보수가 힘들다는 건데

쿼리가 짧다면 큰 문제가 안되지만

2000줄이 넘어가는데 에러가 발생하고

어느 상황인지도 잘 파악이 안된다면

쿼리 몇십개가 도는데 잡아내기도 쉽지 않고

정말 눈 앞이 막막해진다

 

대부분의 프로시저는 몇십년 된 프로시저가 많고

내가 만든 프로시저를 내가 영원히 유지보수하지 않기 때문에

남이 개같이 만들어 놓은거 안된다고 유지보수 하라고 주면

눈 앞이 막막해진다는게 어떤 느낌인지 깨닫게 될거다

 

마지막으로 예제에 사용한 테이블 및 쿼리는 아래와 같다

 

-- 테이블 생성용
CREATE TABLE "SCOTT"."PROC" 
   (	"SEQ" VARCHAR2(20), 
	"ID" VARCHAR2(20) NOT NULL ENABLE, 
	"NAME" VARCHAR2(20), 
	"LOC" VARCHAR2(20), 
	 CONSTRAINT "PROC_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

 

CREATE OR REPLACE PROCEDURE SCOTT.TEST_PROC -- 프로시저명 선언
( 
  PARAM_ID IN VARCHAR2
  , PARAM_NAME IN VARCHAR2
  , PARAM_LOC IN VARCHAR2 -- 파라미터 선언
) AS
  V_NUMBER NUMBER(3); -- 안에서 사용할 변수 선언
BEGIN -- 수행할 쿼리 작성

  SELECT COUNT(*) + 1 -- 최대값 뽑아옴
    INTO V_NUMBER
    FROM PROC;
    
  INSERT INTO PROC VALUES(V_NUMBER, PARAM_ID, PARAM_NAME, PARAM_LOC);
  
EXCEPTION -- 예외처리
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('에러 발생!'); 
END TEST_PROC; -- 프로시저 종료

 

-- 프로시저 호출 예제
SET SERVEROUTPUT ON; -- DBMS_OUTPUT.PUT_LINE 출력 확인
BEGIN
  TEST_PROC('GOGO', 'NAMENAME', '자택');
END;

SELECT * FROM PROC;