ANALYSIS
Line 5 begins a PL/SQL anonymous block. The iifn function is declared within the scope of this outer block (see lines 8 through 21). The keyword BEGIN in line 22 marks the start of the procedural section of the outer block. Because the iifn function is declared within the outer block, it can also be called from the procedural section of the outer block.

Line 23 calls the iifn function with a boolean expression that evaluates as true. It then prints the value returned, which in this case is the second argument.

Line 24 calls the iifn function with a boolean expression that evaluates as false, so the third argument is returned.

Finally, in lines 26_32, a null expression is passed to the function. A null input should result in a null output, and you can see that the function properly handles this case by returning a null value.

A procedure can be nested in the same manner as a function. Listing 3.10 shows a simple
PL/SQL block illustrating the use of the swapn procedure you saw earlier in Listing 3.6.

INPUT/OUTPUT
Listing 3.10. Executing the swapn procedure.

 1: --Demonstration of a nested procedure block.
 2: --Be sure you have executed: SET SERVEROUTPUT ON
 3: --This is a PL/SQL wrapper that executes the
 4: --procedure shown in Listing 3.6.
 5: DECLARE
 6:   first_number    NUMBER;
 7:   second_number   NUMBER;
 8: 
 9:   PROCEDURE swapn (num_one IN OUT NUMBER, num_two IN OUT NUMBER) IS
10:     temp_num    NUMBER;
11:   BEGIN
12:     temp_num := num_one;
13:     num_one := num_two;
14:     num_two := temp_num ;
15:   END;
16:
17: BEGIN
18:   --Set some initial values and display them.
19:   first_number := 10;
20:   second_number := 20;
21:   DBMS_OUTPUT.PUT_LINE(`First Number = ` || TO_CHAR (first_number));
22:   DBMS_OUTPUT.PUT_LINE(`Second Number = ` || TO_CHAR (second_number));
23:
24:   --Swap the values
25:   DBMS_OUTPUT.PUT_LINE(`Swapping the two values now.');
26:   swapn(first_number, second_number);
27:
28:   --Display the results
29:   DBMS_OUTPUT.PUT_LINE(`First Number = ` || to_CHAR (first_number));
30:   DBMS_OUTPUT.PUT_LINE(`Second Number = ` || to_CHAR (second_number));
31: END;
32: /
33: First Number = 10

34: Second Number = 20
35: Swapping the two values now.
36: First Number = 20
37: Second Number = 10
38:
39: PL/SQL procedure successfully completed.

ANALYSIS
The swapn procedure simply swaps two numeric values. You can see in lines 12 through 14 that it does just that, using the temp_num variable to hold the value of num_one until it can be assigned to num_two, after num_two has been assigned to num_one.

Scope Rules

With any language, and PL/SQL is no exception, it is important to have an understanding of the scope of the various variables, procedures, and functions that you declare. Scope means the range of code within which a given identifier can be referenced.

In PL/SQL the general rule is: An identifier (that is, variable name, procedure name, function name) can be referenced only by code executing inside the block in which the identifier was declared. This includes code inside any nested blocks, procedures, or functions.

Take a look at the code in Listing 3.11, which has been carefully constructed to illustrate this point.

INPUT/OUTPUT
Listing 3.11. Illustrates the scope of various identifiers.

 1: --Be sure to execute: SET SERVEROUTPUT ON
 2:
 3: DECLARE
 4:   a_name  VARCHAR2(30) := `Jeff Gennick';
 5:
 6: PROCEDURE name_print IS
 7: BEGIN
 8:   DBMS_OUTPUT.PUT_LINE(a_name);
 9: END;
10:
11: BEGIN
12:   DBMS_OUTPUT.PUT_LINE(a_name);
13:
14:   DECLARE
15:     b_name  VARCHAR2(30) := `Jenny Gennick';
16:   BEGIN
17:       DBMS_OUTPUT.PUT_LINE(`Inside nested block');
18:       DBMS_OUTPUT.PUT_LINE(a_name);
19:       DBMS_OUTPUT.PUT_LINE(b_name);
20:       name_print;
21:   END;
22:

                                                    continues

Listing 3.11. continued

23:   DBMS_OUTPUT.PUT_LINE(`Back in the main block');
24:
25:   --But we cannot compile the following line because b_name
26:   --is not defined in this block.
27:   --DBMS_OUTPUT.PUT_LINE(b_name);
28:
29:   --Our procedure, however, can access the value of a_name.
30:   name_print;
31: END;
32: /
33: Jeff Gennick
34: Inside nested block
35: Jeff Gennick
36: Jenny Gennick
37: Jeff Gennick
38: Back in the main block
39: Jeff Gennick

The code shown in Listing 3.11 consists of two nested anonymous blocks and one procedure definition. The outermost block begins at line 3 and ends on line 31. A nested anonymous block begins on line 14 and ends on line 21. Lines 6 through 9 define the name_print procedure.

The variable a_name is declared in the outermost block (see line 4), thus any nested block, procedure, or function has access to it. To demonstrate this, the outer block displays the value of a_name in line 12, the nested block displays the value of a_name in line 18, and in line 30 of the outer block, the nested procedure name_print is called to also print the value of a_name. The name_print procedure, because it is defined within the outer block, has access to all other identifiers declared at that same level.

The variable b_name is declared in the inner block (see line 15) and can only be referenced within that block. If you were to uncomment line 27 and try to execute the code shown in the listing, you would receive an error because b_name would not be recognized.

Summary

In this chapter you learned about the many datatypes that are available to you when programming in PL/SQL. You learned about the several nuances and variations of the NUMBER datatype, which are important to understand as you continue to work with PL/SQL.

This chapter also discusses the PL/SQL block structure in detail and illustrates several different types of blocks, that is, functions, procedures, triggers, and anonymous blocks.

No discussion of PL/SQL blocks is complete without addressing the issue of scope. You should now feel comfortable that when you declare an identifier, whether it be a variable, function, or procedure, you fully understand from where it can be referenced.

Q&A

Q Why shouldn't I use the VARCHAR datatype?
A Oracle warns against it because the definition of that datatype might be changed in the near future, and any change might have adverse ramifications on your code.
Q What is a subtype?
A A subtype allows you to declare variables of a particular datatype that hold only a subset of the possible values that are normally handled by that datatype.
Q What is the difference between the BINARY_INTEGER type and the PLS_INTEGER type?
A Both use binary representations to store values. However, operations on PLS_INTEGERS use native machine instructions, whereas operations on BINARY_INTEGERS use internal library functions, which are slower.
Q What is the difference between a function and a procedure?
A A function returns a value and can be used in an expression. A procedure does not return a value and cannot be used in an expression.

Workshop

Use the following workshop to test your comprehension of this chapter and put what you've learned into practice. You'll find the answers to the quiz and exercises in Appendix A, "Answers."

Quiz

1. What are three benefits of using functions and procedures?
2. What values can a variable declared as NUMBER(6,2) hold? What will be the maximum value?
3. What values can a variable declared as NUMBER(2,2) hold? Where will rounding occur?
4. What is the maximum length of a VARCHAR2 variable in PL/SQL? In the Oracle database?