Page 170
inadvertently tries to reassign this type of variable a value, Oracle issues an error message and stops execution of the program.
FIG. 8.2
Defining variables.
The "first_name" definition has a not null constraint placed on it. This means that the variable must always have a value in it. If you inadvertently place a null value into the variable, an error occurs and the program terminates. Not null variables must always have a value assigned to them as part of the declaration. Because "first_name" is a not null variable, you must assign it a value during the definition.
The %type definition is an important device for ensuring the local variable has the same data type as the database column that supplies it the value. In Figure 8.2, for example, the "employee.fk_department%type" expression is used to define the "dept" variable the same format type and size as the fk_department field in the Employee table. The %type definition causes this to happen. The syntax requires the table name and field name to precede the %type. A period (.) separates the table name (employee) and the field name (fk_department).
The %rowtype definition performs a similar function. It sets up an array of local variables in memory for each field in the referenced table. In the Figure, the "emp_row" definition creates an array of variables based on the Employee table. The syntax of the definition requires the name of the table to precede the %rowtype. When the variable is used in the PL/SQL program, the variable has the same name as the column in the Employee table, and it must have "emp_row" as a qualifier. An example of the syntax is "emp_row.last_name". It represents the last_name variable in the array defined as emp_row.
Page 171
TIP |
The %type and especially %rowtype definitions are excellent devices for defining variables accurately. One of the most common mistakes I make in writing PL/SQL is defining local variables with a different format or length than the column in the corresponding database table. This causes problems when comparing values in my programs. Varchar2 data types do not compare well with char data types. When mistakes occur on evaluations, the program does not operate as designed. Another error consists of moving larger values into the smaller variables. This error causes the application to stop. |
To assign values to variables, use the Equal to (:=) operator or a Select command. Figure 8.2 shows the use of the Equal to operator. The "pi" and "first_name" variables were assigned a value by using this operator.
CAUTION |
Users sometimes mix up the Equals (=) and Equal to (:=) operators. Such a mix-up causes the PL/SQL program to abort. The Equals (=) operator is used in evaluations such as a = b, true or false. The Equal to operator is used in variable assignments such as a := b. A is now equal to b. |
You can use the select statement to assign values. It has an additional clause not yet discussed called into. The into clause follows the select clause and contains the names of variables that will be assigned values. Listing 8.2 shows two examples of assigning variables by using the select statement. The top example assigns two specific table fields into two variables contained in the into clause. The variables in the into clause are the local variables defined with %rowtype, obvious because of the "emp_row" qualifier.
Listing 8.2 L_08-02.sqlAssigning Values to Variables by Using the SELECT Statement
SQL> set serveroutput on; SQL> declare 2 emprow employee%rowtype; 3 holder varchar2(100); 4 begin 5 select max(first_name), min(last_name) 6 into emprow.first_name, emprow.last_name 7 from employee; 8 holder := (`1st select `||emprow.first_name||' `||emprow.last_name); 9 dbms_output.put_line (holder); 10 select * into emprow from employee where last_name = `WILSON'; 11 holder := (`2nd select `||emprow.first_name||' `||emprow.last_name); 12 dbms_output.put_line (holder); 13 end; 14 /
continues
Page 172
Listing 8.2Continued
1st select WOODROW ANTHONY 2nd select WOODROW WILSON PL/SQL procedure successfully completed.
The second select illustrates assigning values to each field in the "emp_row" array. The names of the fields do not need to be contained in the statement. Oracle knows that all the fields from the Employee record have been selected because of the all (*) symbol. It also knows that the array "emp_row" has all the fields as the Employee table. Oracle ensures that the proper variable gets the right value.When using the select statement to assign values, the select clause must have the same number of variables as the into clause. You must assign the value in the first variable in the select clause to the first variable in the into clause. Likewise, you must assign the second variable to the second one in the into clause and so forth.
TIP |
The select statement is the only method in PL/SQL to assign multiple variables a value at the same time. |
One of the excellent features of PL/SQL is the conditional control that you can exercise by using if-then-else constructs. The if portion of the construct performs the evaluation. When the evaluation is true, the actions following then are performed. Sometimes the statement contains the word else. This part of the construct is optional. When the evaluation is false and an else section exists, the commands following it are performed. The construct is terminated by the words "end if;". Figure 8.3 illustrates the syntax components of an if-then-else statement.
The if clause contains the evaluation expressions. The clause can contain more than one expression. The expressions are separated by either the and predicate or the or predicates. The expressions are evaluated exactly as the conditional statements contained in the where clause of a select statement.
When the initial evaluation is true, the commands following then are executed. This section can contain one or more commands. A semi-colon completes each command in the section. When the evaluation is false, the commands in this section are not executed. The commands following the else are executed. The else section is optional. When it is missing, the program leaves the if-then-else construct any executes and statement following the construct..
Listing 8.3 illustrates a simple PL/SQL block and how it uses an If-then-else statement to determine the current month. The declare section is used to name a variable called "month." In the first line of the begin section the substr function is used to assign "month" with the characters that represent the current month from the sysdate function.