특정 SQL문장을 처리한 결과를 담고 있는 영역(PRIVATE SQL 메모리영역)을 가리키는 일종의 포인터로 커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할수있다.
묵시적 커서 : 오라클 내부에서 자동으로 생성되어 사용하는 커서
명시적 커서 : 사용자가 직접 정의해서 사용하는 커서
커서의 삶은 "커서열기(open) - 패치(fetch) - 커서닫기(close)"로 3단계로 진행이 된다.
명시적 커서는3단계를 선언해야 하지만 묵시적 커서는 3단계가 자동으로 처리된다.
위의 이미지처럼 SQL%ROWCOUNT라는 커서를 사용하여 해당 SQL문에서 처리된 결과 ROW수를 참조한것이다.
※커서의 속성
속성명
설명
SQL%FOUND
결과 집합의 패치 로우수가 1개이상이면 TRUE,아니면 FALSE
SQL%NOTFOUND
결과 집합의 패치로우 수가 0이면 TRUE 아니면 FALSE
SQL%ROWCOUNT
영향받은 결과 집합의 로우수 반환,없으면 0을 반환
SQL%ISOPEN
묵시적커서는 항상 FALSE를 반환 (이속성으로 참조할 때는 이미 해당 묵시적 커서는 닫힌 상태이후이기 때문이다.)
[명시적 커서에 대한 부분 정리]
커서선언
묵시적은 자동으로 되지만 명시적으로 커서를 선언할 경우 선언부에 직접적으로 정의하여 사용해야한다. -> CURSOR 커서명[(매개변수1,매개변수2....)]ISSELECT 문장;
커서 열기
커서를 선언한 후 해당 커서를 사용하려면 먼저 커서를 열어야 한다. ->OPEN 커서명[(매개변수1,매개변수2....)];
패치단계에서 커서 사용
정의한 커서를 열고 SELECT문의 결과로 반환되는 로우에 접근하여 개별로우로 접근한다. 개별로우로 접근하기 위해서는 반복문(FOR,LOOP등)을 사용하여 개별로우에 접근한다. ->LOOP FETCH 커서명 INTO 변수1,변수2......; EXIT WHEN 커서명%NOTFOUND; END LOOP;
커서 닫기
패치 작업이 끝나고 반복문을 빠져 나오면 커서 사용이 모두 끝났으므로 반드시 커서를 닫아야한다. ->CLOSE 커서명; ※프로시저가 종료되면 자동으로 커서가 닫힌다. 하지만 블록이나 서브 프로그램에서 실행할 경우에는 오버헤드를 발생시키므로 커서는 항상 명시적으로 닫아야 한다.
예를 들어보자
명시적 커서를 이용하여 데이터 추출
위의 이미지와 같이 커서를 open하면서 매개변수를 90으로 전달을 하면 loop문을 돌면서 커서결과를 보여준다.
[커서 응용]
※ FOR문에서 커서를 응용할 경우 아래와 같이 사용된다.
FOR 레코드 IN 커서명(매개변수1,매개변수2..)
LOOP
처리문;
END LOOP;
커서에서 FOR문을 사용할 경우
위의 이미지와 같이 커서를 사용할 경우 루프조건이 최종값 대신에 커서명이 들어가고, 인덱스 대신에 레코드가 위치한다는걸 알수 있다. 그리고 해당 커서가 끝나면 자동으로 루프가 종료된다.
[커서 변수 사용]
커서는 변수로 선언하여 사용할수 있는데 다음과 같은 특징이 있다.
한개이상의 쿼리를 연결해 사용할수 있다. 하나의 커서 변수를 선언해 쿼리를 연결하여 사용후, 같은 커서 변수를 다른 쿼리에 연결하여 사용가능
변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달할수 있다.
커서 속성을 사용할 수 있다. test_var란 커서 변수를 선언한 후 커서를 사용하면 test_var%FOUND, test_var%NOTFOUND형태로 커서 속성을 사용할수 있다.
커서변수를 사용하려면 아래와 같은 형식으로 사용해야한다.
커서 변수 선언하기 커서변수 선언하는 방법에는 두가지가 있다. 첫번째(강한녀석) : TYPE 커서_타입명 IS REF CURSOR [RETURN 반환타입]; ( TYPE TEST_CUR_TYPE IS REF CURSOR RETURN var_test%ROWTYPE; ) 두번째(약한녀석) : 커서_변수명 커서 타입명; (TYPE TEST_CUR_TYPE IS REF CURSOR;) 이렇게 2가지 방식이 있는데 첫번째 방식중 RETURN 반환타입은 해당 커서가 반환하는 결과 집합을 나타내며 "%ROWTYPE"속성을 사용하여 정의한다. (커서는 반환하는 결과값이 한개이상의 컬럼(레코드)이므로 %TYPE대신 %ROWTYPE을 사용한다.) 기타형식으로는 TEST_CUR SYS_REFCURSOR;이 있다.
커서 변수 사용하기 커서변수는 커서를 가리키므로 당연히 커서를 정의하는 쿼리가 있어야한다. - OPEN 커서변수명 FOR SELECT 문; 커서변수의 특징중 하나는 위와 같이 여러개의 쿼리를 연결하여 사용할수 있는 점이고, 여러개의 쿼리를 한 커서 변수에 할당해 사용할수 있다.
예)강한녀석으로 커서 선언했을경우 [강한 커서변수 선언] TYPE test_cur IS REF CURSOR RETURN var_test%ROWTYPE; var_test_cur test_cur; [선언한 강한녀석 커서문에 대해 사용] 정상 : OPEN var_test_cur FOR SELECT * FROM departments; >이미 커서 구조가 결정이 되었는데 안맞춰서 할경우 오류 발생 1.OPEN var_test_cur FOR SELECT department_id FROM departments; -- department_id만 선언하여 오류(구조X) 2. OPEN var_test_cur FOR SELECT *FROM employees; -- 전혀 다른 테이블을 참고하여 오류
예)약한 녀석으로 커서 선언한 경우(모두 정상 처리) [약한 커서변수 선언] TYPE test_cur IS REF CURSOR RETURN var_test%ROWTYPE; [선언한 약한녀석 커서문에 대해 사용시 모두가 정상처리] OPEN var_test_cur FOR SELECT * FROM departments; OPEN var_test_cur FOR SELECT department_id FROM departments; OPEN var_test_cur FOR SELECT *FROM employees;
커서 변수에서 결과 집합 가져오기 커서 변수에서 결과 집합을 가져오는 패치작업을 할때는 FETCH문을 사용한다. - FETCH 커서변수명 INTO 변수1,변수2...... - FETCH 커서변수명 INTO 레코드명; 정의결과에 따라 집합 개수에 따라 변수에 받아올수 있고, 레코드를 정의하여 받아올수 있다.그리고 커서 변수를 사용시에는 커서를 닫는 작업이 필요없다.커서 변수를 사용하여 실행
위의 이미지와 같이 커서 변수를 선언하여 커서변수에 결과를 할당하여 보여준다.
커서 변수를 매개변수로 전달 커서 변수도 일종의 매개변수로 사용자 정의 함수나 프로시저의 매개변수로 전달할수 있다. 여기서 중요한 점이 매개변수를 전달하여 사용하므로 전달하는쪽,받는쪽 모두 같은 커서 타입의 변수를 사용해야 한다는점이다. 커서변수를 매개변수로 전달하여 사용
위의 이미지와 같이 A~D까지 커서를 변수로 할당하여 매개변수로 전달하는 과정을 보여준다. [A] SYS_REFCURSOR커서변수를 선언 [B] 프로시저 하나를 만들어서 INOUT형식으로 커서를 정의 [C] 오픈한 커서를 매개변수에 IN,OUT으로 할당 [D] 프로시저 호출시 매개변수에 결과값을 전달하여 LOOP문을 사용해 결과를 출력
[커서 표현식]
왼쪽:커서 미사용 ,오른쪽:커서 사용
SELECT리스트에서 컬럼용으로 서브쿼리를 사용할때는 반드시 반환되는 로우는 한개여야 하지만 3명이 나오므로 오류가난다. 따라서 JOIN을 하여 결과를 출력해야 하는데 커서를 사용하면 문장형태와 비슷한 구문을 사용할수 있고 반환되는 로우 개수는 1개로 서브 쿼리문과 비슷하다.(JOIN시 한개만 나옴.)
