Ref cursor switch based on condition

A ref cursor is a datatype that holds a cursor value in the same way that a varchar2 variable will hold a string value.
Ref cursor will allow us to open difference sets of query where is the explicit cursor will not allow us to open a query other then the one which is associated to that cursor pointer.
Using ref cursors is one of the most powerful, flexible, and scalable ways to return query results from an oracle database to a client application.

Preview

DECLARE
   a          NUMBER := 3333;
   TYPE rc IS REF CURSOR; -- ref cursor declare
   l_cursor   rc;
   l_ename    emp.ename%TYPE;
   l_deptno   dept.deptno%TYPE;
   l_value    NUMBER;
BEGIN
   IF (a = 30)
   THEN
      OPEN l_cursor FOR 'select ename from emp'; -- opening the cursor for query
      FETCH l_cursor INTO l_ename;
      CLOSE l_cursor;
      DBMS_OUTPUT.put_line ('l_ename : = ' || l_ename);
   ELSIF (a = 20)
   THEN
      OPEN l_cursor FOR SELECT deptno FROM dept;
      FETCH l_cursor INTO l_deptno;
      CLOSE l_cursor;
      DBMS_OUTPUT.put_line ('l_deptno : = ' || l_deptno);
   ELSE
      OPEN l_cursor FOR SELECT 1 FROM DUAL;
      FETCH l_cursor INTO l_value;
      CLOSE l_cursor;
      DBMS_OUTPUT.put_line ('l_value : = ' || l_value);
   END IF;
END;
/

You might Like


comments powered by Disqus