04월09일 PL/SQL 실습 및 이론 (1)

하나의 테이블 정보를 다른 특정 테이블로 조건별로 넣기.

(EMP 테이블의 SAL값을 기반으로 등급을 매겨 특정 테이블에 삽입한다.)

 

EMP 테이블에서 SAL 값이 2000 ~ 5000 사이인 직원들을 선별하여 사원번호(EMPNO), 사원이름(ENAME),월급(SAL)의 정보를 'TBLEMPSAL'이란 테이블에 입력한다.

CREATE TABLE TBLEMPSAL TABLESPACE KHTBS AS SELECT EMPNO, ENAME,SAL FROM EMP WHERE SAL BETWEEN 2000 AND 5000;   생성된 TBLEMPSAL 테이블의 정보를 조회해보면,

PROCEDURE를 통해 등급(RANK)을 사용해 값들이 들어갈 테이블을 생성한다.   CREATE TABLE TBLEMPSALRANK (EMPNO NUMBER ,ENAME VARCHAR2(10) ,SAL NUMBER ,GRADE VARCHAR2(1) ,SEOKCHA NUMBER );

프로시저를 생성한다.   CREATE OR REPLACE PROCEDURE P_TBLEMPSAL_INS IS --읽어와야할 대상을 커서로 지정 CURSOR CUR_TBLEMPSAL IS   SELECT * FROM TBLEMPSAL;   V_TBLEMPSALRCD TBLEMPSAL%ROWTYPE; V_GRADE TBLEMPSALRANK.GRADE%TYPE;   --2를 해결하기 위한 커서 생성 CURSOR CUR_TBLEMPSALRANK IS   SELECT EMPNO, SAL FROM TBLEMPSALRANK;   -- 2번째 커서에서 생성한 변V_EMPNO, V_SAL;수에 대한 정의 V_EMPNO TBLEMPSALRANK.EMPNO%TYPE; V_SAL TBLEMPSALRANK.SAL%TYPE;   --석차에 관한 커서 CURSOR CUR_TBLEMPSALRANK2 IS   SELECT SAL FROM TBLEMPSALRANK;   V_SAL_OTHER TBLEMPSALRANK.SAL%TYPE; V_SEOKCHA TBLEMPSALRANK.SEOKCHA%TYPE := 1;   BEGIN   OPEN CUR_TBLEMPSAL;   LOOP   FETCH CUR_TBLEMPSAL INTO V_TBLEMPSALRCD; --읽어온 것을 V_TBLEMPSALRCD 라는 변수로 담아 두겠다.   EXIT WHEN CUR_TBLEMPSAL%NOTFOUND;--읽어올 행이 없을 때 빠져나와라.   IF V_TBLEMPSALRCD.SAL >= 5000 THEN V_GRADE := 'A'; ELSIF V_TBLEMPSALRCD.SAL >= 3000 THEN V_GRADE := 'B'; ELSIF V_TBLEMPSALRCD.SAL >= 1000 THEN V_GRADE := 'C'; ELSE V_GRADE := 'D'; END IF;   -- 등수 구하기 (RANK함수, COUNT() +1 , UPDATE등을 이용할 수 있다.) /* 랭크 함수와 COUNT함수는 행을 다 읽은 다음 다시 한 행씩 읽어오며 등수를 매긴다. EX) 1. 90 2. 85 3. 93 4. 90 5. 70   위와 같을 때 우선 1행을 읽어 '1'을 마크한다. 이제 2행을 읽어 1행과 값을 비교한다. 1행이 더크므로 무시하고 3행으로 간다. 3행과 1행을 비교하여 1행에 '1+1' 마크한다. 4행을 읽어 값이 같으므로 무시하고 5행으로 간다. 5행의 값도 비교되어진 후 1행에 등수는 '2'로 종료되어 진다. 위와같은 LOGIC으로 2행부터 끝까지 계산하여 MARK되어질 값을 결정한다. */     INSERT INTO TBLEMPSALRANK(EMPNO, ENAME, SAL, GRADE) VALUES (V_TBLEMPSALRCD.EMPNO, V_TBLEMPSALRCD.ENAME, V_TBLEMPSALRCD.SAL, V_GRADE );   END LOOP;   CLOSE CUR_TBLEMPSAL;   ----------------------------------------------   OPEN CUR_TBLEMPSALRANK;   LOOP   FETCH CUR_TBLEMPSALRANK INTO V_EMPNO, V_SAL; -- 자신의 사원번호 , 자신의 SAL 값 EXIT WHEN CUR_TBLEMPSALRANK%NOTFOUND;   /* 다른 모든 사원들의 SAL값을 읽어와서 자신의 SAL 값과 비교해주는 것이 필요하다. 비교해서 다른 사람의 SAL 값이 자신의 SAL 값보다 크다라면 자신의 석차는 1씩 증가하도록 한다. */   OPEN CUR_TBLEMPSALRANK2;   LOOP   FETCH CUR_TBLEMPSALRANK2 INTO V_SAL_OTHER;   EXIT WHEN CUR_TBLEMPSALRANK2%NOTFOUND;   IF V_SAL_OTHER > V_SAL THEN V_SEOKCHA := V_SEOKCHA + 1; END IF;   END LOOP;   CLOSE CUR_TBLEMPSALRANK2;   /* 최종적으로 자신의 SAL값과 모든 다른 사원들의 SAL값을 비교한 이후 석차를 변경하는 것이다. */   UPDATE TBLEMPSALRANK SET SEOKCHA = V_SEOKCHA WHERE EMPNO = V_EMPNO; --한행 한행 읽어온 사원번호   V_SEOKCHA := 1;   END LOOP;     CLOSE CUR_TBLEMPSALRANK;   END; /

