PLSQL05_CURSOR
CURSOR
1. 개념
- 사용자(User)가 SQL을 전송하면 Oracle DBMS는 해당 SQL을 실행하기 위한 메모리 공간이 필요하다.
- Rows
- Shared Pool에 저장된 Parsed Query에 대한 포인터
- Context Area : 할당된 영역, Cursor는 해당 영역을 가르키는 포인터나 해당 영역을 제어하기 위한 핸들러의 의미로 사용됨
2. 종류
2-1. 암시적 커서
- 자동적
- 실행부에서 사용되는 모든 커서는 암시적 커서
- PL/SQL 블록 내에서 사용되는 모든 DML 명령어와 SELECT는 암시적 커서를 통해 처리된다.
- 4단계로 처리
DECLARE
->OPEN
->FETCH
->CLOSE
- 암시적 커서는 개발자가 아닌 Oracle DBMS에서 자동으로 처리.
2-2. 명시적 커서
- 수동적으로
- 다중행을 조회하여 데이터를 처리하려할때 사용.
DECLARE
CURSOR CUR_EMP IS
SELECT EMPNO, JOB, SAL, COMM FROM EMP WHERE DEPTNO = 10;
-- 부서번호가 10인 직원에 커서생성
V_ENAME VARCHAR2(10);
V_JOB VARCHAR2(9);
V_SAL NUMBER(7,2);
V_COMM NUMBER(7,2);
BEGIN
OPEN CUR_EMP;
--커서 열기 : sql Execute한 후, ResultSet 생성.
LOOP
FETCH CUR_EMP INTO V_ENAME, V_JOB, V_SAL, V_COMM;
--선언한 변수에 커서에 잡힌 데이터 야금야금 가져옴
EXIT WHEN CUR_EMP%NOTFOUND;
-- 다음 레코드가 없으면 loop빠져나감.
INSERT INTO BONUS(ENAME, JOB, SAL, COMM)
VALUES(V_ENAME, V_JOB, V_SAL, V_COMM);
--db에 넣어줌
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL '||TO_CHAR(CUR_EMP%ROWCOUNT)||'rows precessed');
--몇개 수행했는지
CLOSE CUR_EMP;
--커서 닫기
COMMIT;
END;
/
CUR_EMP%ROWCOUNT
: 처리된 행의 수 보여줌.- commit을 LOOP문 안에 넣었을 때와 LOOP문 밖에두었을 때 성능차이는?
- JDBC,JSP로 해당 로직을 처리하는 것과 PLSQL을 사용했을 때의 성능차이는?
- PLSQL을 사용하는 것이 이득임!
- Fetch는 1Row단위로 수행한다. 1000만건의 대량 데이터를 다룰 때 어떻게 성능을 줄일까?
Bulk Binding
을 사용하면 한번에 여러개를 빠르게 Fetch할 수 있다.Array Processing
,Bulk Collect
3. 커서 속성
커서속성자 | IMPLICIT CURSOR | EXPLICIT CURSOR |
---|---|---|
%ROWCOUNT | SQL문에의해 영향을 받은 ROW총 갯수 | FETCH된 누적 갯수 |
%FOUND | SQL문에 의해 영향을 받은 ROW 존재유무 ( TRUE / FALSE )리턴 |
현재 FETCH된 ROW 존재유무 ( TRUE / FALSE )리턴 |
%NOTFOUND | FOUND의 반대값 | FOUND의 반대값 |
%ISOPEN | 항상 FALSE | CURSORDML Open상태 확인 |
3-1. 예제1
BEGIN
DELETE FROM EMP WHERE SAL > 2000;
DBMS_OUTPUT.PUT_LINE('[1-DELETE]'||TO_CHAR(SQL%ROWCOUNT)||'ROW IS DETETED');
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('SQL%FOUND = TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND = FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');
DELETE FROM EMP WHERE SAL >2000;
DBMS_OUTPUT.PUT_LINE('[2-DELETE]'||TO_CHAR(SQL%ROWCOUNT)||'ROW IS DELETED');
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('SQL%FOUND =TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND = FALSE');
END IF;
ROLLBACK;
END;
/
------>[64초]
[1-DELETE]4ROW IS DETETED
SQL%FOUND = TRUE
-------------------------------------------------------
[2-DELETE]0ROW IS DELETED
SQL%NOTFOUND = FALSE
PL/SQL 프로시저가 성공적으로 완료되었습니다.
3-2. 예제2 : 커서 간단하게 정의하기
- 1단계
DECLARE
CURSOR CUR_EMP IS
SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE DEPTNO = 10;
R_CUR_EMP CUR_EMP%ROWTYPE; -- 전체를 한 변수에 담아서 선언하기도 가능.
BEGIN
OPEN CUR_EMP;
LOOP
FETCH CUR_EMP INTO R_CUR_EMP;
EXIT WHEN CUR_EMP%NOTFOUND;
INSERT INTO BONUS(ENAME, JOB, SAL, COMM)
VALUES(R_CUR_EMP.ENAME, R_CUR_EMP.JOB, R_CUR_EMP.SAL, R_CUR_EMP.COMM);
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL '||TO_CHAR(CUR_EMP%ROWCOUNT)||'rows precessed');
CLOSE CUR_EMP;
COMMIT;
END;
/
- 2단계
- FOR LOOP에서는 OPEN, FETCH, CLOSE의 과정이 자동으로 이루어짐.
DBMS_OUTPUT.PUT_LINE('TOTAL '||TO_CHAR(CUR_EMP%ROWCOUNT)||'rows precessed')
를 주석해제하면 안돌아간다!- 이유 : FOR문에서 커서가 자동으로 닫혔기 때문에 CUR_EMP%ROWCOUNT는 실행되지 않는다.
DECLARE
CURSOR CUR_EMP IS
SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE DEPTNO = 10;
BEGIN
FOR R_CUR_EMP IN CUR_EMP
LOOP
INSERT INTO BONUS(ENAME, JOB, SAL, COMM)
VALUES(R_CUR_EMP.ENAME, R_CUR_EMP.JOB, R_CUR_EMP.SAL, R_CUR_EMP.COMM);
END LOOP;
--DBMS_OUTPUT.PUT_LINE('TOTAL '||TO_CHAR(CUR_EMP%ROWCOUNT)||'rows precessed');
-- FOR문에서 커서가 자동으로 닫혔기 때문에 CUR_EMP%ROWCOUNT는 실행되지 않는다.
COMMIT;
END;
/
- 3단계
- 커서의 정의를 DECLARE에서 하지 않고 IN절에서 정의하여 사용했기 때문에 코드가 간결해짐
- 커서를 여러개 사용하게되면 선언부에 커서가 많아져서 가독성이 떨어짐.
- 재사용되는 커서는 선언을 하는 것이 좋다!
BEGIN
FOR R_CUR_EMP IN (SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE DEPTNO=10)
LOOP
INSERT INTO BONUS(ENAME, JOB, SAL, COMM)
VALUES(R_CUR_EMP.ENAME, R_CUR_EMP.JOB, R_CUR_EMP.SAL, R_CUR_EMP.COMM);
END LOOP;
COMMIT;
END;
/
4. 커서 파라미터
- PL/SQL에서는
parameter Mode
를 지정해준다.- IN mode :
default
값을 모듈에 넣어주는 것 - OUT mode : 값을 모듈 밖으로 꺼내는 것
- IN/OUT mode : 값을 모듈에 넣고, 바꿔서 모듈 밖으로 꺼내는 것
- IN mode :
- 커서 프로세스
Define
- DECLARE
Open
- Bind Variables(변수를 대입)
- Execute SQL(ResultSet)
Fetch
Close
DECLARE
CURSOR CUR_EMP(P_DEPTNO IN NUMBER) IS
SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE DEPTNO = P_DEPTNO;
V_DEPTNO DEPT.DEPTNO%TYPE;
BEGIN
V_DEPTNO := 20;
FOR R_CUR_EMP IN CUR_EMP(V_DEPTNO)
LOOP
INSERT INTO BONUS(ENAME, JOB, SAL, COMM)
VALUES(R_CUR_EMP.ENAME, R_CUR_EMP.JOB, R_CUR_EMP.SAL, R_CUR_EMP.COMM);
END LOOP;
COMMIT;
END;
/