4월 7일 PL/SQL

사원번호를 입력해주면 해당 사원의 사원명, 직종, 연봉이 출력되는 PROCEDURE를 생성하려고 한다.

FROM ( PL/SQL EDITOR )

CREATE OR REPLACE PROCEDURE P_EMPINFO (V_EMPNO IN NUMBER) IS V_ENAME VARCHAR2(10); --변수 선언 V_JOB VARCHAR2(10); V_YEARPAY NUMBER(6);   BEGIN   SELECT ENAME,JOB,SAL*12 + NVL(COMM,0) INTO V_ENAME,V_JOB,V_YEARPAY FROM EMP WHERE EMPNO=V_EMPNO;   DBMS_OUTPUT.PUT_LINE('-------------------'); --뒤에 개행문자가 숨어있어서 라인을 자동으로 넘겨준다. DBMS_OUTPUT.PUT_LINE('사원명 직종 연봉'); DBMS_OUTPUT.PUT_LINE('-------------------');   DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_JOB || V_YEARPAY); END; /

 

위 문장을 컴파일 한 후, SQL EDITOR로 이동하여 실행하여 본다. 아래와 같이 정보를 조회해 보면 아래와 같이 결과가 제대로 나오는 것을 확인할 수 있다.

사용하기 쉬운 SQL문을 두고, 굳이 PL/SQL을 이용하는 이유는?!

한마디로 하면 PL/SQL은 한번 PACING 된 이후로는 SQL QUERY 보다 빠르기 때문이다. 좀더 상세히 살펴보자면 SELECT 문의 처리 순서 1. PARSING -- (COMPILE ):      1-1 –>  SYNTAX(문법검사)를 실행한다.      1-2 –> 개체(테이블, 컬럼,....)검사를 실행한다 .           -- 이때 개체들은 OSYS 소유의 RACLE DATA DICTIONARY에 존재한다. sys의 기본값으로 tablespace는 SYSTEM이다. 그러므로 ORCLE DATA DICTIONARY VIEW는 'SYSTEM' TABLESPACE에 저장되어 있다. 검색할 테이블이 다른 TABLESPACE에 있다 하더라도 개체검사시 SYS소유의 'SYSTEM' 테이블 스페이스를 반드시 조회해야 하기 때문에 I/O가 매우 빈번히 발생하게 된다.       1-3 –>  권한 검사       1-4 –>  실행 계획  2. EXECUTE -- PLSQL로 작성시 위 PARSING이 프로시저가 생성될 때 단 한번만 실행되기 떄문에 성능이 올라간다. 3. FETCH

 

--JOB(직종명)변경하기

update EMP set JOB='기획관리본부실참모장'
where EMPNO=7521;
        --ORA-12899: "SCOTT"."EMP"."JOB" 열에 대한 값이 너무 큼(실제: 20, 최대값: 9)

JOB 컬럼의 사이즈를 늘려주어야 한다.

ALTER TABLE EMP

MODIFY JOB VARCHAR2(30);

 

UPDATE EMP SET JOB='기획관리본부실참모장'

WHERE EMPNO=7521;

 

SELECT ENAME,JOB,SAL*12 + NVL(COMM,0)

FROM EMP

WHERE EMPNO=7521;

EXEC P_EMPINFO(7499);

 

EXEC P_EMPINFO(7521);

--ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다

--ORA-06512: "SCOTT.P_EMPINFO", 줄 9에서

--ORA-06512: 줄 1에서 (프로시저의 number(6) 버퍼 초과인 것이다.)

위 문제에 대한 해결을 위해 PROCEDURE의 소스를 보고, 수정을 해야 한다.

-- 프로시저의 SOURCE 보기 --

select *
from user_source;

   

프로시저에서 JOB에 버퍼가 10 밖에 되지 않기 떄문에 오류가 난것이다.

이렇게 매번 일일이 변경을 하는 작업은 번거롭다.

--변수 선언의 (참조를 통한) 능동적 사용

우선 VEIW를 생성한다.

create or replace view V_EMP

as

select EMPNO,ENAME,JOB,SAL*12 + NVL(COMM,0)"YEARPAY"

FROM EMP;

 

select *

from V_EMP;

 

이제 PL/SQL EDIOTR에서

create or replace procedure P_EMPINFO (V_EMPNO IN number) IS V_ENAME EMP.ENAME%TYPE; V_JOB EMP.JOB%TYPE; V_YEARPAY NUMBER(6);   BEGIN   select ENAME,JOB,SAL*12 + NVL(COMM,0) INTO V_ENAME,V_JOB,V_YEARPAY FROM EMP where EMPNO=V_EMPNO;   DBMS_OUTPUT.PUT_LINE('-------------------' DBMS_OUTPUT.PUT_LINE('사원명 직종 연봉'); DBMS_OUTPUT.PUT_LINE('-------------------');   DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_JOB || V_YEARPAY); END; /

