Search code examples
sqloraclecursor

How does a cursor work , and how is it different from a view(SQL )?

I know this is a newbie question, but cursors have me qconfused:

CREATE OR REPLACE FUNCTION f RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
  OPEN c for select * from dual;
  RETURN c;
END;
/

The main part of the code that I don't get is the line:

OPEN c for select * from dual;

what goes on in there?

thanks!

Solution

  • In short, a cursor evaluates the data row-by-row, whereas any other SQL query evaluates the entire data set.

    Cursors are to be avoided at all costs simply because they are less efficient than traditional SQL queries. Rather than operating across an entire data set, a cursor operates on an individual row. Same end result, just much slower when it's a cursor.

    There are some situations that justify the use of a cursor, but 99 out of a 100 times, they can and should be avoided.