Default values for parameters
Cursor parameters can be assigned default values. Here is an example of a parameterized cursor with a default value:
CURSOR emp_cur (emp_id_in NUMBER := 0) IS SELECT employee_id, emp_name FROM employee WHERE employee_id = emp_id_in;So if Joe Smith's employee ID is 1001, the following statements would set my_ emp_id to 1001 and my_emp_name to JOE SMITH:
OPEN emp_cur (1001);FETCH emp_cur INTO my_emp_id, my_emp_name;Because the emp_id_in parameter has a default value, I can also open and fetch from the cursor without specifying a value for the parameter. If I do not specify a value for the parameter, the cursor uses the default value.
14.4 BULK COLLECT
Oracle8i introduced a very powerful new feature that improves the efficiency of queries in PL/SQL: the BULK COLLECT clause. With BULK COLLECT you can retrieve multiple rows of data through either an implicit or an explicit query with a single roundtrip to and from the database. BULK COLLECT reduces the number of context switches between the PL/SQL and SQL statement executors and thereby reduces the overhead of retrieving data.
Take a look at the following code snippet. I need to retrieve hundreds of rows of data on automobiles that have a poor environmental record. I place that data into a set of collections so that I can easily and quickly manipulate the data for both analysis and reporting.
DECLARE CURSOR major_polluters_cur IS SELECT name, mileage FROM transportation WHERE TYPE = 'AUTOMOBILE' AND mileage < 20; names name_varray; mileages number_varray;BEGIN FOR bad_car IN major_polluters LOOP names.EXTEND; names (major_polluters%ROWCOUNT) := bad_car.NAME; mileages.EXTEND; mileages (major_polluters%ROWCOUNT) := bad_car.mileage; END LOOP; -- Now work with data in the collectionsEND;This certainly gets the job done, but the job might take a long time to complete. Consider this: if the transportation table contains 2,000 vehicles, then the PL/SQL engine issues 2,000 individual fetches against the cursor in the System Global Area (SGA).
To help out in this scenario, use the BULK COLLECT clause for the INTO element of your query. By using this clause in your cursor (explicit or implicit) you tell the SQL engine to bulk bind the output from the multiple rows fetched by the query into the specified collections before returning control to the PL/SQL engine. The syntax for this clause is:
... BULK COLLECT INTO collection_name[, collection_name] ...where collection_name identifies a collection.
Here are some rules and restrictions to keep in mind when using BULK COLLECT:
· Prior to Oracle9i, you could use BULK COLLECT only with static SQL. With Oracle9i, you can useBULK COLLECT with bothdynamic andstatic SQL. See Chapter 15 for more details and an example.
· You can use BULK COLLECT keywords in any of the following clauses: SELECT INTO, FETCH INTO, and RETURNING INTO.
· The collections you reference can store only scalar values (strings, numbers, dates). In other words, you cannot fetch a row of data into a record structure that is a row in a collection.
· The SQL engine automatically initializes and extends the collections you reference in the BULK COLLECT clause. It starts filling the collections at index 1, inserts elements consecutively (densely), and overwrites the values of any elements that were previously defined.
· You cannot use the SELECT...BULK COLLECT statement in a FORALL statement.
Let's explore these rules and the usefulness of BULK COLLECT through a series of examples.
First, here is a recoding of the "major polluters" example using BULK COLLECT:
DECLARE names name_varray; mileages number_varray;BEGIN SELECT name, mileage FROM transportation BULK COLLECT INTO names, mileages WHERE TYPE = 'AUTOMOBILE' AND mileage < 20; -- Now work with data in the collectionsEND;I am now able to remove the initialization and extension code from the row-by-row fetch implementation.
I don't have to rely on implicit cursors to get this job done. Here is another reworking of the major polluters example, retaining the explicit cursor: