Oracle Magazine - September/October 2008

BEGIN DBMS_OUTPUT.PUT_LINE (

mortgage_calcs.loan_rate ( loan_type_in => l_type ,years_in => l_term);

END loan_rate;

codeLISTING 3: Using OPEN FOR, FETCH BULK COLLECT, and LIMIT

PROCEDURE process_rows ( where_in IN VARCHAR2, limit_in IN PLS_INTEGER DEFAULT 100)

IS

TYPE employees_t IS TABLE OF employees.last_name%TYPE INDEX BY PLS_INTEGER;

l_names employees_t; l_cursor sys_refcursor;

Named notation leads to code that is much more readable and easier to maintain, but it also hard-codes the parameter name in the call to the program.

There is, unfortunately, no way to mask changes to parameter names when your code uses named notation. You will need to find every occurrence of named notation calls to the changed program and change the parameter name. Change the program type. Sometimes you may find the need (or feel the desire) to change the type of your program from procedure to function or vice versa. A previous developer might, for example, have created a function that returns values via OUT arguments in the function. Many of us feel that that is a bad design for a function and would prefer to use a procedure.

In such a case, I would make sure that the program is in a package and then create a second overloading of the program with the same name but a different type. Then I would define one of those programs as a pass-through, so that there is a single implementation of the program’s algorithm, but also allow existing calls to the program to remain intact. Add an IN parameter with a default value to the end of the parameter list. The most common change to programs involves the addition of parameters to the parameter list. If you add an IN parameter to the end of the parameter list, none of the existing calls to the program needs to be changed in the least—as long as you provide a default value for that parameter.

If any of those calls need to use a nondefault value for that new IN parameter, however, you will need to change only those calls, adding a value for that parameter. Otherwise, PL/SQL will automatically assign a default value

BEGIN

DBMS_OUTPUT.put_line (

‘Employees identified by “ ’ || where_in || ‘ ” ’);

OPEN l_cursor FOR

‘SELECT last_name FROM employees WHERE ‘ || where_in;

LOOP

FETCH l_cursor

BULK COLLECT INTO l_names LIMIT limit_in;

FOR l_index IN 1 .. l_names.COUNT

LOOP

/* Do the processing here. */ DBMS_OUTPUT.put_line (l_names (l_index));

END LOOP;

EXIT WHEN l_cursor%NOTFOUND;

END LOOP;

CLOSE l_cursor;

END process_rows;

/

as the actual argument and then execute the code in the program.

Make any other changes to the parameter list. Suppose you need to change the parameter list in any other way, such as O Adding an IN parameter inside the parameter list (not at the end)

O Adding an OUT or IN OUT parameter O Removing a parameter

Any of these parameter changes will force you to change every existing call to the program. To avoid this, you should leave the existing program unchanged and add an overloading with that same program name and the new parameter list, which means that all existing calls to the program remain valid. You can then very selectively change only those calls that actually need to take advantage of the change in the parameter list. Conclusion. We change the header of a program because our users have changed the requirements for that program. Sometimes those changes affect every single call to the program already in place in the application. In other circumstances, some or all of the

existing calls are not affected by the change in requirements.

If you are careful about how you implement your changes, you can minimize the need to propagate those changes throughout your application code base. The best ways to do this include moving your schema-level programs into packages and taking advantage of overloading, adding trailing IN parameters with default values, and redefining existing programs as pass-throughs.

CHANGING WHERE

I need to write a procedure to process multiple rows of data from a table, and each time I call the procedure, the WHERE clause may change. I would like to use EXECUTE IMMEDIATE, but that lets me return only a single row of data. How can I avoid the nightmare of maintaining code in multiple procedures that is completely identical except for the WHERE clause?