SQL%ROWCOUNT

SQL%ROWCOUNT

이걸로 프로시져에서 DBMS_OUTPUT.put_line을 통해 적용행수를 보여줄수 있을듯..

DBMS_OUTPUT.put_line은

sql plus에선

SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)

이거 해줘야 보임..

토드에선 아래쪽에서 DBMS Output선택하고..

왼쪽 빨간버튼 초록색으로 활성화 해놓으면 보임..ㅋㅋ

참고로 옛기억을 되살리면.. sql-server에선 @@rowcount 였던걸로 기억함..

SQL%ROWCOUNT : 가장 최근에 수행된 SQL 문에 의해 영향을 받은 행의 갯수 (정수 값)
SQL%FOUND : 가장 최근에 수행된 SQL 문에 의해 영향을 받은 행의 갯수가 한 개 이상이면 TURE가 되는 Boolean속성
SQL%NOTFOUND : 가장 최근에 수행된 SQL 문에 의해 영향을 받은 행이 없으면 TRUE가 되는 Boolean 속성
SQL%ISOPEN : PL/SQL은 실행한 후 바로 Implicit cursor를 닫기 때문에 항상 FALSE로 평가됨

SQL 커서 속성의 예 
S_ITEM 테이블에서 지정된 주문 번호를 갖는 행을 삭제하고 삭제된 행의 갯수를 출력한다.
　
CREATE OR REPLACE PROCEDURE del_rows

(v_ord_id IN NUMBER,
v_rows_deleted OUT VARCHAR2)
IS
BEGIN

DELETE FROM s_item

WHERE ord_id = v_ord_id;

v_rows_deleted:= TO_CHAR(SQL%ROWCOUNT)

|| 'rows deleted.';
END del_rows;

Oracle Document

 

Oracle allows you to access information about the most recently executed implicit cursor by referencing one of the following special implicit cursor attributes:
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN
Click on each of the above to learn more these attributes.
Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement.

First of all, remember that the values of implicit cursor attributes always refer to the most recently executed SQL statement, regardless of the block in which the implicit cursor is executed. And before Oracle opens the first SQL cursor in the session, all the implicit cursor attributes yield NULL. (The exception is %ISOPEN, which returns FALSE.)

Now let’s see how you can use cursor attributes with implicit cursors.

Use SQL%FOUND to determine if your DML statement affected any rows. For example, from time to time an author will change his name and want the new name used for all of his books. So you can create a small procedure to update the name and then report back via a Boolean variable the number of book entries affected:

CREATE OR REPLACE PROCEDURE change_author_name (
old_name_in IN books.author%TYPE,
new_name_in IN books.author%TYPE,
changes_made_out OUT BOOLEAN)
IS
BEGIN
UPDATE books
   SET author = new_name_in
 WHERE author = old_name_in;
changes_made_out := SQL%FOUND;
END;

Use SQL%NOTFOUND to confirm that your DML statement did not affect any rows. This is the inverse of SQL%FOUND.

Use SQL%ROWCOUNT when you need to know exactly how many rows were affected by your DML statement. Here is a reworking of the above name-change procedure that returns a bit more information:

CREATE OR REPLACE PROCEDURE change_author_name (
old_name_in IN books.author%TYPE,
new_name_in IN books.author%TYPE,
rename_count_out OUT PLS_INTEGER)
IS
BEGIN
UPDATE books
   SET author = new_name_in
 WHERE author = old_name_in;
rename_count_out := SQL%ROWCOUNT;
END;