PL 응용문제 (프로시져, 함수)

-- 예제1) 사원번호 입력 받고 그 사원의 급여에 1000을 더한 값으로 갱신하라.
 
ACCEPT p_empno PROMPT '사원의 번호를 입력하시오(+1000):'
DECLARE
    v_sal NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = &p_empno;
 
    SELECT salary INTO v_sal
    FROM employees
    WHERE employee_id = &p_empno;
    
    DBMS_OUTPUT.PUT_LINE('사원번호:'||&p_empno||' '||'급여:'||v_sal);
END;
/
 
/*
예제2) 이름,급여,부서번호를 입력받아 EMP 테이블에 자료를 등록하는 SCRIPT를 작성하여라. 
단 10번 부서일 경우, 입력한 급여의 20%를 추가하고 
초기값이 9000부터 9999까지 1씩 증가하는 SEQUENCE(EMP_EMPNO_SEQ)작성하여 
사용하고 아래의 표를 참고하여라.
이    름: 홍길동
급    여: 2000
부서번호: 10
*/
CREATE SEQUENCE EMP_EMPNO_SEQ
INCREMENT BY 1
START WITH 9000
MAXVALUE 9999;
 
ACCEPT p_name PROMPT '이 름:'
ACCEPT p_sal PROMPT '급 여:'
ACCEPT p_deptno PROMPT '부서번호:'
 
DECLARE
    v_name VARCHAR2(10) := UPPER('&p_name');
    v_sal employees.salary%TYPE := &p_sal;
    v_deptno employees.department_id%TYPE := &p_deptno;
BEGIN
    
    IF v_deptno = 10 THEN
        v_sal := v_sal * 1.2;
    END IF;
    
    INSERT INTO employees(employee_id, last_name, hire_date, salary, 
                        department_id, job_id, email)
    VALUES(EMP_EMPNO_SEQ.nextval, v_name, TO_DATE('191220', 'YYMMDD'), v_sal,
                        v_deptno, 'IT_PROG', v_name || '@daum.net');
END;
/
 
SELECT *
FROM employees
WHERE last_name = 'ADMIN';
 
/*
예제3) 
EVEN_ODD(
    ID:NUMBER(4) 
    GUBUN:VARCHAR2(10)
) 
테이블을 작성하고 START숫자와 END숫자를 입력 받아 그 사이의 숫자를 ID에   1 10
ID의 숫자가 짝수이면 GUBUN에 “짝수”를 홀수이면 GUBUN에 “홀수”라고 입력하는 
SCRIPT를 WHILE문으로 작성하여라.
*/
CREATE TABLE EVEN_ODD(
    ID NUMBER(4),
    GUBUN VARCHAR2(10)
);
 
ACCEPT p_start PROMPT 'start 숫자:'
ACCEPT p_end PROMPT 'end 숫자:'
DECLARE
    v_start NUMBER(4) := &p_start;
    v_end NUMBER(4) := &p_end;
BEGIN
    IF v_start > v_end THEN
        DBMS_OUTPUT.PUT_LINE('start가 end보다 큽니다.');
    ELSE
        WHILE v_start <= v_end LOOP            
            IF MOD(v_start, 2) = 0 THEN
                INSERT INTO EVEN_ODD(id, gubun)
                VALUES(v_start, '짝수');
            ELSE
                INSERT INTO EVEN_ODD(id, gubun)
                VALUES(v_start, '홀수');
            END IF;        
            v_start := v_start + 1;
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE(&p_start || '로부터 ' || &p_end || '까지 '||
                            TO_CHAR(&p_end - &p_start + 1)||'개의 자료가 입력되었습니다');
    END IF;
END;
/
SELECT * FROM EVEN_ODD;
 
