Difference between implicit and explicit cursors in Oracle

Question

I want to know the difference between these two statements. Is one 'better' than the other ?

DECLARE
    myvar varchar2(50);
BEGIN
    SELECT fieldone into myvar FROM tbl_one WHERE id = 1;
END;

AND

DECLARE
    CURSOR L1 IS
    SELECT fieldone FROM tbl_one WHERE id = 1;
BEGIN
    OPEN L1;
    FETCH L1 INTO myvar;
    CLOSE L1;
END;

Answer 1

The first will raise an exception if there are no rows returned or if more than one row is returned. If you don't handle the exception, that gets thrown back to the calling routine or client software. This is known as an implicit cursor.

The second would fail silently. If no rows are returned, then myvar will have a null value (though its preferable if you assume it is undefined). If more than one row would be returned, then only the value from the first row is stored. Without an ORDER BY, which value is 'first' is undefined. This is known as an explicit cursor.

So the question is really, what do YOU want to happen in the event of a no data found or too many rows situation. If you are certain that will never happen, or don't know how to handle it, then go with option 1.

If you do expect a no data found situation only, then go with the implicit cursor but add an exception handler.

If you expect multiple rows, then either the implicit cursor with an exception handler, or a BULK SELECT or CURSOR LOOP if you actually need to process the multiple rows.

If you are going to select multiple fields, it can be useful to define an explicit cursor and use a %TYPE declaration to declare all the necessary variables.

From a performance point of view, there's no difference. From a maintainablilty point of view, some people like their SELECT 'in-line' with their code (so prefer the implicit cursor). I prefer mine 'out of the way', especially if there is a big column list, so I like explicit cursors.