Geek Answers Handbook

In Oracle, what's the difference between opening and opening a cursor with parameters?

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;

A...

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 work. Are they the same or is there any difference I should know about?
+5
source share
2 answers

The second example is an explicit cursor, and it is static. That is, it is a variable associated with a single SQL statement. There is an 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 the ref cursor, which is a pointer to an SQL statement and therefore can be dynamic. For example, we can extend this example as follows:

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;

, ref SQL, . , , ref , . PL/SQL , , JDBC.

+6
  • "".
  • API () - API .
  • /IDE/GUI , .
  • , , ; , - .
+5

More articles:


All Articles