sql%rowcount macro : Cursor ROWCOUNT « Cursor « Oracle PL / SQL






sql%rowcount macro

 
SQL>
SQL> create table myTable2 ( cnt int );

Table created.

SQL>
SQL> insert into myTable2 values ( 0 );

1 row created.

SQL>
SQL> create table t ( x int check ( x>0 ) );

Table created.

SQL>
SQL> create trigger t_trigger before insert or delete on t for each row
  2  begin
  3     if ( inserting ) then
  4          update myTable2 set cnt = cnt +1;
  5     else
  6          update myTable2 set cnt = cnt -1;
  7     end if;
  8     dbms_output.put_line( 'fired and updated '  || sql%rowcount || ' rows' );
  9  end;
 10  /

Trigger created.

SQL>
SQL> create or replace procedure p
  2  as
  3  begin
  4          insert into t values ( 1 );
  5          insert into t values (-1 );
  6  end;
  7  /

Procedure created.

SQL> select * from t;

no rows selected

SQL> select * from myTable2;

       CNT
----------
         0

SQL> begin
  2          p;
  3  end;
  4  /
fired and updated 1 rows
fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (JAVA2S.SYS_C0010265) violated
ORA-06512: at "JAVA2S.P", line 5
ORA-06512: at line 2


SQL> select * from t;

no rows selected

SQL> select * from myTable2;

       CNT
----------
         0

SQL>
SQL>
SQL> begin
  2      p;
  3  exception
  4      when others then null;
  5  end;
  6  /
fired and updated 1 rows
fired and updated 1 rows

PL/SQL procedure successfully completed.

SQL> select * from t;

         X
----------
         1

SQL> select * from myTable2;

       CNT
----------
         1

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> begin
  2      savepoint sp;
  3      p;
  4  exception
  5      when others then
  6          rollback to sp;
  7  end;
  8  /
fired and updated 1 rows
fired and updated 1 rows

PL/SQL procedure successfully completed.

SQL> select * from t;

no rows selected

SQL> select * from myTable2;

       CNT
----------
         0

SQL>
SQL>
SQL>
SQL> drop table t;

Table dropped.

SQL> drop table myTable2;

Table dropped.

   
  








Related examples in the same category

1.Cursor attributes: FOUND NOTFOUND, ROWCOUNT
2.modified the cursor FOR LOOP presented earlier to include %ROWCOUNT:
3.IF csr_org%ROWCOUNT = 1 THEN
4.Check SQL%ROWCOUNT after update statement
5.Implicit Cursor Attributes: If the previous UPDATE statement didn't match any rows, insert a new row into the place table
6.Demonstrates the %ROWCOUNT cursor attribute by using a single-row implicit cursor based on the DUAL pseudotable