SQL PL(Cursor)

-- Cursor

/*

    : 저장 주소 공간 -> Oracle Pointer

    암시적 커서 : 자동생성

        SQL%ROWCOUNT : ROW의 수

        SQL%FOUND : ROW가 한개 이상일 경우

        SQL%NOTFOUND   : ROW가 0개일 경우 

    

    명시적 커서 : 수동생성

*/

CREATE OR REPLACE PROCEDURE Implicit_Cursor  (p_empno IN employees.employee_id%TYPE)

IS

    v_sal EMPLOYEES.SALARY%TYPE;

    v_update_row NUMBER;

    

BEGIN

    SELECT SALARY INTO v_sal 

    FROM EMPLOYEES

    WHERE employee_id = p_empno;

    -- 검색된 데이터가 있는 경우

    IF SQL%FOUND THEN

    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재합니다 : ' || v_sal); 

    END IF;

    

    UPDATE EMPLOYEES

    SET salary=salary*1.1

    WHERE EMPLOYEE_ID = p_empno;

    

    v_update_row := SQL%ROWCOUNT;

    DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원수 : ' || v_update_row);

    

    EXCEPTION

    WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 없습니다');

    

END;

/

    

SET SERVEROUTPUT ON;

EXECUTE Implicit_Cursor(101);

EXECUTE Implicit_Cursor(300);

ACCEPT P_EMPNO PROMPT '사원번호 : '

EXECUTE Implicit_Cursor(&P_EMPNO);

ex)-- 명시적 커서

CREATE OR REPLACE PROCEDURE ExpCursor_Tes 

(v_deptno IN DEPARTMENTS.DEPARTMENT_ID%TYPE)

IS

    CURSOR dept_avg

    

IS

    SELECT B.DEPARTMENT_NAME, COUNT(employee_id) CNT,

    ROUND(AVG(A.SALARY), 3) SAL

    FROM EMPLOYEES A, DEPARTMENTS B

    WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID

    AND B.DEPARTMENT_ID = v_deptno

    GROUP BY B.DEPARTMENT_NAME;  

    

    -- CURSOR를 PATCH 하기 위한 변수 선언

    v_dname DEPARTMENTS.DEPARTMENT_NAME%TYPE;

    emp_cnt NUMBER;

    sal_avg NUMBER;

    

BEGIN

    -- CURSOR OPEN

    OPEN dept_avg;

    

    -- CURSOR를 붙인다

    FETCH dept_avg INTO v_dname, emp_cnt, sal_avg;

    

    DBMS_OUTPUT.PUT_LINE('부서명 : ' || v_dname);

    DBMS_OUTPUT.PUT_LINE('사원수 : ' || emp_cnt);

    DBMS_OUTPUT.PUT_LINE('월급평균 : ' || sal_avg);

      

    -- CURSOR CLOSE

    CLOSE dept_avg;

    EXCEPTION

    WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE(SQLERRM || '에러발생');

END;

/

EXECUTE ExpCursor_Test(60);

ex)-- 암시적커서 + 명시적 커서

CREATE OR REPLACE PROCEDURE AttrCursor_Test

IS

    v_empno employees.employee_id%TYPE;

    v_ename employees.last_name%TYPE;

    v_sal employees.salary%TYPE;

    CURSOR emp_list

    IS

    SELECT DEPARTMENT_ID, LAST_NAME, SALARY

    FROM EMPLOYEES;

BEGIN

    OPEN emp_list;

    

    LOOP

        FETCH emp_list INTO v_empno, v_ename, v_sal;        

        DBMS_OUTPUT.PUT_LINE(v_empno || v_ename || v_sal);

        

        EXIT WHEN emp_list%NOTFOUND;

    

END LOOP;   

    

    DBMS_OUTPUT.PUT_LINE('전체 데이터 수 : ' || emp_list%ROWCOUNT);

            

    CLOSE emp_list;

END;

/

EXECUTE AttrCursor_Test();

CREATE OR REPLACE PROCEDURE emp_name

(id IN NUMBER, emp_name OUT VARCHAR2)

IS

BEGIN

    SELECT FIRST_NAME INTO emp_name        

    FROM EMPLOYEES

    WHERE EMPLOYEE_ID = id;

END;

/

DECLARE

    empName VARCHAR2(20)

    CURSOR id_cur

   

 IS

   

 SELECT EMPLOYEE_ID

    FROM EMPLOYEES; -- 커서 셋팅

    

BEGIN

    FOR emp_rec IN id_cur   -- 커서를 통해서 loop

   

    LOOP

      

        emp_name(emp_rec.EMPLOYEE_ID, empName); --(들어감, 나옴)

        DBMS_OUTPUT.PUT_LINE('사원이름:' || empName || ' 사원번호:' || emp_rec.EMPLOYEE_ID);

    

END LOOP;

END;

/