EXEC P_TBLEMPSAL_INS;   SELECT * FROM TBLEMPSALRANK;

**우선적으로 제대로 결과값이 도출 된 것을 확인할 수 있다.   위 식은 석차를 매김에 있어 EMPNO를 참조하게 되어 있다. 즉, EMPNO가 UNIQUE한 자료이다.   만일 동일한 EMPNO를 가진 사원(현실에서는 그럴일이 없겠지만…)이 있다고 가정하여 보자.

EMPNO 7788인 SCOTT의 EMPNO를 7782로 바꾸어보자.   update tblempsal set empno=7782 --clerk where empno=7788; --scott COMMIT;

이제 다시 Procedure를 실행해 보면,     이는 위에서 살펴보듯이 SCOTT의 EMPNO '7782'로 변경되면서 SCOTT의 석차(SEOKCHA)와 동일해진 것이다.   이제 이 문제를 해결하기 위해서는

위 PROCEDURE에서 2개 LINE을 변경해 주어야 한다.   CREATE OR REPLACE PROCEDURE P_TBLEMPSAL_INS IS   CURSOR CUR_TBLEMPSAL IS   SELECT * FROM TBLEMPSAL;     V_TBLEMPSALRCD TBLEMPSAL%ROWTYPE; V_GRADE TBLEMPSALRANK.GRADE%TYPE;     CURSOR CUR_TBLEMPSALRANK IS   SELECT EMPNO, SAL FROM TBLEMPSALRANK FOR UPDATE OF SAL; --변경내용     V_EMPNO TBLEMPSALRANK.EMPNO%TYPE; V_SAL TBLEMPSALRANK.SAL%TYPE;     CURSOR CUR_TBLEMPSALRANK2 IS   SELECT SAL FROM TBLEMPSALRANK;     V_SAL_OTHER TBLEMPSALRANK.SAL%TYPE; V_SEOKCHA TBLEMPSALRANK.SEOKCHA%TYPE := 1;   BEGIN   OPEN CUR_TBLEMPSAL;   LOOP   FETCH CUR_TBLEMPSAL INTO V_TBLEMPSALRCD;   EXIT WHEN CUR_TBLEMPSAL%NOTFOUND;   IF V_TBLEMPSALRCD.SAL >= 5000 THEN V_GRADE := 'A'; ELSIF V_TBLEMPSALRCD.SAL >= 3000 THEN V_GRADE := 'B'; ELSIF V_TBLEMPSALRCD.SAL >= 1000 THEN V_GRADE := 'C'; ELSE V_GRADE := 'D'; END IF;         INSERT INTO TBLEMPSALRANK(EMPNO, ENAME, SAL, GRADE) VALUES (V_TBLEMPSALRCD.EMPNO, V_TBLEMPSALRCD.ENAME, V_TBLEMPSALRCD.SAL, V_GRADE );   END LOOP;   CLOSE CUR_TBLEMPSAL;   ----------------------------------------------   OPEN CUR_TBLEMPSALRANK;   LOOP   FETCH CUR_TBLEMPSALRANK INTO V_EMPNO, V_SAL;   EXIT WHEN CUR_TBLEMPSALRANK%NOTFOUND;     OPEN CUR_TBLEMPSALRANK2;   LOOP   FETCH CUR_TBLEMPSALRANK2 INTO V_SAL_OTHER;   EXIT WHEN CUR_TBLEMPSALRANK2%NOTFOUND;   IF V_SAL_OTHER > V_SAL THEN V_SEOKCHA := V_SEOKCHA + 1; END IF;   END LOOP;   CLOSE CUR_TBLEMPSALRANK2;       UPDATE TBLEMPSALRANK SET SEOKCHA = V_SEOKCHA WHERE current of cur_tblempsalrank; --변경내용   V_SEOKCHA := 1;   END LOOP;     CLOSE CUR_TBLEMPSALRANK;   commit;   END; /

이제 다시 TBLEMPSAL 테이블의 행들을 제거한 후, 다시 프로시저를 실행해 보면     그림과 같이 제대로 작동하는 것을 확인할 수 있다.   위 LOGIC에서 사용된 개념들에 대한 설명은 추후에~*