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.

Answer 2

I don't know what question you're asking, but here goes.

Should you use PL/SQL like this?

declare
  myvar varchar2(50);
begin
  select fieldone 
    into myvar
    from tbl_one;
end;
/

Well, you can if and only if you know that the select statement can return exactly one row; alternatively, you need error handling for the TOO_MANY_ROWS and NO_DATA_FOUND exceptions which would be raised otherwise.

---

When using explicit cursors (i.e., the CURSOR keyword), there are several operations against it which control its behavior.

declare
  myvar varchar2(50);
  CURSOR L1 IS
  SELECT fieldone FROM tbl_one ;
begin
  OPEN L1;
  FETCH L1 into myvar;
  CLOSE L1;
end;
/

CURSOR L1... is the cursor's declaration. It's nothing more than binding the static SQL statement, and all the PL/SQL engine does is check that the SQL is syntactically and contextually valid - are there missing clauses? Can this user SELECT from this table?

OPEN L1 opens the cursor, establishing the exact point in the history of the system which the results will reflect. Any subsequent FETCHes against that cursor will reflect the data as of that precise point.

FETCH L1... actually returns the first/next row of that result set, whatever it is, into the variables you've specified. It could be a record declared, or it could be list of variables.

CLOSE L1... frees any resources your cursor has open; for example, insert/update/delete operations that affect the records generate undo that your user session has a declared read interest in, so that undo can't be freed or reused until you've closed your cursor.

Answer 3

Personally, I'd got for the first version whenever possible. Keep it simple. One statement instead of five, more readable.

Answer 4

In Oracle (unlike SQL Server) all SELECTs are cursors - all declaring a cursor does is get you a handle that you can then use to manipulate it. The execution plan will be identical in both of your cases.