************** 부서/직원 리포트 ******************

부서 : 부서번호       부서명             위치

        10         ACCOUNTING        NEW YORK

사원 : 사번     이름         급여          급여 등급

       7871    Anderson    2000         ***   
      7890    Andrew       700         * 
      7870    Scott       2400         **** 
      7866    David        900         * 
      7891    Sylvia      3200         *****

2명의 급여가 인상되었습니다.


위와 같은 리포트를 뽑아내고 $1000보다 적은 급여를 받는 직원의 급여를 10%인상하는 프로그램을 작성하여 봅니다.

ACCEPT p_deptno PROMPT 'Please enter the department number : '

DECLARE

   v_deptno    NUMBER(2);

   v_dname     CHAR(14);

   v_loc       CHAR(13);

   v_empno     emp.empno%TYPE;

   v_ename     emp.ename%TYPE;

   v_sal       emp.sal%TYPE;

   v_grade     salgrade.grade%TYPE;

   v_dno       emp.deptno%TYPE := &p_deptno;

   CURSOR emp_cursor IS

          SELECLT empno, ename, sal

          FROM emp

          WHERE deptno = &p_deptno;

   e_no_emp    EXCEPTION;

BEGIN

   -- print out the report title

   DBMS_OUTPUT.PUT_LINE('*********부서/직원 리포트*********');

   SELECT  deptno, dname, loc

      INTO v_deptno, v_dname, v_loc

      FROM dept

      WHERE   deptno = v_dno;

   DBMS_OUTPUT.PUT_LINE('부서 :  부서번호   부서명        위치');

   DBMS_OUTPUT.PUT_LINE('     '∥v_deptno∥'    '∥v_dname∥
                        '   '∥v_loc∥'   ');

   DBMS_OUTPUT.PUT_LINE('사원 : 사번   이름   급여    급여등급');

   OPEN emp_cursor;

   FETCH emp_cursor INTO v_empno, v_ename, v_sal;

   /* according to the salary amount,

      print out the stars */

   WHILE emp_cursor%FOUND LOOP

      SELECT grade

         INTO v_grade

         FROM salgrade

         WHERE v_sal > losal AND v_sal <hisal;

      DBMS_OUTPUT.PUT('      '∥v_empno∥'   '∥v_ename∥
                      '      '∥v_sal∥'     ');

      FOR I in 1..v_grade LOOP

         DBMS_OUTPUT.PUT('*');

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('');

      FETCH emp_cursor INTO v_empno, v_ename, v_sal;

   END LOOP;

   CLOSE emp_cursor;

   UPDATE emp

      SET     sal = sal * 1.1

      WHERE   sal < 1000;

   IF SQL%ROWCOUNT = 0 THEN

      RAISE e_no_emp;

   ELSE

      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ∥'명의 급여가 ' ||
                           '인상되었습니다.');

   END IF;

   COMMIT;

EXCEPTION

   WHEN no_data_found THEN

      DBMS_OUTPUT.PUT_LINE('There is no matching data.');

   WHEN too_many_rows THEN

      DBMS_OUTPUT.PUT_LINE('More than one matching data. ');

   WHEN e_no_emp THEN

      DBMS_OUTPUT.PUT_LINE('No employee with a smaller ' ||
                           'than  $1000.');

   --in case of any other exception

   WHEN others THEN

      DBMS_OUTPUT.PUT_LINE('Some other error occurred.');

END;  

암시적, 명시적 커서

   오라클  서버는  SQL  문장을  실행하고  처리한  정보를 저장하기 위해  Private  SQL    영역이라 불리는 작업 영역을 사용합니다.  private SQL  영역에 이름 붙이고 그것의    저장된 정보를 액세스하기 위해 PL/SQL 커서를 사용할 수 있습니다.
 

커서 유형

설명

암시적

암시적 커서는 하나의 행만 리턴하는 질의를 포함하여 모든 DML과 PL/SQL SELECT 문장에 대해 PL/SQL에 의해 암시적으로 선언됩니다.

명시적

