Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors

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.

Please note that all of the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer to the documentation of the respective version you are using, if any of the programs fail to execute.

Handling and Re-raising an exception

Parts seven and eight of my series gave several tips for using nested blocks with exceptions. Before going into a new concept, I would like to introduce one more tip for using exceptions with nested blocks. Let us consider the following example:

<<parent>>
Declare
  v_empno emp.empno%Type;
Begin

  <<nested1>>
  Declare
    v_empno emp.empno%type := &empno1;
    v_ename emp.ename%Type;
  Begin
    Select ename into v_ename
    From emp Where empno=v_empno;
    dbms_output.put_line(‘Name:’||v_ename);
  Exception
    When no_data_found then
      parent.v_empno := v_empno;
      raise;
  End;

  <<nested2>>
  Declare
    v_empno emp.empno%type := &empno2;
    v_ename emp.ename%Type;
  Begin
    Select ename into v_ename
    From emp Where empno=v_empno;
    dbms_output.put_line(‘Name:’||v_ename);
  Exception
    When no_data_found then
      parent.v_empno := v_empno;
      raise;
  End;

Exception
  When no_data_found then
    dbms_output.put_line(‘Employee not found with ‘||v_empno);
End;

Even though the program looks bit lengthy, there exists nothing new, except the word ‘raise’ without any exception. As explained in my previous articles, RAISE simply raises a specified exception, that could be either pre-defined or user-defined. We handle that exception within the exception handler section. But here, I used RAISE without specifying any exception. That means it should re-RAISE the current exception (even though it is being handled). In the above program, the NO_DATA_FOUND exception is already being handled within the nested blocks. But, you can observe that I am not displaying the message straight away. I am just storing the EMPNO into the parent block related variable (v_empno) and re-raising the exception.

When the exception gets re-raised, it searches for another exception handler within the same block or its parent block (but not its paralleled block, like ‘nested2′). And, in this case, the parent block automatically catches that re-raised exception and handles it by displaying a message to the user along with the ‘empno’. In this way we can maintain a mechanism for handling exceptions centrally situated at a single location (rather than scattering everywhere).

{mospagebreak title=Introduction to Cursors}

This concept is the heart of PL/SQL programming. First of all, this concept is very easy; it is not at all complicated in any sense. Those who are familiar with Visual Basic 6 can just imagine a cursor as a recordset, or “datatable” in .NET (or resultset in java). A cursor can be understood as a pointer (or reference) to a memory area which holds the result of any SQL query. Actually, we already used the concept of cursor (unknowingly) in the section “A Powerful FOR loop in PL/SQL” of part five in this series.

Basically, there exist two types of cursors, IMPLICIT and EXPLICIT cursors. IMPLICIT cursors do not have any declarations. They will be automatically created and lost within the memory. We will not have any control over the IMPLICIT cursors. A FOR loop with a SELECT statement, internally maintains an IMPLICIT cursor in memory which is removed from memory once the loop gets completed (an example is available in part five of this series).

We can identify the status of any cursor by using a set of cursor ATTRIBUTES. These ATTRIBUTES can be used with named cursors only (we will look into this later). These ATTRIBUTES play a very important role in dealing with cursors. The following is the list of ATTRIBUTES used with cursors.

  • %FOUND
  • %NOTFOUND
  • %ROWCOUNT
  • %ISOPEN

The above are just some basic attributes. Oracle 9i and 10g introduced a few more. But I will look into them only in upcoming articles, as they will be used in different contexts, other than just a simple cursor. I would like to explain each of the above using examples rather than just messy paragraphs.

{mospagebreak title=The powerful SQL cursor}

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.

{mospagebreak title=%NOTFOUND, %ROWCOUNT and %ISOPEN attributes}

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

Google+ Comments

Google+ Comments