Oracle PL/SQL PL03 (Procedural extension to Structured Query Language)

PL FUNCTION : 구조 1

SET SERVEROUTPUT ON

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 p_val;

END;
/

SELECT func(24)
FROM dual;

PL FUNCTION : 구조 2

-- (급여와 커미션을 합쳐서 세금을 계산)
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;

PL FUNCTION : 구조 3

-- 사원의 번호를 입력하면, 그사원의 업무명, 부서명을 구할 수 있는 함수
CREATE OR REPLACE FUNCTION getjobName(p_empno IN employees.employee_id%TYPE) RETURN VARCHAR2
IS
v_jobname jobs.job_id%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;

VAR name VARCHAR2(20);
EXEC :name := getjobname(100);
PRINT name;

PL FUNCTION : 예제

1) 두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오.(함수명 add_num)

2) 부서번호를 입력하면 해당 부서에서 근무하는 사원 수를 반환하는 함수를 정의하시오. (함수명 get_emp_count)

3) emp테이블을 이용해서 입사일을 제공하면 근무연차를 구하는 함수를 정의하시오.(소수점 자리 절삭, 함수명 get_info_hiredate)

4) emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을 구하는 함수를 정의하시오.(함수명 get_mgr_name)

5) emp테이블을 이용해서 사원번호를 입력하면 급여 등급을 구하는 함수를 정의하시오.
(4000~5000 A, 3000~4000미만 B, 2000~3000미만 C, 1000~200미만 D, 1000미만 F, 함수명 get_sal_grade)

--1) 두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오.(함수명 add_num)

CREATE OR REPLACE FUNCTION add_num(frst_num IN NUMBER, sec_num IN NUMBER) RETURN NUMBER
IS
BEGIN

RETURN (frst_num + sec_num);

END;
/

SELECT add_num(1,3)
FROM dual;

--2) 부서번호를 입력하면 해당 부서에서 근무하는 사원 수를 반환하는 함수를 정의하시오.
(함수명 get_emp_count)

CREATE OR REPLACE FUNCTION get_emp_count(p_deptno IN employees.department_id%TYPE) RETURN NUMBER
IS
v_sumEmpNum NUMBER;
BEGIN

SELECT DISTINCT COUNT(department_id)OVER(PARTITION BY department_id) INTO v_sumEmpNum
FROM employees
WHERE department_id = p_deptno;

RETURN v_sumEmpNum;

END;
/

SELECT get_emp_count(100)
FROM dual;

--3) emp테이블을 이용해서 입사일을 제공하면 근무연차를 구하는 함수를 정의하시오.
--(소수점 자리 절삭, 함수명 get_info_hiredate)

CREATE OR REPLACE FUNCTION get_info_hiredate(p_hire_date IN employees.hire_date%TYPE) RETURN NUMBER
IS
BEGIN

RETURN (TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(p_hire_date, 'YYYY'));

END;
/

SELECT get_info_hiredate('91/01/12')"연차"
FROM dual;

--4) emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을 구하는 함수를 정의하시오.
-- (함수명 get_mgr_name)

CREATE OR REPLACE FUNCTION get_mgr_name(p_empno employees.employee_id%TYPE)RETURN VARCHAR2
IS
v_name employees.first_name%TYPE;

BEGIN

SELECT b.first_name INTO v_name
FROM employees a, employees b
WHERE a.manager_id = b.employee_id 
AND a.employee_id = p_empno;

RETURN v_name;

END;
/

SELECT get_mgr_name(103)"해당 매니저이름"
FROM dual;

--5) emp테이블을 이용해서 사원번호를 입력하면 급여 등급을 구하는 함수를 정의하시오.
--(4000~5000 A, 3000~4000미만 B, 2000~3000미만 C, 1000~200미만 D, 1000미만 F,
-- 함수명 get_sal_grade)

CREATE OR REPLACE FUNCTION get_sal_grade(p_empno employees.employee_id%TYPE) RETURN VARCHAR2
IS
v_grade VARCHAR2(20);

BEGIN

SELECT CASE
WHEN salary BETWEEN 4000 AND 5000 THEN 'A'
WHEN salary BETWEEN 3000 AND 4000 THEN 'B'
WHEN salary BETWEEN 2000 AND 3000 THEN 'C'
WHEN salary BETWEEN 1000 AND 2000 THEN 'D'
WHEN salary < 1000 THEN 'F'
ELSE 'S'
END 
INTO v_grade
FROM employees
WHERE employee_id = p_empno;

RETURN v_grade;

END;
/

SELECT get_sal_grade(100)
FROM dual;