해주고 다시 아래 EXEC문을 실행해 보면

EXEC P_EMPINFO(7521);

제대로 적용되는 것을 알 수 있다.

이제 연봉의 자릿수를 올리는 실습을 하자.

desc emp;

아래와 같이 7521 사원번호의 SAL(월급)dmf 99999로 업데이트 시켜주고,

update emp set sal=99999
where empno=7521;

commit;

EXEC P_EMPINFO(7521);

를 실행하게 되면 아래와 같은 오류가 발생하게 된다. 이는 프로시저에서 선언된 NUMBER(6) 값보다 큰 값이기 떄문이다.

--ORA-06502: PL/SQL: 수치 또는 값 오류: 숫자 정도가 너무 큽니다
--ORA-06512: "SCOTT.P_EMPINFO", 줄 10에서
--ORA-06512: 줄 1에서

이를 해결하기 위해서는 우선 좀 더 쉽게 매번 결과값을 변동시킬 필요없도록 VIEW를 통해서 한다.

create or replace view V_EMP
as
select EMPNO,ENAME,JOB,SAL*12 + NVL(COMM,0)"YEARPAY"
FROM EMP;

CREATE OR REPLACE PROCEDURE P_EMPINFO (V_EMPNO IN NUMBER) IS V_ENAME V_EMP.ENAME%TYPE; V_JOB V_EMP.JOB%TYPE; V_YEARPAY V_EMP.YEARPAY%TYPE;   BEGIN   SELECT ENAME,JOB,YEARPAY INTO V_ENAME,V_JOB,V_YEARPAY FROM V_EMP WHERE EMPNO=V_EMPNO;   DBMS_OUTPUT.PUT_LINE('-------------------'); DBMS_OUTPUT.PUT_LINE('사원명 직종 연봉'); DBMS_OUTPUT.PUT_LINE('-------------------');   DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_JOB || V_YEARPAY); END;

 

/

위와 같이 변경 후, 

다시 VIEW와 EXEC P_EMPINFO(7521); 를 다시 실행해보면

와 같이 정상적으로 결과값이 출력되는 것을 확인할 수 있다.

다른 방법으로는 위와 같이 컬럼들을 하나 하나 다 읽어오는 방법이 아닌 한번에 행을 다 읽어오는 방법으로

create or replace procedure P_EMPINFO (V_EMPNO IN number) IS V_RCD V_EMP%ROWTYPE;   BEGIN   select * INTO V_RCD   FROM V_EMP where EMPNO=V_EMPNO;   DBMS_OUTPUT.PUT_LINE('-------------------'); DBMS_OUTPUT.PUT_LINE('사원명 직종 연봉'); DBMS_OUTPUT.PUT_LINE('-------------------');   DBMS_OUTPUT.PUT_LINE(V_RCD.ENAME|| ' ' || V_RCD.JOB || V_RCD.YEARPAY); END; /

와 같다. 결과값은 같으므로 그림은 생략한다.

------11시부터 다시 시작~

-------------------------------------------------------------------------------
"사원명 직종 연봉" 의 변수를 하나로 묶기

--새로운 RECORD TYPE 생성하기
(출력되어지는 결과 값은 같다.)

CREATE OR REPLACE PROCEDURE P_EMPINFO (V_EMPNO IN NUMBER) IS TYPE VEMPRCDTYPE IS RECORD (A V_EMP.ENAME%TYPE ,B V_EMP.JOB%TYPE ,C V_EMP.YEARPAY%TYPE ); --VEMPRCDTYPE는 레코드 타입인데 ABC가 있다.   V_RCD VEMPRCDTYPE; --V_RCD는 VEMPRCDTYPE 와 같은 데이터 타입을 쓰는 것이다.   BEGIN   SELECT ENAME,JOB,YEARPAY INTO V_RCD   FROM V_EMP WHERE EMPNO=V_EMPNO;   DBMS_OUTPUT.PUT_LINE('-------------------'); DBMS_OUTPUT.PUT_LINE('사원명 직종 연봉'); DBMS_OUTPUT.PUT_LINE('-------------------');   DBMS_OUTPUT.PUT_LINE(V_RCD.A|| ' ' || V_RCD.B || V_RCD.C); END;

 

--아래와 같이 '사원명 직종 연봉 '의 컬럼 정보와 함께 연봉레벨에 따른 별을 찍어보자

