Each row in an Oracle table has a unique internal key associated with it. This key can be useful if you are planning to access the same record multiple times. For example, you might use a cursor to retrieve a number of rows, including the ROWID for each row, and then use that ROWID in a DELETE statement to delete some of the rows. Using the ROWID results in better performance because it tells Oracle exactly where to find the record so no index searches or table scans are necessary.

MSLABEL

The MSLABEL datatype is used with Trusted Oracle, which is a version of Oracle designed for use in high security environments such as those dealing with classified data.

The Syntax for the MSLABEL Datatype

variable_name MSLABEL;

In this syntax, variable_name is the name you want to give this variable.

Here is an example:

binary_label MSLABEL;

In a Trusted Oracle system, this datatype is used to store binary operating system labels. Standard Oracle allows the datatype to be declared, but only null values can be assigned
to it.

Block Structure

On Day 1, "Learning the Basics of PL/SQL," you saw that the fundamental programming structure in PL/SQL is referred to as a block. In order to master PL/SQL, it is essential to understand the block structure, to understand the various types of blocks, and to understand how blocks are used. In the rest of this chapter, you will learn about anonymous blocks, trigger blocks, function blocks, and procedure blocks. You will also learn that blocks can be nested and what the implications are in terms of scoping.

Anonymous Blocks

NEW TERM
An anonymous block is one that is unnamed and that does not form the body of a procedure, function, or trigger. Remember the examples and exercises from Day 1? They were all anonymous blocks.

Anonymous blocks can be used inline as part of a SQL*Plus script, and can also be nested inside procedure and function blocks for purposes of error handling.

The Syntax for PL/SQL Anonymous Blocks

[DECLARE
  variable_declarations]
BEGIN
  program_code
[EXCEPTION
  error_handling_code]
END;

In this syntax, variable_declarations is where you declare your variables. program_code is where you write your PL/SQL program statements. error_handling_code is an optional section to which control branches in the event of an error.

As you can see, the keyword DECLARE is used to begin the block. Any variable declarations must follow this and precede the next keyword, which is BEGIN.

The keyword BEGIN signifies the beginning of the procedural section of the block. The program code goes here.

The keyword EXCEPTION begins the portion of the block that contains exception-handling code. The exception-handling portion of a block is optional, and you might not always use it. If the exception-handling portion is present, any runtime error or exception will cause program control to branch to this part of the block. The word exception is used to connote something that is outside the normal flow of events. It is used rather than error because an exception does not always imply that something is wrong. For example, issuing a SELECT statement and not getting any data back might be an exception to what you would normally expect, but it does not necessarily mean that an error occurred.

Listing 3.4 shows an example of an anonymous block. Note especially the declaration used for the hundreds_counter variable.

INPUT/OUTPUT
Listing 3.4. An example of an anonymous block.

 1: --Count up by hundreds until we get an error.
 2: DECLARE
 3:   --Note that with a scale of -2 this variable can only
 4:   --hold values like 100,200,300... up to 900.
 5:   hundreds_counter  NUMBER(1,-2);
 6: BEGIN
 7:   hundreds_counter := 100;
 8:   LOOP
 9:     DBMS_OUTPUT.PUT_LINE(hundreds_counter);
10:     hundreds_counter := hundreds_counter + 100;
11:   END LOOP;
12: EXCEPTION
13: WHEN OTHERS THEN
14:   DBMS_OUTPUT.PUT_LINE(`That is as high as you can go.');
15: END;

                                                  continues

Listing 3.4. continued

16: /
17:
18: 100
19: 200
20: 300
21: 400
22: 500
23: 600
24: 700
25: 800
26: 900
27: That is as high as you can go.

ANALYSIS
Take a look at Listing 3.4. A counter variable named hundreds_counter is declared in line 5. Because it is defined with a precision of one, it is only using one digit to represent the value. The scale of -2 tells you that you are using that one digit to represent hundreds. Lines 8 through 11 contain a loop that prints the value of the counter and then increments it by one hundred. Because the counter's precision is only one digit and the two zeros are assumed, the program can only count up to 900. When you try to go past 900 to 1000, the variable won't be able to hold the value and an exception will be triggered.

Look at the output from Listing 3.4. You can see that the code indeed works as described. It successfully counted up to 900, and when the variable was incremented to 1000, an exception was generated that displayed a message before execution ended.

Function and Procedure Blocks

PL/SQL allows you to define functions and procedures. These are similar to functions and procedures defined in any other language, and they are always defined as one PL/SQL block.

The Syntax for Defining a Function

FUNCTION name [( argument_list )] RETURN datatype {IS,AS}
  variable_declarations
BEGIN
  program_code
[EXCEPTION
  error_handling_code]
END

In this syntax, the placeholders are as follows:

• name—The name you want to give the function.
• argument_list—A list of input and/or output parameters for the function.
• datatype—The datatype of the function's return value.

• variable_declarations—Where you declare any variables that are local to the function.
• program_code—Where you write the PL/SQL statements that make up the function.
• error_handling_code—Where you write any error-handling code.

Notice that the keyword DECLARE has been replaced by the function header, which names the function, describes the parameters, and indicates the return type. Except for this, the function block looks just like the declarations for the anonymous blocks that you have seen so far.

You will learn more about functions on Day 5, "Using Functions, IF Statements, and Loops." Listing 3.5 shows an example of a function.

INPUT
Listing 3.5. A function block.

 1: FUNCTION iifn(boolean_expression in BOOLEAN,
 2:               true_number IN NUMBER,
 3:               false_number IN NUMBER)
 4: RETURN NUMBER IS
 5: BEGIN
 6:   IF boolean_expression THEN
 7:     RETURN true_number;
 8:   ELSIF NOT boolean_expression THEN
 9:     RETURN false_number;
10:   ELSE
11:     --nulls propagate, i.e. null input yields null output.
12:     RETURN NULL;
13:   END IF;
14: END;

ANALYSIS
To execute the function shown in Listing 3.5, you need to declare it and execute from within a PL/SQL block. The section "Nesting Functions and Procedures," later in this chapter, shows how this is done.

The declaration for a procedure is almost identical to that of a function.

The Syntax for Declaring a Procedure

PROCEDURE name [( argument_list )] {IS,AS}
  variable_declarations
BEGIN
  program_code
[EXCEPTION
  error_handling_code]
END

In this syntax, the placeholders are as follows:

• name—The name you want to give the procedure.
• argument_list—A list of input and/or output parameters for the procedure.