Record, Cursor, Collection
Oracle PL/SQL에서 Record, Cursor, Collection은 모두 데이터를 저장하고 처리하는데 사용되는 데이터 구조(타입)이다.
Record는 한 줄(1개의 데이터 행)의 데이터를 구성하는 필드 집합으로, 서로 다른 데이터 타입을 포함할 수 있다.
Cursor는 여러 줄(1개 이상의 데이터 행)의 데이터를 처리하는 데이터 집합으로, SQL쿼리 결과를 처리하는데 사용된다.
Collection은 여러 개의 다양한 데이터를 저장할 수 있는 데이터 집합으로 프로그래밍의 배열과 비슷하다.(Collection에는 Record와 Cursor 타입의 데이터가 들어갈 수 있다.)
✅ Record
사용자가 특정 속성만을 추출하여 별도의 Record 타입을 정의할 수 있다.
(구조체와 비슷함)-- 특정 속성만을 추출하여 레코드 타입 정의 -- 사용자가 만든 레코드 타입이며, 타입 이름은 CUSTOM_TYPE 이다. CUSTOM_TYPE IS RECORD ( CTP_NAME STUDENT.NAME%TYPE , CTIP_AGE STUDENT.AGE%TYPE , CTIP_CLASS STUDENT.CLASS%TYPE );-- CUSTOM_TYPE 타입으로 변수 선언 STUDENT_TP CUSTOM_TYPE;
레코드는 하나의 데이터 행만을 할당 받을 수 있다.
때문에WHERE STUDENT_ID = P_STUDENT_ID와 같은 조건이 필요할 수 있다.만약 여러행의 데이터를 레코드에 할당하면 아래와 같은 에러가 발생한다.
ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다.-- SQL문으로 트정 속성 값만을 추출하여 레코드에 할당 SELECT NAME, AGE, CLASS INTO STUDENT_TP --위에서 선언한 레코드 변수에 할당 FROM STUDENT WHERE STUDENT_ID = P_STUDENT_ID ;
✅ Cursor
▶︎ 일반적인 커서 사용
정석적인 Cursor 사용은 아래와 같다
1. 커서 정의
2. 커서 열기 - OPEN
3. 변수에 커서 할당하기 - FETCH
4. 사용이 끝난 커서 닫기 - CLOSE
DECLARE CURSOR CS_STUDENT -- 커서 정의 IS SELECT * FROM STUDENT; -- 정의한 커서에 데이터 기입 R_STUDENT STUDENT%ROWTYPE; -- 커서 데이터를 할당 받을 변수 선언 BEGIN OPEN CS_STUDENT; -- 커서 open LOOP FETCH CS_STUDENT INTO R_STUDENT; -- 커서 데이터를 변수에 할당 EXIT WHEN CS_STUDENT%NOTFOUND; -- 커서(CS_STUDENT)의 다음 값이 없으면 LOOP 나가기 -- 커서에 있는 데이터로 Logic 수행 dbms_output.put_line(R_STUDENT.NAME); dbms_output.put_line(R_STUDENT.AGE); END LOOP; CLOSE CS_STUDENT; -- 사용이 끝난 커서 닫기 END;
FOR문을 사용하면 보다 간편하게 Cursor를 사용할 수 있다.
DECLARE BEGIN FOR C1 IN (SELECT * FROM STUDENT) -- C1이라는 이름으로 커서 정의 (커서 open, fetch, close를 한 줄로 정의) LOOP -- 커서에 있는 데이터로 Logic 수행 dbms_output.put_line(C1.NAME); dbms_output.put_line(C1.AGE); END LOOP; END;
▶︎ 커서를 변수로 사용
Oracle PL/SQL은 커서를 변수처럼 사용할 수 있다.
(커서를 파라미터 등으로 사용하기 위해서는 변수로 선언해야 한다.)
이때 커서의 타입은 강한 타입 / 약한 타입으로 만들 수 있다.
강한 타임은 커서의 반환 타입을 고정해서 사용하는 방식이며, 약한 타입은 반환 타임을 정하지 않고 사용하는 방식이다.
강한 타입으로 커서 정의
DECLARE TYPE STUDENT_CURTP IS REF CURSOR RETURN STUDENT%ROWTYPE; -- 강한 타입으로 커서 타입 정의 STUDENT_CUR STUDENT_CURTP; -- 커서 타입의 변수 선언 R_STUDENT STUDENT%ROWTYPE; BEGIN OPEN STUDENT_CUR FOR SELECT * FROM STUDENT; LOOP FETCH STUDENT_CUR INTO R_STUDENT; EXIT WHEN STUDENT_CUR%NOTFOUND; -- 커서에 있는 데이터로 Logic 수행 dbms_output.put_line(R_STUDENT.NAME); END LOOP; CLOSE STUDENT_CUR; END;
약한 타입으로 커서 정의
DECLARE TYPE STUDENT_CURTP IS REF CURSOR; -- 강한 타입으로 커서 타입 정의 STUDENT_CUR STUDENT_CURTP; -- 커서 타입의 변수 선언 R_STUDENT STUDENT%ROWTYPE; BEGIN OPEN STUDENT_CUR FOR SELECT * FROM STUDENT; LOOP FETCH STUDENT_CUR INTO R_STUDENT; EXIT WHEN STUDENT_CUR%NOTFOUND; -- 커서에 있는 데이터로 Logic 수행 dbms_output.put_line(R_STUDENT.NAME); END LOOP; CLOSE STUDENT_CUR; END;
약한 타입으로 커서 정의_2
DECLARE STUDENT_CUR SYS_REFCURSOR; -- 오라클에서 재공하는 SYS_REFCURSOR 타입으로 정의 R_STUDENT STUDENT%ROWTYPE; BEGIN OPEN STUDENT_CUR FOR SELECT * FROM STUDENT; LOOP FETCH STUDENT_CUR INTO R_STUDENT; EXIT WHEN STUDENT_CUR%NOTFOUND; -- 커서에 있는 데이터로 Logic 수행 dbms_output.put_line(R_STUDENT.NAME); END LOOP; CLOSE STUDENT_CUR; END;
✅ Collection
PL/SQL에서는 주로 세 가지 유형의 컬렉션이 사용된다.
1. VARRAY - 크기가 고정되어 있는 가변배열
DECLARE TYPE BRAND IS VARRAY(3) OF VARCHAR2(64); SMARTPHONE BRAND := BRAND('삼성','애플','샤오미'); --배열 초기화 BEGIN FOR I IN 1..SMARTPHONE.COUNT --배열 인덱스는 1번부터 시작 LOOP DBMS_OUTPUT.PUT_LINE(SMARTPHONE(I)); END LOOP; --값을 추가할 때 SMARTPHONE.EXTEND; --배열 크기 1 증가 SMARTPHONE(SMARTPHONE.LAST) := 'LG스마트폰'; --배열 마지막에 값 추가 END;2. Nested Table - 크기가 고정되어 있지 않은 가변배열
DECLARE TYPE BRAND IS TABLE OF VARCHAR2(64); SMARTPHONE BRAND := BRAND('삼성','애플','샤오미'); --배열 초기화 BEGIN FOR I IN 1..SMARTPHONE.COUNT --배열 인덱스는 1번부터 시작 LOOP DBMS_OUTPUT.PUT_LINE(SMARTPHONE(I)); END LOOP; --값을 추가할 때 SMARTPHONE.EXTEND; --배열 크기 1 증가 SMARTPHONE(SMARTPHONE.LAST) := 'LG스마트폰'; --배열 마지막에 값 추가 END;3. Associative Array - Key와 Value가 짝을 이루는 데이터 집합
-- Associative Array DECLARE TYPE BRAND IS TABLE OF VARCHAR2(50) --value INDEX BY VARCHAR2(50); --key SMARTPHONE BRAND; --Associative Array 변수 선언 I VARCHAR2(50); BEGIN /************* key : 스마트폰리 value : 회사명 *************/ SMARTPHONE('갤럭시') := '삼성'; SMARTPHONE('아이폰') := '애플'; SMARTPHONE('포코') := '샤오미'; I := SMARTPHONE.first; while I is not null loop dbms_output.put_line(SMARTPHONE(I)); I := SMARTPHONE.next(I); -- 다음 키 접근 end loop; END;
▶︎ 배열에 레코드/커서를 넣어 사용
레코드와 커서를 사용한 방법은 아래와 같다.
for 문과 loop 문을 사용하여 커서 하나하나를 배열에 할당하는 방식이다.
커서의 데이터 행이 적을 때는 괜찮지만, 데이터 행의 값이 많은 경우(대략 1만 건 이상..) 성능적인 부분에서 문제가 발생할 수 있다.
DECLARE TYPE STUDENT_ARR_TP IS TABLE OF STUDENT%ROWTYPE; --배열 타입 정의(배열 내부에 들어갈 수 있는 데이터는 레코드 타입) STUDENT_ARR STUDENT_ARR_TP; --배열 변수 선언 BEGIN STUDENT_ARR := STUDENT_ARR_TP(); --배열 초기화 FOR C1 IN 1..STUDENT_ARR.COUNT LOOP STUDENT_ARR.EXTEND; --배열 확장 STUDENT_ARR(STUDENT_ARR.LAST) := C1; --배열에 1개 행의 커서 데이터 할당 END LOOP; END;
커서 데이터의 행이 많을 경우 bulk collect into를 사용하는 게 좋다.
(커서 데이터의 행이 정말 많은 경우 bulk collect into 사용 또한 시스템에 부담이 되기 때문에 limit을 사용하여 배열에 할당할 데이터 개수를 조절할 수 있다.)
DECLARE TYPE STUDENT_ARR_TP IS TABLE OF STUDENT%ROWTYPE; --배열 타입 정의(배열 내부에 들어갈 수 있는 데이터는 레코드 타입) STUDENT_ARR STUDENT_ARR_TP; --배열 변수 선언 BEGIN STUDENT_ARR := STUDENT_ARR_TP(); --배열 초기화 SELECT * BULK COLLECT INTO STUDENT_ARR --select 쿼리 결과를 한 번에 STUDENT_ARR 배열에 할당 FROM STUDENT ; END;
만약 SELECT * FROM STUDENT; 의 행 수가 100만 건 이상의 대용량 데이터일 경우 limit을 사용하여 일정 개수 단위만큼 bulk collect into 하는 것이 좋다.
DECLARE TYPE STUDENT_ARR_TP IS TABLE OF STUDENT%ROWTYPE; --배열 타입 정의(배열 내부에 들어갈 수 있는 데이터는 레코드 타입) STUDENT_ARR STUDENT_ARR_TP; --배열 변수 선언 STUDENT_CURSOR SYS_REFCURSOR; --커서 변수 선언 BEGIN STUDENT_ARR := STUDENT_ARR_TP(); --배열 초기화 OPEN STUDENT_CURSOR FOR SELECT * FROM STUDENT; LOOP EXIT WHEN STUDENT_CURSOR%NOTFOUND; FETCH STUDENT_CURSOR BULK COLLECT INTO STUDENT_ARR LIMIT 100; -- 행 100개를 배열에 할당 END LOOP; CLOSE STUDENT_CURSOR; END;