조건문 살펴보기

/*      조건문     */ IF       조건1   THEN 실행명령1; ELSIF    조건2   THEN 실행명령2; END IF;    조건1이 참이라면 실행명령1을 하고 끝내고, 만약 아니라면 조건2를 비교해서 맞다면 실행명령2를 실행하고 끝낸다.    만일 그것도 아니라면 그냥 종료한다.

------------------------
사원명 직종  연봉  연봉레벨(10000~20000 = ★★, 20000~30000 = ★★★
------------------------

CREATE OR REPLACE PROCEDURE P_EMPINFO2 (V_EMPNO IN NUMBER) IS V_ENAME V_EMP.ENAME%TYPE; V_JOB V_EMP.JOB%TYPE; V_YEARPAY V_EMP.YEARPAY%TYPE;   V_STAR VARCHAR2(6);   BEGIN   SELECT ENAME, JOB, YEARPAY INTO V_ENAME, V_JOB, V_YEARPAY FROM V_EMP WHERE EMPNO = V_EMPNO;   IF V_YEARPAY >= 30000 THEN V_STAR := '★★★'; ELSIF V_YEARPAY >= 20000 THEN V_STAR := '★★'; ELSIF V_YEARPAY >= 10000 THEN V_STAR := '★'; ELSE V_STAR := ' '; END IF;     DBMS_OUTPUT.PUT_LINE('-----------------------------'); DBMS_OUTPUT.PUT_LINE('사원명 직종 연봉 별'); DBMS_OUTPUT.PUT_LINE('-----------------------------');   DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_JOB || ' ' || V_YEARPAY || ' ' || V_STAR); END; /

 

이제 SQL 에서

EXEC P_EMPINFO2(7499);

 

**반복문

1. 기본 LOOP 문

    EXEC P_MEMBER_INS(10);

위 명령어를 실행하게 되면 MEMBER테이블에 10번의 LOOP문이 실행되도록 하는 것이다.

실습을 위해 아래와 같은 테이블을 생성한다.

CREATE TABLE MEMBER

(BUNHO NUMBER

,NAME VARCHAR2(20) DEFAULT '홍길동'

,DATETIME DATE DEFAULT SYSDATE

);

SELECT *

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME='MEMBER' AND COLUMN_NAME='NAME';

