210602 oracle - CURSOR

CURSOR

[형식] - 단계
  1. CURSOR  cursor_name is query; : 하나 이상의 ROW를 담을 객체
  2. OPEN cursor_name; : 실제 실행 코드에서 커서를 시작한다
  3. LOOP, FETCH를 이용해서 값을 선언된 변수에 대입
  4. CLOSE cursor_name; : 객체를 닫는다. 

명시적 커서 확인(SQL%~)
IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재한다  ' || V_SAL);
END IF;

CURSOR를 만들어서 SELECT 결과를 저장한 다음, 오픈하고 코드, 클로드한다.
1단계에서 그냥 query만 작성하면 SQL%FOUND, NOTFOUND 등 CURSOR의 속성으로 호출가능

함수는 RETURN에 있는 값을 리턴하지만 
프로시져는 OUT 매개변수에 있는 매개변수를 알아서 리턴

1. SQL%ROWCOUNT : 해당 SQL문에 영향을 받는 행의 수
2. SQL%FOUND : 해당 SQL문에 영향을 받는 행의 수가 1개 이상일 경우 TRUE를 리턴
3. SQL%NOTFOUND : 해당 SQL문에 영향을 받는 행의 수가 없으면 TRUE를 리턴
4. SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려있는지 유무

 

매개변수로 CURSOR를 받는 경우
[형식]
CREATE OR REPLACE PROCEDURE 프로시져명 ( 매개변수명 IN OUT SYS_REFCURSOR )
OPEN 매개변수명 FOR SELECT EMPNO, ENAME FROM EMP WHERE JOB = P_JOB;

============================================================================

Q1) 부서번호 20번인 사원의 부서번호 이름, 봉급을 구하는 프로시저를 만들어보자
SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20;

CREATE OR REPLACE PROCEDURE EMP_EX01 AS 

    -- SELECT에서 리턴 받을 변수 선언
    V_EMPNO EMP.EMPNO%TYPE;
    V_ENAME EMP.ENAME%TYPE;
    V_SAL NUMBER(7,2);
    
    -- 1. CURSOR  cursor_name is query; : 하나 이상의 ROW를 담을 객체
    CURSOR EMP_CURSOR IS 
        SELECT EMPNO, ENAME, SAL 
        FROM EMP 
        WHERE DEPTNO = 20;

BEGIN
    -- 2. OPEN cursor_name; : 실제 실행 코드에서 커서를 시작한다
    OPEN EMP_CURSOR;

    -- 3. LOOP, FETCH를 이용해서 값을 선언된 변수에 대입
        LOOP
            FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
            EXIT WHEN EMP_CURSOR%NOTFOUND OR EMP_CURSOR%ROWCOUNT > 5;

            DBMS_OUTPUT.PUT_LINE(V_EMPNO);
            DBMS_OUTPUT.PUT_LINE(V_ENAME);
            DBMS_OUTPUT.PUT_LINE(V_SAL);

        END LOOP;

    -- 4. CLOSE cursor_name; : 객체를 닫는다. 
    CLOSE EMP_CURSOR;
END EMP_EX01;


Q2) 사원테이블에서 사원의 이름과 봉급을 출력해보자
SELECT ENAME, SAL FROM EMP;

CREATE OR REPLACE PROCEDURE EX_VIEW AS
    V_ENAME EMP.ENAME%TYPE;
    V_SAL EMP.SAL%TYPE;

    CURSOR C_VIEW IS SELECT ENAME, SAL FROM EMP;

BEGIN
    OPEN C_VIEW;
        LOOP
            FETCH C_VIEW INTO V_ENAME, V_SAL;
            EXIT WHEN C_VIEW%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(V_ENAME || V_SAL);

        END LOOP;
    CLOSE C_VIEW;
END EX_VIEW;


Q3) 사원테이블에서 커미션이 측정된 사원의 이름, 봉급, 커미션을 출력해보자 EX_VIEW01;
CREATE OR REPLACE PROCEDURE EX_VIEW01 AS 
    R_EMP EMP%ROWTYPE;

    CURSOR RES IS
        SELECT *    -- 1
        FROM EMP
        WHERE COMM IS NOT NULL;

BEGIN
    OPEN RES;
        LOOP
            FETCH RES INTO R_EMP;    -- 2  : 1이랑 2개 개수 같아야 함
            EXIT WHEN RES%NOTFOUND;
            
            dbms_output.put(R_EMP.ENAME || '        ');
            dbms_output.put(R_EMP.SAL || '        ');
            dbms_output.put_line(R_EMP.COMM);
        END LOOP;
    CLOSE RES;
END EX_VIEW01;


Q4) 부서번호를 커서로 전달해서 해당 사원의 번호, 이름, 봉급을 출력해보자
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = 10;

