Cursor Expressions
Oracle9i has incorporated the facility to nest cursors in PL/SQL cursor declarations in the form of cursor expressions. In this section, I discuss the method of declaring and using cursor expressions in PL/SQL 9i. I also outline the method of passing cursors as actual parameters to functions.
Why Use Cursor Expressions?
Cursor expressions eliminate the use of declaring and using multiple cursors and hence result in a more effective optimization scheme by the SQL engine as it involves only one SQL statement as opposed to multiple cursors, which result in multiple SQL statements. Also, cursor expressions eliminate the use of complicated joins involved in SQL SELECT statements. As a third benefit, Oracle9i removes the limitation of using cursor expressions in SQL embedded in PL/SQL code. Now you can use cursor expressions as part of PL/SQL cursors. Also, when you use dynamic SQL, you can use cursor expressions and fetch into REF CURSOR variables. In this case, they support complex binds and defines needed for REF CURSORS. This isn't supported by DBMS_SQL.
Declaring Cursor Expressions
Basically, a cursor expression is a cursor declaration in PL/SQL in which the cursor SELECT statement contains one column as a cursor. This results in the declaration of nested cursors. A cursor expression is declared using this syntax:
CURSOR <parent-cursor-name> is
SELECT col_name, CURSOR (SELECT ... ) ...
Here's an example of a cursor expression:
CURSOR csr_hierarchy IS
SELECT h.hrc_descr,
CURSOR(SELECT o.org_long_name
FROM org_tab o
WHERE o.hrc_code = h.hrc_code) long_name
FROM hrc_tab h;
This provides the functionality of a single query returning sets of values from multiple tables.
Prior to Oracle9i, CURSOR subqueries were supported in top-level SQL SELECT statements only. For example, a SELECT statement such as this:
SELECT h.hrc_descr,
CURSOR(SELECT o.org_long_name
FROM org_tab o
WHERE o.hrc_code = h.hrc_code) long_name
FROM hrc_tab h;
runs perfectly well in releases prior to Oracle9i, with the following output in SQL*Plus:
However, before Oracle9i, declaring a cursor in PL/SQL with this SELECT statement resulted in the compilation error shown here:
In Oracle8i and earlier, you could achieve the same function in PL/SQL by using two cursors with corresponding cursor FOR LOOPs. Here's the code for the same:
BEGIN
FOR i IN (SELECT hrc_code, hrc_descr FROM hrc_tab) LOOP
FOR j IN (SELECT org_long_name
FROM org_tab
WHERE hrc_code = i.hrc_code) LOOP
dbms_output.put_line(i.hrc_descr||' '||j.org_long_name);
END LOOP;
END LOOP;
END;
/
Using a cursor expression has the advantage of using only one SELECT statement to achieve the result. As such, it is optimized more effectively. The method of using a cursor expression in PL/SQL 9i is explained in the next section, "Using Cursor Expressions."
| Tip� | Multiple nesting using the CURSOR (subquery SELECT) is allowed. |
A cursor expression isn't allowed for an implicit cursor, in a view declaration, or in a subquery of a parent query. It is allowed in a parent query (i.e., the outer-most SELECT list of a query).
Using Cursor Expressions
As I mentioned earlier, a cursor expression enables a single query to return sets of values from multiple tables. Here are the steps for using a cursor expression:
Declare the cursor expression with nested cursors.
Open the parent cursor. There's no need to open the nested cursors.
Use nested loops that fetch first from the rows of the result set and then from any nested cursors within these rows.
Declare a REF CURSOR to hold the nested cursor resultset while fetching.
Close the parent cursor. There's no need to close the nested cursors.
I wrote a PL/SQL function to use the cursor expression declared here. Here's the code:
create or replace function f_cursor_exp return NUMBER
is
TYPE rc is REF CURSOR;
/* declare the cursor expression */
CURSOR csr_hierarchy IS
SELECT h.hrc_descr,
CURSOR(SELECT o.org_long_name
FROM org_tab o
WHERE o.hrc_code = h.hrc_code) long_name
FROM hrc_tab h;
/* Declare a REF CURSOR variable to hold the nested cursor resultset
while fetching. */
hrc_rec rc;
v_hrc_descr VARCHAR2(20);
v_org_long_name VARCHAR2(60);
BEGIN
/* Open the parent cursor */
OPEN csr_hierarchy;
LOOP
/* fetch the column csr_hierarchy.hrc_descr,
then loop through the resultset of the nested cursor. */
FETCH csr_hierarchy INTO v_hrc_descr, hrc_rec;
EXIT WHEN csr_hierarchy%notfound;
/* Use a nested loop that fetches from the nested cursor
within the parent rows. */
LOOP
-- Directly fetch from the nested cursor, there is no need to open it.
FETCH hrc_rec INTO v_org_long_name;
EXIT WHEN hrc_rec%notfound;
DBMS_OUTPUT.PUT_LINE(v_hrc_descr ||' '||v_org_long_name);
END LOOP;
END LOOP;
/* Close the parent cursor. No need to close the nested cursor. */
close csr_hierarchy;
RETURN (0);
EXCEPTION WHEN OTHERS THEN
RETURN (SQLCODE);
END;
/
The following points are worth noting:
There's no need to open the nested cursor. It's implicitly opened when a row is fetched from the parent cursor.
There's no need to close the nested cursor. It's implicitly closed when the parent cursor is closed.
Cursor Expressions Using Multiple Levels of Nested Cursors
This example demonstrates multiple levels of nested cursors. In the following code, I display the complete hierarchy, org, and org-site details:
create or replace function f_cursor_exp_complex return NUMBER
is
TYPE rc is REF CURSOR;
/* declare the cursor expression */
CURSOR csr_hierarchy IS
SELECT h.hrc_descr,
CURSOR(SELECT o.org_long_name,
CURSOR (SELECT s.site_descr
FROM org_site_tab os, site_tab s
WHERE os.site_no = s.site_no
AND os.org_id = o.org_id) as site_name
FROM org_tab o
WHERE o.hrc_code = h.hrc_code) long_name
FROM hrc_tab h;
/* Declare two REF CURSOR variables to hold the nested cursor resultset
while fetching. */
hrc_rec rc;
org_rec rc;
v_hrc_descr VARCHAR2(20);
v_org_long_name VARCHAR2(60);
v_site_name VARCHAR2(20);
BEGIN
/* Open the parent cursor */
OPEN csr_hierarchy;
LOOP
/* fetch the column csr_hierarchy.hrc_descr,
then loop through the resultset of the nested cursors. */
FETCH csr_hierarchy INTO v_hrc_descr, hrc_rec;
EXIT WHEN csr_hierarchy%notfound;
LOOP
/* Use a nested loop that fetches from the first nested cursor
within the parent rows */
FETCH hrc_rec INTO v_org_long_name, org_rec;
EXIT WHEN hrc_rec%notfound;
LOOP
-- Directly fetch from the second nested cursor, there is no need to open it
FETCH org_rec INTO v_site_name;
EXIT WHEN org_rec%notfound;
DBMS_OUTPUT.PUT_LINE(v_hrc_descr ||' '||v_org_long_name||' '||
v_site_name);
END LOOP;
END LOOP;
END LOOP;
/* Close the parent cursor. No need to close the nested cursors. */
close csr_hierarchy;
RETURN (0);
EXCEPTION WHEN OTHERS THEN
RETURN (SQLCODE);
END;
/
You can now execute this function as shown here:
SQL> set serverout on;
SQL> VAR ret_code NUMBER;
SQL> exec :ret_code := f_cursor_exp_complex;
Cursor Expressions as Arguments to Functions Called from SQL
I mentioned earlier that you can use cursor variables as formal parameters to a function. Also, cursor expressions refer to actual cursors. Now the following question arises: Can cursor expressions be used as actual parameters to such functions having REF CURSORS or SYS_REFCURSOR as formal parameter types? The answer to this question is yes, provided the function is called in a top-level SQL statement only.
Consider the second example presented in the earlier section "Dynamism in Using Cursor Variables." It describes a scenario in which a report is required of all organizations and their hierarchy levels depending on different conditions such as
All organizations that are located in more than one site
All organizations that don't have a particular hierarchy level
All organizations that belong to the highest hierarchy level
All organizations having same hierarchy as those in a particular site
In this case, it suffices to write a function that takes a cursor expression as input along with the title of the report and generates the report. The cursor expression is passed as an actual parameter with different WHERE conditions each time, but the columns in the SELECT will be the same each time. Here's the code for this function:
CREATE OR REPLACE FUNCTION f_report(p_cursor SYS_REFCURSOR, p_title VARCHAR2)
RETURN NUMBER
IS
v_hrc_descr VARCHAR2(20);
v_org_short_name VARCHAR2(30);
v_ret_code NUMBER;
BEGIN
BEGIN
dbms_output.put_line(p_title);
dbms_output.put_line(rpad('Hierarchy',20,' ')||' '||
rpad('Organization',30,' '));
dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
LOOP
FETCH p_cursor INTO v_hrc_descr, v_org_short_name;
EXIT WHEN p_cursor%NOTFOUND;
dbms_output.put_line(rpad(v_hrc_descr,20,' ')||' '||
rpad(v_org_short_name,30,' '));
END LOOP;
v_ret_code := 1;
EXCEPTION WHEN OTHERS THEN
v_ret_code := SQLCODE;
END;
RETURN (v_ret_code);
END;
/
You can now invoke this function with a cursor expression as an actual parameter to generate the different reports mentioned previously. Here's the SELECT statement:
SELECT 'Report Generated on '||TO_CHAR(SYSDATE,'MM/DD/YYYY') "Report1"
FROM DUAL
WHERE f_report(
CURSOR(SELECT h.hrc_descr, o.org_short_name
FROM hrc_tab h, org_tab o
WHERE o.hrc_code = h.hrc_code
AND 1 < (SELECT count(os.site_no)
FROM org_site_tab os
WHERE os.org_id = o.org_id)
),
'List of Organizations located in more than one site'
) = 1;
Because dbms_output.put_line is being called from inside a function used in a SQL SELECT, the output buffer should be flushed. You do this by executing a small procedure called "flush," as follows:
CREATE OR REPLACE PROCEDURE flush
IS
BEGIN
NULL;
END;
/
Here's the output of this SELECT statement after executing flush:
You can use the same function to generate a different report—for example, a report that contains a list of organizations that don't have a vice president (VP). In this case, the function is invoked with a different cursor expression. Here's the second SELECT statement:
SELECT 'Report Generated on '||TO_CHAR(SYSDATE,'MM/DD/YYYY') "Report2"
FROM DUAL
WHERE f_report(
CURSOR(SELECT h.hrc_descr, o.org_short_name
FROM hrc_tab h, org_tab o
WHERE o.hrc_code = h.hrc_code
AND NOT EXISTS (SELECT *
FROM org_tab o1
WHERE o1.org_id = o.org_id
AND o1.hrc_code = 2 )
), 'List of Organizations not having a VP'
) = 1;
Here's the output of the second SELECT statement (the output buffer is flushed in this case also):
Instead of using the function f_report with dbms_output.put_line called to display output, you can directly generate the output using a SELECT column list. For this I use the following function:
CREATE OR REPLACE FUNCTION f_cursor(p_cursor SYS_REFCURSOR)
RETURN NUMBER
IS
v_org_short_name VARCHAR2(30);
v_cnt NUMBER := 0;
v_ret_code NUMBER;
BEGIN
BEGIN
LOOP
FETCH p_cursor INTO v_org_short_name;
EXIT WHEN p_cursor%NOTFOUND;
v_cnt := v_cnt + 1;
END LOOP;
IF (v_cnt > 0) THEN
v_ret_code := 1;
ELSE
v_ret_code := 0;
END IF;
EXCEPTION WHEN OTHERS THEN
v_ret_code := SQLCODE;
END;
RETURN (v_ret_code);
END;
/
Then you can generate the first report by using the following SELECT statement (there's no need to flush the output buffer):
SELECT rpad(h.hrc_descr,20,' ') "Hierarchy",
rpad(o.org_short_name,30,' ') "Organization"
FROM hrc_tab h, org_tab o
WHERE h.hrc_code = o.hrc_code
AND f_cursor(
CURSOR(SELECT o1.org_short_name
FROM org_tab o1
WHERE o1.org_id = o.org_id
AND 1 < (SELECT count(os.site_no)
FROM org_site_tab os
WHERE os.org_id = o1.org_id)
)
) = 1;
Here's the output generated:
Similarly, you can generate the second report by using the following SELECT (there's no need to flush the output buffer):
SELECT rpad(h.hrc_descr,20,' ') "Hierarchy",
rpad(o.org_short_name,30,' ') "Organization"
FROM hrc_tab h, org_tab o
WHERE h.hrc_code = o.hrc_code
AND f_cursor(
CURSOR(SELECT o1.org_short_name
FROM org_tab o1
WHERE o1.org_id = o.org_id
AND NOT EXISTS (SELECT *
FROM org_tab o2
WHERE o2.org_id = o1.org_id
AND o2.hrc_code = 2 )
)
) = 1;
Here's the output generated:
Both the functions f_report and f_cursor are invoked by passing a cursor expression as an actual parameter.
You can't use cursor expressions as actual parameters to functions with formal parameters of type REF CURSOR or SYS_REFCURSOR if the function is called in PL/SQL. For example, the following code is invalid:
DECLARE
v_num NUMBER;
BEGIN
v_num := f_report(
CURSOR(SELECT h.hrc_descr, o.org_long_name
FROM hrc_tab h, org_tab o
WHERE o.hrc_code = h.hrc_code
AND 1 < (SELECT count(os.site_no)
FROM org_site_tab os
WHERE os.org_id = o.org_id)
),
'List of Organizations located in more than one site'
) ;
END;
/
The preceding code raises the following error:
No comments:
Post a Comment