Eyeballing Equivalent Implementations

First, let's compare the DBMS_SQL and NDS implementations of a program that displays all the employees for the specified and very dynamic WHERE clause.

The DBMS_SQL implementation:

CREATE OR REPLACE PROCEDURE showemps (where_in IN VARCHAR2 := NULL)IS cur INTEGER := DBMS_SQL.OPEN_CURSOR; rec employee%ROWTYPE; fdbk INTEGER;BEGIN DBMS_SQL.PARSE (cur, 'SELECT employee_id, last_name FROM employee WHERE ' || NVL (where_in, '1=1'), DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN (cur, 1, 1); DBMS_SQL.DEFINE_COLUMN (cur, 2, user, 30); fdbk := DBMS_SQL.EXECUTE (cur); LOOP /* Fetch next row. Exit when done. */ EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0; DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id); DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name); DBMS_OUTPUT.PUT_LINE ( TO_CHAR (rec.employee_id) || '=' || rec.last_name); END LOOP; DBMS_SQL.CLOSE_CURSOR (cur);END;

The NDS implementation:

CREATE OR REPLACE PROCEDURE showemps (where_in IN VARCHAR2 := NULL)IS TYPE cv_typ IS REF CURSOR; cv cv_typ; v_id employee.employee_id%TYPE; v_nm employee.last_name%TYPE;BEGIN OPEN cv FOR 'SELECT employee_id, last_name FROM employee WHERE ' || NVL (where_in, '1=1'); LOOP FETCH cv INTO v_id, v_nm; EXIT WHEN cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE ( TO_CHAR (v_id) || '=' || v_nm); END LOOP; CLOSE cv;END;

As you can see (and this is true in general), you'll write dramatically less code using NDS. And because the code you write will rely less on built-in packaged programs and more on native, standard elements of PL/SQL, that code is easier to build, read, and maintain.

15.7.2 What Are NDS and DBMS_SQL Good For?

Here are the reasons you should use NDS whenever possible:

· NDS is always at least a little bit more efficient than DBMS_SQL, and sometimes significantly more efficient.

· NDS is much easier to write; you need less code, and the code you write is more intuitive, leading to many fewer bugs. This reality is demonstrated in the next section, Section 15.7.1.

· NDS works with all SQL datatypes, including user-defined objects and collection types (variable arrays, nested tables, and associative arrays). DBMS_SQL works only with Oracle7-compatible datatypes.

· NDS allows you to fetch multiple columns of information directly into a PL/SQL record. With DBMS_SQL, you must fetch into individual variables.

Given this situation, why would anyone use DBMS_SQL ever again? Because NDS cannot do everything. The following list shows what can be handled only (or most easily) with DBMS_SQL:

· DBMS_SQL supports "Method 4" dynamic SQL, which means that at compile time you don't know how many columns you will be querying and/or how many bind variables will need to be set. Method 4 is the most complex form of dynamic SQL, and NDS doesn't support it directly. With NDS, you have to hardcode the number of elements in the USING and INTO clauses. If you don't know how many values you are binding until you execute the SQL statement, you can't very easily write the USING clause of your EXECUTE IMMEDIATE statement.

Now, to be completely honest, you can get around this restriction by constructing not just the dynamic query string, but also a dynamic PL/SQL block containing the dynamic query string with the variable number of INTO and USING elements. However, that is a very complicated workaround, in which you would be running an EXECUTE IMMEDIATE statement inside a dynamic string.

· DBMS_SQL allows you to describe the columns of your dynamic cursor, returning information about each column in an associative array of records. This capability offers the possibility of writing very generic cursor-processing code. See desccols.pkg for a demonstration of how to use this feature.

· With DBMS_SQL, you can parse arbitrarily long SQL and PL/SQL statements by relying on a collection to pass the dynamic statement. In NDS, you are restricted to a string of no more than 32 KB in length.

· You can call DBMS_SQL programs from within client-side PL/SQL code, such as an Oracle Forms library. You cannot yet do the same for NDS statements because they are not supported in the version of PL/SQL available in the developer tools.

The bottom line is that the NDS implementation will be able to handle 80 to 90% of the dynamic SQL requirements you are likely to face. It is good to know, however, that there is still a place for DBMS_SQL. (Especially since I wrote a 100-page chapter on that package in Oracle Built-in Packages!)

 

Part V: PL/SQL Application Construction

This part of the book is where it all comes together. By now, you've learned the basics. You know about declaring and working with variables. You're an expert on error handling and loop construction. Now it's time to build an application—and you do that by constructing the building blocks, made up of procedures, functions, packages, and triggers, as described in Chapters 16 through 18. The final chapter in this part, Chapter 19, discusses managing your PL/SQL code base, including suggestions for tuning and debugging that code.

