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

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

        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;  

 

PL/SQL을 사용하여 데이터 읽기

데이터베이스에서 데이터를 읽어 들이기 위해 SELECT 문장을 사용합니다.  
 
구문에서,

select_list

최소한 한 개 이상의 열의 목록이며, SQL 표현식, 행 함수, 그룹 함수를 포함할 수 있습니다.

variable_name

읽어 들인 값을 저장하기 위한 스칼라 변수입니다.

record_name

읽어 들인 값을 저장하기 위한 PL/SQL RECORD입니다.

table

데이터베이스 테이블 명을 지정합니다.

condition

PL/SQL 변수와 상수를 포함하여 열명, 표현식, 상수, 비교연산자로 구성됩니다.

SELECT 문장에 대한 모든 구문형식을 이용하십시오.
호스트 변수는 콜론으로 접두어를 붙여야 함을 명심하십시오.

INTO 절

   INTO 절은 필수이며 SELECT와 FROM 사이에 위치합니다. 이것은 SELECT 절에서    SQL이 리턴하는 값을 저장하기 위한 변수의 이름을 지정하기 위해 사용됩니다. 선택된    각 항목 당 하나의 변수를 제공해야 하고, 순서는 선택된 항목에 대해 같아야 합니다.    PL/SQL 변수 또는 호스트 변수도 INTO 절에 사용합니다.

   
질의는 하나의 행만 리턴해야 합니다.

   PL/SQL 블록 내의 SELECT 문장은 다음 규칙을 적용하는, Embbeded SQL의 ANSI    범주에 속합니다. 질의는 하나의 행만 리턴해야 합니다. 하나의 행 이상 또는 행이 없는    것은 에러를 생성합니다.

   PL/SQL은 NO_DATA_FOUND 와 TOO_MANY_ROWS 예외(예외처리는 후속 장에서    설명합니다.)로 블록의 예외 섹션에서 추적할 수 있는 표준 예외를 조성하여 이 에러를    처리합니다. 단일 행을 리턴하기 위해 SELECT 문장을 만들어야 합니다.

지침

   PL/SQL에서 데이터를 읽어 들이기 위해 이 지침들을 이행합니다.


PL/SQL을 사용한 데이터 조작

  DML(data manipulation) 명령어를 사용하여 데이터베이스에서 데이터를 조작합니다.   PL/SQL에서 제한 없이  DML  명령  INSERT,  UPDATE  그리고  DELETE를 생성할 수 있습니다. PL/SQL코드에 COMMIT  또는  ROLLBACK  문장을 포함함으로써 행 잠금 (테이블 잠금)은 해제됩니다


  • INSERT 문장은 테이블에 데이터의 새 행을 추가합니다.  
  • UPDATE 문장은 테이블에서 존재하는 행을 수정합니다. 
  • DELETE 문장은 테이블에서 원치 않는 행을 제거합니다

데이터 삽입

주: INSERT  명령에서 식별자와 열 이름에 모호성이 있는 것은 불가능합니다. INSERT 절에 있는 식별자는 데이터베이스 열 이름이어야 합니다.

데이터 갱신과 삭제

    지정 연산자의 좌측에 있는 식별자는 항상 데이터 베이스 열이지만, 오른쪽에 있는     식별자도 데이터베이스 열 또는 PL/SQL 변수도 될 수 있기 때문에 UPDATE 문장의     SET 절에서 모호성이 존재할 수 있습니다. PL/SQL에서의 SELECT 문장과 달리 수정된     행이 없으면  에러가  발생하지 않습니다.

    주: PL/SQL 변수 지정은 항상  :=  을,  SQL  열 지정은 항상 = 을 사용합니다. 열명과     식별자명이 WHERE 절에서 같다면, 오라클 서버는 이름에 대해서 우선 데이터베이스를     조사함을 상기하십시오.


데이터삭제

 지정된 주문을 삭제합니다.
 

DECLARE
  v_ordid       ord.ordid%TYPE := 605;
BEGIN
  DELETE FROM   item
    WHERE       ordid = v_ordid;
END;

