PL function(함수)

-- 함수
-- 매개변수, return 값
-- select 구문 
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;
/
 
SELECT func(24) FROM DUAL;
 
SET SERVEROUTPUT ON
 
CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER) return NUMBER
IS
BEGIN
    return (p_value * 0.15);
END;
/
--
--VAR tax_val NUMBER;
--EXECUTE tax_val := tax(1000);
--PRINT tax_val;
--
 
-- 급여와 커미션을 합쳐서 세금을 계산
CREATE OR REPLACE FUNCTION tax2(p_sal IN employees.salary%TYPE, p_bonus IN employees.commission_pct%TYPE)
    RETURN NUMBER
IS
 
BEGIN
    RETURN ((p_sal + NVL(p_bonus, 0) * p_sal) * 0.15);
END;
/
 
SELECT first_name, salary + salary * NVL(commission_pct, 0) 실급여,
    tax2(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;
/
 
SELECT getJobName(100)
FROM dual;