CREATE OR REPLACE PROCEDURE EMP_EX04 AS 
    V_EMPNO EMP.EMPNO%TYPE;
    V_ENAME EMP.ENAME%TYPE;
    V_SAL NUMBER(7,2);

    CURSOR EMP_CURSOR (V_DEPTNO NUMBER) IS
        SELECT EMPNO, ENAME, SAL
        FROM EMP
        WHERE DEPTNO = V_DEPTNO;

BEGIN
    OPEN EMP_CURSOR(10);
        LOOP
            FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
            EXIT WHEN EMP_CURSOR%NOTFOUND;
            
            DBMS_OUTPUT.PUT(V_EMPNO || '    ');
            DBMS_OUTPUT.PUT(V_ENAME || '    ');
            DBMS_OUTPUT.PUT_LINE(V_SAL || '    ');
        END LOOP;
    CLOSE EMP_CURSOR;
        
    OPEN EMP_CURSOR(10);
        LOOP
            FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
            EXIT WHEN EMP_CURSOR%NOTFOUND;
            
            DBMS_OUTPUT.PUT(V_EMPNO || '    ');
            DBMS_OUTPUT.PUT(V_ENAME || '    ');
            DBMS_OUTPUT.PUT_LINE(V_SAL || '    ');
        END LOOP;
    CLOSE EMP_CURSOR;
END EMP_EX04;


Q5) 사원번호를 입력받아 데이터를 확인하고 급여를 1.1배 하는 프로시져를 만들자.
CREATE OR REPLACE PROCEDURE EMP_EX05 (P_EMPNO IN EMP.EMPNO%TYPE) AS 
    V_SAL EMP.SAL%TYPE;
    V_UPDATE_ROW NUMBER;

BEGIN
    SELECT SAL INTO V_SAL
    FROM EMP
    WHERE EMPNO = P_EMPNO;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재한다  ' || V_SAL);
        
        UPDATE EMP
        SET SAL = SAL * 1.1
        WHERE EMPNO = P_EMPNO;
        
        V_UPDATE_ROW := SQL%ROWCOUNT;
        DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원의 수  ' || V_UPDATE_ROW);
        
    END IF;
END EMP_EX05;


Q6) 사원번호를 입력해서 무슨 부서인지를 리턴 받아보자. 프로시져
CREATE OR REPLACE PROCEDURE EMP_EX06(P_EMPNO IN EMP.EMPNO%TYPE,
                                                                    V_RES OUT VARCHAR2) 
AS
    V_DEPTNO EMP.DEPTNO%TYPE;

BEGIN
    SELECT DEPTNO INTO V_DEPTNO
    FROM EMP
    WHERE EMPNO = P_EMPNO;
    
    IF V_DEPTNO = 10 THEN
        V_RES := '10번 부서 사원이야';
    ELSIF V_DEPTNO = 20 THEN
        V_RES := '20번 부서 사원이야';
    ELSIF V_DEPTNO = 30 THEN
        V_RES := '30번 부서 사원이야';
    END IF;
        
END EMP_EX06;

VAR RES VARCHAR2(50);
EXEC EMP_EX06(7900, :RES);
PRINT RES;


Q7) 이름을 입력하면 직업을 리턴하는 코드를 작성하자
CREATE OR REPLACE PROCEDURE EMP_EX07 
(
  MY_RES IN OUT VARCHAR2 
) AS 
    V_RES EMP.JOB%TYPE;
BEGIN
    SELECT JOB INTO V_RES
    FROM EMP
    WHERE ENAME = MY_RES;
    
    MY_RES := V_RES;
    
END EMP_EX07;

VAR RES02 VARCHAR2(50);
EXECUTE :RES02 := 'SMITH';
EXECUTE EMP_EX07(:RES02);
PRINT RES02;

Q8) 직업을 입력하면 사번과 이름을 포함하는 객체를 리턴받는 프로시져를 생성
create or replace PROCEDURE EMP_EX08 (p_job varchar2,
  P_EMP_REFCUR IN OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_emp_refcur FOR
                    SELECT empno, ename FROM emp WHERE job = p_job;
END EMP_EX08;

VAR EX_08 REFCURSOR;
EXECUTE EMP_EX08('SALESMAN', :EX_08);
PRINT EX_08;


Q9) Q8을 이용해서 사번과 이름을 출력하는 프로시져를 생성
create or replace PROCEDURE EMP_EX09 AS 
V_EMPNO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE := 'SALESMAN';
V_EMP_REFCUR SYS_REFCURSOR;

BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPLYEES WITH JOB ' || V_JOB );
    DBMS_OUTPUT.PUT_LINE('EMPNO     ENAME');
    DBMS_OUTPUT.PUT_LINE('============================');

    EMP_EX08(V_JOB, V_EMP_REFCUR);
    LOOP
        FETCH V_EMP_REFCUR INTO V_EMPNO, V_ENAME;
        EXIT WHEN V_EMP_REFCUR%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(V_EMPNO || '        ' || V_ENAME);
    
    END LOOP;     
    CLOSE V_EMP_REFCUR;
END EMP_EX09;

EXECUTE EMP_EX09;