Oracle PLSQL Language- P12

Tham khảo tài liệu oracle plsql language- p12, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Oracle PLSQL Language- P12 3. Using another exception section within the first exception section, trap this exception as the else in this pseudo-IF statement. Within the exception handler, try to convert the string with TO_DATE and the third mask, MM/YY. If it works, I am done. If it doesnt work, an exception is raised. 4. I have only three masks, so if I cannot convert the string after these three TO_DATE calls, the user entry is invalid and I will simply return NULL. The function convert_date that follows illustrates the full PL/SQL version of the preceding pseudocode description. I make liberal use of the WHEN OTHERS exception handler because I have no way of knowing which exception would have been raised by the conversion attempt: FUNCTION convert_date (value_in IN VARCHAR2) RETURN DATE IS return_value DATE; BEGIN IF value_int IS NULL THEN return_value := NULL; ELSE BEGIN /* IF MM/DD/YY mask works, set return value. */ return_value := TO_DATE (value_in, MM/DD/YY); EXCEPTION /* OTHERWISE: */ WHEN OTHERS THEN BEGIN /* IF DD-MON-YY mask works, set return value. */ return_value := TO_DATE (value_in, DD-MON- YY); EXCEPTION /* OTHERWISE: */ WHEN OTHERS THEN BEGIN /* IF MM/YY mask works, set return value. */ return_value := TO_DATE (value_in, MM/YY); EXCEPTION /* OTHERWISE RETURN NULL. */ WHEN OTHERS THEN return_value := NULL; END; END; END; END IF; RETURN (return_value); END;Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 8.8 Oracle PL/SQL Next: 8.10 RAISE Nothing NO_DATA_FOUND: Programming, 2nd Edition but Exceptions Multipurpose Exception 8.8 NO_DATA_FOUND: Book Index 8.10 RAISE Nothing but Multipurpose Exception Exceptions The Oracle Library Navigation Copyright (c) 2000 OReilly & Associates. All rights reserved.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 8.9 Exception Chapter 8 Next: 9. Records in PL/SQL Handler as IF Statement Exception Handlers 8.10 RAISE Nothing but Exceptions Have you noticed that the RAISE statement acts in many ways like a GOTO statement? The GOTO statement in PL/SQL looks like this: GOTO label_name; where label_name is the name of a label. This label is placed in a program as follows: When PL/SQL encounters a GOTO statement, it immediately shifts control to the first executable statement following the label (which must still be in the execution section of the PL/SQL block). The RAISE statement works much the same way: when PL/SQL encounters a RAISE, it immediately shifts control to the exception section, and then looks for a matching exception. A very significant and fundamental difference between GOTO and RAISE, however, is that GOTO branches to another execution statement, whereas RAISE branches to the exception section. The RAISE statement, in other words, shifts the focus of the program from normal execution to error handling mode. Both from the standpoint of code readability and also of maintenance, you should never use the RAISE statement as a substitute for a control structure, be it a GOTO or an IF statement. If you have not tried to use RAISE in this way, you might think that I am building up a straw man in order to knock it down. Would that it were so. Just in the process of writing this book, I ran across several examples of this abuse of exception handling. Check out, for example, the function description for GET_GROUP_CHAR_CELL in Oracle Corporations Oracle Forms Reference Volume 1. It offers a function called Is_Value_In_List, which returns the row number of the value if it is found in the record group, as an example of a way to use GET_GROUP_CHAR_CELL. The central logic of Is_Value_In_List is shown in the following example. The function contains three different RAISE statements -- all of which raise the exit_function exception: 1 FUNCTION Is_Value_In_ListPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 2 (value VARCHAR2, rg_name VARCHAR2, rg_column V ...