Oracle에서 커서를 사용하다 보면 커서 속성인 ROWCOUNT를 사용할 수 있습니다. 그렇지만 ROWCOUNT의 조회 범위나 횟수의 정확성에 대해서 좀 더 이해하려면 ROWCOUNT가 어떤 커서 속성인지 정확하게 이해해야 합니다.
1. 커서 속성에서 ROWCOUNT 속성
Oracle 10g 문서에서 아래와 같은 내용이 있습니다.
%ROWCOUNT Attribute: How Many Rows Affected So Far?
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. In the following example, you use %ROWCOUNT to take action if more than ten rows have been deleted:
DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN -- more than 10 rows were deleted
...
END IF;
If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
ROWCOUNT는 SELECT INTO, INSERT, UPDATE, DELETE문은 영향을 받은 ROW의 개수를 반환합니다.
만약 해당하는 행이 없다면 ROWCOUNT는 0이 됩니다.
그런데 SELECT INTO문에서 2개 이상의 행이 반환되면 TOO_MANY_ROWS Exception이 발생합니다. 그리고 ROWCOUNT는 1을 반환합니다.
%ROWCOUNT Attribute: How Many Rows Fetched So Far?
A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.
커서가 오픈되면 ROWCOUNT는 0이 됩니다.
FETCH 전에는 cursor_name%ROWCOUNT는 0을 반환합니다. 그리고 FETCH 중에는 지금까지 FETCH 한 ROW의 수를 저장하게 됩니다.
2. 묵시적(implicit) 커서에서 ROWCOUNT
묵시적 커서에서는 커서 속성 앞에 커서명 대신 SQL을 붙여서 사용합니다.
즉 ROWCOUNT를 조회하려면 SQL%ROWCOUNT로 접근하면 됩니다.
만약 다수의 행을 조회하려고 하면 INTO절에서 TOO_MANY_ROWS Exeption을 발생하므로 네임드 커서를 사용하여 Fetch 해야 합니다.
DECLARE
vs_emp_name employees.emp_name%TYPE;
BEGIN
SELECT emp_name
INTO vs_emp_name
FROM employees;
DBMS_OUTPUT.PUT_LINE('Implicit ROWCOUNT: ' || SQL%ROWCOUNT);
EXCEPTION
-- ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
-- exact fetch returns more than requested number of rows
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS - Implicit ROWCOUNT: ' || SQL%ROWCOUNT);
END;
-- 위 PL/SQL 실행 결과
TOO MANY ROWS - Implicit ROWCOUNT: 1
만약 EXCEPTION을 발생시키고 싶지 않다면 해당 쿼리의 결과의 ROW가 1개가 되어야 합니다.
조회한 결과의 ROW가 0이 되면 NO_DATA_FOUND Exception이 발생합니다.
DECLARE
vs_emp_name employees.emp_name%TYPE;
BEGIN
SELECT emp_name
INTO vs_emp_name
FROM employees
WHERE emp_name = '1';
DBMS_OUTPUT.PUT_LINE('Implicit ROWCOUNT: ' || SQL%ROWCOUNT);
EXCEPTION
-- ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
-- exact fetch returns more than requested number of rows
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS - Implicit ROWCOUNT: '
|| SQL%ROWCOUNT);
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND - Implicit ROWCOUNT: '
|| SQL%ROWCOUNT);
END;
-- 위 PL/SQL 실행 결과
NO DATA FOUND - Implicit ROWCOUNT: 0
만약 NO_DATA_FOUND Exception을 발생하지 않고 위 문제를 다루는 방법은 아래에 있습니다.
https://dev-handbook.tistory.com/17
[Oracle] ORA-01403: 데이터를 찾을 수 없습니다. NO DATA FOUND 에러 해결 방법 3가지
PL/SQL을 작성하면서 볼 수 있는 에러입니다. PL/SQL 내에서 SELECT문을 사용하여 테이블에서 어떤 조건에 맞는 데이터를 검색해 INTO 절을 사용하여 데이터를 입력하려 할 때, 조건에 맞는 데이터가
dev-handbook.tistory.com
3. 이름있는 커서(named cursor)에서 ROWCOUNT
이름있는 커서는 명시적 커서(explicit cursor)와 커서 변수(cursor variable)가 있습니다. 커서 변수와 명시적 커서를 사용하면 여러 행을 조회하여 FETCH를 하면서 각 ROW에 대한 처리를 수행할 수 있습니다.
간단하게 %NOTFOUND 커서 속성을 사용하여 LOOP 안에서 처리하곤 합니다만 처음부터 SELECT 한 ROW의 수를 알고 싶어 하는 경우가 많습니다. 그러나 커서를 OPEN 해야만 ROWCOUNT 커서 속성에 접근이 가능하며, SELECT의 경우 FETCH를 해야만 %ROWCOUNT가 올라가게 됩니다.
-- 명시적 커서에서 ROWCOUNT
DECLARE
CURSOR my_cur IS
SELECT emp_name
FROM employees
WHERE emp_name LIKE 'C%';
vs_emp_name employees.emp_name%TYPE;
BEGIN
-- ORA-01001: 커서가 부적합합니다
-- invalid cursor
-- DBMS_OUTPUT.PUT_LINE('Explicit ROWCOUNT: ' || my_cur%ROWCOUNT);
OPEN my_cur;
DBMS_OUTPUT.PUT_LINE('Explicit ROWCOUNT: ' || my_cur%ROWCOUNT);
LOOP
FETCH my_cur INTO vs_emp_name;
EXIT WHEN my_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ROWCOUNT: ' || my_cur%ROWCOUNT);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Explicit ROWCOUNT: ' || my_cur%ROWCOUNT);
CLOSE my_cur;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND - Explicit ROWCOUNT: '
|| my_cur%ROWCOUNT);
END;
-- 위 PL/SQL문 실행 결과
Explicit ROWCOUNT: 0
ROWCOUNT: 1
ROWCOUNT: 2
ROWCOUNT: 3
ROWCOUNT: 4
Explicit ROWCOUNT: 4
위의 코드를 살펴보면 커서를 OPEN 하기 전에 커서 속성을 조회하면 ORA-01001: 커서가 부적합합니다. "invalid cursor" 에러가 발생합니다. ROWCOUNT 커서 속성에 접근하려면 먼저 커서를 OPEN 해야 합니다.
그리고 FETCH를 하기 전에는 ROWCOUNT는 0입니다.
FETCH를 하면서 1씩 증가하고, NOTFOUND까지 수행하고 나면 전체 ROW의 수가 ROWCOUNT에 저장됩니다.
-- CURSOR를 사용하여 전체 ROW수 카운트
...
OPEN my_cur;
LOOP
FETCH my_cur INTO vs_emp_name;
EXIT WHEN my_cur%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Explicit ROWCOUNT: ' || my_cur%ROWCOUNT);
CLOSE my_cur;
...
만약 커서를 이용해서 작업 전에 전체 ROW의 수를 알고 싶다면 임의로 FETCH를 하는 수밖에 없습니다.
SELECT COUNT() ... 문을 사용하는 것이 불가능하다면 위의 방법으로 조회는 가능합니다.
4. INSERT, UDPATE, DELETE에서 ROWCOUNT
INSERT, UPDATE, DELETE문에서 ROWCOUNT를 사용하여 적용한 ROW의 수를 파악하려면 아래와 같습니다.
이들 DML에서는 실행 시 동작한 ROWCOUNT가 계속 갱신이 되므로, LOOP에서 적용한 ROW 수를 파악하려면 별도의 변수에 값을 누적해야 합니다.
DECLARE
cnt NUMBER:= 0;
BEGIN
FOR i IN 1 .. 10
LOOP
INSERT INTO employees (id)
VALUES(i);
cnt := cnt + SQL%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('INSERT COUNT: ' || cnt);
END;
좀 더 상세한 내역이나 부가적인 설명은, 영어의 압박이 있겠지만 (^-^;), 아래 참고 문헌을 보시기 바랍니다.
참고
Oracle 10g Doc
https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems011.htm
https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/06_ora.htm#sthref802
Stack overflow
https://stackoverflow.com/questions/41230578/count-the-number-of-records-in-a-oracle-cursor
https://stackoverflow.com/questions/42006406/oracle-sqlrowcount-doesnt-work-inside-a-cursor-fetch
SELECT COUNT(*) vs. fetching twice with an explicit cursor : 추천
https://stackoverflow.com/questions/297671/select-count-vs-fetching-twice-with-an-explicit-cursor
댓글