하나 이상을 반환하는 모든 질의에 대해 명시적 커서는 프로그래머에 의해 선언되고 이름 붙여지며 블록의 실행 섹션 안의 특정 명령문을 통해 조작됩니다.

  
암시적 커서

   오라클 서버는 명시적으로 선언된 커서와 관련 없는 각 SQL 문장을 프로세스 하기    위해 커서를 암시적으로 개방합니다. PL/SQL은 SQL 커서로서 가장 최근의 암시적    커서를 참조할 수 있도록 해 줍니다.  SQL 커서를 제어하기 위해  OPEN,  FETCH    그리고 CLOSE 문장을 사용할 수 없지만 가장 최근에 실행된 SQL 문장에 대한 정보를    얻기 위한 커서 속성을 사용할 수는 있습니다.

명시적 커서

   다중 행 SELECT 문장에 의해 리턴되는 각 행을 개별적으로 처리하기 위해 명시적    커서를 사용합니다. 다중 행 질의에 의해 리턴된 행의 집합은 result set 이라 불립니다.    그것의 크기는 검색조건에 일치하는 행의 수입니다.
   
   슬라이드에서 다이어그램은  result set 에서  current row 를 명시적 커서가 어떻게    가리키는가를 보여 줍니다.       이것은  프로그램이  한번에  한 행을 처리하는 것을    허용합니다.
   
   PL/SQL 프로그램은 질의에 의해 리턴된  행을 처리하고 그런 다음 커서를 닫습니다.
   
   커서는 result set에서 현재 위치를 표시합니다.

명시적 커서 함수

  • 질의에 의해 리턴된 첫번째 행부터, 행 하나 하나씩 처리 할 수 있습니다.
  • 현재 처리되는  행의 트랙을 유지합니다.
  • 프로그래머가 PL/SQL 블록에서  수동으로 제어할 수 있습니다.

  
주: 암시적 커서에 대한 인출(fetch)은 배열 인출(fetch)이며, 두 번째 행의 존재는
    여전히 TOO_MANY_ROWS  예외가 발생합니다. 그러므로, 다중 인출(fetch)을
    수행하고 작업 영역에서 구문 분석된 질의를 재실행 하기 위해 명시적 커서를 사용할
    수 있습니다.   


명시적 커서 (계속)

   커서에 대해 개념적인 이해는 하였으므로, 그것들을 사용하기 위한 단계를 살펴봅니다.    각 단계에 대한 구문은 다음에 있습니다.

4가지 명령을 사용하는 명시적 커서 제어

   1.   수행되기 위한 질의의 구조를 정의하고  이름을 지정함으로써 커서를 선언합니다.
   
   2.   커서를 엽니다. OPEN 문장은 질의를 실행하고, 참조되는 임의의 변수를 바인드          합니다. 질의에 의해 식별된 행은 active set 이라 불리고 인출(fetch)가능합니다.

   3.   커서에서 데이터를 인출(fetch)합니다. FETCH 문장은 커서에서 변수로 현재 행을          로드 합니다. 각 인출(fetch)은 활성 셋( set )에서 다음 행으로 그 포인터를   이동          하도록 합니다. 그러므로 각 인출(fetch)은 질의에 의해 리턴되는 각기 다른 행을          액세스 합니다. 슬라이드에서 보여 준 흐름 다이어그램에서, 각 인출(fetch)시에          행이 존재하는 지를 검사합니다. 행이 발견되면 그것은 현재 행을 변수로 로드합니          다. 그렇지 않으면 커서를 닫습니다.
   
   4.   커서를 닫습니다. CLOSE 문장은 행의 활성 셋(set)을 해제합니다. 이제 새로운          활성 셋(set)을 생성하기 위해 커서를 다시 열 수 있습니다.

명시적 커서 선언

   명시적으로 커서를 선언하기 위해 CURSOR 문장을 사용합니다.
   질의 내에서 변수를 참조할 수 있지만, CURSOR 문장 전에 선언되어야 합니다

구문에서

        cursor_name                     PL/SQL 식별자입니다.
        select_statement                INTO 절 없는 SELECT 문장입니다.

주: INTO 절은 FETCH 문장에 있기 때문에 커서 선언에서 INTO 절을 포함하지 마십시오.

커서 선언 (계속)

   하나씩 종업원을 읽어 들입니다.
 

