Up to this point, I've presented SQL as a language without procedural capabilities. However, Oracle offers procedural language extensions to SQL through the PL/SQL language. PL/SQL is the basis for the following application logic elements:
The purpose of this chapter is to introduce the fundamental elements of PL/SQL. You should feel comfortable with the syntax and use of PL/SQL before you attempt to design stored procedures (Chapter 19, "Creating and Using Stored Procedures, Functions, and Packages") and database triggers (Chapter 23, "Enforcing Business Rules with Database Triggers"). This chapter addresses the features in PL/SQL version 2.1.
TipDon't use SQL*Plus to type in each line of a PL/SQL script. If you make a typo, SQL*Plus won't provide any feedback until it reads the / that terminates the PL/SQL block--;SQL*Plus passes the entire block to the PL/SQL engine only when the block is complete. The PL/SQL engine is a component of the Oracle RDBMS. Instead, use a text editor, such as Notepad, for developing your PL/SQL scripts. You can paste the script directly into SQL*Plus or invoke it with the START or @ command.
PL/SQL is a block-structured language with a syntax similar to the C programming language. In addition to supporting embedded SQL statements, PL/SQL offers standard programming constructs such as procedure and function declarations, control statements such as IF-THEN-ELSE and LOOP, and declared variables. A PL/SQL program consists of procedures, functions, or anonymous blocks. An anonymous block is an unnamed PL/SQL block that has no arguments and returns no value. Anonymous blocks are common in scripts that are executed in a SQL*Plus session.
From a top-level perspective, Figure 18.1 illustrates the structure of a PL/SQL
block, which includes an optional declaration section, an executable section, and
an optional section for handling PL/SQL and SQL exceptions and errors. *
Figure 18.1. Top-level structure of a PL/SQL
block.
The following code is a simple anonymous PL/SQL block that generates some test data.
The block, which is executed from a SQL*Plus script, inserts 100 rows into TEST_TABLE.
SQL> @c:\po7_book\chap17\fig1 SQL> drop table test_table; Table dropped. SQL> SQL> create table test_table ( 2 record_number int, 3 current_date date); Table created. SQL> SQL> DECLARE 2 2 max_records CONSTANT int := 100; 3 i int := 1; 4 4 BEGIN 5 5 FOR i IN 1..max_records LOOP 6 6 INSERT INTO test_table 7 (record_number, current_date) 8 VALUES 9 (i, SYSDATE); 10 10 END LOOP; 11 11 COMMIT; 12 END; / PL/SQL procedure successfully completed.
The preceding script is an anonymous PL/SQL block because it has no name--;it isn't declared as a procedure, function, or package. A single SQL*Plus script contains all the lines.. The first two SQL commands drop the TEST_TABLE and then create it.
The PL/SQL block actually starts with the word DECLARE. The declaration section declares the constant max_records and the variable i, which serves as a counter. The beginning of the executable portion of the block is designated by BEGIN. The block contains a single FOR LOOP that inserts a row into TEST_TABLE while i is less than or equal to max_records. When the FOR LOOP completes, the transaction is committed. The last line of the script is a /, which causes SQL*Plus to submit the PL/SQL block to the PL/SQL engine.
Unless a PL/SQL compilation error occurs, the only feedback that SQL*Plus provides is the message PL/SQL procedure successfully completed. In Chapter 19 I'll show you how to generate diagnostics from PL/SQL so that you can see the progress of a PL/SQL subprogram's execution.
The declaration section of a PL/SQL block is optional. However, you must declare all variables and constants that are referenced in the PL/SQL statements. To include a declaration section in a PL/SQL block, begin the PL/SQL block with the word DECLARE. Each variable or constant declaration consists of its name, datatype, and an optional default value. As with all PL/SQL statements, each variable and constant declaration ends with a semicolon. Here are some examples of declared variables and constants:
Fax_Number VARCHAR2(10); Current_Used_Value NUMBER(6,2) := 100.00; Max_Current_Used_Value REAL := 9999.99; State VARCHAR2(2) := 'CA';
The executable section of a PL/SQL block follows the keyword BEGIN. Each PL/SQL statement ends with a semicolon. These statements can be categorized as
An exception is an error condition that occurs during the execution of a PL/SQL program. An exception might be predefined--;for instance, an INSERT statement attempts to add a duplicate row to a table, resulting in the DUP_VAL_ON_INDEX exception begin raised. You can also define exceptions that are specific to your application. The exception section defines the exception handlers invoked for both predefined and user-defined exceptions. Each exception handler consists of one or more PL/SQL statements.
Unlike SQL, PL/SQL provides additional datatypes. In addition to the normal Oracle SQL datatypes, PL/SQL enables you to declare variables with these datatypes:
In addition, PL/SQL provides two composite datatypes: TABLE and RECORD. Chapter 20, "PL/SQL Datatypes and Variables," addresses the details of using these composite datatypes.
The syntax for declaring a variable with %TYPE is
variable-name table-name.column-name%TYPE;where column-name is a column defined in table-name.
For example, you declare a variable to store a repair depot technician's name in this way:
Tech_Name Depot_Estimate.Technician%TYPE;
The benefit of using %TYPE in a variable declaration is that the PL/SQL code is dependent on the definition of the Technician column in the Depot_Estimate table.
The syntax for declaring a variable with %ROWTYPE is
variable-name table-name%ROWTYPE;For instance, a composite variable that stores a row from the Depot_Estimate table is declared like this:
Depot_Est_Row Depot_Estimate%ROWTYPE;Here's how to access an element of Depot_Est_Row:
Depot_Est_Row.Technician := 'RICHARDSON';
Several PL/SQL statements control the flow of execution of a PL/SQL subprogram.
Before you can build stored procedures and triggers, you should be familiar with the basics of PL/SQL programming. The next part of this chapter explains how to use these statements.
The syntax of PL/SQL's IF-THEN-ELSIF statement differs somewhat from that of the comparable statement in the C programming language. The PL/SQL syntax is
IF condition THEN statement; ... statement; [ELSIF condition THEN statement; ... statement;] ... [ELSIF condition THEN statement; ... statement;] [ELSE statement; ... statement;] END IF;
where condition is a valid PL/SQL condition and statement is a valid PL/SQL statement.
Regarding the IF-THEN-ELSIF statement, be aware of the following:
Here's a simple example of the IF-THEN-ELSIF statement:
if MOD(i,5) = 0 then rec_number := 5; elsif MOD(i,7) = 0 then rec_number := 7; else rec_number := i; end if;
The most basic type of loop is the LOOP statement without any additional qualifiers.
LOOP statement; ... statement; END LOOP;
Obviously, this loop is an infinite loop. To exit this loop when a required condition is satisfied, use the EXIT statement.
The EXIT statement has two forms:
The first form of EXIT causes control to exit the loop that encloses the EXIT statement.
The second form of EXIT causes control to exit the enclosing loop when the specified condition is met, as shown in this example:
SQL> declare 2 2 i positive := 1; 3 max_loops constant positive := 100; 4 4 begin 5 5 loop 6 6 i := i + 1; 7 exit when i > max_loops; 8 8 end loop; 9 9 end; 10 / PL/SQL procedure successfully completed.
The WHILE-LOOP statement adds a condition to a loop.
WHILE condition LOOP statement; ... statement; END LOOP;
Here's a simple example of a WHILE-LOOP statement:
WHILE I < 100 LOOP I := I + 1; insert into temp_table (rec_number) values (I); END LOOP;
The FOR-LOOP is quite similar to the WHILE-LOOP. The syntax is
FOR loop-variable IN [REVERSE] lower-bound..upper-bound LOOP statement; ... statement; END LOOP;
where loop-variable is an integer variable that serves as a counter, lower-bound is the lower bound of the increment range, and upper-bound is the upper bound of the increment range.
REVERSE is an optional keyword that, if used, causes the loop to decrement from upper-bound to lower-bound.
Here is a simple example of a FOR-LOOP statement:
for i in 1..max_loops loop
j := j + j;
dbms_output.put_line('j: ' || to_char(j));
end loop;
Yes, PL/SQL allows you to use the potentially dangerous GOTO statement. Of course, to use GOTO, you must provide a label to which control is transferred. In PL/SQL, a statement label is defined in this way:
<<my_label>>
Here's an alternative to the EXIT statement that uses GOTO and a label:
SQL> declare 2 2 i positive := 1; 3 max_loops constant positive := 100; 4 4 begin 5 5 i := 1; 6 6 loop 7 7 i := i + 1; 8 if i > max_loops then 9 goto more_processing; 10 end if; 11 11 end loop; 12 12 <<more_processing>> 13 i := 1; 14 14 end; 15 / PL/SQL procedure successfully completed.
For certain situations, you should indicate to PL/SQL that no action is to be taken. For instance, in an exception handler, you might not want to do anything when a particular exception occurs. For the sake of clarity, use the NULL statement in an IF-THEN-ELSIF to indicate that no action is to be taken for a particular ELSIF clause.
Unfortunately, Oracle chose to name this statement NULL, even though it has nothing to do with the null value. Here's an example of the NULL statement:
if (mod(i,10) = 0) then
i := i + 1;
else
NULL;
end if;
As you've already seen, PL/SQL uses := to assign a value to a PL/SQL variable. You can define a constant's value or a variable's default value in the declaration section. One point is worth noting: You can't assign a NULL to a variable that was declared using the %TYPE notation when the referenced column is defined as NOT NULL.
PL/SQL gives you two ways to document your code. First, you can add a comment on any line by placing a -- followed by the comment, as shown: ***Production: Please make sure the double hyphens in the paragraph above and the code below do not become em dashes. Thanks.***
Depot_Est_Row.Technician := Last_Tech_Name; -- Assign the name of the last technician involved
You can also add comments in the C-style--;by enclosing them within /* and */. This method is best suited for including multiline comments:
j := j + 1; /* The next section inserts a row into the Utility_Audit table to record the name of the current Oracle user and the current date and time (SYSDATE). */ insert into Utility_Audit ...
You can use SQL statements in an anonymous block, procedure, or function as they are used in SQL*Plus, with a few differences. As with other PL/SQL statements, each SQL statement must end with a semicolon. However, PL/SQL enables you to reference declared variables in a SQL statement, as shown here:
DECLARE
max_records CONSTANT int := 100;
i int := 1;
BEGIN
FOR i IN 1..max_records LOOP
if (mod(i,10) = 0) then
INSERT INTO test_table
(record_number, current_date)
VALUES
(i, SYSDATE);
else
NULL;
end if;
END LOOP;
COMMIT;
END;
/
In this example the INSERT statement uses the numeric variable i and the pseudocolumn SYSDATE to place values in the Record_Number and Current_Date columns.
In a PL/SQL subprogram, the SELECT statement employs another clause--;INTO--;to identify the PL/SQL variables that should receive column values. Place the INTO clause between the select list and the FROM clause. Here is an example of an anonymous PL/SQL block that contains a SELECT statement:
SQL> set serveroutput on
SQL>
SQL> declare
2
2 Average_Body_Temp Patient.Body_Temp_Deg_F%type;
3
3 begin
4
4 dbms_output.enable;
5
5 select avg(Body_Temp_Deg_F)
6 into Average_Body_Temp
7 from Patient;
8
8 dbms_output.put_line('Average body temp in Deg. F: ' ||
to_char(Average_Body_Temp,'999.99'));
9
9 end;
10 /
Average body temp in Deg. F: 99.80
PL/SQL procedure successfully completed.
PL/SQL also supports the use of subprograms--;named procedures and functions. A PL/SQL procedure performs some action and can accept optional parameters. A PL/SQL function returns a value of some specified datatype and can also accept optional parameters.
PL/SQL permits you to include sub-blocks within a block. For instance, this anonymous block contains another anonymous sub-block that has its own declaration section.
SQL> declare
2
2 max_i constant int := 100;
3 i int := 1;
4 rec_number int;
5
5 begin
6
6 for i in 1..max_i loop
7
7 if mod(i,5) = 0 then
8 rec_number := 5;
9 elsif mod(i,7) = 0 then
10 rec_number := 7;
11 else
12 rec_number := i;
13 end if;
14
14 insert into test_table
15 (record_number, current_date)
16 values
17 (rec_number, sysdate);
18
18 -- Here is a sub-block:
19
19 declare
20 max_j constant int := 20;
21 j int := 1;
22
22 begin
23
23 for j in 1..max_j loop
24
24 rec_number := rec_number * j;
25
25 insert into test_table
26 (record_number, current_date)
27 values
28 (rec_number, sysdate);
29
29 end loop;
30
30 end;
31
31 commit;
32 end loop;
33
33 end;
34 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test_table;
COUNT(*)
---------
2100
TipAlthough PL/SQL allows you to embed blocks within one another, this practice is not desirable for two reasons. First, it reduces the readability--;and the resulting maintainability--;of your code. Second, other PL/SQL subprograms cannot use embedded blocks. You should strive to design procedures and functions for improved code reuse and maintainability.
In addition to anonymous blocks, you can also declare PL/SQL procedures and functions. The syntax for declaring a procedure is
PROCEDURE procedure-name [(argument1 ... [, argumentN) ] IS [local-variable-declarations] BEGIN executable-section [exception-section] END [procedure-name];
where procedure-name is the procedure name and subject to Oracle database object-naming restrictions.
argument1 through argumentN are optional argument declarations that consist of
argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]
local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local to procedure-name.
executable-section is the PL/SQL statements that compose the procedure.
exception-section is the optional exception-handling section of the procedure.
NoteThe distinction between stored procedures and procedures that are declared and used in anonymous blocks is important. The procedures that are declared and called in anonymous blocks are temporal; when the anonymous block has completed execution, they no longer exist as far as Oracle is concerned. A stored procedure that is created with a CREATE PROCEDURE statement or contained in a package is permanent in the sense that it can be invoked by a SQL*Plus script, a PL/SQL subprogram, or a database trigger.
To illustrate this syntax, here is an example of an anonymous block that declares a procedure named Record_Patient_Temp_Deg_C. This procedure has two arguments: the patient ID and the patient's body temperature as measured in degrees Centigrade.
SQL> declare 2 2 New_Patient_ID Patient.Patient_ID%type; 3 High_Fever constant real := 42.0; 4 4 procedure Record_Patient_Temp_Deg_C (Patient_ID varchar2, 5 Body_Temp_Deg_C real) is 6 6 Temp_Deg_F real; 7 7 begin 8 8 Temp_Deg_F := (9.0/5.0)*Body_Temp_Deg_C + 32.0; 9 9 insert into Patient 10 (Patient_ID, Body_Temp_Deg_F) 11 values 12 (Patient_ID, Temp_Deg_F); 13 13 commit; 14 end; 15 15 begin 16 16 New_Patient_ID := 'GG9999'; 17 17 Record_Patient_Temp_Deg_C (New_Patient_ID, High_Fever); 18 18 end; 19 / PL/SQL procedure successfully completed. SQL> select Patient_ID, Body_Temp_Deg_F 2 from Patient 3 where 4 Patient_ID = 'GG9999'; PATIEN BODY_TEMP_DEG_F ------ --------------- GG9999 107.6
Notice that the variables declared within a procedure are not accessible outside the procedure.
SQL> declare 2 2 procedure Delete_Patients is 3 3 Temp_Deg_F real; 4 4 begin 5 5 delete from Patient 6 where 7 Patient_ID = 'GG3333'; 8 8 commit; 9 9 end; 10 10 begin 11 11 Temp_Deg_F := 100.0; 12 12 end; 13 / Temp_Deg_F := 100.0; * ERROR at line 11: ORA-06550: line 11, column 1: PLS-00201: identifier 'TEMP_DEG_F' must be declared ORA-06550: line 11, column 1: PL/SQL: Statement ignored
For further information on the use of stored procedures, please refer to Chapter 19.
A PL/SQL function declaration is similar to a procedure declaration--;except that the function returns a value of a predefined datatype. The syntax for declaring a function is
FUNCTION function-name [(argument1 ... [, argumentN) ] RETURN function-datatype IS [local-variable-declarations] BEGIN executable-section [exception-section] END [function-name];
where function-name is the function name and subject to Oracle database object-naming restrictions.
argument1 through argumentN are optional argument declarations that consist of
argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]
function-datatype is the datatype returned by function-name.
local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local to function-name.
executable-section is the PL/SQL statements that compose the function
exception-section is the optional exception-handling section of the function.
For instance, a function that returned the highest patient temperature expressed in degrees Centigrade via an argument would be declared in this way:
SQL> declare
2
2 Pat_ID Patient.Patient_ID%type;
3 High_Fever real;
4 status boolean;
5
5 function Highest_Fever_Temp_Deg_C (Patient_ID OUT varchar2,
6 Body_Temp_Deg_C OUT real)
7 return boolean is
8
8 Max_Fever_Deg_F real;
9
9 begin
10
10 select Patient_ID, Body_Temp_Deg_F
11 into Patient_ID, Max_Fever_Deg_F
12 from Patient
13 where
14 Body_Temp_Deg_F =
15 (select max(Body_Temp_Deg_F)
16 from Patient);
17
17 Body_Temp_Deg_C := (Max_Fever_Deg_F - 32.0)*5.0/9.0;
18
18 return true;
19
19 end;
20
20 -- Beginning of executable section of anonymous block.
21
21 begin
22
22 dbms_output.enable;
23
23 status := Highest_Fever_Temp_Deg_C (Pat_ID, High_Fever);
24
24 dbms_output.put_line('Patient ' || Pat_ID || ' has a fever of ' ||
25 to_char(High_Fever,'999.9') || ' C. ');
26
26 end;
27 /
Patient GG9999 has a fever of 42.0 C.
PL/SQL procedure successfully completed.
You can optionally define each procedure and function argument as one of the following:
The next example uses these three types of arguments:
SQL> declare 2 2 This_Arg1 number; 3 This_Arg2 number; 4 This_Arg3 number; 5 5 procedure Different_Arguments 6 (arg1 IN number, 7 arg2 OUT number, 8 arg3 IN OUT number) is 9 9 begin 10 10 arg2 := arg1; 11 arg3 := arg3 + 1; 12 12 end; 13 13 -- Beginning of executable section of anonymous block. 14 14 begin 15 15 This_Arg1 := 3.14159; 16 16 Different_Arguments (This_Arg1, This_Arg2, This_Arg3); 17 17 end; 18 /
PL/SQL procedure successfully completed.
In addition, a default value can be defined for an argument.
procedure Different_Arguments
(arg1 IN number := 100,
arg2 OUT number,
arg3 IN OUT number) is
This chapter covers the essential elements of PL/SQL.