-- 예제4)사원번호를 입력 받으면 다음과 같이 출력되는 PROCEDURE를 작성하라
-- Purchasing 부서명의 사원입니다
CREATE OR REPLACE PROCEDURE getDeptName(empno IN NUMBER, deptname OUT VARCHAR2)
IS  
BEGIN
    SELECT d.department_name INTO deptname
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
        AND e.employee_id = empno;
END;
/
 
VAR dname VARCHAR2;
EXECUTE getDeptName(100, :dname);
PRINT dname;
 
-- 예제5)사원번호를 입력받고, 소속부서의 최고, 최저연봉 차액을 파라미터로 출력하는 
-- PROCEDURE를 작성하라.
CREATE OR REPLACE PROCEDURE emp_deptTopMoney(empno IN employees.employee_id%TYPE,
                                        diff OUT NUMBER)
IS
    deptno employees.department_id%TYPE;
BEGIN
    -- 부서 번호
    SELECT department_id INTO deptno
    FROM employees
    WHERE employee_id = empno;
    
    -- 최고, 최소의 차액
    SELECT (MAX(salary) - MIN(salary)) INTO diff
    FROM employees
    WHERE department_id = deptno;
    
END;
/
 
VAR di NUMBER;
execute emp_deptTopMoney(107, :di);
PRINT di;
 
-- function(SCOTT)
-- 1) 두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오.(함수명 add_num)
CREATE OR REPLACE FUNCTION add_num(num1 INTEGER, num2 INTEGER) 
    RETURN INTEGER
IS    
BEGIN
    RETURN num1 + num2;
END;
/
 
SELECT add_num(23, 34)
FROM dual;
 
/*
2) 부서번호를 입력하면 해당 부서에서 근무하는 사원 수를 반환하는 함수를 정의하시오.
   (함수명 get_emp_count)
*/
CREATE OR REPLACE FUNCTION get_emp_count(dept_no IN NUMBER) RETURN NUMBER
IS  
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM emp
    WHERE deptno = dept_no
    GROUP BY deptno;
    
    RETURN v_count;
END;
/
 
VAR count NUMBER;
EXEC :count := get_emp_count(10);
PRINT count;
 
-- 3) emp테이블을 이용해서 입사일을 제공하면 근무연차를 구하는 함수를 정의하시오.(소수점 
--   자리 절삭, 함수명 get_info_hiredate)
CREATE OR REPLACE FUNCTION get_info_hiredate(hire_date emp.hiredate%TYPE)
    RETURN NUMBER
IS
    v_years NUMBER;
BEGIN
    v_years := trunc( MONTHS_BETWEEN(sysdate, hire_date) / 12 );
    RETURN v_years;
END;
/
SELECT ename, get_info_hiredate(hiredate) as 근무연차
FROM emp;
 
-- 4) emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을 구하는 함수를
--   정의하시오.(함수명 get_mgr_name)
CREATE OR REPLACE FUNCTION get_mgr_name(emp_no IN INTEGER) RETURN VARCHAR2
IS
    mgrname VARCHAR2(10);
BEGIN
    SELECT b.ename INTO mgrname
    FROM emp a, emp b
    WHERE a.mgr = b.empno
        AND a.empno = emp_no;
        
    RETURN mgrname;
END;
/
 
SELECT ename, get_mgr_name(empno) as "관리자명"
FROM emp;
 
/*
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(emp_no emp.empno%TYPE) 
    RETURN VARCHAR2
IS
    sgrade CHAR(1);
BEGIN
    SELECT
        CASE WHEN sal >= 4000 AND sal <= 5000 THEN 'A'
             WHEN sal >= 3000 AND sal < 4000 THEN 'B'
             WHEN sal >= 2000 AND sal < 3000 THEN 'C'
             WHEN sal >= 1000 AND sal < 2000 THEN 'D'
             ELSE 'F'
        END INTO sgrade
    FROM emp
    WHERE empno = emp_no;
    
    RETURN sgrade;
END;
/
 
SELECT ename, sal, get_sal_grade(empno) AS "급여등급"
FROM emp;