DECLARE
  v_empno               emp.empno%TYPE;
  v_ename               emp.ename%TYPE;
  CURSOR c1 IS
    SELECT empno, ename
    FROM   emp;
BEGIN
...

     
   주: 질의에서 변수를 참조할 수 있지만 CURSOR 문장 전에 선언합니다.

OPEN 문장

   질의를 수행하고 질의 검색조건을 충족하는 모든 행으로 구성된  결과 셋 ( set ) 을    생성하기 위해 커서를 엽니다.  커서는 이제 결과 셋(set)에서 첫번째 행을 가리킵니다.

   구문에서
   
        cursor_name             이전에 선언된 커서 이름입니다.

OPEN 은 다음작업을 수행합니다.

   1.   문맥 영역(context area)에 대해 동적으로 메모리를 할당하여 중요한 프로세싱          정보를 포함합니다.

   2.   SELECT 문장을 구문분석 합니다.

   3.   입력변수를 바인드 합니다. - 즉, 그들의 메모리 주소를 획득하여 입력 변수에          대한 값을 설정합니다.

   4.   결과 셋(set)을 식별합니다. - 즉,  검색 조건을 충족시키는 행의 집합입니다.          OPEN 문장이 실행될 때 결과 셋(set)에 있는 행을 변수로 읽어 들이지 않습니다.          그대신 FETCH 문장이 행을 읽습니다.

   5.   포인터는 활성 셋(set)에서 첫번째 행 앞에  위치합니다.

   주: 커서가 개방될 때 질의가 아무 행도 리턴하지 않으면, PL/SQL 은 예외를 일으키지         않습니다. 그러나 인출(fetch)후에 커서의 상태를 테스트 할 수 있습니다.

FOR UPDATE 절을 사용하는 커서를 선언할 경우에는, OPEN 문장은 그 행들을 잠급니다 (lock).

 

 

FETCH 문장

   FETCH 문장은 결과 셋(set)에서 한번에 하나의 행을 읽어 들입니다. 각 인출(fetch)    후에 커서는 결과 셋(set)에서 다음 행으로 움직입니다.
   
   구문에서,

        cursor_name         이전에 선언된 커서의 이름입니다.

        variable                 결과를 저장하기 위한 출력 변수입니다.

        record_name         읽어 들인 데이터가 저장되는 레코드의 이름입니다. 레코드                                     변수는 %ROWTYPE 속성을 이용하여 선언 될 수 있습니다.
지침

주: FETCH 문장은 다음 작업을 이행합니다.

   1. 활성 셋(set)에서 다음 행으로 포인터를 움직입니다.
   2. 현재 행에 대한 정보를 출력 PL/SQL 변수로 읽어 들입니다.
   3. 포인터가 활성 셋(set)의 끝에 위치하게 되면 커서는 FOR LOOP를 빠져 나갑니다.

FETCH 문장 (계속)

   현재 행의 값을 출력 변수로 읽어 들이기 위해 FETCH 문장을 사용할 수 있습니다.
   인출(fetch) 후에 다른 문장들을 써서 변수를 조작할 수 있습니다.   커서와 관련된    질에 의해 리턴되는 각 열 값에 대응하는 해당변수가 INTO 절에 있어야만 합니다. 또한    그들의 데이터 형은 같아야 합니다.
   
   첫번째 10명의 종업원을 하나씩 읽어 들입니다.
 

DECLARE
  v_empno               emp.empno%TYPE;
  v_ename               emp.ename%TYPE;
  CURSOR c1 IS
    SELECT empno, ename
    FROM   emp;
BEGIN
  OPEN c1;
  FOR i IN 1..10 LOOP
    FETCH c1 INTO v_empno, v_ename;
    ...
  END LOOP;
END ;

CLOSE 문장

   CLOSE 문장은 커서를 사용 할 수 없게 하고 결과 셋(set)은 정의가 해제됩니다.
   SELECT 문장이 다 처리된 완성 후에 커서를 닫습니다.  필요하다면 커서를 다시 열 수    있습니다. 그러므로 활성 셋(set)을 여러 번 설정 할 수 있습니다.
   
        cursor_name             이전에 선언된 커서의 이름입니다.

   일단 닫아진 커서에서 데이터를 인출 ( fetch ) 하려는 시도는 하지 마십시오.   그렇지    안으면 INVALID_CURSOR 예외가 일어나게 됩니다.

