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

The powerful SQL cursor - 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

First of all, don't confuse yourself. SQL is a great RDBMS query language, no doubt about it. But I am not talking about the "SQL language." Instead, I am talking about a pre-defined IMPLICIT cursor named "SQL cursor."

When you execute any DML statement within PL/SQL, we can know its status of execution through this "SQL" implicit cursor. Oracle opens the SQL cursor automatically (when DML starts its execution) and closes it (when the DML statement finishes its execution). We can invoke its attributes at any time in PL/SQL, when working with DML statements.

Let us start with a simple example.

Declare
  v_empno emp.empno%type := &empno;
  v_sal emp.sal%Type := &sal;
Begin
  Update emp set sal = v_sal
    Where empno=v_empno;
  dbms_output.put_line('Salary got Succesfully updated.');
End;

I don't think that I need to explain much about the above program. It just updates the salary for the given employee number. But just consider what would happen if I provided an invalid employee number. Will it raise a NO_DATA_FOUND exception? No. Not at all. It will simply gives the message 'Salary got successfully updated', even though if I gave a wrong employee number. Why?

NO_DATA_FOUND and TOO_MANY_ROWS work only with SELECT..INTO statements, not with any other DML statements. In the above case, it doesn't even raise a proper exception to handle and show to the user that the employee number is invalid.

In these types of situations, the SQL cursor comes to the rescue. We can always know the status of a DML statement with this SQL cursor very efficiently. ONE SHOULD UNDERSTAND THAT THE SQL CURSOR ALWAYS REFERS TO THE MOST RECENTLY EXECUTED DML STATEMENT. That statement is very important to follow. Now, let us modify the above program to a meaningful program.

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%found then
    dbms_output.put_line('Salary got Succesfully updated.');
  else
    dbms_output.put_line('No employee found.');
  end if;
End;

The only difference in the above program is the following statement.

if SQL%found then
  dbms_output.put_line('Salary got Succesfully updated.');
else
  dbms_output.put_line('No employee found.');
end if;

Within 'SQL%found', SQL refers to the SQL cursor and '%found' is the cursor attribute we are using with the SQL cursor. Finally, 'SQL%found' returns 'true' if the most recent DML statement (in this case UPDATE) gets successfully executed. And I hope you can understand the rest.



 
 
>>> 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: