|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle8i / Oracle 9i
OS Details :
Sun Solaris9
Reference
: Oracle Metalink & Tom Kyte's Post
Cursor
expression returns a nested cursor. Each row in the result set can
contain values as usual, plus cursors produced by sub queries
involving the other values in the row. Thus, a single query can return
a large set of related values retrieved from multiple tables. You can
process the result set with nested loops that fetch first from the
rows of the result set, then any nested cursors within those rows.
Cursor Expressions return
irregularly shaped result sets. Normal SQL returns "square" results --
rows and columns. This does too but the cursor columns are result sets
themselves. A simple Example:
SELECT d.deptno, CURSOR(SELECT
e.empno, CURSOR(SELECT p.projnum, p.projname
FROM projects p
WHERE p.empno = e.empno) as projdetails FROM employees e where e.depno
= d.depno) empdetails
FROM dept d;
What are the advantages of using
cursor expressions over inline views?
Cursor expressions reduces the
number of rows and bytes that would be processed as a result of a
regular join operation.
Based on the example statement
included above let's say there are 10 deptnos. Let's assume that there
are 100 bytes of DEPT information per row.
Let's also assume that each
deptno has 100 employees with 100 bytes of information for each EMP
row and each emp has 5 projects with a row size of 50 bytes.
Normally to return the result set of the query above through regular
joins, there would be 10 x 100 x 5 (5000) records at 250 bytes each or
1,250,000 bytes.
Using the Cursor Expression method would retrieve :
10*100 (dept) + 1000*100 (emp) + 5000*50 (proj) => 1,000 + 100,000 +
250,000 = 351,000 bytes
The reduction in the amount of bytes returned is due to the fact that
the dept info comes back just 10 times, not 5000 times and the emp
info comes just 1,000 times, not 5,000 times.
PL/SQL starting 9i, supports
queries with cursor expressions as part of cursor declarations, REF
CURSOR declarations and ref cursor variables. You can also use
cursor expressions in dynamic SQL queries as well.
Examples of using cursor
expression from within PLSQL :
(CASE 1) :
CREATE OR REPLACE procedure
emp_report(p_locid number) is
TYPE refcursor is ref cursor;
-- The query returns only 2 columns, but the second column is
-- a cursor that lets us traverse a set of related information.
CURSOR c1 is
SELECT l.city,
CURSOR( SELECT d.department_name,
CURSOR( SELECT e.last_name
FROM employees e
WHERE e.department_id = d.department_id
) as ename
FROM departments d
where l.location_id = d.location_id
) dname
FROM locations l
WHERE l.location_id = p_locid;
loccur refcursor;
deptcur refcursor;
empcur refcursor;
V_city locations.city%type;
V_dname departments.department_name%type;
V_ename employees.last_name%type;
BEGIN
OPEN c1;
LOOP
FETCH C1 INTO v_city, loccur;
EXIT WHEN c1%notfound;
-- We can access the column C1.city, then process the results of
-- the nested cursor.
LOOP
FETCH loccur INTO v_dname, deptcur; -- No need to open
EXIT WHEN loccur%notfound;
LOOP
FETCH deptcur into v_ename; -- No need to open
EXIT WHEN deptcur%notfound;
DBMS_OUTPUT.PUT_LINE(v_city ||' '||v_dname||' '||v_ename);
END LOOP;
END LOOP;
END LOOP;
close c1;
END;
/
(CASE 2) :
DECLARE
TYPE refcursor is ref cursor;
CURSOR c1 is
SELECT l.dname,
CURSOR( SELECT d.ename
FROM EMP D
WHERE l.DEPTNO =D.DEPTNO
)
FROM DEPT l;
dnamecur refcursor;
V_DNAME DEPT.DNAME%type;
V_ENAME EMP.ENAME%type;
BEGIN
OPEN c1;
LOOP
FETCH C1 INTO V_DNAME, dnamecur;
EXIT WHEN c1%notfound;
LOOP
FETCH dnamecur INTO v_ENAME; -- No need to open
EXIT WHEN dnamecur%notfound;
DBMS_OUTPUT.PUT_LINE(v_DNAME ||' '||v_ENAME);
END LOOP;
END LOOP;
close c1;
END;
/
|
