In PL/SQL what is a difference between a cursor and a reference cursor?

Question

As per my knowledge, a cursor is used to process SQL statements in private area and we can use it further. A Ref cursor is defining a cursor at the spot where it is needed. Please correct me if I am wrong..

Answer 1

A cursor is really any SQL statement that runs DML (select, insert, update, delete) on your database.

A ref cursor is a pointer to a result set. This is normally used to open a query on the database server, then leave it up to the client to fetch the result it needs. A ref cursor is also a cursor, though normally ther term cursor is used when discussing static SQL.

Ref cursors are typically used to change the where clause of a query, based on user input. For example, this function, either opens a query to the emp table or the dept table, depending upon what the user has selected:

create or replace function f (input in varchar2) return sys_refcursor as
  cur sys_refcursor;
begin

  if input = 'EMP' then
    open cur for select * from emp;
  elsif input = 'DEPT' then
    open cur for select * from dept;
  end if;
  return cur;
end;
/