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