|
Chapter 4 Native Dynamic SQL in Oracle8i |
|
Now that you have seen the syntax of OPEN FOR and been introduced to cursor variables, let's explore the nuances involved in multirow queries with NDS.
When you execute an OPEN FOR statement, the PL/SQL runtime engine does the following:
Associates a cursor variable with the query found in the query string
Evaluates any bind arguments and substitutes those values for the placeholders found in the query string
Executes the query
Identifies the result set
Positions the cursor on the first row in the result set
Note that any bind arguments (provided in the USING clause) in the query are evaluated only when the cursor variable is opened. This means that if you want to use a different set of bind arguments for the same dynamic query, you must issue a new OPEN FOR statement with those arguments.
TIP: This approach is actually less efficient than the DBMS_SQL approach, which will allow you to simply rebind and then execute without having to reparse.
To perform a multirow query, you take these steps:
Declare a REF CURSOR type (if one is not already available, as it could be if defined in a package specification).
Declare a cursor variable based on the REF CURSOR.
OPEN the cursor variable FOR your query string.
Use the FETCH statement to fetch one row at a time from the query.
Check cursor attributes (%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN) as necessary.
Here is a simple program to display the specified column of any table for the rows indicated by the WHERE clause (it will work for number, date, and string columns):
/* Filename on companion disk: showcol.sp */ CREATE OR REPLACE PROCEDURE showcol ( tab IN VARCHAR2, col IN VARCHAR2, whr IN VARCHAR2 := NULL) IS TYPE cv_type IS REF CURSOR; cv cv_type; val VARCHAR2(32767); BEGIN /* Construct the very dynamic query and open the cursor. */ OPEN cv FOR 'SELECT ' || col || ' FROM ' || tab || ' WHERE ' || NVL (whr, '1 = 1'); LOOP /* Fetch the next row, and stop if no more rows. */ FETCH cv INTO val; EXIT WHEN cv%NOTFOUND; /* Display the data, with a header before the first row. */ IF cv%ROWCOUNT = 1 THEN DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-')); DBMS_OUTPUT.PUT_LINE ( 'Contents of ' || UPPER (tab) || '.' || UPPER (col)); DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-')); END IF; DBMS_OUTPUT.PUT_LINE (val); END LOOP; CLOSE cv; --All done, so clean up! END; /
Here are some examples of output from this procedure:
SQL> exec showcol ('emp', 'ename', 'deptno=10') -------------------------------------------------- Contents of EMP.ENAME -------------------------------------------------- CLARK KING MILLER
BEGIN showcol ( 'emp', 'ename || ''-$'' || sal', 'comm IS NOT NULL'); END; / -------------------------------------------------- Contents of EMP.ENAME || '-$' || SAL -------------------------------------------------- ALLEN-$1600 WARD-$1250 MARTIN-$1250 TURNER-$1500
The FETCH statement in the showcol procedure shown in the previous section fetches into an individual variable. You could also FETCH into a sequence of variables, as shown here:
DECLARE TYPE cv_type IS REF CURSOR; cv cv_type; mega_bucks company.ceo_compensation%TYPE; achieved_by company.layoff_count%TYPE; BEGIN OPEN cv FOR 'SELECT ceo_compensation, layoff_count FROM company WHERE ' || NVL (whr, '1 = 1'); LOOP FETCH cv INTO mega_bucks, achieved_by;
Working with a long list of variables in the FETCH list gets cumbersome and inflexible: you have to declare the variables, keep that set of values synchronized with the FETCH statement, and so on. To ease our troubles, NDS allows us to fetch into a record, as shown here:
DECLARE TYPE cv_type IS REF CURSOR; cv cv_type; ceo_info company%ROWTYPE; BEGIN OPEN cv FOR 'SELECT * FROM company WHERE ' || NVL (whr, '1 = 1'); LOOP FETCH cv INTO ceo_info;
Of course, in many situations you will not want to do a SELECT *; this statement can be very inefficient if your table has hundreds of columns and you only need to work with three of those hundreds. A better approach is to create record TYPEs that correspond to different requirements. The best place to put these structures is in a package specification, so they can be used throughout your application. Here's one such package:
CREATE OR REPLACE PACKAGE company_struc IS TYPE dynsql_curtype IS REF CURSOR; TYPE ceo_info_rt IS RECORD ( mega_bucks company.ceo_compensation%TYPE, achieved_by company.layoff_count%TYPE); END company_struc;
With this package in place, I can rewrite my CEO-related code as follows:
DECLARE cur company_struc.dynsql_curtype; rec company_struc.ceo_info_rt; BEGIN OPEN cv FOR 'SELECT ceo_compensation, layoff_count FROM company WHERE ' || NVL (whr, '1 = 1'); LOOP FETCH cv INTO rec;
As with the EXECUTE IMMEDIATE statement, you can pass in bind arguments when you open a cursor. You can only provide IN arguments for a query. By using bind arguments you can improve the performance of your SQL also, and also make it easier to write and maintain that code. (See Section 4.4, "Binding Variables later in this chapter for information about this technique.)
Let's revisit the showcol procedure. That procedure accepted a completely generic WHERE clause. Suppose that I have a more specialized requirement: I want to display (or in some other way process) all column information for rows that contain a date column with a value within a certain range. In other words, I want to be able to satisfy this query:
SELECT ename FROM emp WHERE hiredate BETWEEN x AND y;
as well as this query:
SELECT name FROM war_criminal WHERE killing_date BETWEEN x AND y;
I also want to make sure that the time component of the date column does not play a role in the WHERE condition.
Here is the header for the procedure:
/* Filename on companion disk: showcol2.sp */ PROCEDURE showcol ( tab IN VARCHAR2, col IN VARCHAR2, dtcol IN VARCHAR2, dt1 IN DATE, dt2 IN DATE := NULL)
The OPEN FOR statement now contains two placeholders and a USING clause to match:
OPEN cv FOR 'SELECT ' || col || ' FROM ' || tab || ' WHERE ' || dtcol || ' BETWEEN TRUNC (:startdt) AND TRUNC (:enddt)' USING dt1, NVL (dt2, dt1+1);
I have crafted this statement so that if the user does not supply an end date, the WHERE clause returns rows whose date column is the same day as the dt1 provided. The rest of the showcol procedure remains the same, except for some cosmetic changes in the display of the header.
The following call to this new version of showcol asks to see the names of all employees hired in 1982:
BEGIN showcol ('emp', 'ename', 'hiredate', '01-jan-82', '31-dec-82'); END; / ---------------------------------------------------------------------- Contents of EMP.ENAME for HIREDATE between 01-JAN-82 and 31-DEC-82 ---------------------------------------------------------------------- MILLER
How many times have you written a query along these lines:
SELECT some-columns, COUNT(*) FROM your-table GROUP BY some-columns;
And then there is the variation involving the HAVING clause (you don't want to see all the counts, you just want to see those groupings where there is more than one identical value, and so on). These are very common requirements, but with NDS, you can easily build a program that does all the work for you, for any table, and for any single column (and this is extensible to multiple columns as well).
Here is the header of such a procedure:
/* Filename on companion disk: countby.sp */ PROCEDURE countBy ( tab IN VARCHAR2, col IN VARCHAR2, atleast IN INTEGER := NULL, sch IN VARCHAR2 := NULL, maxlen IN INTEGER := 30)
The name of the table.
The name of the column.
The name of the schema (default of NULL = USER).
If you supply a non-NULL value for atleast, then the SELECT statement includes a HAVING COUNT(*) greater than that value.
Used for formatting of the output.
You can look at the countby.sp file on the companion disk to see the full implementation; here is all the code except that used to do the formatting (header string and so on):
IS TYPE cv_type IS REF CURSOR; cv cv_type; SQL_string VARCHAR2(32767) := 'SELECT ' || col || ', COUNT(*) FROM ' || NVL (sch, USER) || '.' || tab || ' GROUP BY ' || col; v_val VARCHAR2(32767); v_count INTEGER; BEGIN IF atleast IS NOT NULL THEN SQL_string := SQL_string || ' HAVING COUNT(*) >= ' || atleast; END IF; OPEN cv FOR SQL_String; LOOP FETCH cv INTO v_val, v_count; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE (RPAD (v_val, maxlen) || ' ' || v_count); END LOOP; CLOSE cv; END; /
As you start to build more and more of these generic utilities, you will find that it doesn't take very much code or effort -- you just have to think through the steps of the SQL string construction carefully.
Displaying information is useful for test purposes, but in many cases you want to work with the queried information further, not simply show it. Let's build on the countby procedure shown in the previous section to provide an implementation in which the results of the dynamic query are stored in an index-by table for subsequent analysis.
Here is the specification of the package:
/* Filename on companion disk: countby.pkg */ CREATE OR REPLACE PACKAGE grp IS TYPE results_rt IS RECORD ( val VARCHAR2(4000), countby INTEGER); TYPE results_tt IS TABLE OF results_rt INDEX BY BINARY_INTEGER; FUNCTION countBy ( tab IN VARCHAR2, col IN VARCHAR2, atleast IN INTEGER := NULL, sch IN VARCHAR2 := NULL, maxlen IN INTEGER := 30) RETURN results_tt; END grp; /
The implementation of the countby function is virtually the same as the procedure. The main difference is that I now have a record structure to fetch into, and an index-by table to fill. You can see both these changes in the loop that fetches the rows:
LOOP FETCH cv INTO rec; EXIT WHEN cv%NOTFOUND; retval(cv%ROWCOUNT) := rec; END LOOP;
With this package in place, I can very easily build programs that access this analytical information. Here is one example: :
/* Filename on companion disk: countby.tst */ DECLARE results grp.results_tt; indx PLS_INTEGER; minrow PLS_INTEGER; maxrow PLS_INTEGER; BEGIN results := grp.countby ('employee', 'department_id'); /* Find min and max counts. */ indx := results.FIRST; LOOP EXIT WHEN indx IS NULL; IF minrow IS NULL OR minrow > results(indx).countby THEN minrow := indx; END IF; IF maxrow IS NULL OR maxrow < results(indx).countby THEN maxrow := indx; END IF; /* Perform other processing as well... */ /* Move to next group count. */ indx := results.NEXT(indx); END LOOP; END; /
|
|
|
|
| 4.2 NDS Statement Summary |
|
4.4 Binding Variables |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.