Cursor Expression

SET SERVEROUTPUT ON
DECLARE
  TYPE ref_emp_cursor IS REF CURSOR;
  emp_cur ref_emp_cursor;
  l_deptno  dept.deptno%TYPE;
  l_ename    emp.ename%TYPE;
  
  CURSOR c1 IS SELECT deptno, 
         CURSOR(SELECT e.ename FROM emp e 
         WHERE e.deptno = d.deptno) 
         FROM dept d where d.dname like 'A%'; 
BEGIN
   OPEN c1;
   LOOP
    FETCH c1 INTO  l_deptno, emp_cur;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line('Department No '||l_deptno);
    dbms_output.put_line('========================');
    LOOP
      FETCH emp_cur INTO l_ename;
      EXIT WHEN emp_cur%NOTFOUND;
      dbms_output.put_line(emp_cur%ROWCOUNT||' Employee name is '||l_ename);
    END LOOP;
   END LOOP;
   CLOSE c1;
END;
/
Department No 10
========================
1 Employee name is CLARK
2 Employee name is KING
3 Employee name is MILLER       

Weak Ref Cursor

DECLARE
     rcur_emp  SYS_REFCURSOR;
     PROCEDURE process_emp_cur(rcur_emp IN SYS_REFCURSOR) IS
       l_emp emp%ROWTYPE;
     BEGIN
        dbms_output.put_line('Emp table details');
        loop
            fetch rcur_emp into l_emp;
            exit when rcur_emp%notfound;
            dbms_output.put_line('Empno : '|| l_emp.empno);
            dbms_output.put_line('Ename : '|| l_emp.ename);
        end loop;
     END;
BEGIN
  OPEN rcur_emp FOR select * from emp where rownum < 2;
  process_emp_cur (rcur_emp);
  CLOSE rcur_emp;
END;

Weak Ref Cursor

DECLARE

    TYPE  REFCUR_EMP_TYPE IS REF CURSOR;

     rcur_emp  REFCUR_EMP_TYPE;

     PROCEDURE process_emp_cur(rcur_emp IN REFCUR_EMP_TYPE) IS

       l_emp emp%ROWTYPE;

     BEGIN

        dbms_output.put_line('Emp table details');

        loop

            fetch rcur_emp into l_emp;

            exit when rcur_emp%notfound;

            dbms_output.put_line('Empno : '|| l_emp.empno);

            dbms_output.put_line('Ename : '|| l_emp.ename);

        end loop;

     END;

BEGIN

  OPEN rcur_emp FOR select * from emp where rownum < 2;

  process_emp_cur (rcur_emp);

  CLOSE rcur_emp;

END;

Strong Ref cursor

DECLARE
    TYPE  REFCUR_EMP_TYPE IS REF CURSOR RETURN  emp%ROWTYPE;
     rcur_emp  REFCUR_EMP_TYPE;


     PROCEDURE process_emp_cur(rcur_emp IN REFCUR_EMP_TYPE) IS
       l_emp emp%ROWTYPE;
     BEGIN
        dbms_output.put_line('Emp table details'); 
        loop
            fetch rcur_emp into l_emp;
            exit when rcur_emp%notfound;
            dbms_output.put_line('Empno : '|| l_emp.empno);
            dbms_output.put_line('Ename : '|| l_emp.ename);
            
        End loop;
     END;
BEGIN
  OPEN rcur_emp FOR select * from emp where rownum < 2;
  process_emp_cur (rcur_emp);
  CLOSE rcur_emp;  
END;

Dynamic SQL - Execute Immediate & Bulk Collection

DECLARE
  TYPE emp_ref_cursor IS REF CURSOR;
  TYPE empno_tab IS TABLE OF NUMBER;
  TYPE ename_tab IS TABLE OF VARCHAR2(50);
 
  v_emp_cur  emp_ref_cursor;
  vc_empno   empno_tab;
  vc_sal     empno_tab;
  vc_ename   ename_tab;
  vc_ename1  ename_tab;
  vc_ename2  ename_tab;
  v_sql_stmt VARCHAR2(500);
  v_bonus    CONSTANT NUMBER := 500;
  a_null CHAR(1);
