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! I am confident that you will find it easier than you think. There are two reasons for my 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, 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 look at a few examples that demonstrate some key elements of both PL/SQL structure and functionality.
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” such as 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:
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.)
The BEGIN keyword indicates the beginning of my execution section—the code that will be run when I pass this block to SQL*Plus.
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.
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.
This single-line comment explains the purpose of the UPDATE.
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”.
- IF and CASE statements
- A full complement of looping or iterative controls
- The GOTO statement
Here is a procedure (a reusable block of code that can be called by name) that demonstrates some of these features:
1 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:
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).
This is the declaration section of the procedure. Notice that instead of using a DECLARE keyword, as in the previous example, the keyword IS (or AS) is used to separate the header from the declarations.
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.
Here, I call to 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.
Here is an IF statement that can be interpreted as follows: if the account balance has fallen below $1,000, stop allocating funds to cover bills. Otherwise, apply the balance to the next charge.
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 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;
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.
This query retrieves the name for the account. If there is no account for this ID, the database raises the predefined NO_DATA_FOUND exception, causing the program to stop.
If the balance is too low, I explicitly raise my own exception because I have encountered a serious problem with this account.
The EXCEPTION keyword denotes the end of the executable section and the beginning of the exception section in which errors are handled.
This is the error-handling section for the situation in which the account is not found. If NO_DATA_FOUND was the exception raised, it is trapped here, and the error is logged with the log_error procedure.
This is the error-handling section for the situation in which the account balance has gotten too low (my application-specific exception). If l_balance_below_minimum is raised, it’s trapped here, and the error is logged. Then, due to the seriousness of the error, I raise the same exception again, 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 PL/SQL’s error-handling mechanisms.
There is, of course, much more that can be said about PL/SQL—which is why you have hundreds more pages of material to study in this book! These initial examples should, however, give you a 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.