Page 230
especially true for loops. If you don't code a block with an exception handler for an SQL statement within a loop, any exception will cause the loop to immediately terminate by jumping to the enclosing block's exception handler (the "bubble up" syndrome). To illustrate the point:
LOOP -- get some rows from a master table FETCH master_cursor INTO master_rec; EXIT WHEN master_cursor%NOTFOUND; BEGIN -- delete some child table rows -- master primary key is foreign key in child DELETE FROM child__table WHERE master_fkey = master_rec.master_pkey; EXCEPTION -- something untoward occurred WHEN OTHERS THEN -- so output a message, say status := SQLCODE; -- always capture error code! DBMS_OUTPUT.put_line(`during delete: ` || SQLERRM(status)); END; -- delete some child table rows END LOOP;
If the enclosing block with an exception handler was not present, an exception (such as no rows found) would immediately bomb out of the loop. The graceful thing you probably wanted to do is to skip this row and go on to the next. Handling the exception inline meets this desire.
Now you're going to look at subprograms. Subprograms enable you to modularize our code and make it more efficient.
What's a Subprogram?A subprogram is a subroutine that can be called one or more times. I use the term subprogram generically and interchangeably with subroutine. Actually, subprogram is the term Oracle started with, and I just picked up on it.
PL/SQL has two types of subprograms (or subroutines): procedures and functions. In an unnamed PL/SQL block, functions and procedures are declared (giving the name and optional parameters and type) and defined (giving the executable code) in the Declaration section. You can think of them as callable static code fragments; they must be referenced at runtime in order to execute the code they contain. These subprograms, like most blocks, contain declaration (except the subprogram name substitutes for the keyword DECLARE), body and optional exception handler sections, and are terminated with an END statement, optionally labeled with the subroutine name.
FunctionsA function is a subroutine whose name returns a single value of some one particular data type. You assign this return value to a variable of the same data type, or you use the return value in an expression. Functions often take parameters. When no parameters are passed, no parentheses are used. Some simple and familiar examples:
status := SQLCODE; -- note no parentheses NL CONSTANT VARCHAR2(1) := CHR(10); -- ASCII character (newline) IF (employee_exists(`Scaboda')) THEN -- use them in expressions
In an unnamed PL/SQL block, you declare and implement them at the same time, in the block's declaration section, following all other declarations. Listing 10.26 some examples of functions.
Page 231
Listing 10.26 boolsub.sqlFunctions Make Repetitive Tasks Easier
DECLARE -- local variables x NUMBER(3); -- local subprograms (must follow all other declarations!) FUNCTION is_even(Pnum IN x%TYPE) -- returns TRUE if number is even RETURN BOOLEAN IS BEGIN RETURN(MOD(Pnum, 2) = 0); -- test for evenness EXCEPTION WHEN OTHERS THEN RETURN (NULL); -- indeterminate END is_even; FUNCTION bool_to_char(Pbool IN BOOLEAN) 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 -- executable code x := 0; DBMS_OUTPUT.put_line(`It is ` || bool_to_char(is_even(x)) || ` that ` || TO_CHAR(x) || ` is even'); x := 1; DBMS_OUTPUT.put_line(`It is ` || bool_to_char(is_even(x)) || ` that ` || TO_CHAR(x) || ` is even'); x := 2; DBMS_OUTPUT.put_line(`It is ` || bool_to_char(is_even(x)) || ` that ` || TO_CHAR(x) || ` is even'); x := 3; DBMS_OUTPUT.put_line(`It is ` || bool_to_char(is_even(x)) || ` that ` || TO_CHAR(x) || ` is even'); x := 4; DBMS_OUTPUT.put_line(`It is ` || bool_to_char(is_even(x)) || ` that ` || TO_CHAR(x) || ` is even'); DBMS_OUTPUT.put_line(bool_to_char(NULL) || ` is neither TRUE nor FALSE'); END; /
The server's output is
It is TRUE that 0 is even It is FALSE that 1 is even It is TRUE that 2 is even It is FALSE that 3 is even It is TRUE that 4 is even NULL is neither TRUE nor FALSE PL/SQL procedure successfully completed.
Page 232
This rather lengthy example illustrates a few properties and restrictions of functions:
Note the verbose declaration of the function. The scope of parameter variables are strictly local to the function or any nested subprograms within it. You can nest other subprograms, which will exist only when program execution enters the enclosing subprogram, by defining them between the IS BEGIN portion. This technique of limiting scope to the smallest program unit helps reduce coupling between program routines, which is a good thing.
While it is technically legal to return a value on the parameter list (by specifying OUT or IN OUT for the mode), it is generally frowned upon as a poor programming style. If you feel obliged to do this, you should have a compelling reason and must document the precise usage, raising it to the level of a standard. This avoids abuses and nonstandard coding styles (one could argue that a function returning a value on the parameter list is already an abuse of style).
NOTE |
Most Oracle developers avoid returning values on a function's parameter list. To prevent the proliferation of hideous, unmaintainable code, your organization might want to prohibit this practice altogether. |
It is perfectly normal and acceptable to enclose very small chunks of code, even one-liners, in a function. This technique reduces code size and simplifies code maintenance. For example, if later on you wished to add some additional data validation to your function, you would only need to add it in the one place.
ProceduresA procedure is a subroutine that performs a bit of repetitive work, passing values in and out strictly through the parameter list. A procedure is always a single statement; you cannot insert it in an expression. The same scoping rules as for functions apply. The syntax is almost identical to functions, less the return statement.
If no mode is specified, it defaults to IN. As a matter of style, I always specify the mode. The compiler will catch whether a parameter is used inconsistently with its mode. An IN parameter must appear only on the right side of an assignment statement, or in any expression that can be