PL/SQL 커서(cursor) -암시적 커서, 명시적 커서(선언, 비선언, 파라미터)-

암시적 커서(implicit cursor)

create or replace procedure implicit_cursor

--implicit_cursor (암시적 커서)

(

    p_empno in  emp.empno%type  

)

is

    v_sal   emp.sal%type;

begin

    DBMS_OUTPUT.ENABLE;

    --insert, update, delete문에서 사용 (select는 의미가 없음!)

   

    update emp2

    set sal=3000

    where empno = to_number(p_empno);

   

    if sql%found then

        dbms_output.put_line('변경된 데이터가 존재합니다.');

        dbms_output.put_line(sql%rowcount);

    else

        dbms_output.put_line('변경된 데이터가 존재하지 않습니다.');

    end if;

end;

 

 

 

 

 

명시적 커서(Explicit cursor)

CREATE OR REPLACE PROCEDURE ExpCursor_Test1

--Explicit_cursor (명시적 커서)

--한번 패치

    (v_deptno dept.deptno%TYPE)

IS

    CURSOR dept_avg IS

        SELECT b.dname, COUNT(a.empno) cnt, ROUND(AVG(a.sal),3) salary

        FROM emp a, dept b

        WHERE a.deptno = b.deptno

        AND b.deptno = v_deptno

        GROUP BY b.dname ;

   

    -- 커서를 패치하기 위한 변수 선언

    v_dname dept.dname%TYPE;

    emp_cnt NUMBER;

    sal_avg NUMBER;

BEGIN

    DBMS_OUTPUT.ENABLE;

   

    -- 커서의 오픈

    OPEN dept_avg;

   

    -- 커서의 패치

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

   

    -- 커서의 CLOSE

    CLOSE dept_avg;

   

    EXCEPTION

    WHEN OTHERS THEN

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

END;

 

 

 

 

 

 

명시적 커서(Explicit cursor) -여러번 패치-

CREATE OR REPLACE PROCEDURE ExpCursor_Test2

--Explicit_cursor (명시적 커서)2

--여러번 패치

IS

    CURSOR dept_avg IS

        SELECT b.dname, COUNT(a.empno) cnt, ROUND(AVG(a.sal),3) salary

        FROM emp a, dept b

        WHERE a.deptno = b.deptno

        GROUP BY b.dname ;

   

    -- 커서를 패치하기 위한 변수 선언

    v_dname dept.dname%TYPE;

    emp_cnt NUMBER;

    sal_avg NUMBER;

BEGIN

    DBMS_OUTPUT.ENABLE;

     

    for emp_list in dept_avg loop

    -- for문에서 알아서 커서의 오픈과 패티,클로즈를 다 해줌

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

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

        DBMS_OUTPUT.PUT_LINE('평균급여 : ' || emp_list.salary);

    end loop;

   

    EXCEPTION

    WHEN OTHERS THEN

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

END;

 

 

 

 

 

 

명시적 커서(Explicit cursor) -이름 선언 안하기-

CREATE OR REPLACE PROCEDURE ExpCursor_Test3

--Explicit_cursor (명시적 커서)3

--여러번 패치, 커서 이름선언 안하고 사용하기 (for문)

IS   

    v_dname dept.dname%TYPE;

    emp_cnt NUMBER;

    sal_avg NUMBER;

BEGIN

    DBMS_OUTPUT.ENABLE;

     

    for emp_list in (SELECT b.dname, COUNT(a.empno) cnt, ROUND(AVG(a.sal),3) salary

                    FROM emp a, dept b

                    WHERE a.deptno = b.deptno

                    GROUP BY b.dname

                    ) loop

    -- for문에서 알아서 커서의 오픈과 패티,클로즈를 다 해줌

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

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

        DBMS_OUTPUT.PUT_LINE('평균급여 : ' || emp_list.salary);

    end loop;

   

    EXCEPTION

    WHEN OTHERS THEN

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

END;

 

 

 

 

 

 

 

명시적 커서(Explicit cursor) -파라미터가 있는 커서-

CREATE OR REPLACE PROCEDURE ParamCursor_Test

--Explicit_cursor (명시적 커서)

--파라미터가 있는 커서

    (param_deptno emp.deptno%TYPE)

IS

    v_ename emp.ename%TYPE;

   

    -- Parameter가 있는 커서의 선언 (커서의 이름이 있어야 파라미터 사용가능)

    CURSOR emp_list(v_deptno emp.deptno%TYPE) IS

    SELECT ename FROM emp WHERE deptno = v_deptno;

BEGIN

    DBMS_OUTPUT.ENABLE;

   

    DBMS_OUTPUT.PUT_LINE(' ****** 입력한 부서에 해당하는 사람들 ****** ');

   

    -- Parameter변수의 값을 전달(OPEN될 때 값을 전달한다)

    FOR emplst IN emp_list(param_deptno) LOOP

        DBMS_OUTPUT.PUT_LINE('이름 : ' || emplst.ename);

    END LOOP;

   

    EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

END;