PROCEDURE

 
-- update_sal 프로시저는 사원번호를 입력 받아서 급여를 인상
CREATE OR REPLACE PROCEDURE update_sal( v_empno IN NUMBER )
IS
BEGIN
    UPDATE EMPLOYEES
    SET SALARY = SALARY *1.3
    WHERE EMPLOYEE_ID = v_empno;
END update_sal;
/
 
SELECT *
FROM EMPLOYEES;
 
EXECUTE update_sal(100);
ROLLBACK;
                                                -- EMPLOYEE_ID의 자료형을 가져옴
CREATE OR REPLACE PROCEDURE emp_info(p_empno IN EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
    v_empno EMPLOYEES.EMPLOYEE_ID%TYPE;
    v_ename EMPLOYEES.FIRST_NAME%TYPE;
    v_sal EMPLOYEES.SALARY%TYPE;
BEGIN
    SELECT EMPLOYEE_ID, FIRST_NAME, SALARY INTO v_empno,v_ename,v_sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = p_empno;
    
   
    DBMS_OUTPUT.PUT_LINE('사원번호: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('이름: ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('급여: ' || v_sal);
END;
/
 
SET SERVEROUTPUT ON;
EXECUTE emp_info(100);
 
CREATE OR REPLACE PROCEDURE EMP_INFO(e_empno IN employees.employee_id%TYPE)
IS
    v_emp employees%ROWTYPE;    -- employee의 모든 자료형이 다 들어가잇음
BEGIN
    SELECT EMPLOYEE_ID, FIRST_NAME, SALARY INTO v_emp.EMPLOYEE_ID, v_emp.FIRST_NAME, v_emp.SALARY
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = e_empno;
    
    DBMS_OUTPUT.PUT_LINE('사원번호: ' || v_emp.EMPLOYEE_ID);
    DBMS_OUTPUT.PUT_LINE('이름: ' || v_emp.FIRST_NAME);
    DBMS_OUTPUT.PUT_LINE('급여: ' || v_emp.SALARY);
END;
/
SET SERVEROUTPUT ON;
EXECUTE EMP_INFO(100);
 
-- 같은 자료형의 묶음 : ARRAY
CREATE OR REPLACE PROCEDURE TABLE_TEST( v_deptno IN DEPARTMENTS.DEPARTMENT_ID%TYPE )
IS
     TYPE empno_table IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE
        INDEX BY BINARY_INTEGER;   -- 배열 선언
        
     TYPE ename_table IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE
        INDEX BY BINARY_INTEGER;   -- 배열 선언
        
     TYPE sal_table IS TABLE OF EMPLOYEES.SALARY%TYPE
        INDEX BY BINARY_INTEGER;   -- 배열 선언
    
    empno_tap empno_table;
    ename_tap ename_table; 
    sal_tap sal_table;
    
    i BINARY_INTEGER := 0; -- int i = 0;
    
BEGIN
    FOR emp_list IN (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
                     FROM EMPLOYEES
                     WHERE  DEPARTMENT_ID = v_deptno)
    LOOP
        i := i+1;
        empno_tap(i) := emp_list.EMPLOYEE_ID;
        ename_tap(i) := emp_list.FIRST_NAME;
        sal_tap(i) := emp_list.SALARY;
    END LOOP;
    
    -- 출력
    FOR cnt IN 1 .. i
    LOOP
        DBMS_OUTPUT.PUT_LINE('사원번호: ' || empno_tap(cnt));
        DBMS_OUTPUT.PUT_LINE('이름: ' || ename_tap(cnt));
      DBMS_OUTPUT.PUT_LINE('급여: ' || sal_tap(cnt));
    END LOOP;
END;
/
SET SERVEROUTPUT ON;
EXECUTE TABLE_TEST(60);
 
/*
 이름, 급여, 부서번호를 입력받아 EMPLOYEES 테이블에 자료를
 등록하는 SCRIPT를 작성하여라.
 단 10번 부서일 경우, 입력한 급여의 20%를 추가하고
 초기값이 9000부터 9999까지 1씩 증가하는 
 SEQUENCE(EMP_EMPNO_SEQ)작성하여 사용하고 아래의 표를
 참고하여라
 
 이   름: 홍길동
 급   여: 2000
 부서번호: 10
*/
 
-- SEQUENCE 만들어줌
 
CREATE SEQUENCE EMP_EMPNO_SEQ
INCREMENT BY 1  -- 1씩 증가
START WITH 9000 
MAXVALUE 9999;
 
CREATE OR REPLACE PROCEDURE SCRIPT_TEST(p_ename IN EMPLOYEES.LAST_NAME%TYPE, sal IN EMPLOYEES.SALARY%TYPE,
                                                p_deptno IN EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
    p_sal EMPLOYEES.SALARY%TYPE := sal;
BEGIN
  
    IF p_deptno = 10 THEN
        p_sal := p_sal*1.2; --파라미터를 바로 사용하면 에러떠서 선언부에서 값을 받아서 바꿔줌
    END IF;
    
    INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, EMAIL,
             HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID)
    VALUES (EMP_EMPNO_SEQ.NEXTVAL, p_ename, p_ename||'@naver.com',
            SYSDATE,'IT_PROG',p_sal,p_deptno);
            
END;
/
SET VERIFY OFF; 
SET SERVEROUTPUT ON; 
 
ACCEPT p_ename PROMPT '이름 입력:';
ACCEPT p_sal PROMPT '급여 입력:';
ACCEPT p_deptno PROMPT '부서번호 입력:';
 
EXECUTE SCRIPT_TEST('&p_ename','&p_sal','&p_deptno');
SET VERIFY ON; 
SET SERVEROUTPUT OFF; 
 
select *
from employees;