Oracle
  Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, Intr...
Dev Shed Forums 
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Sun Developer Network 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Moblin 
JMSL Numerical Library 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ORACLE

Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors
By: Jagadish Chatarji
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 34
    2005-07-19

    Table of Contents:
  • Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors
  • Introduction to Cursors
  • The powerful SQL cursor
  • %NOTFOUND, %ROWCOUNT and %ISOPEN attributes

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors - %NOTFOUND, %ROWCOUNT and %ISOPEN attributes


    (Page 4 of 4 )

    '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.


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

       · Hello guys, this is my article on cursos and implicit cursors in PL/SQL. You can...
       · Hello guys, this is my article on cursos and implicit cursors in PL/SQL. You can...
     

       

    ORACLE ARTICLES

    - Implementing and Using Oracle`s Restore Poin...
    - Tuning PL/SQL Code
    - Debugging PL/SQL Code
    - Testing PL/SQL Code
    - Working With PL/SQL Code
    - Conditional Compilation for Oracle Database ...
    - Compile-Time Warnings for Oracle DB 10g
    - Compiling PL/SQL Code for an Oracle Database
    - Troubleshooting PL/SQL Code
    - Managing PL/SQL Code
    - Data Manipulation and More for HTML DB Appli...
    - Oracle Database Fundamentals
    - Adding Processes to HTML DB Applications
    - Adding Computations, Processes, and Validati...
    - Sub-templates and More with Oracle HTML DB





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway