Page 237
binary_search(numarr, 100, isfound, rowout); DBMS_OUTPUT.put_line(`FOUND=' || bool_to_char(isfound) || `, ROW=' || TO_CHAR(rowout) || ` SB=1'); binary_search(numarr, 145, isfound, rowout); DBMS_OUTPUT.put_line(`FOUND=' || bool_to_char(isfound) || `, ROW=' || TO_CHAR(rowout) || ` SB=11'); binary_search(numarr, 108, isfound, rowout); DBMS_OUTPUT.put_line(`FOUND=' || bool_to_char(isfound) || `, ROW=' || TO_CHAR(rowout) || ` SB=4'); binary_search(numarr, 105, isfound, rowout); DBMS_OUTPUT.put_line(`FOUND=' || bool_to_char(isfound) || `, ROW=' || TO_CHAR(rowout) || ` SB=4'); END; -- bintest /
The output from the server is
FOUND=FALSE, ROW=1 SB=1 FOUND=FALSE, ROW=13 SB=13 FOUND=TRUE, ROW=1 SB=1 FOUND=TRUE, ROW=11 SB=11 FOUND=TRUE, ROW=4 SB=4 FOUND=FALSE, ROW=4 SB=4 PL/SQL procedure successfully completed.
Note the OUT parameter mode, which means output only. This means inside the procedure, this variable can only be written to. If you need to read and write from a parameter variable, declare it as IN OUT.
Does that binary to char conversion routine look familiar? Wouldn't it be nice not to have to paste into every PL/SQL program that needs it?
Default Parameter ValuesParameters can receive default values, to be used when the parameter is not provided in the actual call to the subprogram. This makes the subprogram appear as if it can have a variable list of parameters.
DECLARE ... -- types, constants, variables FUNCTION get_data (Pkey IN CHAR, Pflag IN BOOLEAN DEFAULT FALSE, Psort IN CHAR DEFAULT ` `) RETURN VARCHAR2 IS ... -- function implementation BEGIN -- executable code IF get_data(key1) THEN -- valid call (Pflag, Psort defaulted) ... ELSIF get_data(key2, TRUE) -- valid call (Psort defaulted) ... ELSIF get_data(key3, , `ASCENDING') THEN -- invalid!
Note the use of the keyword DEFAULT. You could also use the assignment operator (:=). As a coding convention, only DEFAULT is used in this context, to distinguish this semantically unusual construction from the more straightforward assignment upon declaration.
Page 238
Both default parameters can be left off intentionally, so in the first call to get_data the flag parameter is defaulted to FALSE, and the sort parameter is defaulted to spaces. This makes for a very clean coding style where you only specify the parameters of interest to you. Note, however, that you cannot skip a default parameter and provide the next one because this notation for specifying parameters is positional. The positions of the parameters is significant. You cannot try to use a placeholder, such as the extra comma above.
Positional and Named NotationYou can use an alternate notation, however, called named notation to specify parameters in any order. You provide the name of the parameter along with the value.
ELSIF get_data(key3, Psort => `ASCENDING') THEN -- valid (Pflag defaulted)
You can start off left to right using positional notation, then switch to named notation, which is known as mixed notation. Once you use named notation, you must then stick with it for subsequent parameters. Named notation can be used for any parameter, not just any that were defaulted.
ELSIF get_data(key3, Psort => `ASCENDING', Pflag => TRUE) THEN -- right ... ELSIF get_data(Pkey => key3, `ASCENDING', Pflag => TRUE) THEN -- wrong!
Although this seems convenient and unusual among programming languages, I've never needed it. But if you had a bunch of parameters and nearly every parameter was defaulted and you wanted to have maximum flexibility calling the subroutine, this is practically indispensable.
Built-in FunctionsNearly all the built-in functions and operators you use in SQL can also be used in PL/SQL expressions and procedural statements. There are only a few exceptions to this rule.
You cannot use `= ANY ( )' in an expression. Instead, use the IN operator, as in:
IF (key IN (`A', `B', `C')) THEN -- acts like an OR conditional
You can still use operators such as BETWEEN, IS NULL, IS NOT NULL, LIKE, and so on.
You cannot use DECODE in procedural statements. Also, none of the SQL group functions are allowed in procedural statements. They don't make much sense in this context, either. Of course, there are no such restrictions for SQL statements embedded in PL/SQL.
You have seen how to use SQLCODE to trap the numeric exception error value. SQLERRM (sqlcode) is used to convert the SQLCODE value to the error message string associated with the exception. All the other built-in functions are fair game.