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;
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 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;
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 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;
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. |
Kaynak: Oracle PL/SQL Programming, Fourth Edition
belgesi-1152
ÖZELLİKLER: Boyut: 28x8x6 cm Ağırlık: 850gr Ekran: Yok Devre sayısı: 30 Konuşma süresi: 35 dakika…
There are two kinds of questions: yes or no questions and wh- questions. You ask…
A positive sentence tells you that something is so. A sentence that tells you something…
Use the base form of a verb to give commands or make direct requests. This…
A sentence is a group of words that expresses a complete thought. A sentence must…
An interjection is a word that expresses a sudden, strong feeling such as surprise, pain,…