1.6.1 Anchored declarations

You can use the %TYPE and %ROWTYPE declaration attributes to anchor the datatype of one variable to that of a previously existing variable or data structure. The anchoring data structure can be a column in a database table, the entire table itself, a programmer-defined record, or a local PL/SQL variable. In the following example, I declare a local variable with the same structure as the company name:

my_company company.name%TYPE;

See Chapter 4 for details.

1.6.2 Built-in functions

PL/SQL offers dozens of built-in functions to help you get your job done with the minimum amount of code and fuss possible. Some of them are straightforward, such as the LENGTH function, which returns the length of the specified string. Others offer subtle variations which will aid you greatly -- but only when you are aware of those variations.

Two of my favorites in this category of hidden talents are SUBSTR and INSTR, both character functions. SUBSTR returns a subportion of a string. INSTR returns the position in a string where a substring is found. Most developers only use these functions to search forward through the strings. By passing a negative starting location, however, SUBSTR will count from the end of the string. And INSTR will actually scan in reverse through the string for the nth occurrence of a substring.

See the chapters in Part 3 for details.

1.6.3 Built-in packages

In addition to the many built-in functions provided by PL/SQL, Oracle Corporation also offers many built-in packages. These packages of functions, procedures, and data structures greatly expand the scope of the PL/SQL language. With each new release of the Oracle Server, we get new packages to improve our own programs.

It is no longer sufficient for a developer to become familiar simply with the basic PL/SQL functions like TO_CHAR, ROUND, and so on. Those functions have now become only the innermost layer of useful functionality. Oracle Corporation has built upon those functions, and you should do the same thing.

See Appendix C for a summary of the Application Programming Interfaces (APIs) of the built-in packages.

1.6.4 The cursor FOR loop

The cursor FOR loop is one of my favorite PL/SQL constructs. It leverages fully the tight and effective integration of the Ada-like programming language with the power of the SQL database language. It reduces the volume of code you need to write to fetch data from a cursor. It greatly lessens the chance of introducing loop errors in your programming -- and loops are one of the more error-prone parts of a program. Does this loop sound too good to be true? Well, it isn't -- it's all true!

See Chapter 7, Loops, for more information.

1.6.5 Scoping with nested blocks

The general advantage of -- and motivation for -- a nested block is that you create a scope for all the declared objects and executable statements in that block. You can use this scope to improve your control over activity in your program, particularly in the area of exception handling.

In the following procedure, I have placed BEGIN and END keywords around a sequence of DELETE statements. This way, if any DELETE statement fails, I trap the exception, ignore the problem, and move on to the next DELETE:

PROCEDURE delete_details
IS
BEGIN
   BEGIN
      DELETE FROM child1 WHERE ...;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;

   BEGIN
      DELETE FROM child2 WHERE ...;
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;
END;

I can in this way use my nested blocks to allow my PL/SQL program to continue past exceptions.

See Chapter 15, Procedures and Functions, for details.

1.6.6 Module overloading

Within a package and within the declaration section of a PL/SQL block, you can define more than one module with the same name! The name is, in other words, overloaded. In the following example, I have overloaded the value_ok function in the body of the check package:

PACKAGE BODY check
IS
   /* First version takes a DATE parameter. */
   FUNCTION value_ok (date_in IN DATE) RETURN BOOLEAN
   IS
   BEGIN
      RETURN date_in <= SYSDATE;
   END;

   /* Second version takes a NUMBER parameter. */
   FUNCTION value_ok (number_in IN NUMBER) RETURN BOOLEAN
   IS
   BEGIN
      RETURN number_in > 0;
   END;
END;

Overloading can greatly simplify your life and the lives of other developers. This technique consolidates the call interfaces for many similar programs into a single module name. It transfers the burden of knowledge from the developer to the software. You do not have to try to remember, for example, the six different names for programs which all add values (dates, strings, Booleans, numbers, etc.) to various PL/SQL tables.

Instead, you simply tell the compiler that you want to "add" and pass it the value you want added. PL/SQL and your overloaded programs figure out what you want to do and they do it for you.

See Chapter 15 for details.

1.6.7 Local modules

A local module is a procedure or function defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is only defined within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside of that enclosing block.

See Chapter 15 for details.

1.6.8 Packages

A package is a collection of related elements, including modules, variables, table and record TYPEs, cursors, and exceptions. Packages are among the least understood and most underutilized features of PL/SQL. That is a shame, because the package structure is also one of the most useful constructs for building well-designed PL/SQL-based applications. Packages provide a structure in which you can organize your modules and other PL/SQL elements. They encourage proper programming techniques in an environment that often befuddles the implementation of good design.

With packages, you can:

• Create abstract datatypes and employ object-oriented design principles in your Oracle-based applications.

• Use top-down design techniques comprehensively. You can build package specifications devoid of any code and actually compile programs that call the modules in these "stub" packages.

• Create and manipulate data that persist throughout a database session. You can use variables that are declared in a package to create global data structures.

See Chapter 16, Packages, for details. The disk that accompanies this book contains many examples of packages. The frontend software gives you an easy-to-use interface to the code and explanations for using it.