SQL PL(3)

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

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(101);

CREATE OR REPLACE PROCEDURE EMP_INFO(e_empno IN employees.employee_id%TYPE)

IS

    v_emp employees%ROWTYPE;

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(102);

-- 같은 자료형의 묶음 : 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_tab empno_table;

    ename_tab ename_table;

    sal_tab sal_table;

    i BINARY_INTEGER := 0;

    

BEGIN

    FOR emp_list IN(SELECT EMPLOYEE_ID, FIRST_NAME, SALARY

                    FROM EMPLOYEES

                    WHERE DEPARTMENT_ID = v_deptno)

    LOOP 

        i := i + 1;

        

        empno_tab(i) := emp_list.EMPLOYEE_ID;

        ename_tab(i) := emp_list.FIRST_NAME;

        sal_tab(i) := emp_list.SALARY;

   END LOOP;

   

   -- 출력

   FOR cnt IN 1 .. i

   LOOP

        DBMS_OUTPUT.PUT_LINE('사원번호 : ' || empno_tab(cnt));

        DBMS_OUTPUT.PUT_LINE('사원명 : ' || ename_tab(cnt));

        DBMS_OUTPUT.PUT_LINE('급여 : ' || sal_tab(cnt));

   END LOOP;

    

END;

/

EXECUTE TABLE_TEST(60);

/*

이름,급여,부서번호를 입력받아 EMPLOYEES 테이블에 자료를 

등록하는 SCRIPT를 작성하여라. 

단 10번 부서일 경우, 입력한 급여의 20%를 추가하고 

초기값이 9000부터 9999까지 1씩 증가하는 

SEQUENCE(EMP_EMPNO_SEQ)작성하여 사용하고 아래의 표를 

참고하여라.

이    름: 홍길동

급    여: 2000

부서번호: 10

*/

CREATE OR REPLACE PROCEDURE TABLE_TEST

IS

    ENAME EMPLOYEES.FIRST_NAME%TYPE;

BEGIN

    SELECT FIRST_NAME INTO ENAME         

    FROM EMPLOYEES

    WHERE EMPLOYEE_ID = 100;

    

    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);

END;

/

EXECUTE TABLE_TEST();

위의 문제는 문제풀이에서 확인하세요