Previous | Table of Contents | Next

Page 233

evaluated. An OUT parameter may only appear on the left side of an assignment statement. An IN OUT parameter can appear anywhere.

In general, use the most restrictive mode as needed for the subprogram. This provides the greatest protection from programming errors where variables are inadvertently trashed during a subprogram execution.

Try coding all your single row lookup routines as procedures. Follow the same coding style for single-row lookups. Pass in the key values, a return row type record variable, and a status indicator. This style has the advantage of encapsulating the SQL statement or cursor in order to handle exceptions inline, as well as making for reusable code fragments. It is also an easily maintainable style. Listing 10.27 shows an example of this style:

Listing 10.27 table.sql—A Standardized Coding Style Lends Itself to Maintainable Code

DECLARE
  -- constants
  TB CONSTANT VARCHAR2(1) := CHR(9);  -- TAB
  -- variables
  status NUMERIC;
  table_rec all_tables%TYPE;
  -- routines
  PROCEDURE get_table(Powner  IN     all_tables.owner%TYPE,
                      Ptable  IN     all_tables.table_name%TYPE,
                      Prec       OUT all_tables%TYPE,
                      Pstatus IN OUT NUMERIC) IS
    -- local cursors
    CURSOR table_cur(Cowner all_tables.owner%TYPE,
                     Ctable all_tables.table_name%TYPE) IS
      SELECT *
      FROM all_tables
      WHERE owner = Cowner AND table_name = Ctable;
    -- local variables
    Lowner all_tables.owner%TYPE;
    Ltable all_tables.table_name%TYPE;
  BEGIN
    Pstatus := 0;  -- OK
    Lowner := UPPER(Powner);
    Ltable := UPPER(Ptable);
    OPEN table_cur(Lowner, Ltable);
    FETCH table_cur INTO Prec;
    IF (table_cur%NOTFOUND) THEN
      RAISE NO_DATA_FOUND;
    END IF;
    CLOSE table_cur;
  EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      Pstatus := SQLCODE;  -- capture error code
      IF (table_cur%ISOPEN) THEN  -- close the open cursor
                                                            continues

Page 234

