Sunday, November 25, 2012

%NOTFOUND

This simple example was tested on Oracle 11.2. It uses SQL*Plus to list the tables owned by a user and counts the rows in each one:

SQL> select table_name
  2  from dba_tables
  3  where owner = 'ANDREW'
  4  /
 
TABLE_NAME
------------------------------
T1
T2
T3
 
SQL> select count(*) from andrew.t1
  2  /
 
  COUNT(*)
----------
      3082
 
SQL> select count(*) from andrew.t2
  2  /
 
  COUNT(*)
----------
      4851
 
SQL> select count(*) from andrew.t3
  2  /
 
  COUNT(*)
----------
     73942
 
SQL>

Then it does the same thing in PL/SQL. Note the use of %NOTFOUND to jump out of the loop when there is nothing more to return from the cursor:

SQL> DECLARE
  2   CURSOR c1 is
  3    SELECT owner||'.'||table_name fqn
  4    FROM dba_tables
  5    WHERE owner = 'ANDREW';
  6   c1_rec c1%ROWTYPE;
  7   row_count NUMBER;
  8   sql_statement VARCHAR2(200);
  9  BEGIN
10   OPEN c1;
11    LOOP
12     FETCH c1 INTO c1_rec;
13     EXIT WHEN c1%NOTFOUND;
14     sql_statement :=
15      'SELECT COUNT(*) FROM '||c1_rec.fqn;
16     DBMS_OUTPUT.PUT_LINE(sql_statement);
17     EXECUTE IMMEDIATE sql_statement
18      INTO row_count;
19     DBMS_OUTPUT.PUT_LINE(c1_rec.fqn||
20      ' has '||row_count||' rows');
21    END LOOP;
22   CLOSE c1;
23  END;
24  /
SELECT COUNT(*) FROM ANDREW.T1
ANDREW.T1 has 3082 rows
SELECT COUNT(*) FROM ANDREW.T2
ANDREW.T2 has 4851 rows
SELECT COUNT(*) FROM ANDREW.T3
ANDREW.T3 has 73942 rows
 
PL/SQL procedure successfully completed.
 
SQL>

2 comments:

Martien van den Akker|Darwin-IT said...

Hi Andrew,

Nice example. But this could be done a little easier by using a for loop (for c1_rec in c1 loop). This prevents using the exit-when construction (that I think should be avoided when possible).

But most of all the for-loop does exactly what you meant to do here.

Regards,
Martien

Andrew Reid said...

Dear Martien,

Thank you for taking the time to comment on my example. As you may have noticed from my other posts, I do not have a great deal of experience in PL/SQL - I'm just picking it up as time permits so all suggestions are welcome. I will do a new post based on your suggestion in the near future.

Kind regards,

Andrew