27일차//[Oracle] FUNCTION 정리

	procedure	function
실행속도	이미 컴파일 된 결과를 바로 실행	컴파일을 거친 후 진행해야 되기 때문에 Procedure보다 성능이 느림
리턴값	리턴값은 없지만 파라미터에 in, out을 사용하여 입력값과 출력값을 가져올 수 있다. OUT파라미턱 많으면 출력을 많이 할 수 있다.	선언한 정해진 값만 리턴이 가능하다.
SELECT 문 호출 여부	리턴값은 없지만 파라미터에 in, out을 사용하여 입력값과 출력값을 가져올 수 있으며, out	SELECT 문 내부에서 사용가능하다.
그외✴ 사용가능	insert, delete, update	select

대부분 SELECT 절에서 사용할 시에는 주로 FUNCTION을 사용한다

SELECT를 제외한 부분에서는 PROCEDURE을 사용한다

FUNCTION은 INSERT, DELETE, UPDATE에서는 사용하지 않는다

FUNCTION은 리턴값을 갖지만 INSERT,DELETE, UPDATE는 리턴값이 없기 때문이다

 

함수의 구조

문법

 1. 함수(Function)의 선언

CREATE OR REPLACE FUNCTION 함수이름

여기까지는 프로시저의 선언방식과 동일하다. 사용자가 정의한 함수이름이 이미 존재하면 기존 것을 대체하고, 존재하지 않는다면 새로 만든다는 의미이다. 다음은 'fn_info'라는 이름을 가진 함수를 선언하는 방법이다.

CREATE OR REPLACE FUNCTION fn_info

 

 

2. 매개변수의 선언

편의상 파라미터라고 한다. Function 내 계산식에서 사용할 갖가지 매개변수들을 명시하는데, 방법은 프로시저에서 매개변수들을 선언할 때와 동일하다. 여러 개의 파라미터가 필요하다면, 쉼표로 구분하여 나란히 적어준다. (매개변수명1 IN 변수타입, 매개변수명2 IN 변수타입, ...)

 

다만, FUNCTION은 프로시저와 다르게 'IN' 파라미터만 허용한다. (OUT 파라미터는 사용 불가)

CREATE OR REPLACE FUNCTION fn_info (name IN VARCHAR2, age IN NUMBER)

 

 

3. Return 타입의 선언

다음으로 return 타입의 선언부가 있다. 함수는 반드시 return 값을 가지는데, 그 값이 어떤 타입인지(VARCHAR, NUMBER, CHAR, ...)를 적어준다. 단, return 값은 함수의 실행부에서 넣어주기 때문에 여기에서는 단순히 '리턴 타입'만 적어준다.

RETURN 변수타입

RETURN VARCHAR2

RETURN NUMBER

CREATE OR REPLACE FUNCTION fn_info (name IN VARCHAR2, age IN NUMBER) RETURN VARCHAR2

 

4. 지역변수의 선언

이제는 BLOCK 안에서만 사용될 지역변수의 선언부이다. 이곳도 프로시저의 지역변수 선언부와 동일하다. 리턴 타입의 선언이 끝나면, 'IS'로 구분하여 함수의 실행부에서 사용될 지역변수들에 대한 정의를 한다.

변수명 변수타입;

 

변수타입은 VARCHAR2, NUMBER와 같이 오라클 자료형을 그대로 써줘도 되고, 존재하는 테이블의 특정 컬럼 타입을 형태를 그대로 가져올 수도 있다.

name_result VARCHAR2(300); --OR name_result TABLE.COLUMN%TYPE; 

age_result NUMBER;

CREATE OR REPLACE FUNCTION fn_info
(name IN VARCHAR2, age IN NUMBER)

RETURN VARCHAR2 

IS 

name_result VARCHAR2(300); --OR name_result TABLE.COLUMN%TYPE;
age_result NUMBER;

 

5. 실행부

FUNCTION 내에서 실제 실행할 핵심 내용을 BEGIN ~ END 구문 안에 적어주면 된다.IF, WHILE, FOR, LOOP 등의 제어문을 사용해도 되고, 특정 테이블에 대한 CRUD 작업을 진행해도 된다. 그러나 실행부가 끝나기 전에, 반드시 위에 기술한 RETURN 타입의 반환값을 뱉어야 한다.리턴값이 없으면, 함수 선언시 에러가 발생한다.

BEGIN 실행내용; 

RETURN 리턴값;

END;

 

FUNCTION 생성

CREATE OR REPLACE FUNCTION func(p_val IN NUMBER)
RETURN NUMBER
IS
    v_val NUMBER;
BEGIN
    v_val := p_val;
    v_val := v_val * 2;
    
    RETURN v_val;
END;
/

FUNCTION 출력

SELECT func(3)
FROM DUAL;

 

세금계산 함수

CREATE OR REPLACE FUNCTION TAX(p_sal IN employees.salary%TYPE,
                                p_bonus IN employees.commission_pct%TYPE)
                                RETURN NUMBER
                                IS
                                    val NUMBER;
                                BEGIN
                                    val := (p_sal + NVL( p_bonus,0) * p_sal) * 0.15;
                                    RETURN val;
                                END;
                                /
                                
                                SELECT first_name, salary * nvl(commission_pct, 0) 실급여,
                                tax(salary,commission_pct) 세금
                                FROM employees;
                                

 

사원번호를 입력하면 업무명을 출력할 수 있다.

CREATE OR REPLACE FUNCTION getjobName(p_empno IN employees.employee_id%TYPE)
    RETURN VARCHAR2
    
    IS
        v_jobname jobs.job_title%TYPE;
    BEGIN
        SELECT j.job_title INTO v_jobname
        FROM employees e, jobs j
        WHERE e.job_id = j.job_id
            AND e.employee_id = p_empno;
            
            RETURN v_jobname;
    END;
    /
    
    VAR jname VARCHAR2(20);
    EXEC :jname := getjobname(100);
    PRINT jname;
    
    SELECT getjobname(100)
    FROM DUAL;
    
    SELECT employee_id ,getjobname(employee_id)
    FROM employees;