BEGIN
  -- example of ref cursor with bulk collect
  v_sql_stmt := 'SELECT empno, ename FROM emp_t WHERE deptno = 10';
  OPEN v_emp_cur FOR v_sql_stmt;
  FETCH v_emp_cur BULK COLLECT INTO vc_empno,vc_ename;
  CLOSE v_emp_cur;
 
  -- example of dynamic sql with bull collect
  v_sql_stmt := 'SELECT sal FROM emp_t';
  EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO vc_sal;
 
  -- example of dynamic sql with returning bulk collect
  v_sql_stmt := 'UPDATE emp_t SET sal = sal + :1
                 RETURNING ename INTO :2';
  EXECUTE IMMEDIATE v_sql_stmt
              USING v_bonus RETURNING BULK COLLECT INTO vc_ename1;
 
  --dynamic sql inside forall statement
  v_sql_stmt := 'UPDATE emp_t SET sal = sal - :1
                WHERE empno = :2 RETURNING ENAME INTO :3';
  FORALL i in vc_empno.first..vc_empno.last
     EXECUTE IMMEDIATE v_sql_stmt USING v_bonus, vc_empno(i)
                      RETURNING BULK COLLECT INTO vc_ename2;
    
  FOR i in vc_ename2.first..vc_ename2.last LOOP
     dbms_output.put_line(vc_ename2(i));
  END LOOP;
 
  -- passing null to dynamic sql
  EXECUTE IMMEDIATE 'UPDATE emp_t SET comm = :1' USING a_null;
  ROLLBACK;
 
EXCEPTION
 WHEN OTHERS THEN
  dbms_output.put_line('When others exception raised '|| sqlerrm);
END;
 

Dynamic SQL - Execute Immediate -that accept table name and where clause

CREATE OR REPLACE PROCEDURE delete_rows ( 

             v_table_name USER_OBJECTS.OBJECT_NAME%TYPE,
             v_where_clause VARCHAR2 ) 

IS
   l_table_name    USER_OBJECTS.OBJECT_NAME%TYPE;
   l_sql_stmt VARCHAR2(2000);
   l_count PLS_INTEGER;
BEGIN
   SELECT object_name INTO l_table_name
       FROM user_objects
       WHERE object_type = 'TABLE'
       AND object_name = UPPER(v_table_name);

   l_sql_stmt := 'DELETE FROM '|| l_table_name || ' WHERE '
                 ||v_where_clause;
   EXECUTE IMMEDIATE l_sql_stmt;
   l_count := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE (l_count||' Record(s) from '

         ||v_table_name ||' table has been deleted');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Table Name');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Other exception raised '||sqlerrm);
END;

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2   delete_rows('Emp_t','empno = 7654');
  3  end;
  4  /

1 Record(s) from Emp_t table has been deleted

PL/SQL procedure successfully completed

Dynamic SQL - Execute Immediate -1

CREATE OR REPLACE PROCEDURE raise_emp_sal
       (v_emp_column_name VARCHAR2,
        v_column_value NUMBER, v_amount NUMBER) IS

  l_sql_stmt VARCHAR2(2000);
BEGIN
  
  l_sql_stmt :='UPDATE emp_t SET sal = sal + :1'||
               'WHERE '|| v_emp_column_name || '= :3';
  EXECUTE IMMEDIATE l_sql_stmt USING v_amount,v_column_value;

  IF SQL%ROWCOUNT > 0 THEN
     DBMS_OUTPUT.PUT_LINE('Salary has been updated for '

              ||v_emp_column_name || ' = '||v_column_value||

              ' by '||v_amount||' Rupees');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Invalid column name '

                         ||v_emp_column_name);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Others exception raised '|| sqlerrm);
END;

SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
  2    RAISE_EMP_SAL('EMPNO',7654,1200);
  3    RAISE_EMP_SAL('DEPTNO',20,100);
  4    COMMIT;
  5  END;
  6  /

Salary has been updated for EMPNO = 7654 by 1200 Rupees
Salary has been updated for DEPTNO = 20 by 100 Rupees

PL/SQL procedure successfully completed