Chapter 16

Chapter 17

Chapter 18

Chapter 19

Chapter 16. Procedures, Functions,and Parameters

Earlier parts of this book have explored in detail all of the components of the PL/SQL language: cursors, exceptions, loops, variables, and so on. While you certainly need to know about these components when you write applications using PL/SQL, putting the pieces together to create well-structured, easily understood, and smoothly maintainable programs is even more important.

Few of our tasks are straightforward. Few solutions can be glimpsed in an instant and immediately put to paper or keyboard. The systems we build are usually large and complex, with many interacting and sometimes conflicting components. Furthermore, as users deserve, demand, and receive applications that are easier to use and vastly more powerful than their predecessors, the inner world of those applications becomes correspondingly more complicated.

One of the biggest challenges in our profession today is finding ways to reduce the complexity of our environment. When faced with a massive problem to solve, the mind is likely to recoil in horror. Where do I start? How can I possibly find a way through that jungle of requirements and features?

A human being is not a massively parallel computer. Even the brightest of our bunch have trouble keeping track of more than seven tasks (plus or minus two) at one time. We need to break down huge, intimidating projects into smaller, more manageable components, and then further decompose those components into individual programs with an understandable scope. We can then figure out how to build and test those programs, after which we can construct a complete application from these building blocks.

Whether you use "top-down design" (a.k.a. step-wise refinement, which is explored in detail in Section 16.5 of this chapter) or some other methodology, there is absolutely no doubt that you will find your way to a high-quality and easily maintainable application by modularizing your code into procedures, functions, and object types.

16.1 Modular Code

Modularization is the process by which you break up large blocks of code into smaller pieces (modules) that can be called by other modules. Modularization of code is analogous to normalization of data, with many of the same benefits and a few additional advantages. With modularization, your code becomes:

 

More reusable

By breaking up a large program or entire application into individual components that "plug-and-play" together, you will usually find that many modules are used by more than one other program in your current application. Designed properly, these utility programs could even be of use in other applications!

 

More manageable

Which would you rather debug: a 10,000-line program or five individual 2,000-line programs that call each other as needed? Our minds work better when we can focus on smaller tasks. You can also test and debug on a smaller scale (called unit testing ) before individual modules are combined for a more complicated system test.

 

More readable

Modules have names, and names describe behavior. The more you move or hide your code behind a programmatic interface, the easier it is to read and understand what that program is doing. Modularization helps you focus on the big picture rather than on the individual executable statements.

 

More reliable

The code you produce will have fewer errors. The errors you do find will be easier to fix because they will be isolated within a module. In addition, your code will be easier to maintain because there is less of it and it is more readable.

Once you become proficient with the different iterative, conditional, and cursor constructs of the PL/SQL language (the IF statement, loops, etc.), you are ready to write programs. You will not really be ready to build an application, however, until you understand how to create and combine PL/SQL modules.

PL/SQL offers the following structures that modularize your code in different ways:

 

Procedure

A program that performs one or more actions and is called as an executable PL/SQL statement. You can pass information into and out of a procedure through its parameter list.

 

Function

A program that returns a single value and is used just like a PL/ SQL expression. You can pass information into a function through its parameter list.

 

Database trigger

A set of commands that are triggered to execute (e.g., log in, modify a row in a table, execute a DDL statement) when an event occurs in the database.

 

Package

A named collection of procedures, functions, types, and variables. A package is not really a module (it's more of a meta-module), but it is so closely related that I mention it here.

 

Object type or instance of an object type.

Oracle's version of (or attempt to emulate) an object-oriented class. Object types encapsulate state and behavior, combining data (like a relational table) with rules (procedures and functions that operate on that data).

Packages are discussed in Chapter 17; database triggers are explored in Chapter 18. You can read more about object types in Chapter 21. This chapter focuses on how to build procedures and functions, and how to design the parameter lists that are an integral part of well-designed modules.

I use the term module to mean either a function or a procedure. As is the case with many other programming languages, modules can call other named modules. You can pass information into and out of modules with parameters. Finally, the modular structure of PL/SQL also integrates tightly with exception handlers to provide all-encompassing error-checking techniques (see Chapter 6).

This chapter explores how to define procedures and functions, and then dives into the details of setting up parameter lists for these programs. We also examine some of the more "exotic" aspects of program construction, including local modules, overloading, forward referencing, deterministic functions, and table functions.