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

오라클(Oracle)에서 Function이란

호출 시 Function 내에 작성한 쿼리를 수행해서

특정 값을 반환하는데 사용한다

 

대표적인 활용 예제는

사번을 넣었을 경우 뭔가를 뽑아내는 경우가 엄청나게 많은데

실제 업무를 하다 보면 테이블에서 Key에 해당하는 값을 넣으면

이름, 부서, 회사, 주소 등을 반환하게 하는 함수가 대부분이다

 

아마 이름도 GET_CD, GET_NAME, GET_LOC

이런 식으로 되어있는게 90% 이상일 거다

 

함수의 내용물은 다음과 같은데

함수명 선언

사용할 파라미터 선언

반환형 선언

함수 내에서 사용할 변수 선언

쿼리 수행

반환

예외처리

END 함수명;

 

이런 식으로 이루어져 있는데

한번에 보면 머리가 아플거다

하지만 잘라놓고 보면 정말 필수적인 부분은

함수명 선언, 파라미터 선언, 반환형 선언, 쿼리 수행, 반환, END 함수명;

정도인데

 

쿼리 수행만 제외하면

나머지는 복사 붙여넣기로 쉽게 채워넣을 수 있고

쿼리 자체도 위 이미지의 쿼리를 밑에 작성해 놨으니

그대로 갖다가 속의 내용만 바꿔주면 된다

 

다음으로 함수 작성방법을 확인해 보자면

먼저 사용할 파라미터 선언은

파라미터명 IN 데이터타입

으로 사용하며 크기를 설정해주지 않아도 된다

 

다음으로 반환형 선언은

RETURN 데이터타입 IS

이렇게 작성해주면 되고

 

함수 내에서 사용할 변수 선언은

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

이렇게 작성한다

 

쿼리 수행은 특이하게

INTO가 들어가 있는 것이 보이는데

 

조회한 내용을 변수명 안에 넣어준다는 얘기다

어디에 넣어줘야 그 값을 반환할 것 아닌가!

조회 시 INTO는 필수이기 때문에

SELECT FROM 했는데 에러가 난다면

침착하게 INTO를 추가해주면 된다

 

다음으로는 RETURN으로 반환인데

여기서 딱 느낌이 올거다

조회 시 INTO로 변수에 넣어준 값을

RETURN 변수명; 해서 반환시키면 된다

 

마지막으로 예외처리와 END 함수명;인데

먼저 END 함수명;은 해당 함수를 끝내는 것을 알려주기 때문에

필수지만 대부분은 복붙으로 해결하기 때문에

딱히 문제가 되지는 않을거다

 

예외처리는 필수는 아니지만 꼭 해두는 것이 좋은데

만약 위 예제에서 NULL을 넣는다면 IF 처리가 있기 때문에

NULL을 반환시키지만 만약 테이블에 없는 값을 파라미터로 넣는다면?

 

조회할 결과가 없기 때문에 반환할 값이 없게 되고

이러면 에러가 난다

 

고객한테 없을만한 값은 넣지 마세요 이럴 수는 없으니

값이 없을 경우를 대비해 예외처리를 만드는 것이다

조회했는데 값이 없다? 문자만 넣어야 되는데 숫자를 넣었다?

이런 각종 에러상황에 예외처리를 작성해 놓으면

 

이런 상황에 무슨 값을 반환할 지 설정할 수 있게 된다

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

아래 글을 참조해주면 좋겠다

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

이후 작성한 함수를 호출해 봤는데

값이 있는 경우에는 제대로 조회한 값을 가져오고

NULL일 경우에는 NULL을

없는 값일 경우에는 예외처리로 빠진 것이 보인다

 

지금까지 함수를 어떻게 작성하고 사용하는지

한번 쭉 봤는데 결코 복잡한게 아니다

 

그냥 파라미터 넣고 변수 선언하고

조회하고 변수에다가 조회한 값 넣고

그걸 반환!

예외를 대비해 예외처리!

 

이게 전부기 때문에

딴걸 생각하느라 어렵게 받아들이면 안된다

 

마지막으로 예제에 사용한 쿼리는 다음과 같다

 

CREATE OR REPLACE FUNCTION SCOTT.GET_NAME ( -- 함수명
  PARAM_EMPNO IN NUMBER -- 파라미터 선언
)
  RETURN VARCHAR2 IS -- 반환형 선언
  V_NM VARCHAR2(10); -- 변수 선언
BEGIN -- 쿼리 수행 부분

  -- 파라미터로 NULL을 넣었을 경우 NULL 반환
  IF PARAM_EMPNO IS NULL OR PARAM_EMPNO = NULL THEN
      V_NM := NULL;
  ELSE -- 파라미터를 넣었을 경우
    SELECT ENAME -- 조회한 컬럼을
      INTO V_NM  -- V_NM에 넣는다
      FROM EMP
     WHERE EMPNO = PARAM_EMPNO;
  END IF;
  
  RETURN V_NM; -- 조회한 내용 반환
-- 예외처리
EXCEPTION 
  WHEN OTHERS THEN
    RETURN '없는 번호'; -- 예외의 경우 NULL 반환
END GET_NAME;