Oracle PL/SQL Programming, Third Edition

So This Is PL/SQL

If you are completely new to programming or to working with PL/SQL (or even SQL, for that matter), learning PL/SQL may seem an intimidating prospect. If this is the case, don’t fret! We are confident that you will find it easier than you think. There are two reasons for our optimism:

• Computer languages in general are not that hard to learn, at least compared to a second or third “human language.” The reason? It’s simply that computers are not particularly smart (they “think”—perform operations—rapidly, but not at all creatively). We must rely on a very rigid syntax in order to tell a computer what we want it to do. So the resulting language is also rigid (no exceptions!) and therefore easier for us to pick up.

• PL/SQL truly is an easy language as compared to other programming languages. It relies on a highly structured “block” design with different sections, all identified with explicit, self-documenting keywords.

Let’s take a look at a few examples that demonstrate some of the key elements of both PL/SQL structure and functionality.

Integration with SQL

One of the most important aspects of PL/SQL is its tight integration with SQL. You don’t need to rely on any intermediate software “glue” like ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity) to run SQL statements in your PL/SQL programs. Instead, you just insert the UPDATE or SELECT into your code, as shown here:

 1  DECLARE
 2     l_book_count INTEGER;
 3    
 4  BEGIN
 5     SELECT COUNT(*)
 6       INTO l_book_count
 7       FROM books
 8      WHERE author LIKE '%FEUERSTEIN, STEVEN%';
 9 
10     DBMS_OUTPUT.PUT_LINE (
11        'Steven has written (or co-written) ' ||
12         l_book_count ||
13         ' books.');
14
15     -- Oh, and I changed my name, so...
16     UPDATE books
17        SET author = REPLACE (author, 'STEVEN', 'STEPHEN')
18      WHERE author LIKE '%FEUERSTEIN, STEVEN%';
19  END;

Let’s take a more detailed look at this code in the following table:

Line(s)	Description
1-3	This is the declaration section of this so-called “anonymous” PL/SQL block, in which I declare an integer variable to hold the number of books that I have authored or coauthored. (I’ll say much more about the PL/SQL block structure in Chapter 3.)
4	The BEGIN keyword indicates the beginning of my execution section—the code that will be run when I pass this block to SQL*Plus.
5-8	I run a query to determine the total number of books I have authored or coauthored. Line 6 is of special interest: the INTO clause shown here is actually not part of the SQL statement, but instead serves as the “bridge” from the database to local PL/SQL variables.
10-13	I use the DBMS_OUTPUT.PUT_LINE built-in procedure (i.e., a procedure in the DBMS_OUTPUT package supplied by Oracle) to display the number of books.
15	This single-linecomment explains the purpose of the UPDATE.
16-18	I have decided to change the spelling of my first name to “Stephen”, so I issue an update against the books table. I take advantage of the built-in REPLACE function to locate all instances of “STEVEN” and replace them with “STEPHEN”.

Control and Conditional Logic

PL/SQL offers a full range of statements that allow us to very tightly control which lines of our programs execute. These statements include:

IF and CASE statements

These implement conditional logic; for example, “If the page count of a book is greater than 1000, then . . . "

A full complement of looping or iterative controls

These include the FOR loop, the WHILE loop, and the simple loop.

The GOTO statement

Yes, PL/SQL even offers a GOTO that allows you to branch unconditionally from one part of your program to another. That doesn’t mean, however, that you should actually use it.

Here is a procedure (a reusable block of code that can be called by name) that demonstrates some of these features:

 1  CREATE OR REPLACE PROCEDURE pay_out_balance (
 2     account_id_in IN accounts.id%TYPE)
 3  IS
 4     l_balance_remaining NUMBER;
 5  BEGIN
 6     LOOP
 7        l_balance_remaining := account_balance (account_id_in);
 8  
 9        IF l_balance_remaining < 1000
10        THEN
11           EXIT;
12        ELSE
13           apply_balance (account_id_in, l_balance_remaining);
14        END IF;
15     END LOOP;
16  END pay_out_balance;

Let’s take a more detailed look at this code in the following table:

Line(s)	Description
1-2	This is the header of a procedure that pays out the balance of an account to cover outstanding bills. Line 2 is the parameter list of the procedure, in this case consisting of a single incoming value (the identification number of the account).
3-4	This is the declaration section of the procedure. Notice that instead of using a DECLARE keyword, the keyword IS (or AS) is used to separate the header from the declarations.
6-15	Here is an example of a simple loop. This loop relies on an EXIT statement (see line 11) to terminate the loop; FOR and WHILE loops specify the termination condition differently.
7	Here I call the account_balance function to retrieve the balance for this account. This is an example of a call to a reusable program within another reusable program. Line 13 demonstrates the calling of another procedure within this procedure.
9-14	An IF statement that can be interpreted as follows: if the account balance has fallen below $1000, then stop allocating funds to cover bills. Otherwise, apply the balance to the next charge.

When Things Go Wrong

The PL/SQL language offers a powerful mechanism for both raising and handling errors. In the following procedure, I obtain the name and balance of an account from its ID. I then check to see if the balance is too low; if it is, I explicitly raise an exception, which stops my program from continuing:

 1  CREATE OR REPLACE PROCEDURE check_account (
 2     account_id_in IN accounts.id%TYPE)
 3  IS
 4     l_balance_remaining       NUMBER;
 5     l_balance_below_minimum   EXCEPTION;
 6     l_account_name            accounts.name%TYPE;
 7  BEGIN
 8     SELECT name
 9       INTO l_account_name
10       FROM accounts
11      WHERE id = account_id_in;
12
13     l_balance_remaining := account_balance (account_id_in);
14
15     DBMS_OUTPUT.put_line (
16        'Balance for ' || l_account_name ||
17         ' = ' || l_balance_remaining);
18
19     IF l_balance_remaining < 1000
20     THEN
21        RAISE l_balance_below_minimum;
22     END IF;
23
24  EXCEPTION
25     WHEN NO_DATA_FOUND
26     THEN
27        -- No account found for this ID
28        log_error (...);
29
30     WHEN l_balance_below_minimum
31     THEN
32        log_error (...);
33        RAISE;
34  END;

Let’s take a more detailed look at the error-handling aspects of this code in the following table:

Line(s)	Description
5	I declare my own exception, called l_balance_below_minimum. Oracle provides a set of predefined exceptions, such as DUP_VAL_ON_INDEX, but I need something specific to my application, so I must define it myself in this case.
8-11	This query retrieves the name for the account. If there is no account for this ID, then Oracle will raise the predefinedNO_DATA_FOUND exception, causing the program to stop.
19-22	If the balance is too low, I will explicitly raise my own exception, as I have encountered a serious problem with this account.
24	The EXCEPTION keyword denotes the end of the executable section and the beginning of the exception section in which errors are handled.
25-28	This is the error-handling section for the situation where the account is not found. If NO_DATA_FOUND was the exception raised, it will be “trapped” here and the error will be logged.
30-33	This is the error-handling section for the situation where the account balance has gotten too low (my application-specific exception). If l_balance_below_minimum was raised, it will be “trapped” here and the error will be logged. Then, due to the seriousness of the error, I will re-raise the same exception, propagating that error out of the current procedure and into the PL/SQL block that called it.

Chapter 6 takes you on an extensive tour of the error-handling mechanisms of PL/SQL.

There is, of course, much more that can be said about PL/SQL—which is why you have about another 950 pages of material to study in this book! However, these initial examples should give you a good feel for the kind of code you will write with PL/SQL, some of its most important syntactical elements, and the ease with which one can write—and read—PL/SQL code.