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
01. Et ve Et Ürünlerinde Boya Maddeleri Aranması 01.01. Organik Boya Aranması …
"Islahat hareketlerinin babası ve 19.yüzyıl Osmanlı siyaset adamlarının fikir ustası" (1) olarak tanınan Hariciye Nazırı…
DUSUNCE AKIMLARI Ortaya atilan her yeni "dusunce akimi"nin yandaslari, ileri surdukleri goruslerin bir "yeni dunya…
01. Yöntemin Prensibi Domateslerde 4-CPA kalıntı analizi yönteminin temel prensibi örneğe uygulanan…
01. Meyve Sularında Etanol Tayini 01.01. Yöntemin Prensibi Örnekten damıtılarak ayrılan etanolün,…