주: CLOSE 문장은 context area를 해제 합니다. 커서를 닫지 않고 PL/SQL 블록을 종료하는 것이 가능하다 할 지라도 리소스를 다시 사용 가능하게 하기 위해 명시적으로 선언된 임의의 커서를 닫는 습관을 들여야 합니다. 데이터베이스 매개변수 필드에서 OPEN_CURSORS 매개변수에 의해 결정되는 사용자마다 해당하는 커서의 수에는 최대 한계가 있습니다. 디폴트로 OPEN_CURSORS = 50 입니다.
 

...
  FOR i IN 1..10 LOOP
    FETCH c1 INTO v_empno, v_ename;
    ...
  END LOOP;
END;

명시적 커서 속성

명시적 커서로, 커서에 대해 상태 정보를 얻기 위한 4가지 속성이 있습니다.
커서 또는 커서 변수에 붙을 때, 이 속성은 데이터 조작 문장의 실행에 대해 유용한 정보를 리턴합니다.

주: SQL 문장에서 직접적으로 커서 속성을 참조할 수 없습니다.
 

IF NOT c1%ISOPEN THEN
     OPEN c1;
END IF;
LOOP
    FETCH c1...

  

 DECLARE
     v_empno                      emp.empno%TYPE;
     v_ename                      emp.ename%TYPE;
     CURSOR c1 IS
          SELECT empno, ename
          FROM    emp;
 BEGIN
     OPEN c1;
     LOOP
          FETCH c1 INTO v_empno, v_ename;
          EXIT WHEN c1%ROWCOUNT > 10 OR c1%NOTFOUND;
          ...             
     END LOOP;
     CLOSE c1;
 END;

매개변수 데이터형은 스칼라 변수의 데이터형과 동일하지만, 크기는 제공하지 않습니다.매개변수 명은 커서 질의에서 참조하기 위한 것입니다.  다음 예에서 , 두 변수와 커서가 선언됩니다. 커서는 두 매개변수로 정의됩니다.
 

DECLARE
   job_emp      emp.job%TYPE := 'CLERK';
   v_ename      emp.ename%TYPE;
   CURSOR c1 (v_deptno NUMBER, v_job VARCHAR2) is
      SELECT ...     

다음 문장은 둘 다 커서를 엽니다.
 

OPEN c1(10, job_emp);
OPEN c1(20, 'ANALYST');    

FOR UPDATE 절

주: 오라클 서버는 SELECT FOR UPDATE에서  필요로 하는 행의 잠금을 얻을 수 없다면, 막연하게 기다립니다. SELECT FOR UPDATE 문장에서  NOWAIT 절을 사용할 수 있고 루프에서  잠금을 얻는데 실패하여 생기는 오류 코드를 테스트할 수 있습니다.  그러므로 PL/SQL 블록을 종료하기 전에 커서  OPEN을 n번 다시 시도할 수 있습니다. 대형 테이블이라 면, 테이블의 모든 행을 잠그기 위해 LOCK TABLE 문장을 사용함으로써  더 나은 성능을 얻을 수도 있습니다. 그러나 LOCK TABLE을 사용할 때, WHERE CURRENT OF 절을 사용할 수 없고 WHERE column = identifier를 사용해야 합니다.

FOR UPDATE OF 절이 열을 참조 하는 것이 필수적이지는 않지만 이것은 더 쉽게 읽고 유지할 수 있게 하기 위해  추천되어집니다.

WHERE CURRENT OF 절

명시적 커서에서 현재 행을 참조할 때 WHERE CURRENT OF 절을 사용합니다. 이를 통해서 명시적으로 ROWID를 참조하지 않고 현재 처리 중인 행을 갱신하고, 삭제할 수 있게 해 줍니다. 행을 OPEN 시에 잠기게 하기 위해서 커서 질의에 FOR UPDATE 절을 포함해야 합니다.

구문에서,

cursor

선언된 커서의 이름입니다. 커서는 FOR UPDATE 절로 선언되어졌어야 합니다.

X 정답:A


X 정답:D


X 정답:C


X 정답:A