이름 지정 규약

    PL/SQL 변수명과 데이터베이스 열명을 구분해 주는 이름 지정 규약을 따름으로써     WHERE 절에서 모호성을 회피합니다.

    슬라이드에서 보여준 예는 다음과 같이 정의됩니다. 출하 일자가 오늘인 경우, ORD     테이블 에서 주문일과  출하 일자를 읽어 들입니다.

    PL/SQL은 식별자가 데이터베이스내의 열인지 아닌지를 먼저 확인하고, 아니면     PL/SQL의 식별자가 되는 것으로 가정합니다.

    주: SELECT 절에서 임의의 식별자는 데이터베이스 열 이름이어야 하기 때문에     SELECT절에서 모호성의 가능성은 없습니다. INTO 절에서 식별자는 PL/SQL 변수여야     하기 때문에  INTO  절에서 모호성의 가능성은 없습니다.  WHERE  절에서만 혼란의     가능성이 있습니다. NO_DATA_FOUND와 다른 예외에 대한 자세한 내용은 다음 장에서     설명됩니다.


트랜잭션 제어

COMMIT과 ROLLBACK SQL 문장으로 트랜잭션 논리를 제어함으로써  데이터베이스를 영구적으로 변경하게 합니다. 오라클 서버에서와 마찬가지로, DML 트랜잭션은 COMMIT 또는 ROLLBACK을 수행한 다음에 시작하고 성공적인 COMMIT 또는 ROLLBACK 다음에 종료합니다. 이 작업은 PL/SQL 블록에서 발생할 수 있거나 호스트 환경( 예를 들면, SQL*Plus  섹션 종료는  자동적으로  미결  트랜잭션을 커밋 ) 에서 이벤트의 결과로서 발생할 수 있습니다.
   
COMMIT 문장

COMMIT은 데이터베이스에 대한 모든 미결 변경을 영구화함으로써 현재 트랜잭션을    종료합니다.
   
구문
 

COMMIT [WORK];

ROLLBACK [WORK];


   여기서:         WORK            ANSI 표준을 따르기 위해 사용합니다.
  

주: 호스트 환경마다 그 사용에 대해 약간의 제한이 있을 수 있지만 트랜잭션 제어 명령은  PL/SQL에서 모두 유효합니다. 블록에서 또한 명시적으로 잠금 명령 (LOCK TABLE 과 SELECT ... FOR UPDATE 같은)을  이용할 수 있습니다. (후속 장에서 FOR-UPDATE에 대한 자세한 내용은 설명할 것입니다.)  그것들은 트랜잭션 종료까지 영향을 미치며 하나의 PL/SQL 블록은  하나의  트랜잭션을 반드시 의미하지는 않습니다.

SQL 커서

   SQL 문장을 실행할 때마다 오라클 서버는 명령이 분석되고 실행되는 곳에서 메모리 영역을 개방합니다. 이 영역은 cursor 라 불립니다. 블록의 실행 부분이 SQL 문장을 실행할 때, PL/ SQL은 SQL 식별자를 가지는 암시적 커서를 생성합니다. PL/SQL은 자동적으로 이 커서를 관리합니다. 명시적 커서는 명시적으로 선언되고 프로그래머에 의해 명명됩니다. PL/SQL 커서에 적용될 수 있는 이용 가능한 4가지 속성이 있습니다.

   주: 명시적 커서에 대한 자세한 내용은 후속 장에서 논합니다.

   자세한 내용은 PL/SQL User's Guide and Reference, Release 7.3 또는 8.0,   “ Interaction with Oracle.”를 참조하십시오.


SQL 커서 속성

   SQL 커서 속성은 암시적 커서가 마지막으로 사용된 때 일어난 일을 평가할 수 있도록 해 줍니다. PL/SQL 문장에서 이 속성을 함수처럼 사용합니다. 이것들은 SQL 문장에서 사용할 수 없습니다. 데이터 조작 문장의 실행에 대한 정보를 얻기 위해 블록의 예외 섹션에서, SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, 그리고SQL%ISOPEN 속성을 사용할 수 있습니다. PL/SQL은 예외를 리턴하는 SELECT 문장과  달리, 아무런 행에 영향을 미치지 않는 DML 문장을 실패한 것으로 간주하지 않습니다.

X 정답:C


X 정답:A


X 정답:DE