PL/SQL - 배열
PL/SQL에서 배열이 필요할때가 있다. 바로 테이블에서 여러행의 결과물을 추출해서 프로시저에서 처리해야할때이다.
일단 배열을 사용하지 않으면 안되는때 2가지를 생각해보자
1. 배열로 처리해야할 수밖에 없는 예
다음과 같은 사원정보가 담긴 TBL_NEWSAWON 이라는 테이블 있다고 해보자
사원번호 이름 직위 연봉
_________________________
SANO, NAME . JIK , SAL
1001 김씨 사장 5000
1002 신씨 과장 3000
1003 박씨 과장 2800
1004 천씨 과장 2500
__________________________
이중 특정직위를 가진 예를들어서 사장,또는 과장직위를 가지 사람들의 정보를 추출해 프로시저를 통해 추출해 보도록
해보자.
CREATE OR REPLACE PROCEDURE PCD_SAWON
(V_JIK IN TBL_NEWSAWON.JIK%TYPE)
IS
V_SANO TBL_NEWSAWON.SANO%TYPE;
V_NAME TBL_NEWSAWON.NAME%TYPE;
V_YEARPAY NUMBER;
BEGIN
SELECT SANO, NAME, SAL
INTO
V_SANO, V_NAME, V_YEARPAY
FROM TBL_NEWSAWON
WHERE JIK = V_JIK;
DBMS_OUTPUT.PUT_LINE( LPAD('-', 20, '-') );
DBMS_OUTPUT.PUT_LINE( V_SANO || ' ' ||
V_NAME || ' ' ||
V_YEARPAY );
DBMS_OUTPUT.PUT_LINE( LPAD('-', 20, '-') );
END;
위 프로시저를 통해서 사장과 과장의 정보를 추출해 보자.
EXEC PCD_SAWON('사장')
사장의 정보는 문제없이 출력된다.
EXEC PCD_SAWON('과장')
과장을 출력하려하면 에러가 난다. 에러메세지는 다음과 같다.
실제로 보면 과장은 3명이다. 이 3개의 행을 출력하기위해 프로시저는 준비가 되지 않아있다.
실제 쿼리문에서 출력되기위해 테이블에서 변수를 받아들이는 다음 구문을 보면
INTO
V_SANO, V_NAME, V_YEARPAY
다수의 여러행을 모두 보관하기 위한 장소가 아니다. 각변수는 모두 1개의 결과값만을 저장가능한 변수 형태이다.
2. 해결법
이를 해결하기위한 방법으로 여러개의 필드값들을 한꺼번에 넣을수 있는 변수 구조가 있다.
배열이다. 배열은 같은 형태의 데이터값을 같이 넣을수 있다.
위에는 다른 프로그래밍의 배열에 대한 개념도지만 차이는 없다. 배열은 위처럼 여러개의 테이터들을
메모리에 연속적으로 저장한다. 그리고 각 데이터의 저장장소는 인덱스라는 위치값을 가진다.
(위에서는 0번부터 차례대로 번호를 부여해서 위치를 지정하는데, 오라클의 경우는 처음 위치를 1로잡는
것만 다를 뿐이다.)
따라서 앞에서 과장이 3명일 경우 배열로
[과장1의 데이터] [과장2의 데이터][과장3의데이터]로 저장할수 있는 변수를 만들어준다면
프로시저에서 이를 처리할 수 있게 될것이다.
위 1번의 배열 문제를 해결한 코드를 보도록하자.
CREATE OR REPLACE PROCEDURE PCD_SAWONJIK
(V_JIK IN TBL_NEWSAWON.JIK%TYPE)
IS
TYPE NUMBERTABTYPE
IS
TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
TYPE NAMETABTYPE
IS
TABLE OF TBL_NEWSAWON.NAME%TYPE
INDEX BY BINARY_INTEGER;
V_SANO NUMBERTABTYPE;
V_NAME NAMETABTYPE;
V_YEARPAY NUMBERTABTYPE;
i BINARY_INTEGER := 0;
BEGIN
FOR k IN (SELECT SANO, SAL)
FROM TBL_NEWSAWON
WHERE JIK = V_JIK ) LOOP
i := i+1;
V_SANO(i) := k.SANO;
V_NAME(i) := k.NAME;
V_YEARPAY(i) := k.YEARPAY;
END LOOP;
DBMS_OUTPUT.PUT_LINE( LPAD('-', 20, '-') );
DBMS_OUTPUT.PUT_LINE('사원번호 사원명 연봉' );
DBMS_OUTPUT.PUT_LINE( LPAD('-', 20, '-') );
FOR n IN 1..i LOOP
DBMS_OUTPUT.PUT_LINE( V_SANO(n) || ' ' ||
V_NAME(n) || ' ' ||
V_YEARPAY(n)
);
END LOOP;
END;
1) 배열의 선언
배열 또한 IS 이하구문 선언부에서 선언해줘야 사용할수 있다.
배열의 선언은 다음과 같이 한다.
TYPE NUMBERTABTYPE
IS
TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
TYPE으로 지정해주고 타입 이름을 쓴다. 여기서는 NUMBERTABTYPE이라는 이름으로 지정해 주었다.
자신이 알아볼수 있는 이름으로 지정해 주었다. 그리고 TABLE OF 구문으로 이것이 배열선언임을 알려주고
NUMBER 라는 숫자형의 데이터들이 배열에 저장될것임을 알린다. 마지막에 INDEX BY 구문은 아까말한
배열의 위치를 지정해주는데 사용하는 것으로 이것을 이용해서 몇번째 배열인지를 알아낼수 있다.
다시말해서
TYPE [타입이름]
TABLE OF [배열에저장될 형지정]
INDEX BY BINARY_INTEGER
형태로 배열을 선언해주는것이다. 오라클에서는 배열을 테이블 타입이라고 이야기 해준다. 위에서처럼 배열형태의
선언시 TABLE OF 라는 키워드를 적어주기 때문이다.
(일반적으로 프로그래밍에서 배열에는 같은 데이터형드링 들어가게 한다. 숫자형만 들어가거나, 문자형만 들어가는 식으로 해서
물론 JAVA같은 경우는 배열에 다양한 객체를 넣는 경우도 있으나 객체지향의 특이할 특성인 경우에 한해서이다)
2) 배열의 사용
배열 변수로 지정된 V_SANO(i) 의 경우를 생각해보면 배열은
배열변수(양의정수) 형태로 쓰인다.
만일 V_SANO라는 이름의 배열에 저장된 첫번째 배열요소를 사용하거나 공간을 지정하고자 한다면 V_SANO(1)
두번째 요소를 꺼내거나 공간을 사용하고자 한다면 V_SANO(2)라는 식으로 1번부터 위치 주소가 매겨진다고 볼수 있다.
참고로 FOR LOOP를 쓴이유는
FOR문의 경우에 FOR ...IN () 에서 IN 다음에 나오는 SELECT 쿼리문의 경우 추출되는
행들만큼 실행하게되므로, 주로 FOR LOOP를 많이 쓰는것이 더 유용하다.
일반 주로 FOR LOOP를 많이 쓰는것이 더 유용할 경우가 많다
<참고> 위를 LOOP 문으로 구현할수 있으나 문제가 생긴다. EXIT할 조건을 만들수 없다.
CREATE OR REPLACE PROCEDURE PCD_SAWONJIK
(V_JIK IN TBL_NEWSAWON.JIK%TYPE)
IS
TYPE NUMBERTABTYPE
IS
TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
TYPE NAMETABTYPE
IS
TABLE OF TBL_NEWSAWON.NAME%TYPE
INDEX BY BINARY_INTEGER;
V_SANO NUMBERTABTYPE;
V_NAME NAMETABTYPE;
V_YEARPAY NUMBERTABTYPE;
i BINARY_INTEGER := 0;
BEGIN
LOOP
EXIT WHEN ??????
i := i + 1;
SELECT SANO, NAME,
NVL2(COMM, SAL*12+COMM, SAL*12)
INTO
V_SANO(i), V_NAME(i), V_YEARPAY(i)
FROM TBL_NEWSAWON
WHERE JIK = V_JIK;
END LOOP;
END;
위에 코드가 FOR구문대신 LOOP구문을 쓴경우인데 LOOP 가끝날 시점을 알수가 없다.
상황에따라 SELECT문에서 추출될 데이터의 양이 다르므로 특정한 숫자로 지정해 줄수 없기 때문이다.