Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors

%NOTFOUND, %ROWCOUNT and %ISOPEN attributes - Oracle

This is part nine of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we looked at different tips for using nested blocks together with exceptions. In this article, we will see how to handle exceptions centrally and have a look at cursors; we will also receive an introduction to the SQL cursor.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors
  2. Introduction to Cursors
  3. The powerful SQL cursor
  4. %NOTFOUND, %ROWCOUNT and %ISOPEN attributes
By: Jagadish Chatarji
Rating: starstarstarstarstar / 42
July 19, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

'SQL%NOTFOUND' returns 'true' if the most recent DML statement could not get successfully executed (quite the opposite of 'SQL%found'). We can also implement 'SQL%NotFound' in the above program as follows:

Declare
  v_empno emp.empno%type := &empno;
  v_sal emp.sal%Type := &sal;
Begin
  Update emp set sal = v_sal 
    Where empno=v_empno;
  if SQL%notfound then
    dbms_output.put_line('No employee found.');
  else
    dbms_output.put_line('Salary got Succesfully updated.');
  end if;
End;

If you carefully observe the above program, apart from replacing 'SQL%found' with 'SQL%notfound', I also swapped the DBMS_OUTPUT statements. This is because 'SQL%notfound' is negative.

If you ask me a question, which is the better of 'SQL%found' and 'SQL%notfound', it would be something like asking which eye is better! We require both, but we need to use them based on the situation.

Now, let us consider another example as following:

Declare
  v_deptno emp.deptno%type := &deptno;
  v_sal emp.sal%Type := &sal;
Begin
  Update emp set sal = v_sal
    Where deptno=v_deptno;
  if SQL%notfound then
    dbms_output.put_line('No employee found.');
  else
    dbms_output.put_line('Salary got Succesfully updated.');
  end if;
End;

The only difference is that I changed the 'empno' to 'deptno' in the above program. That means that the program gets 'deptno' and 'sal' from the user, and updates all of the employees within that department with the new salary provided. There is no doubt that the program works perfectly with 'SQL%notfound'.

But now the issue is not simply displaying the 'Succesfully updated.' message. I would also like to show the number of rows updated as part of the same message. Certainly the user will be gladder to see that than not. How do I achieve that? As you guessed, it is with 'SQL%rowcount' and the following program demonstrates that.

Declare
  v_deptno emp.deptno%type := &deptno;
  v_sal emp.sal%Type := &sal;
Begin
  Update emp set sal = v_sal
    Where deptno=v_deptno;
  if SQL%notfound then
    dbms_output.put_line('No employee found.');
  else
    dbms_output.put_line('Updated ' || SQL%rowcount || ' employees.');
  end if;
End;

Only the second DBMS_OUTPUT statement got modified with 'SQL%rowcount'. The 'SQL%rowcount' gives the number of rows affected by the most recent DML command within a PL/SQL program.

Coming to our last %ISOPEN attribute, it generally yields false, when used with SQL cursor. Oracle automatically closes the SQL cursor, once the DML statement finishes its execution. So, we will never get any chance to work with %ISOPEN by using SQL cursor. It is generally used with EXPLICIT cursors. My next article (part 10) will deal the concept of EXPLICIT cursors in detail.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: