Example for using Constrained Cursors:
Let consider a query which displays records from multiple tables.
If Emp table is selected then it displays empno,ename and if Dept table is selected, which must display deptno,dname and if product is selected, which displays pcode and pname. In the above, records come from variant tables but the type of data which is retrieved is of the similar structure.
DECLARE
TYPE rectab IS RECORD (code NUMBER, name VARCHAR2 (40)); #declaration#
TYPE refcur IS REF CURSOR RETURN rectab; # return type#
Recvar RECTAB;
Refcur_var refcur; Opt NUMBER: =&N;
BEGIN
IF opt=1 THEN
OPEN refcur_var FOR SELECT empno,ename FROM emp; #for selecting employee no and name from employee table#
ELSIF opt=2 THEN
OPEN refcur_var FOR SELECT deptno,dname FROM dept; #for selecting department no and name from department table#
ELSIF opt=3 THEN
OPEN refcur_var FOR SELECT pcode,pname FROM product; #for selecting product no and name from product table#
ELSE
DBMS_OUTPUT.PUT_LINE ('INVALID OPTION SELECTED');
END IF;
LOOP
FETCH refcur_var INTO recvar;
EXIT WHEN refcur_var%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Code is '||recvar.code ||' Name is '||recvar.name); # show the output#
END LOOP;
CLOSE refcur_var;
END;
When this block obtains executed and if the option entered is 1, it shows the following output:
Enter value for n: 1
old 6: Opt NUMBER:=&N;
new 6: Opt NUMBER:=1;
Code is 7839 Name is KING
Code is 7698 Name is BLAKE
Code is 7782 Name is CLARK
Code is 7566 Name is JONES
Code is 7654 Name is MARTIN
Code is 7499 Name is ALLEN
Code is 7844 Name is TURNER
Code is 7900 Name is JAMES
Code is 7521 Name is WARD
Code is 7902 Name is FORD
Code is 7369 Name is SMITH
Code is 7788 Name is SCOTT
Code is 876 Name is ADAMS
Code is 7934 Name is MILLER
Similarly for 2 and 3 options, the corresponding values are selected. However, if the option is not valid, it shows:
INVALID OPTION SELECTED DECLARE
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 18
In sequence to capture this Exceptions are used. Instance for Un-Constrained Variables
DECLARE
TYPE TCUR IS REF CURSOR; TVAR TCUR;
EREC EMP%ROWTYPE; DREC DEPT%ROWTYPE;
CHOICE NUMBER:=&C;
BEGIN
IF CHOICE=1 THEN
OPEN TVAR FOR SELECT * FROM EMP;
LOOP
FETCH TVAR INTO EREC;
EXIT WHEN TVAR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(erec.ename);
END LOOP; CLOSE TVAR;
ELSIF CHOICE=2 THEN
OPEN TVAR FOR SELECT * FROM DEPT;
LOOP
FETCH TVAR INTO DREC;
EXIT WHEN TVAR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(drec.deptno||' '||drec.dname);
END LOOP;
CLOSE TVAR;
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID CHOICE');
END IF;
The show of the program based on the condition will be:
Enter value for c: 2
old 6: CHOICE NUMBER:=&C;
new 6: CHOICE NUMBER:=2;
10 ACCOUNTING
20 RESEARCH
30 SALES OPERATIONS