Previous | Table of Contents | Next

Page 168

You can execute PL/SQL programs from SQL*PLUS.Oracle does not run the program until it encounters the slash (/) symbol. You normally place this symbol in the script file after the keyword end. You can also enter the slash symbol at the SQL*PLUS prompt.

Nested Blocks and Block Labels

Blocks can have nested blocks within them. The nested block can have all the sections of any other PL/SQL block. The nested block is contained in the executable section of the outer block. As the processor moves through the executable section, it begins processing the code in the nested block when it is reached. Variables declared in the nested block can only be used in that block.You can identify blocks with block labels. You can use these undeclared identifiers as a qualifier for a block's local variables. The label name enclosed by greater than and less than symbols (<< >>) denotes the beginning of the block label. The label must appear at the beginning of the block. The label name preceded by the word end denotes the completion of the marked code. Labels prove useful in marking sections of code. When the label is used as a qualifier, it tells Oracle in which block the variable was defined.

TIP
Block labels prove very useful when you name your local variables the same name as your table columns. It easy to get the rdBms and yourself mixed up as to which is a table column or local variable. The block label can be used as a qualifier for the local variables. This reduces confusion as to what the variable represents.

PL/SQL has a Goto command used in conjunction with a label. A label is an undeclared identifier enclosed by double brackets. It differs from the block label in that you can place it anywhere in the block and it does not have an end keyword. The label is the destination for the Goto command. It is used to redirect the flow of the program.

Listing 8.1 illustrates an anonymous block that contains only the executable section. It has a block label called "ONE". It also contains labels and a Goto command. The program prints the word "Hello," and then is redirected to label "Two" to print the words "What did you say." The Goto command causes the program to skip the line that would print "Goodbye."

Listing 8.1 L_08_01.sql—Using the GOTO and Label Commands to Change the Program Flow

SQL> set serveroutput on;
SQL> <<one>>
 2 begin
 3  dbms_output.put_line (`Hello');
 4  goto two;
 5  dbms_output.put_line (`Good Bye');
 6  <<two>>
 7  dbms_output.put_line (`What did you say?');
 8 end one;
 9 /
Hello

Page 169

What did you say?

PL/SQL procedure successfully completed.
NOTE
I do not find the Goto command very useful. I believe it is a throwback to early programming techniques. It is hard for me to follow the logic of the program when the Goto commands are included. I recommend that you use Goto commands with special care. In the next several chapters, I use the dbms output package to display values. This is a package available for displaying information while running the program. PL/SQL normally has no output except the words PL/SQL procedure successfully completed. These statements help you follow the logic and execution of the programs.
To see the output of these commands, the set serveroutput on setting must be used.

Defining Your PL/SQL Variables

Variables are the mechanism PL/SQL uses to hold data for the purpose of evaluation and modification. If you want to read records from the database; evaluate them; modify them when the conditions are correct; and return the modified values back to the database, you need local variables. The reason is Oracle cannot act on the values unless they are placed in memory. The Select command acquires the record and values from the database table for the PL/SQL program, but the select clause does not put it into memory. This is done with the into clause or by a Fetch command. They are the devices that assign values to local variables. The local variables are holders of values in memory. Thus the PL/SQL program can now use the values for its purposes.

You name variables in the Declare section of the PL/SQL block. The definition of variables reserves a place in memory for the variable. The variables use the same data types that Table 6.1 illustrated. These types can be alphanumeric or numeric. The alphanumeric variables can be fixed length (char) or variable length (varchar2). The numeric fields can be an integer or have a specified precision. An example of a variable declaration is: payment number(6,2). This declaration defines a numeric value that contains six spaces with two decimal positions.

You can define two additional types of variables in the Declare section. The first is a Boolean variable. This data type contains an evaluation value of true, false, or null (unknown). It is used when a function returns an evaluation rather than a specific value. The value is true if all conditions have been met. It is false if some of the conditions have not been met, and null if the results of the evaluation are unknown.The second variable type is exception. This data type is used to set up the variable for a user-defined exception that will be used in the Exception section of the program. This chapter discusses this in greater detail in following sections.

The data type, size specification, and a semicolon follow the name of the variable. The variable name must start with a letter, but can have any alphanumeric character up to 30 characters. It cannot be an Oracle reserved word and must be continuous. Figure 8.2 illustrates methods of defining variables. The most common data types are char, varchar2, and number. In the Figure, the definition of "pi" uses the word constant. This means that the value cannot be changed at any time during the program. You must assign it a value at the time it is created. If the program

Previous | Table of Contents | Next