04월09일 PL_SQL 실습 및 이론 (2)

--OS에서 *.txt 파일에 저장된 데이터를 읽어와 테이블을 하나 생성(External Table)하고 그 테이블의 내용을 기반으로 아래와 같이 '이름'과 '과목별 점수'항목을 나타낸다.

Jumsu.txt

조영빈,100,90,95 신일섭,100,100,100 허준구,80,70,90 송미리,80,90,100 최민성,90,70,80 정지수,30,20,10

 

우선 실습을 위해 directory 생성 권한을 부여한다.

(권한이 없다면 아래와 같이 오류가 발생한다.)

 

이땐,

SYS 에서 GRANT CREATE ANY DIRECTORY TO SCOTT;

 

그리고 'FILEPATH' 라는 이름으로 "D:\FILEDB\"의 경로로 디렉토리 경로를 인식시켜준다.

CREATE OR REPLACE DIRECTORY FILEPATH

AS 'D:\FILEDB\';

CREATE TABLE TBLJUMSU_EXT (NAME VARCHAR2(20) ,KOR NUMBER(3) ,ENG NUMBER(3) ,MATH NUMBER(3) ) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY FILEPATH ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE 'JUMSU.BAD' LOGFILE 'JUMSU.LOG' FIELDS TERMINATED BY ',' (NAME CHAR ,KOR CHAR ,ENG CHAR ,MATH CHAR) ) LOCATION('JUMSU.TXT') ) REJECT LIMIT UNLIMITED;

 

 

CREATE OR REPLACE PROCEDURE P_TBLJUMSUINS IS   CURSOR CUR_TBLJUMSU_EXT IS SELECT * FROM TBLJUMSU_EXT;   V_TBLJUMSURCD TBLJUMSU_EXT%ROWTYPE;   V_CNT NUMBER := 0; V_AVRG TBLJUMSU.AVRG%TYPE; V_HAKJUM TBLJUMSU.HAKJUM%TYPE;   CURSOR CUR_TBLJUMSU IS SELECT TOT FROM TBLJUMSU FOR UPDATE OF TOT;   V_MYTOT TBLJUMSU.TOT%TYPE;   CURSOR CUR_TBLJUMSU2 IS SELECT TOT FROM TBLJUMSU;   V_OTHERTOT TBLJUMSU.TOT%TYPE;   V_RANK TBLJUMSU.SEOKCHA%TYPE := 1;   BEGIN   OPEN CUR_TBLJUMSU_EXT;   LOOP FETCH CUR_TBLJUMSU_EXT INTO V_TBLJUMSURCD;   EXIT WHEN CUR_TBLJUMSU_EXT%NOTFOUND;   V_CNT := V_CNT + 1;   V_AVRG := ROUND( (V_TBLJUMSURCD.KOR + V_TBLJUMSURCD.ENG + V_TBLJUMSURCD.MATH) / 3 , 1);   IF V_AVRG >= 95 THEN V_HAKJUM := 'A+'; ELSIF V_AVRG >= 90 THEN V_HAKJUM := 'A'; ELSIF V_AVRG >= 85 THEN V_HAKJUM := 'B+'; ELSIF V_AVRG >= 80 THEN V_HAKJUM := 'B'; ELSIF V_AVRG >= 75 THEN V_HAKJUM := 'C+'; ELSIF V_AVRG >= 70 THEN V_HAKJUM := 'C'; ELSIF V_AVRG >= 65 THEN V_HAKJUM := 'D+'; ELSIF V_AVRG >= 60 THEN V_HAKJUM := 'D'; ELSE V_HAKJUM := 'F'; END IF;     INSERT INTO TBLJUMSU(NO, NAME, KOR, ENG, MATH, TOT, AVRG, HAKJUM) VALUES (V_CNT ,V_TBLJUMSURCD.NAME ,V_TBLJUMSURCD.KOR ,V_TBLJUMSURCD.ENG ,V_TBLJUMSURCD.MATH ,(V_TBLJUMSURCD.KOR + V_TBLJUMSURCD.ENG + V_TBLJUMSURCD.MATH) ,V_AVRG ,V_HAKJUM );   END LOOP;   CLOSE CUR_TBLJUMSU_EXT; ----------------------------------------------------   OPEN CUR_TBLJUMSU;   LOOP FETCH CUR_TBLJUMSU INTO V_MYTOT; EXIT WHEN CUR_TBLJUMSU%NOTFOUND;   OPEN CUR_TBLJUMSU2; LOOP FETCH CUR_TBLJUMSU2 INTO V_OTHERTOT; EXIT WHEN CUR_TBLJUMSU2%NOTFOUND; IF V_OTHERTOT > V_MYTOT THEN V_RANK := V_RANK + 1; END IF; END LOOP; CLOSE CUR_TBLJUMSU2;   UPDATE TBLJUMSU SET SEOKCHA = V_RANK WHERE CURRENT OF CUR_TBLJUMSU;   V_RANK := 1;   END LOOP;   CLOSE CUR_TBLJUMSU;   COMMIT; END;

 

SELECT

*

FROM TBLJUMSU_EXT;