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