Listing 10.27 Continued


        CLOSE table_cur;
      END IF;
      Prec := NULL;  -- clear return values and display input values
      DBMS_OUTPUT.put_line(`get_table: ` || SQLERRM(Pstatus));
      DBMS_OUTPUT.put_line(`OWNER = ` || `<` || Lowner || `>');
      DBMS_OUTPUT.put_line(`TABLE = ` || `<` || Ltable || `>');
    EXCEPTION
    WHEN OTHERS THEN
      NULL;  -- don't care (avoid infinite loop)
    END;
  END get_table;
BEGIN  -- display storage parameters for a given table
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.put_line(`TABLE'   || TB || `TABLESPACE'  || TB ||
                       `INITIAL' || TB || `NEXT'  || TB || `MAX');
  DBMS_OUTPUT.put_line(RPAD(`-', 43, `-'));  -- just an underline
  get_table(`scott', `dept', table_rec, status);
  IF (status = 0) THEN
    DBMS_OUTPUT.put_line(
      table_rec.table_name      || TB ||
      table_rec.tablespace_name || TB ||
      table_rec.initial_extent  || TB ||
      table_rec.next_extent     || TB ||
      table_rec.max_extents);
  END IF;
  get_table(`scott', `garbage', table_rec, status);
  IF (status = 0) THEN
    DBMS_OUTPUT.put_line(
      table_rec.table_name      || TB ||
      table_rec.tablespace_name || TB ||
      table_rec.initial_extent  || TB ||
      table_rec.next_extent     || TB ||
      table_rec.max_extents);
  END IF;
END;
/

The server returns the following:

TABLE   TABLESPACE      INITIAL NEXT    MAX
-------------------------------------------
DEPT    USER_DATA       10240   10240   121
get_table: ORA-01403: no data found
OWNER = <SCOTT>
TABLE = <GARBAGE>
PL/SQL procedure successfully completed.

If you anticipate an exact match using a unique key, manage the cursor yourself and perform exactly one fetch. When detecting no rows, close the cursor in the exception handler, rather than inside the conditional block (it has to be closed in the exception block anyway, so why code it three times?). Note that you must raise the predefined exception NO_DATA_FOUND,

Page 235

because the fetch does not generate one automatically. The input values are converted to uppercase using local variables because the converted values are used in more than one place.

Also take note of the additional information displayed by the exception handler. Why not take the opportunity to show the key values that the exception occurred on? This would be especially valuable when processing a large number of rows. This information could also have been dumped to an error table for post mortem analysis.

You might be thinking, "I can get the same information with a simple SELECT statement. What does all this buy me?" In the larger scheme of things, canned queries are more efficient because they can be found in the shared SQL area and reused. The manual control of the cursor with its single fetch is certainly more efficient, especially when it is run thousands of times over and over. Remember, your goal is to write efficient applications. After you have the row that was found, you can programmatically do anything you want with it. You have total flexibility and control, yet the underlying procedure is coded once and reused.

Listing 10.28 shows another example that implements a binary search on a PL/SQL table containing numeric values.

Listing 10.28 bintest.sql—A Binary Search Routine Made Easy to Use with a Procedure

SET SERVEROUTPUT ON
DECLARE
  -- constants
  FIXED_TOP CONSTANT NUMBER := 12;  -- fixed # of elements
  -- data types
  TYPE NUMARR_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  -- global variables
  numarr NUMARR_TYPE;
  isfound BOOLEAN;
  rowout NUMBER;

  -- routines
  PROCEDURE binary_search(  -- binary search on sorted array
      Parr    IN NUMARR_TYPE,
      Pnum    IN NUMBER,
      Pfound OUT BOOLEAN,
      Prow   OUT NUMBER) IS
    local_found BOOLEAN := NULL;
    top BINARY_INTEGER := FIXED_TOP;
    bottom BINARY_INTEGER := 1;
    middle BINARY_INTEGER := NULL;
  BEGIN
    local_found := FALSE;
    LOOP  -- binary search
      middle := ROUND((top + bottom) / 2);  -- find middle
      IF (Parr(middle) = Pnum) THEN  -- exact match
        local_found := TRUE; -- match succeeded
        EXIT;  -- break
                                                           continues

Page 236

Listing 10.28 Continued

      ELSIF (Parr(middle) < Pnum) THEN  -- GO UP
        bottom := middle + 1;
      ELSE  -- GO DOWN
        top := middle - 1;
      END IF;  -- test for match
      IF (bottom > top) THEN  -- search failed
        IF (Pnum > Parr(middle)) THEN
          middle := middle + 1;    -- MAY BE OUTSIDE ARRAY!
        END IF;  -- insert after
        EXIT;
      END IF;  -- failed
    END LOOP;  -- search
    Pfound := local_found;
    Prow := middle;
  EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(middle));
  END binary_search;

  FUNCTION bool_to_char(Pbool IN BOOLEAN)  -- convert Boolean to char
  RETURN VARCHAR2 IS
    str VARCHAR2(5);  -- capture string to return
  BEGIN
    IF (Pbool) THEN  -- test Boolean value for TRUE
      str := `TRUE';
    ELSIF (NOT Pbool) THEN  -- FALSE
      str := `FALSE';
    ELSE  -- must be NULL
      str := `NULL';
    END IF;  -- test Boolean value
    RETURN (str);
  END bool_to_char;
BEGIN  -- bintest executable code
  DBMS_OUTPUT.enable;
  numarr(1) := 100;  -- fill array with numbers in order
  numarr(2) := 103;
  numarr(3) := 104;
  numarr(4) := 108;
  numarr(5) := 110;
  numarr(6) := 120;
  numarr(7) := 121;
  numarr(8) := 122;
  numarr(9) := 130;
  numarr(10) := 140;
  numarr(11) := 145;
  numarr(12) := 149;
  binary_search(numarr, 90, isfound, rowout);
  DBMS_OUTPUT.put_line(`FOUND=' || bool_to_char(isfound) ||
                      `, ROW=' || TO_CHAR(rowout) || ` SB=1');
  binary_search(numarr, 150, isfound, rowout);
  DBMS_OUTPUT.put_line(`FOUND=' || bool_to_char(isfound) ||
                      `, ROW=' || TO_CHAR(rowout) || ` SB=13');

Previous | Table of Contents | Next