Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors - The powerful SQL cursor
(Page 3 of 4 )
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.
Next: %NOTFOUND, %ROWCOUNT and %ISOPEN attributes >>
More Oracle Articles
More By Jagadish Chatarji
/ 34 