CREATE OR REPLACE PROCEDURE P_MEMBER_INS (V_NUM NUMBER) --V_NUM이 10이라 가정, V_NUM 은 상수값 IS   V_CNT MEMBER.BUNHO%TYPE:=1; --V_CNT는 변수, 초기값 1을 입력해줌. 해주지 않을시 NULL값이므로 문제 발생 V_NAMEDEFAULT MEMBER.NAME%TYPE;   BEGIN   SELECT DATA_DEFAULT INTO V_NAMEDEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='MEMBER' AND COLUMN_NAME='NAME';       LOOP EXIT WHEN V_NUM < V_CNT ; -- 조건이 참이면 EXIT로 빠져나오고 거짓이면 LOOP를 돈다. -- (V_NUM : V_CNT (10,1),(10,2), ....(10,10),(10,11)이 되면 거짓이므로 'LOOP문'을 탈출한다. INSERT INTO MEMBER(BUNHO, NAME) VALUES(V_CNT, RTRIM(V_NAMEDEFAULT)||LTRIM(V_CNT));   V_CNT:=V_CNT+1;   --1 ~ 10 까지 입력되어짐. V_NUM과 V_CNT 값을 비교. END LOOP;   COMMIT; END;

 

 

   

-실습2

EXEC P_HAP(1,10);
--1부터 10까지의 합은 55입니다.
EXEC P_HAP(2,9);
--2부터 9까지의 합은 44입니다.

와 같은 결과값을 얻도록 두 수 사이의 SUM값을 구하는 프로시저를 생성해보자.

CREATE OR REPLACE PROCEDURE P_HAP (V_START NUMBER,V_END NUMBER) IS V_CNT NUMBER:=V_START; V_TOT NUMBER:=0; BEGIN   LOOP EXIT WHEN V_END < V_CNT; -- 최종값이 얼마이상인지 확인   -- 누적치를 저장하는 값 V_TOT:=V_TOT+V_CNT; -- +1 증가하는 값 V_CNT:=V_CNT+1; END LOOP; DBMS_OUTPUT.PUT_LINE(V_START||'부터'||V_END||'까지의합은'||V_TOT||'입니다.'); END; /

 --> 결과 확인

EXEC P_HAP(2,9);

   

-실습3

Exec P_HAP2(1,10,0);
--1부터 10까지 짝수의 합은 xx입니다. (0이 들어오면 짝수의 합)

Exec P_HAP2(1,10,1);
--1부터 10까지 홀수의 합은 xx입니다. (1이 들어오면 홀수의 합)

Exec P_HAP2(1,10,2);
--세번째 인자값은 0 또는 1로 하세요!!!

와 같은 결과값을 얻도록 구해보자.
------------------------------------------------------

CREATE OR REPLACE PROCEDURE P_HAP2 (V_START NUMBER, V_END NUMBER, V_FLAG NUMBER) IS   V_TOT NUMBER:= 0; V_CNT NUMBER; V_HOLJAK VARCHAR2(4); BEGIN   IF V_FLAG NOT IN (0,1) THEN DBMS_OUTPUT.PUT_LINE('세번째 인자값은 0 또는 1로 하세요!!!');   ELSE   IF V_FLAG = 0 AND MOD(V_START, 2) != 0 THEN V_CNT := V_START + 1; V_HOLJAK := '짝';   ELSIF V_FLAG = 0 AND MOD(V_START, 2) = 0 THEN V_CNT := V_START; V_HOLJAK := '짝';     ELSIF V_FLAG = 1 AND MOD(V_START, 2) != 0 THEN V_CNT := V_START; V_HOLJAK := '홀';   ELSIF V_FLAG = 1 AND MOD(V_START, 2) = 0 THEN V_CNT := V_START + 1; V_HOLJAK := '홀';   END IF;     LOOP EXIT WHEN V_CNT > V_END ; V_TOT := V_TOT + V_CNT; V_CNT := V_CNT + 2; END LOOP;     DBMS_OUTPUT.PUT_LINE(V_START || '부터 ' || V_END || '까지 ' || V_HOLJAK || '수의 합은 ' || V_TOT || '입니다');   END IF; END; /

   

-실습4

입력되는 숫자 만큼 ★ 찍기

   

CREATE OR REPLACE PROCEDURE P_STAR1 (V_STARNO NUMBER) IS   V_RESULT VARCHAR2(100); V_CNT NUMBER:=0;   BEGIN   LOOP   EXIT WHEN V_CNT > V_STARNO; V_RESULT:=V_RESULT||'★'; V_CNT:=V_CNT+1;   END LOOP;   DBMS_OUTPUT.PUT_LINE(V_RESULT);   END; /
EXEC P_STAR(5);

   

실습5-- 실습 4에 심화 학습으로 다중 LOOP 이용하기

EXEC P_STAR2(5,5);	EXEC P_STAR2(5,3);	EXEC P_STAR2(5,1);
★★★★★ ★★★★★ ★★★★★ ★★★★★ ★★★★★	★★★★★ ★★★★★ ★★★★★	★★★★★

   

CREATE OR REPLACE PROCEDURE P_STAR2 (V_STARNO NUMBER, V_ROWSU NUMBER) IS V_RESULT VARCHAR2(100); V_CNT NUMBER := 0; V_ROWCNT NUMBER := 0; BEGIN   LOOP   EXIT WHEN V_ROWCNT = V_ROWSU ;     LOOP EXIT WHEN V_CNT = V_STARNO; V_RESULT := V_RESULT || '★'; V_CNT := V_CNT + 1; END LOOP;   DBMS_OUTPUT.PUT_LINE(V_RESULT);     V_ROWCNT := V_ROWCNT + 1;   END LOOP;   END; /

실습6

EXEC P_STAR3(5,5);	EXEC P_STAR3(5,3);	EXEC P_STAR3(5,1);
★★★★★ ★★★★ ★★★ ★★ ★	★★★ ★★ ★	★★★★★

행이 늘어날 때마다 한 개씩 줄이기

CREATE OR REPLACE PROCEDURE P_STAR3 (V_STARNO NUMBER, V_ROWSU NUMBER) IS V_RESULT VARCHAR2(100); V_CNT NUMBER := 0; V_ROWCNT NUMBER := 0; BEGIN   LOOP   EXIT WHEN V_ROWCNT = V_ROWSU ;   LOOP -- EXIT WHEN V_CNT = V_STARNO;   -- 찍어주는 별의 갯수는 전체행수(5) - 자신의행수   EXIT WHEN V_CNT = (V_ROWSU - V_ROWCNT) ; V_RESULT := V_RESULT || '★'; V_CNT := V_CNT + 1; END LOOP;   DBMS_OUTPUT.PUT_LINE(V_RESULT);   V_RESULT := NULL; V_CNT := 0;   V_ROWCNT := V_ROWCNT + 1;   END LOOP;   END; /