Oracle Web Applications: PL/SQL Developer's Intro

Programming Constructs

Most programs are built out of a fairly standard set of programming constructs. For example, to write a useful program, I need to be able to store values in variables, test these values against a condition, or loop through a set of instructions a certain number of times. In this section, we’ll see how to use these and other constructs in PL/SQL. Specifically, we’ll cover comments, variables, conditionals, loops, cursors, and index-by tables (PL/SQL’s version of an array).

Comments

Comments allow you to document your PL/SQL programs. These comments are stored in the database along with the rest of the PL/SQL code. PL/SQL has two types of comments: multiline and single-line.

Multiline comments are enclosed between the delimiters /* and */. Here’s an example:

/*
|| The following procedure unconditionally deletes all
|| rows from the customer's table.
*/
PROCEDURE delete_all_customers is
...

Single-line comments are denoted by two consecutive dashes. The comment can appear either on its own line or after a PL/SQL instruction, as illustrated in the following example:

CREATE OR REPLACE PROCEDURE delete_all_customers
IS
BEGIN
   -- The delete statement blows away all customers
   DELETE
     FROM customers;
   COMMIT;   -- Confirm changes
END;

Variables

The second construct, variables, allows you to save values in memory. For example, you may want to keep a counter inside a loop, or store a string value for processing. In this section, we’ll see how to declare a variable and assign ...