커서
프로시저를 사용하다보면 데이터를 하나의 로우씩 처리해야하는 경우가 있다. 이 경우에 커서를 사용하면 여러개의 로우를 처리할 수 있다.
- 커서는 sql문장을 처리한 결과, private sql이라는 메모리 영역을 가리키는 포인터이다
- 커서를 이용하면 처리된 sql문장의 결과 집합에 접근 가능
- 개별로우에 순차적으로 접근 가능
- 묵시적 커서, 명시적 커서
- 커서열기 - fetch - 커서 닫기 (c언어 포인터와 유사)
- 묵시적 커서 :오라클 내부에 자동으로 생기는 커서(위 세가지 단계를 오라클에서 제어)
- 명시적 커서 :사용자가 직접 정의하여 사용하는 커서
묵시적 커서와 커서 속성
| 속성명 | 설명 |
|---|---|
| SQL%FOUND | 결과 집합의 패치로우 수가 1개 이상이면 TRUE, 아니면 FALSE |
| SQL%NOTFOUND | 결과 집합의 패치 로우 수가 0이면 TRUE, 아니면 FALSE |
| SQL%ROWCOUNT | 영향 받은 결과 집합의 로우 수 반환, 없으면 0을 반환 |
| SQL%ISOPEN | 묵시적 커서는 항상 FALSE를 반환 |
명시적 커서
- 커서 선언 - 커서 열기 - FETCH - 커서 닫기
-- 커서 선언
CURSOR 커서명[(매개변수1, 매개변수2, ...)]
IS
SELECT 문장;
-- 커서 열기
OPEN 커서명 [(매개변수1, 매개변수2, ...)]
-- 패치 단계에서 커서 사용
LOOP
FETCH 커서명 INTO 변수1, 변수2, ...;
EXIT WHEN 커서명%NOTFOUND;
END LOOP;
-- 커서 닫기
CLOSE 커서명;DECLARE
vs_emp_name employees.emp_name%TYPE;
CURSOR cur_emp_dep (cp_department_id employees.department_id%TYPE)
IS
SELECT emp_name
FROM employees
WHERE department_id = cp_department_id;
BEGIN
OPEN cur_emp_dep(90);
LOOP
FETCH cur_emp_dep INTO vs_emp_name;
EXIT WHEN cur_emp_dep%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vs_emp_name);
END LOOP;
CLOSE cur_emp_dep;
END;
- 커서와 loop문을 사용한 익명 블록이다.
- 커서를 열고 사용이 끝나면 필시 닫아야 한다.
- 프로시저가 끝나면 알아서 닫아주긴 하지만 pl/sql 블록이나 서브 프로그램을 실행하면서 닫히지 않는 커서를 닫는 작업은 오버헤드를 발생시킴
커서와 FOR문
FOR 레코드 IN 커서명(매개변수1, 매개변수2, ...)
LOOP
처리문;
END LOOP;--예제
DECLARE
CURSOR cur_emp_dep(cp_department_id employees.department_id%TYPE)
IS
SELECT emp_name
FROM employees
WHERE department_id = cp_department_id;
BEGIN
FOR emp_rec IN cur_emp_dep(100)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name);
END LOOP;
END;커서변수
- 명시적 커서는 변수가 아니라 커서 상수의 느낌이다. 한번 참조하면 정의한 내용만 참조할 수 있기 때문
- 변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달할 수 있다
- 커서 속성을 사용할 수 있다
- 한 개 이상의 쿼리를 연결하여 사용가능
TYPE 커서_타입명 IS REF CURSOR[ RETURN 반환 타입 ];
커서_변수명 커서_타입명;- 리턴타입 생략 - 약한 커서 타입 <-> 리턴타입 존재 - 강한 커서 타입
- 빌트인 커서 타입 test_cur SYS_REFCURSOR;
DECLARE
-- 사원명을 받아오기 위한 변수 선언
vs_emp_name employees.emp_name%TYPE;
-- 약한 커서 타입 선언
TYPE emp_dep_curtype IS REF CURSOR;
-- 커서 변수 선언
emp_dep_curvar emp_dep_curtype;
BEGIN
-- 커서 변수를 사용한 커서 정의 및 오픈
OPEN emp_dep_curvar FOR SELECT emp_name
FROM employees
WHERE department_id = 90;
-- LOOP문
LOOP
-- 커서 변수를 사용해 결과 집합을 vs_emp_name 변수에 할당
FETCH emp_dep_curvar INTO vs_emp_name;
-- 더 이상 패치된 참조 로우가 없으면 LOOP 탈출(커서 변수를 이용한 커서 속성 참조)
EXIT WHEN emp_dep_curvar%NOTFOUND;
-- 사원명을 출력
DBMS_OUTPUT.PUT_LINE(vs_emp_name);
END LOOP;
END;
/
DECLARE
-- 사원명을 받아오기 위한 변수 선언
vs_emp_name employees.emp_name%TYPE;
-- SYS_REFCURSOR 타입의 커서 변수 선언
emp_dep_curvar SYS_REFCURSOR;
BEGIN
-- 커서 변수를 사용한 커서 정의 및 오픈
OPEN emp_dep_curvar FOR SELECT emp_name
FROM employees
WHERE department_id = 100;
-- LOOP문
LOOP
-- 커서 변수를 사용해 결과 집합을 vs_emp_name 변수에 할당
FETCH emp_dep_curvar INTO vs_emp_name;
-- 더 이상 패치된 참조 로우가 없으면 LOOP 탈출(커서 변수를 이용한 커서 속성 참조)
EXIT WHEN emp_dep_curvar%NOTFOUND;
-- 사원명을 출력
DBMS_OUTPUT.PUT_LINE(vs_emp_name);
END LOOP;
END;커서 표현식
커서 표현식은 select문에서 컬럼으로 커서를 사용하는 것
-- 커서는 항상 한개의 로우를 반환할 수밖에 없음
SELECT d.department_name,
CURSOR (
SELECT e.emp_name
FROM employees e
WHERE e.department_id = d.department_id) AS emp_name
FROM departments d
WHERE d.department_id = 90;레코드, 컬렉션
데이터 타입의 변수는 한 번에 하나의 값만 설정하지만, 레코드와 컬렉션을 이용하면 여러개의 값을 설정하여 사용할 수 있다. (데이터 집합, 배열?과 같은 개념인거 같음)
레코드
- 문자형, 숫자형과 같은 기본 빌트인 타입이 아닌 복합 구조
- 테이블이 여러개의 컬럼을 갖고 각각의 컬럼은 서로 다른 데이터 타입을 선언해서 사용하는 것과 같이 레코드도 동일
- 커서 변수와 선언이 유사
사용자 정의형 레코드
TYPE 레코드명 IS RECORD(
필드명1 필드1 타입 [[NOT NULL] := 디폴트값],
필드명2 필드2 타입 [[NOT NULL] := 디폴트값],
);
레코드변수명 레코드명;- C언어 구조체와 유사한 구조이다.
DECLARE
-- 부서 레코드 타입
TYPE depart_rect IS RECORD(
department_id departments.department_id%TYPE,
department_name departments.department_name%TYPE
);
vr_dep depart_rect;
vr_dep2 depart_rect;
BEGIN
vr_dep.department_id := 000;
vr_dep.department_name := '레코드 테스트 부서';
vr_dep2 := vr_dep;
DBMS_OUTPUT.PUT_LINE( 'vr_dep2.department_id :' || vr_dep2.department_id);
DBMS_OUTPUT.PUT_LINE( 'vr_dep2.department_name :' || vr_dep2.department_name);
END;
- 레코드 변수끼리 할당은 레코드 변수의 타입(필드 순서, 구조, 데이터 타입)이 같아야 함
- 레코드 변수를 활용하여 Insert, Update도 할 수 있음
-- 레코드 필드 순서와 개수, 타입이 같다면 레코드 변수명으로만 insert가능
DECLARE
TYPE depart_rect IS RECORD(
department_id departments.department_id%TYPE,
department_name departments.department_name%TYPE
);
vr_dep depart_rect;
BEGIN
vr_dep.department_id := 9999;
vr_dep.department_name := '테스트';
-- insert into ch10_departments values (vr_dep.department_id, vr_dep.department_name);
insert into ch10_departments values vr_dep;
COMMIT;
END;
/
select * from ch10_departments;테이블형 레코드
-- 특정 테이블의 컬럼 값을 받아오는 변수를 선언할 때, 다음과 같이 선언하면 굳이 해당 컬럼의 데이터타입을 찾을 필요 없음
변수명 테이블명.컬럼명%TYPE;
-- 아래와 같이 하면 테이블 전체 데이터 타입을 레코드의 데이터 타입으로 주입가능
레코드 변수명 테이블명%ROWTYPE;-- 예제
CREATE TABLE ch11_dep2 AS
SELECT *
FROM DEPARTMENTS;
TRUNCATE TABLE ch11_dep2;
select * from ch11_dep2;
DECLARE
vr_dep departments%ROWTYPE;
BEGIN
SELECT *
INTO vr_dep
FROM departments
WHERE department_id = 20;
INSERT INTO ch11_dep2 VALUES vr_dep;
COMMIT;
END;
/
SELECT * FROM CH11_DEP2;커서형 레코드
- 커서를 레코드 변수로 받는 것을 커서형 레코드라 함
- 커서명%ROWTYPE
-- 예제
create table ch11_dep as
select department_id, department_name
from departments;
truncate TABLE ch11_dep;
DECLARE
CURSOR c1 IS
SELECT department_id, department_name
FROM departments;
-- 커서형 레코드 변수 선언
vr_dep c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO vr_dep;
EXIT WHEN c1%NOTFOUND;
INSERT INTO ch11_dep VALUES vr_dep;
END LOOP;
COMMIT;
END;
/
SELECT * FROM ch11_dep;-- 레코드 변수만 기술해서 타입이 같다면 열 전체 업데이트
DECLARE
vr_dep ch11_dep%ROWTYPE;
BEGIN
vr_dep.department_id := 60;
vr_dep.department_name := '테스트';
UPDATE ch11_dep
SET ROW = vr_dep
WHERE department_id = vr_dep.department_id;
COMMIT;
END;
/
select * from ch11_dep;컬렉션
하나의 종류의 데이터 타입이 여러 개 붙어 있는 데이터 타입 (배열)
- 사용법이 객체지향 프로그램 클래스와 유사
- 연관 배열
- VARRAY
- 중첩 테이블
연관 배열
- 키와 값으로 구성된 컬렉션으로 키는 인덱스라 함.
- INDEX-BY 테이블
- 전화번호부와 유사한 구조
TYPE 연관_배열명 IS TABLE OF 연관_배열_값타입 INDEX BY 인덱스타입;VARRAY
- 가변 길이 배열로서 연관 배열과는 달리 그 크기에 제한 있음
Grind