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');