231102 PL/SQL 함수(FUNCTION)

■ 함수(function)
- 함수는 값을 반환할 수 있는 이름이 있는 PL/SQL 블록이다.
- 함수는 값을 계산 할 때 많이 사용하는 서브 프로그램이다.

# 프로시저 생성

CREATE OR REPLACE PROCEDURE get_sal (
    p_id   IN   NUMBER,
    p_sal  OUT  NUMBER
)
IS 
BEGIN
    SELECT salary
    INTO p_sal
    FROM hr.employees
    WHERE employee_id = p_id;
EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        NULL;
END get_sal;
/

# 프로시저 호출

DECLARE
    v_sal NUMBER;
BEGIN 
    get_sal(100, v_sal); --프로시저 호출
    dbms_output.put_line(v_sal);
END;
/

# 함수 호출 방식
- IN 모드만 받는다.

# RETURN : 한 값만 리턴 한다. header 에 RETURN문 타입만 써야 한다.

CREATE OR REPLACE FUNCTION get_sal_func (
    p_id IN NUMBER -- 필수는 아니다
) RETURN NUMBER -- header 부분에 ; 을 쓰지 않음. 타입만 작성. size X.
IS 
    v_sal NUMBER := 0;
BEGIN
    SELECT salary
    INTO v_sal
    FROM hr.employees
    WHERE employee_id = p_id;
    
    RETURN v_sal;
EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        RETURN v_sal;
END get_sal_func;
/

# 함수 호출방식

BEGIN
    DBMS_OUTPUT.PUT_LINE(get_sal_func(1000));
END;
/

# 함수의 호출방식

DECLARE
    v_sal NUMBER;
BEGIN 
    v_sal := get_sal_func(100); 
    dbms_output.put_line(v_sal);
END;
/

# SELECT문 함수 호출방식

SELECT employee_id, get_sal_func(employee_id)
FROM hr.employees;

# return 문

CREATE OR REPLACE FUNCTION hr.today 
    RETURN VARCHAR2
IS 
BEGIN
    RETURN to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
END;
/

# SELECT문 으로 확인

SELECT HR.today
FROM DUAL;

 

[문제] 급여에 3.3% 를 계산하는 tax 함수를 생성해주세요.
SELECT employee_id, salary, tax(salary)
FROM hr.employees;

 

정답) 

CREATE OR REPLACE FUNCTION hr.tax (
    p_sal IN NUMBER
) RETURN NUMBER 
IS
BEGIN
    RETURN(p_sal * 0.033); -- 한 문장만 return
END tax;
/

# SELECT문 으로 확인

SELECT employee_id, salary, tax(salary)
FROM hr.employees;

# 소스 확인

SELECT text
FROM user_source
WHERE name = 'TAX';

[문제] 급여를 계산하는 get_annual_comp 함수를 생성해주세요.
(nvl 함수를 사용하지 마시고, 조건제어문 이용해서 문제풀어주세요.)

SELECT
    employee_id,
    salary,
    commission_pct,
    salary * 12 + salary * 12 * commission_pct annual_salary_1, 
    salary * 12 + salary * 12 * nvl(commission_pct,0) annual_salary_2,
    get_annual_comp(salary, commission_pct)
FROM hr.employees;

1#) nvl() 함수

CREATE OR REPLACE FUNCTION hr.get_annual_comp(
    p_sal in NUMBER , 
    p_comm in NUMBER )
    RETURN NUMBER
IS 
    v_comm number := 0;
BEGIN 
    RETURN(p_sal * 12 + p_sal * 12 * nvl(p_comm,0));    
END get_annual_comp;
/

2#) nvl() 함수 사용 x

CREATE OR REPLACE FUNCTION hr.get_annual_comp(
    p_sal IN NUMBER , 
    p_comm IN NUMBER )
    RETURN NUMBER
IS 
BEGIN 
    IF p_comm IS NOT NULL THEN 
        RETURN(p_sal * 12 + p_sal * 12 * p_comm);
    ELSIF p_comm IS NULL THEN 
        RETURN (p_sal*12);
    END IF; 
END get_annual_comp;
/

[문제] 사원번호를 입력값으로 받아서 그 사원의 근무 연수를 출력하는 함수를 작성해주세요. 
단, 없는 사원번호가 입력값으로 들어오면 내가 만든 오류번호, 오류메시지를 출력해주세요.

SELECT employee_id, hire_date, get_year(employee_id)
FROM hr.employees;

 

EXECUTE dbms_output.put_line(get_year(100));
EXECUTE dbms_output.put_line(get_year(300));

# 함수 생성

내가 쓴 코드)

CREATE OR REPLACE FUNCTION hr.get_year (
    p_id IN NUMBER
) RETURN NUMBER 
IS
    v_year NUMBER;
BEGIN 
    select trunc(months_between(sysdate, hire_date)/12) year
    into v_year
    from hr.employees
    where employee_id = p_id;
     
    return(v_year); -- 값: 근무연수
EXCEPTION
    when OTHERS then
        raise_application_error(-20000, '조회할 수 없습니다.');
END get_year;
/

# SELECT문 으로 확인

SELECT employee_id, hire_date, get_year(employee_id)
FROM hr.employees;

정답) 

CREATE OR REPLACE FUNCTION hr.get_year (
    p_id IN hr.employees.employee_id%TYPE
) RETURN NUMBER 
IS
    v_years NUMBER;
BEGIN 
    SELECT TRUNC(months_between(sysdate, hire_date)/12) 
    INTO v_years
    FROM hr.employees
    WHERE employee_id = p_id;
    
    RETURN(v_years); -- 값: 근무연수
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, p_id||'번 사원은 존재하지 않습니다.', FALSE);
END get_year;
/

[문제] 문자타입의 컬럼이지만 data는 숫자형식만 입력되어 있어야 한다.
숫자+문자 = 1 ,문자+null = 0 리턴하는 함수를 생성해 주세요. 

SELECT postal_code, as_number(post_code)
FROM hr.locations;

# 힌트 to_number() 

SELECT to_number('10') FROM dual;
SELECT to_number(10) FROM dual;
SELECT to_number(NULL) FROM dual;

# 함수 생성

CREATE OR REPLACE FUNCTION hr.as_number (
    p_num IN VARCHAR2
) RETURN NUMBER 
IS
    v_num NUMBER;
BEGIN
    v_num := to_number(p_num);
    IF p_num IS NULL THEN
        RETURN 0;
    ELSE
        RETURN 1;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END as_number;
/

# 문자+숫자 타입만 있는 DATA만 뽑기

SELECT postal_code, as_number(postal_code)
FROM hr.locations
WHERE as_number(postal_code) = 0;