3. 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 operationsrapidly, 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.

1.3.1. 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" 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    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 sectionthe 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-line comment 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".

1.3.2. 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, as in the previous example, 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	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.

1.3.3. 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, Oracle raises the predefined NO_DATA_FOUND exception, causing the program to stop.
19-22	If the balance is too low, I explicitly raise my own exception because 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 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 .
30-33	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 reraise 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 PL/SQL’s error-handling mechanisms.

There is, of course, much more that can be said about PL/SQLwhich is why you have hundreds more pages of material to study in this book! These initial examples should, however, 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 writeand readPL/SQL code.

Kaynak: Oracle PL/SQL Programming, Fourth Edition
belgesi-1152

Belgeci , 2280 belge yazmış

Cevap Gönderin