21장 커서(CORSOR)_처리 결과가 여러 개의 행으로 구해지는 SELECT문 설명 및 예제(SQL 오라클)

안녕하세요 JJU_DEVELOPER입니다.

이번 시간에는 21강 이어서 COURSOR에 대해 설명드리겠습니다.

 

✔ 처리 결과가 여러 개의 행으로 구해지는 SELECT 문

대부분의 SELECT문은 수행 후 반환되는 행의 개수가 한 개 이상입니다.

처리 결과가 여러 행으로 구하려면 SELECT문을 처리하면서 커서를 이용해야 합니다.

커서는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위해서

테이블의 행을 순서대로 가리키는 데 사용합니다.

 

✔ 커서와 관련된 키워드

키워드	역할
CURSOR <CURSOR이름> IS <커서 정의>	커서를 생성	-- 형식 CURSOR cursor_name IS select_statement; -- 예 CURSOR C1 IS SELECT * FROM DEPT;
OPEN <CURSOR이름>	커서의 사용을 시작	-- 형식 OPEN cursor_name; -- 예 OPEN c1;
FETCH <CURSOR이름> INTO <변수>	한 행의 데이터를 가져옴  FETCH 문은 결과 셋에서 로우 단위로 데이터를 읽어 들인다. 각 인출(FETCH) 후에 CURSOR는 결과 셋에서 다음 행으로 이동한다.	-- 형식 FETCH cursor_name INTO {variable1[,variable2, . . . .]}; -- 예 LOOP   FETCH C1 INTO VDEPT.DEPTNO,   VDEPT.DNAME, VDEPT.LOC;     EXIT WHEN C1%NOTFOUND; END LOOP;
CLOSE <CURSOR이름>	커서의 사용을 끝냄	-- 형식 CLOSE cursor_name;

 

 

✔ 커서의 형식과 개념

-- 형식
DECLARE 
	CURSOR cursor_name IS statement; -- 커서 선언
BEGIN 
	OPEN cursor_name; -- 커서 열기
	FECTCH cur_name INTO variable_name; --커서로부터 데이터를 읽어와 변수에 저장
	CLOSE cursor_name; --커서 닫기
END;

정의한 커서를 열었으면 패치단계에서 커서를 사용하면 됩니다.

커서는 SQL 결과 집합이 여러 개 이상에서 순차적으로 접근하기 위해 사용하는 방식이므로 거의 필수적으로 LOOP를 비롯한 반복문과 같이 사용하게 됩니다

 

<커서 선언>

CURSOR C1 IS
SELECT * FROM DEPT;

<패치 선언>

FETCH C1 INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
EXIT WHEN C1%NOTFOUND;

패치 커서 C1을 뒤에 있는 변수에 담습니다.

만약 커서 C1이 더 이상 없으면 exit 합니다.

✔ 커서의 상태

오라클 내부에서 PL_SQL(INSERT, UPDATE, DELETE 등등.) 실행 시 자동으로 생성되어 사용된다.
이러한 커서를 아래와 같이 사용할 수 있습니다.

함수로 치면 내장 함수라고 생각하면 됩니다.

속성	의미
%NOTFOUND	커서 영역의 자료가 모두 FETCH됐었다면 TRUE
%FOUND	커서 영역에 FETCH 되지 않은 자료가 있다면 TRUE 해당 SQL문에 의해 반환된 총 행수가 1개 이상일 경우TRUE (BOOLEAN)
%ISOPEN	커서가 OPEN된 상태이면 TRUE   *항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색 (PL/SQL은 실행 후 바로 묵시적 커서를 닫기 때문에 항상 false)
%ROWCOUNT	커서가 얻어 온 레코드의 개수 해당 SQL문에 의해 반환된 총 행수, 가장 최근 수행된 SQL문에 의해 영향을 받은 행의 갯수(정수)

 

 

✔ [실습]  커서를 이용한 부서 테이블의 모든 내용을 조회하기

 

1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력한다. (실습파일: PROC05.SQL)

(레코드 타입은 자바의 클래스형과 유사합니다.

클래스는 필드들이 있고 그 필드의 형이 다 다릅니다. 인트형, 문자형 등등)

 

<커서를 활용한 PROCEDURE 생성하기>

--커서를 이용한 부서 테이블의 모든 내용 조회하기

CREATE OR REPLACE PROCEDURE CURSOR_SAMPME01
IS
    VDEPT DEPT%ROWTYPE;
    --레코드 타입입니다
    --레코드 타입은 각 열마다 타입이 다릅니다.
    
    --커서 명시정의
    CURSOR C1 IS
    SELECT * FROM DEPT;
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------');
    --명시적 커서 오픈
    OPEN C1;
    
    LOOP
    --패치로 하나씩 가져옵니다.
        FETCH C1 INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
        -- 가져온 값을 정의한 레코드 로컬변수에 담습니다.
        EXIT WHEN C1%NOTFOUND;
        --더이상 읽을 것이 없으면 EXIT합니다.
        
        --만약 데이터가 있다면 아래 문장을 출력합니다.
        DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||'   '||VDEPT.DNAME||'  '||VDEPT.LOC);
                
    END LOOP;
    
    CLOSE C1;
    --커서 닫기

END;
/

Procedure CURSOR_SAMPME01이(가) 컴파일되었습니다.

 

<실행문>

SET SERVEROUT ON
EXECUTE CURSOR_SAMPME01;

<결과>

-> 단순한 루프를 쓰고 패치로 커서를 하나씩 이동하면서 출력해 보았습니다.

 

이번에는 FOR LOOP를 활용하여 만들어 보겠습니다.

 

 

 

 

✔ CURSOR와 FOR LOOP

그냥 LOOP를 쓰게 되면 OPEN과 CLOSE를 해주는데

CURSOR FOR LOOP는 명시적 CURSOR에서 행을 처리합니다. 

FOR LOOP에서 각 반복마다 알아서 CURSOR를 열고 행을 인출(FETCH)하고

모든 행이 처리되면 자동으로 CURSOR가 CLOSE 되므로 사용하기가 편리합니다.

 

OPEN, FETCH, CLOSE를 생략합니다.

 

-- 형식
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . . . . .
END LOOP

 

 

✔ CURSOR와 FOR LOOP 예제

[실습] 부서 테이블의 모든 내용을 조회하기 (CURSOR와 FOR LOOP)

 

--CURSOR와 FOR LOOP 예제

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE02
IS
    VDEPT DEPT%ROWTYPE;
    CURSOR C1
IS
    SELECT * FROM DEPT;
BEGIN
   -- 실행 1. 화면 출력
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
    
    -- 실행 2. FOR + 변수 IN 커서 + 루프
    FOR VDEPT IN C1 LOOP
    --알아서 OPEN해서 C1에 담습니다.
        EXIT WHEN C1%NOTFOUND;
        --없으면 자동종료
        
        --있으면 아래 내용 출력=> 레코드 변수 데이터를 가져옵니다.
        DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||
        ' '||VDEPT.DNAME||' '||VDEPT.LOC);
                
    END LOOP;
    
END;
/

--실행문
EXECUTE CURSOR_SAMPLE02;

 

<결과>

 

 

 

 

 

그럼 지금까지 커서에 대한 설명이었습니다.

 

감사합니다.

 

 

지난 시간- 저장함수, PL/SQL

21장 저장 프로시저, 저장 함수, 커서, 트리거 개념 및 예제 (SQL 오라클)

 

20장 PL/SQL 기초 (SQL 오라클)