Week 2

Day 9

Programming an Oracle Database with PL/SQL

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:

• SQL*Plus script: An SQL*Plus script can incorporate PL/SQL block subprograms.
• Stored procedure or function: A stored procedure or function is a PL/SQL subprogram that can be invoked by a client application, a database trigger, or an Oracle tool application trigger.
• Database trigger: A database trigger is a PL/SQL subprogram that performs some action based on the execution of a DML statement—such as INSERT, UPDATE, or DELETE—against a database table.

• Package: A set of PL/SQL procedures, functions, cursors, and other PL/SQL variables are bundled together into a package.
• Application trigger: Oracle application development tools such as Oracle Forms and Oracle Reports are equipped with a PL/SQL engine so that developers can construct application triggers using PL/SQL.

The purpose of today's lesson 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 on Day 10, "Program Development in PL/SQL," and Day 11, "More Programming Techniques with PL/SQL." This lesson addresses the features contained in PL/SQL through version 2.3.

PL/SQL Is a Block-Structured Language

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 an SQL*Plus session.

From a top-level perspective, Figure 9.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.

Let's look at a simple anonymous PL/SQL block that generates some test data. Listing 9.1 contains an anonymous block which is executed from an SQL*Plus script and inserts 100 rows into TEST_TABLE.

Listing 9.1. Execution of an anonymous PL/SQL block in SQL*Plus.

SQL> @c:\tyo\day10_1
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.

ANALYSIS
Take a look at some of the elements in the previous PL/SQL script. This script is an anonymous PL/SQL block because it has no name—it isn't declared as a procedure, function, or package. All the lines in this script are contained in a single SQL*Plus script. 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 a constant, max_records (in line 2), and a variable, i (in line 3), which serves as a counter. The beginning of the executable portion of the block is designated by BEGIN (in line 4). The block contains a single FOR LOOP (in line 5) 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 (in line 11). 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 a message: PL/SQL procedure successfully completed. In the next lesson, you'll see how to produce diagnostics from PL/SQL, which enables you to see the progress of a PL/SQL subprogram's execution.

The Declaration Section

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 is terminated with a semicolon. Listing 9.2 contains some examples of declared variables and constants.

Listing 9.2. 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

The executable section of a PL/SQL block follows the keyword BEGIN. Each PL/SQL statement is terminated by a semicolon. These statements can be categorized as follows:

• Assignment statements
• Flow-of-control statements
• SQL statements
• Cursor statements