커서 ( Cursor )
커서 (Cursor)
- SQL문을 실행하기 위한 메모리 공간
-> 특정 SQL 문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터
- 커서를 사용하면 처리된 SQL 문장
- 기존에 사용한 SQL 문은 묵시적 커서를 사용
종류
1. 묵시적(암시적) 커서
1) 오라클 내부에서 자동으로 생성되어 사용하는 커서
2) PL/SQL 블록에서 실행되는 문장
3) ( INSERT, UPDATE, MERGE, DELETE, SELECT INTO )가 실행될 때마다 자동으로 만들어져 사용 됌
4) 개발자 입장에서는 이러한 커서의 동작에 관여할 수 없음
5) 커서 속성을 이용하면 해당 커서에 대한 여러가지 정보를 알아낼 수 있음
* 묵시적 커서 속성
1) SQL%FOUND : 결과 집합의 패치 로우 수가 1개 이상이면 TRUE, 아니면 FALSE
2) SQL%NOTFOUND : 결과 집합의 패치 ROW수가 0이면 TRUE, 아니면 FALSE
3) SQL%ROWCOUNT : 영향 받은 결과 집합의 ROW 수 반환, 없으면 0 반환
4) SQL%ISOPEN : 묵시적 커서는 항상 FALSE를 반환
(이 속성으로 참조할 때는 이미 해당 묵시적 커서는 닫힌 상태 이후이기 때문)
예제
DECLARE
V_dept_id emp.dept_id%type := 80;
BEGIN
UPDATE emp SET ename = ename;
WHERE dept_id = V_dept_id;
DBMS_OUTPUT.PUT.LINE(SQL%ROWCOUNT); -> 몇 건의 데이터가 갱신 되었는지 출력
COMMIT;
END;
/
2. 명시적 커서
1) 사용자가 직접 정의해서 사용하는 커서
2) 사용할 커서에 이름을 부여하고 이 커서에 대한 쿼리를 선언해야 함
3) 명시적 커서는 결과 데이터 집합을 ROW별로 참조해서 어떠한 작업을 하기 위한 용도이므로
커서를 정의해 사용하는 문장은 SELECT이다.
4) SELECT 결과 여러건을 TABLE 타입의 변수에 저장할 수 있지만 메모리는 한정적이기 때문에
많은 양의 데이터를 담기에는 제한이 따름
-> SQL 커서를 통해 개발자가 직접 데이터를 FETCH함으로써 SELECT 결과를 전부 불러오지 않고도 개발이 가능
- 커서 선언 방법
DECLARE -> 선언부에서 선언
CURSOR 커서이름 IS
제어할 쿼리;
- 커서 열기
BEGIN -> 실행부에서 커서 열기
OPEN 커서이름;
- 커서로부터 데이터 FETCH
FETCH 커서이름 INTO 변수; -> 위 실행부의 커서 열기에 이어서
- 커서 닫기
CLOSE 커서이름; -> 실행부에서 커서 닫기 까지
실습
- 부서 테이블을 활용하여 모든 행에 대해 부서번호와 부서 이름을 cursor를 통해 FETCH하고 결과 확인
SET SERVEROUTPUT ON;
DECLARE
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
CURSOR dept_cursor IS
SELECT deptno, dname
FROM dept;
BEGIN
OPEN dept_cursor;
LOOP
FETCH dept_cursor INTO v_deptno, v_dname;
EXIT WHEN dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_deptno || ' : ' || v_dname);
END LOOP;
END;
/
※ 커서를 열고 닫는 과정이 다소 길기 때문에 Cursor는 일반적으로 loop와 함께 사용함
=> 명시적 커서를 FOR LOOP에서 사용할 수 있게 끔 문법으로 제공
* JAVA의 향상된 FOR문과 유사한 형태
JAVA
for(String UserName : userNameList){
UserName 값을 사용..;
}
PL/SQL
FOR record_name(한 행의 정보를 담을 변수이름 / 변수를 직접 선언안함) IN 커서이름 LOOP
record_name.컬럼명
END LOOP;
실습
DECLARE
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
CURSOR dept_cursor IS
SELECT deptno, dname
FROM dept;
BEGIN
FOR rec IN dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE(rec.deptno || ' : ' || rec.dname);
END LOOP;
END;
/
인자가 있는 커서
기존의 커서 선언 방법
CURSOR 커서이름 IS
서브쿼리..;
인자가 있는 커서 선언 방법
CURSOR 커서이름(인자1 인자1 타입, 인자2 인자2 타입, ...) IS
서브쿼리;
(커서 선언시에 작성한 인자를 서브쿼리에서 사용할 수 있다.)
실습
DECLARE
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
CURSOR dept_cursor(p_deptno dept.deptno%TYPE) IS
SELECT deptno, dname
FROM dept
WHERE deptno <= p_deptno;
BEGIN
FOR rec IN dept_cursor(:deptno) LOOP
DBMS_OUTPUT.PUT_LINE(rec.deptno || ' : ' || rec.dname);
END LOOP;
END;
/
더 간단하게 FOR LOOP 에서 커서를 인라인 형태로 작성
FOR 레코드 이름 IN 커서이름
==>
FOR 레코드 이름 IN (서브쿼리);
실습
DECLARE
BEGIN
FOR rec IN (SELECT deptno, dname FROM dept) LOOP
DBMS_OUTPUT.PUT_LINE(rec.deptno || ' : ' || rec.dname);
END LOOP;
END;
/
심화실습
CREATE TABLE DT
( DT DATE);
insert into dt
select trunc(sysdate + 10) from dual union all
select trunc(sysdate + 5) from dual union all
select trunc(sysdate) from dual union all
select trunc(sysdate - 5) from dual union all
select trunc(sysdate - 10) from dual union all
select trunc(sysdate - 15) from dual union all
select trunc(sysdate - 20) from dual union all
select trunc(sysdate - 25) from dual;
commit;
SELECT *
FROM dt;
위의 테이블 데이터들의 평균차이값을 구해라
DECLARE
TYPE dt_tab IS TABLE OF dt%ROWTYPE INDEX BY BINARY_INTEGER;
v_dt_tab dt_tab;
v_sum number(5) := 0;
v_avg number(5) := 0;
BEGIN
SELECT * BULK COLLECT INTO v_dt_tab
FROM dt;
FOR i IN 1..(v_dt_tab.count-1) LOOP
v_sum := v_sum + TO_NUMBER(TO_CHAR(v_dt_tab(i).dt - v_dt_tab(i+1).dt));
DBMS_OUTPUT.PUT_LINE(v_sum);
END LOOP;
v_avg := v_sum/(v_dt_tab.count-1);
DBMS_OUTPUT.PUT_LINE(v_avg || ' , '|| v_sum);
END;
/
위의 쿼리를 분석함수를 써서 SQL로 작성
SELECT avg(gap)
from
(
SELECT DT, lead_dt, dt-lead_dt GAP
FROM
(
SELECT dt,
LEAD(DT) OVER(ORDER BY DT DESC) lead_dt
FROM dt));
SELECT (MAX(dt) - MIN(dt)) / (COUNT(dt)-1) avg
FROM dt;