 | Chapter 6 PL/SQL |  |
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 /* 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;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 it a value. We'll also look at how to turn a variable into a constant by permanently fixing its value.
The syntax for a variable declaration is:
name datatype(size) DEFAULT default_value;
You can also assign the default value using the := operator. In this case, the syntax is:
name datatype(size) := default_value;
The next three sections describe the name, datatype, and default value.
The name may be up to 30 characters long, and may include letters, numbers, or underscores. Variable names must start with a letter. A good variable is descriptive; many programmers like to use one- or two-letter variable names like X or A1 because they are easy to type,[4] but this does not make for very readable code. If a variable represents an employee's monthly pay rate, then call it "monthly_pay_rate," not "mpr" or, even worse, "r." Let's face it -- most of our time as developers is spent either fixing our old programs or helping someone else fix theirs, not writing new ones. You have only one chance to develop a program. You have the rest of your working life to support it. The few seconds you save by using a short, meaningless name are not worth the future maintenance hassle for either you or the poor sap who'll take your place when you become a consultant.
[4] I once saw a program in which the variables were named after the developer's coworkers.
The datatype specifies the type and amount of data a variable can hold. While there are a variety of different datatypes, in this section we'll look at the two most common: NUMBER and VARCHAR2.
The NUMBER type holds general numbers, such as 1, - 457, or 3.14. You can assign a number variable a precision and scale to set its maximum size. The precision is the maximum number of digits allowed. The scale controls rounding. A positive number indicates the number of places to the right of the decimal place to round, and a negative number indicates the number of units to the left of the decimal. The size used in the declaration is written as a combination of the precision and the scale. For example, the number 1523.567 is rounded to 1523.6 if the datatype is NUMBER(4,1); it would be 1500 if the datatype is NUMBER(4, -2).
The VARCHAR2 datatype holds character strings, like `Hello, world!', `Saturday', or `Buster Keaton'. In PL/SQL, the value of a string is enclosed by single quotation marks (ticks), not double quotes. The compiler can get very confused if you mistakenly use double quotes. Also, the declaration of the VARCHAR2 type must include the maximum size of the string, which can range from 1 to 32,767 characters.[5] For example, a string of 50 characters is declared as a VARCHAR2(50).
[5] Be careful if you plan to use a PL/SQL variable to populate a VARCHAR2 column in a database table. The maximum size in the RDBMS is just 2000 characters.
In addition to explicitly declaring a variable's type, you can implicitly declare it using an anchored declaration. The anchored declaration directly associates a variable's type with the type of a column in a database table. For example, suppose you want to use a PL/SQL variable to hold an employee's last name from an employee table. Rather than hardcoding the column definition in the program, you can simply anchor the variable to the last-name column in the employee table.
Anchored declarations have other benefits besides ease of use. They simplify long-term maintenance in two ways: first, they improve readability, because the relationship between a variable and a column is explicit; second, they minimize problems caused by changes in the database schema. How many times have you had to fix a program because a column was redefined? The syntax for an anchored declaration is:
variable_name table_name.column_name%TYPE;
Here are a few examples that help reduce the chance of "overflowing" a VARCHAR2 variable with too many characters:
Emp_name EMP.ENAME%TYPE; Emp_Dept_code EMP.DEPT%TYPE; Dept_name DEPT.DNAME%TYPE;
Each parameter must have a name, a mode, a datatype, and (optionally) A variable default value is the value a variable contains when it's referenced for the first time. If you don't know what this value is, then you're asking for trouble; it can be very difficult to track down bugs caused by uninitialized variables. Here are some sample variable declarations:
rec_count NUMBER default 0; yearly_interest_rate NUMBER(5,4) := 0.08; account_status_code VARCHAR2(10) default 'OPEN'; emp_last_name emp.lname%TYPE;
Once we've declared the variable, we can assign it a value in the body of our program. PL/SQL uses the := operator to assign a value to a variable.
Here are a few sample assignments:
count := 0; emp_count := emp_count + 1; annual_salary := hourly_rate * 2000;
You should make sure you declare the variables large enough to hold the full range of potential values. If the value you assign exceeds the maximum size declared for the variable, the RDBMS generates the VALUE_ERROR exception. For example, assigning the last name `Pantanizoupolos' to a VARCHAR2(10) raises an exception.
A constant is a fixed variable, which means you can't change its assigned value inside your program. To turn a variable into a constant, you simply include the CONSTANT keyword in the variable's declaration. Note that, by convention, constant variable names are usually uppercase. Here are some examples:
PI CONSTANT NUMBER := 3.14159; YEARLY_WORK_HOURS CONSTANT NUMBER := 2000; LINES_PER_PAGE CONSTANT NUMBER := 60; OPEN_STATUS CONSTANT VARCHAR(1) := 'O';
The main use for constants is to replace a program's magic values -- numbers or strings meaningful only to the programmer or business -- with more easily understood names. For instance, the last example in the previous section used the number 2000 to calculate an annual salary. While we can often deduce the meaning of a magic value (in this case, the number of hours in the work year), it's just bad coding practice to randomly sprinkle your program with literals.
The problem with sticking these values directly into the code is that -- believe it or not -- constants can change. Your employer might decide to adopt a six-hour workday (perhaps a German conglomerate buys your company) and reduce the number of hours in the work year to 1500. Using literals, you would have to go through your code line by line and replace all the 2000s with 1500s. Constants eliminate this tedious make-work. We can simply create a constant called YEARLY_WORK_HOURS, assign it a value of 2000, or 1500, or whatever value we want, and use it to clarify our calculations. This is shown in the following example:
annual_salary := hourly_rate * YEARLY_WORK_HOURS;
In this section, we'll look at how to create conditional statements. A conditional statement executes a code segment based on a condition, such as an equality test (a = b), a comparison test (a > b), or a Boolean test. PL/SQL has three conditional structures: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF-THEN-...-ELSE.
The IF-THEN format executes a code block if the condition is TRUE. For example:
IF line_count > LINES_PER_PAGE
THEN
line_count := 0;
DBMS_SQL.PUT_LINE ('--------');
END IF;The IF-THEN-ELSE format has two code blocks. If the condition is TRUE, the first block is executed; otherwise, the second block is executed. For example:
IF items_sold > get_employee_target (emp_id) THEN over_quota_count := over_quota_count + 1; give_raise (emp_id); ELSE give_talking_to (emp_id); END IF;
The IF-THEN-ELSIF-THEN-...-ELSE, PL/SQL's equivalent of the CASE or SWITCH statement, can contain multiple conditions. The statement executes the code block associated with the first TRUE condition. Here's an example:
IF is_number (current_char) OR is_letter (current_char) THEN new_char := current_char; ELSIF current_char = ' ' THEN new_char := '+'; ELSE new_char := convert_to_hex (current_char); END IF;
NOTE: Be careful with conditional syntax. Every PL/SQL programmer has made at least one of the following two mistakes: using END instead of END IF, or adding an "E" in the "ELSIF" keyword. In either case, the compiler gets confused and generates an error.
Looping, or iteration, causes the block between the keywords LOOP and END LOOP to be repeatedly executed. The loop ends, or terminates, when an exit condition is met. Once a loop terminates, program control is returned to the first line after the END LOOP keyword. There are three looping structures: simple, WHILE, and FOR.
In the simple loop, the exit condition is embedded inside the loop body. The EXIT command terminates the loop immediately, and is usually embedded inside an IF...THEN statement. EXIT WHEN combines EXIT with a conditional to form a more compact syntax. Here are two constructions of a simple loop. The first example uses EXIT:
LOOP
COUNT := COUNT + 1;
IF COUNT > 10
THEN
EXIT;
END IF;
END LOOP;The second example uses EXIT WHEN:
LOOP COUNT := COUNT + 1; EXIT WHEN COUNT > 10; END LOOP;
In the second kind of loop, the WHILE loop, the exit condition is outside the body of the loop. The code within the body of the loop iterates while the loop condition is true. The loop terminates when the condition is false, for example:
WHILE (COUNT <= 10) LOOP COUNT := COUNT + 1; END LOOP;
The last kind of loop, the FOR loop, iterates a predetermined number of times. For example, the number of loops needed to process each month in the year does not depend on a complex condition; it always requires 12 passes through the loop. A FOR loop is controlled by an index variable that ranges from a lower bound to an upper bound. The index variable begins at the lower bound. Each pass through the loop increments it. The loop terminates when the index reaches the upper bound, for example:
FOR month_index IN 1 .. 12 LOOP process_month_sales (month_index); END LOOP;
There are a few things to be aware of when using FOR loops:
The lower bound and upper bound are evaluated only once, on the first pass through the loop. Changes made to the bounds inside the body, assuming they are local variables, are ignored.
It's generally considered bad practice to use the EXIT command to short circuit the fixed nature of the FOR loop. If the number of loops depends on a condition, then a simple loop or WHILE loop is a clearer construct than a FOR loop.
A cursor is a PL/SQL construct used to process a SQL statement one row at a time. Each cursor is associated with a SELECT statement and a number of attributes. The SELECT statement defines a virtual table called the result set that contains all the rows of the underlying SELECT statement. The cursor's attributes provide information about the cursor's structure and current status.
The first step in the life of most cursors is a two-part declaration. The first part of the declaration names the cursor and binds it to a SELECT statement. The second part uses this name and a cursor attribute to create a PL/SQL data structure that holds the rows of the result set. Once these two elements are declared, the cursor is ready for processing, which requires three steps:
The cursor is opened by executing the query and building the result set.
Each row in the result is processed inside the body of a loop by fetching the current row of the result set into the PL/SQL data structure. Each fetch advances the current row pointer.
The cursor is closed and the memory taken by the result set is freed.
Let's look at declaring and processing a cursor in more detail.
The declaration of a cursor binds a name to a SQL SELECT statement. In addition to the cursor declaration itself, you'll also need to declare a variable that will hold the information read from the cursor, since the cursor is only a pointer to a row, not the row itself. This variable, declared as a record, holds data from the current row of the cursor.
A record is similar to a row in a table; it is a single entity made up of named fields, exactly as a row is composed of columns. Each field has its own datatype. The syntax for referencing a field is similar to the syntax used for referencing a table column; it requires the name of the record, a period, and the name of the field.
The simplest way to create a record is to anchor it to the cursor's structure using the %ROWTYPE attribute. The following example shows the declaration for a cursor and a record variable to hold its results:
CURSOR emp_cur
IS
SELECT *
FROM emp
ORDER BY lname;
emp_rec emp_cur%ROWTYPE;You can limit the rows returned in a cursor by using variables in the statement's WHERE clause. You can also include parameters as part of the declaration of a cursor itself. The syntax for declaring a parameter is the same as for procedures and functions. This is useful when you want to create a modular cursor declaration shared by a number of modules. For instance, to limit the previous example to a single individual, we could pass a primary key as a parameter (the record is still required, even if there is just one row in the result set):
CURSOR emp_cur (id IN NUMBER)
IS
SELECT *
FROM emp
WHERE emp.emp_id = id;
emp_rec emp_cur%ROWTYPE;You can process the rows of a cursor after you declare it. The first step in the process is the OPEN command, which executes the query and builds the result set. The OPEN command takes the name of the cursor to open, and must also provide values for any of the cursor's parameters. The syntax for passing cursor parameters is the same as that for procedures and functions:
OPEN emp_cur; OPEN emp_cur(102);
Once the cursor is open, its individual rows can be processed. Usually, this happens within a loop. The FETCH statement pulls the current row from the result set into the PL/SQL record and advances the current pointer to the next record. The values of the FOUND and NOTFOUND cursor attributes indicate whether the most recent fetch returned a row and can be used as the exit condition for the loop. The cursor is positioned immediately before the first row when it is opened. Once the row is fetched into a record data structure, its individual columns can be used just like local variables. Once the records are processed, the cursor must be closed using the CLOSE command. For example:
/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PROCEDURE print_emps
IS
CURSOR emp_cur
IS
SELECT *
FROM emp
ORDER BY lname;
emp_rec emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur; -- open the cursor
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%notfound; -- exit condition
/*
|| Print employee information. Note that the syntax for the
|| field names uses the record variable, not the cursor.
*/
HTP.print (emp_rec.lname);
HTP.print (get_department_name (emp_rec.dpt_id));
HTP.print (emp_rec.lname);
END LOOP;
CLOSE emp_cur;
END;As another example, here is a formal implementation of a slightly modified version of the get_emp_info procedure. In this example, only one fetch is necessary, so there is no need for a loop. However, the procedure must test to see if a matching record was found before it returns a value. This is done using the FOUND attribute:
/* Formatted by PL/Formatter v.1.1.13 */
/*
|| Procedure to return employee information
|| for the passed employee id
|| Parameters
|| ----------
|| e_id IN - employee to return (primary key)
|| e_num OUT - employee number
|| e_name OUT - employee name
|| e_dpt_id OUT - employee'sn department name
*/
CREATE OR REPLACE PROCEDURE get_emp_info (
e_id IN NUMBER,
e_num OUT VARCHAR2,
e_name OUT VARCHAR2,
e_dpt_id OUT NUMBER
)
IS
-- Tests for the parameter value in the WHERE clause
CURSOR emp_cur
IS
SELECT *
FROM emp
WHERE emp.emp_id = e_id;
emp_rec emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
IF emp_cur%found
THEN
e_num := emp_rec.emp_number;
e_name := emp_rec.fname || ' ' || emp_rec.lname;
e_dpt_id := emp_rec.dpt_id;
ELSE
e_num := NULL;
e_name := NULL;
e_dpt_id := NULL;
END IF;
CLOSE emp_cur;
END get_emp_info;The previous examples were all explicit cursors. We declared the cursor, opened it, processed its rows, then closed it. There is a second type of hidden cursor called an implicit cursor that allows us to skip these steps. The SELECT...INTO command, which programmers use to save time, is the most common example of an implicit cursor. Here's an example that loads information from a table into a local variable, all in one step:
SELECT emp_rec.emp_number,
emp_rec.fname || ' ' || emp_rec.lname,
emp_rec.dpt_id
INTO e_num, e_name, e_dpt_id
FROM emp
WHERE emp.emp_id = e_id;Despite its brevity, there are three reasons to avoid SELECT...INTO:
SELECT...INTO is slower than an explicit cursor because it makes two fetches instead of one. The first fetch determines how many rows the query returns, and the second fetch actually retrieves the data and assigns the columns to the variables.
SELECT...INTO raises an exception if the underlying query doesn't return exactly one row. If it returns no rows, it raises the NO_DATA_FOUND exception. If it finds more than one row, it raises the TOO_MANY_ROWS exception. This behavior often results in unhandled exceptions, because the harried programmer, in a rush to finish, makes a wrong assumption about the query.
SELECT...INTO makes you lazy. We should take positive steps to prevent foreseeable errors, not simply respond to them as if they're uncontrollable acts of God. The extra time it takes to implement a single-row SELECT using an explicit cursor almost always outweighs the short-term benefits of the SELECT...INTO command.
The last construct we'll look at is the index-by table, PL/SQL's version of an array (prior to Oracle8i, these were called PL/SQL tables). Like a true array, an index-by table is made up of elements indexed by unique integers. This, however, is about as far as the analogy goes. Table 6.1 summarizes the difference between real arrays and index-by tables.
| Real Array | Index-by Table |
|---|---|
| Multidimensional. | One-dimensional. |
| Contains a fixed number of elements. | Contains an "unlimited" number of elements. |
| Memory is allocated for every element in the array, even if it's never used. | Memory is allocated only when an element is added. |
| Elements are consecutive (i.e., a(1), a(2), a(3) . . . a(N)). | Elements are non-consecutive (i.e., a(1), a(5632), a(1013), a(999), . . . ). |
In reality, an index-by table is much closer to a linked-list or single-column table than to an array. Despite this (or because of it, depending on the application), it's an extremely useful construct with a wide range of applications. In this section, we'll see how to declare an array, assign values to its elements, and then retrieve the values.
There are two steps in creating an index-by table. The first is to define a new datatype for the table. The second is to declare the actual table variable itself by assigning it to the new table datatype created in the first step.
You define a table's datatype by placing the following command in the declaration section of a procedure or function:[6]
[6] You can also declare the type in a package specification or body. We'll see how to do this in the next section.
TYPE table_type_name IS TABLE OF element_datatype INDEX BY BINARY_INTEGER;
The table_type_name is the name of the index-by table datatype (not the name of the table variable itself ), and the element_datatype specifies the type of elements the table contains. These elements can be any simple scalar datatype, like a number, date, or string; unfortunately, you can't use complex types like records or other index-by tables. You can either define the type directly (for example, by declaring it as a NUMBER) or use an anchored declaration to link it to a table in a column. Here are a few examples of declaring an index-by table datatype:
TYPE monthly_sales_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE ssn_array_type IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER; TYPE emp_array_type IS TABLE OF emp.lname%TYPE INDEX BY BINARY_INTEGER;
You can create a table variable after you've defined the table datatype. These are like normal variable declarations, except that they use the datatypes you defined in the previous step. Here are a few examples of creating a table variable:
sales_by_month monthly_sales_type; ssn_array ssn_array_type; emp_name_array emp_array_type;
You can begin adding elements to the table after you have created its type and an associated variable.
You assign values to an index-by table by associating its elements with unique integer indexes. The syntax is similar to that used in C or Pascal. Here are some examples for a table of string elements:
emp_name_array (16) := 'albee'; emp_name_array (21) := 'mcmanus'; emp_name_array (1043) := 'jenkins'; emp_name_array (1013) := 'harrington';
Unlike most 3GL languages, where the index is a fixed offset from the start of the array, the index of an index-by table is basically a primary key. Assigning an element for the first time is similar to inserting a record into a normal database table. Subsequent changes to the value are like UPDATE statements.
You retrieve the value for an element using the same syntax you would for a 3GL language. Here are a few examples:
HTP.print (emp_name_array (21));
HTP.print (emp_name_array (1013));
IF emp_name_array (1043) = 'jenkins'
THEN
HTP.print ('Hi, Garry.');
END IF;There is one major difference between the retrieval of an index-by table element and a 3GL array element: you can only retrieve elements to which you have previously assigned values. The retrieval process is a lot like the SELECT...INTO statement we looked at in the section on cursors: the RDBMS raises a NO_DATA_FOUND exception if the element doesn't exist. To prevent this, make sure you initialize your arrays before you use them.
|  | |
| 6.1 Structured Programming in PL/SQL |  | 6.3 Packages |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.