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.

Comments On: Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors

· 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...   >>> Post your comment now!