[오라클] 함수생성, 사용법

오라클은사용자 함수를 직접 생성하여 사용할 수 있다. 보통 하나의 값만 반환하는 함수를 자주 사용하며, 테이블을 반환하는 파이프라인(PIPELINED) 함수도 생성하여 사용할 수 있다.

기본 함수 생성 방법

기본 함수는 하나의 값만 반환이 가능하다.

CREATE OR REPLACE FUNCTION fn_get_dept_name (p_deptno NUMBER) 
    RETURN VARCHAR2 
IS 
    v_dname VARCHAR2(14); 
BEGIN 
    SELECT dname 
      INTO v_dname 
      FROM dept 
     WHERE deptno = p_deptno; 

    RETURN v_dname; 
END;

① 함수생성 DDL 명령어

• CREATE 명령문만 사용 시 함수를 재컴파일 할 때 오류가 발생하기 때문에 REPLACE 구문을 같이 사용한다.

② 함수명 정의

③ 함수 파리미터 선언

• 매개변수1, 매개변수2, 매개변수3... 선언

④ 리턴 타입 지정

• VARCHAR2, NUMBER, DATE 등 반환할 데이터 타입을 지정한다.

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

⑤ 함수 결과 리턴

파이프라인 테이블 함수 생성 방법

파이프라인 테이블 함수는 테이블로 반환이 가능하다.

 -- 1. Object Type을 생성
CREATE OR REPLACE TYPE type_dept AS OBJECT 
( 
    DEPTNO NUMBER, 
    DNAME VARCHAR2(14), 
    LOC VARCHAR2(13) 
); 

-- 2. 반환 테이블 정의
CREATE OR REPLACE TYPE table_dept  
AS TABLE OF type_dept; 

-- 3. 파이프라인 테이블 함수 생성
CREATE OR REPLACE FUNCTION fn_get_dept (p_deptno NUMBER) 
    RETURN table_dept 
    PIPELINED 
IS 
    v_dept type_dept; 
BEGIN 
    FOR rec IN ( 
        SELECT DEPTNO 
             , DNAME 
             , LOC 
          FROM DEPT 
         WHERE DEPTNO >= p_deptno 
    ) LOOP 
        v_dept := type_dept(rec.deptno, rec.dname, rec.loc);         
        PIPE ROW(v_dept); 
    END LOOP; 
       
    RETURN; 
END;