In Oracle what is the difference between open-for and opening a cursor with parameters?

Question

What is the difference between these two pieces of code?

TYPE t_my_cursor IS REF CURSOR; 
v_my_cursor t_my_cursor;
OPEN v_my_cursor FOR SELECT  SomeTableID 
        FROM MYSCHEMA.SOMETABLE
        WHERE SomeTableField = p_parameter;

And...

CURSOR v_my_cur(p_parameter VARCHAR2) IS
SELECT SomeTableID
FROM MYSCHEMA.SOMETABLE
WHERE SomeTableField = p_parameter;

OPEN presf_cur(p_subscriber_id);

They both seem to work. Are they the same or is there some difference I should be aware of?

Answer 1

The second example is an explicit cursor, and it is static. That is, it is a variable associated with one SQL statement. There is a implicit equivalent...

FOR lrec in ( SELECT  SomeTableID 
              FROM MYSCHEMA.SOMETABLE
              WHERE SomeTableField = p_parameter )
LOOP
    do_something_with (lrec.sometableid);
END LOOP;

The first example is a ref cursor, which is a pointer to a SQL statement and so can be dynamic. For instance we can extend that example like this:

TYPE t_my_cursor IS REF CURSOR; 
v_my_cursor t_my_cursor;

...

if flag = 1 then
    OPEN v_my_cursor FOR SELECT  SomeTableID 
        FROM MYSCHEMA.SOMETABLE
        WHERE SomeTableField = p_parameter;
else
    OPEN v_my_cursor FOR SELECT  SomeTableID 
        FROM MYSCHEMA.ANOTHERTABLE
        WHERE AnotherTableField = p_parameter;
end if;

Or even:

    l_stmt := 'SELECT * FROM your_table WHERE ';
    if p_parameter is not null then
        l_stmt := l_stmt ||'id = :1'; 
        open v_my_cursor for l_stmt using p_parameter;
    else
        l_stmt := l_stmt ||'created_date > trunc(sysdate)'; 
        open v_my_cursor for l_stmt;
    end if;

So using a ref cursor gives us a lot more control over the final SQL statement which gets executed. The other difference is that, because a ref cursor is a pointer it can be passed between programs. This is very useful for passing data from PL/SQL to other languages, for instance a JDBC result set.

Answer 2

1. Strongly typed cursors can be 'described'.
2. If your building an API (a package) you can place your cursor definitions at the specification level and give the client programmer a better sense of what your API does and returns without needing to be aware of the source code.
3. Layout/IDE/GUI tools will likely play nicer with a named cursor.
4. There is possibly a negligible performance benefit having a known typed cursor; but I wouldn't count on it being anything significant.