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 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
*/. Here’s an
/* || 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;
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 ...