Difference Between cursor and ref cursor:
Ref cursor are variable of type cursor. Once you will declare a Ref cursore you can place any cursor in that ref cousor. In short words you can say ref cursors are holder of of any type of cursor.
if you will see the below code rc is a ref cursor and c is a cursor. rc is opened with different sql queries later but c (normal cursor) which is fixed.
other differences are there also.
> ref cursors can sent to the client. in word of java you can say ref cursors are result sets you can pass to other sub programs
>you cannot declare ref cursor out side procedure.
> A ref cursor can be passed to subroutine to subroutine but normal cursors cannot passed.
Important difference is that
Static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to
- when returning result sets to clients
- when there is NO other efficient/effective means of achieving the goal
Declare type rc is ref cursor; cursor c is select * from dual; l_cursor rc; begin if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) then open l_cursor for select * from dept; else open l_cursor for select * from dual; end if; open c; end; /