Shulou(Shulou.com)06/01 Report--

1. The concept and classification of exception handling. What is an exception? Exception: an unexpected event that occurs during the normal execution of a program. B. what is exception handling? Exception handling is to improve the robustness of the program, the use of exception handling part can effectively solve all kinds of errors that may occur in the normal execution of the program, making the program run normally. .d. Position DECLARE / * * declaration part in the PL/SQL block of exception handling code-- declaration of variables, constants, complex data types, cursors, etc. * / BEGIN / * * execution part-- PL/SQL statement and SQL statement * / EXCEPTION / * * exception handling part-handling run error * / END DECLARE v_name emp.ename%TYPE; v_sal emp.sal%TYPE;BEGIN SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = & no; IF v_sal < 3000 THEN dbms_output.put_line (v_name | | 'salary is:' | | v_sal); END IF;EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line ('incorrect employee ID!') ; WHEN OTHERS THEN dbms_output.put_line ('other errors!') END;e, classification of predefined exceptions, non-predefined exceptions and custom exceptions 2. Predefined exceptions refer to the system exceptions provided by PL/SQL. Oracle provides more than 20 predefined exceptions, each of which corresponds to a specific Oracle error. When these Oracle errors occur in the PL/SQL block, the corresponding predefined exceptions are implicitly triggered.

For the handling of predefined exception cases, there is no need to define it in the program, just refer to the corresponding exception case name directly in the exception handling part of the PL/SQL block, and complete the corresponding exception error handling. Sample code: query the name of the employee according to the salary entered. And output the employee's name and salary. DECLARE v_name emp.ename%TYPE; v_sal emp.sal%TYPE: = & salary; BEGIN SELECT ename INTO v_name FROM emp WHERE sal = vested salar; DBMS_OUTPUT.put_line (v_name | | 'salary is:' | | v_sal); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('employee who does not have the salary') WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.put_line ('multiple employees have this salary'); WHEN OTHERS THEN DBMS_OUTPUT.put_line ('other errors'); END;3, non-predefined exceptions are used to handle ORACLE errors that cannot be handled by predefined exceptions, which need to be defined in the program. A. The handling of non-predefined exceptions includes three steps.

1. Define the exception in the PL/SQL block: EXCEPTION

2. Associate the defined exception with the standard ORACLE error, using the PRAGMA EXCEPTION_INIT statement: PRAGMA EXCEPTION_INIT (,)

3. In the exception handling part of the PL/SQL block, the exception is handled accordingly.

B. the code demonstrates deleting the specified part of the information in the dept table. (when deleting a department, you should ensure that there are no employees in the specified department, because the dept and emp tables are stored in the primary foreign key relationship.)

1. When deleting a department with employees, get the error number: BEGIN DELETE FROM dept WHERE deptno = & deptno;EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLCODE | |'#'| | SQLERRM); END; when you press F8 and type 20, the console prints the following:-2292###ORA-02292: violation of complete constraints (SCOTT.FK_DEPTNO)-child records have been found

DECLARE-- 1, define the identifier of the non-predefined exception e_fk EXCEPTION;-- 2, associate the Oracle error with the exception information-2292 the error number PRAGMA EXCEPTION_INIT (e_fk,-2292) that violates the foreign key constraint; BEGIN DELETE FROM dept WHERE deptno = & deptno EXCEPTION WHEN e_fk THEN-- 3. Catch and handle exception dbms_output.put_line ('there are employees in this department and cannot be deleted!') ; WHEN OTHERS THEN dbms_output.put_line (SQLCODE | |'#'| | SQLERRM); END; when you press F8 to execute, after typing 20, the console prints the following: there are employees in this department and cannot be deleted! 4. Custom exception if you want to send some warning messages to the user of the application when a particular event occurs. The event itself does not throw an Oracle internal exception, which is a specific exception that belongs to the application. Then you need a custom exception. Triggered by the explicit use of a RAISE statement when a user-defined exception error occurs. When an exception error is thrown, control goes to the exception error section of the EXCEPTION block and executes the error handling code. A. Custom exception handling steps for handling such exceptions are as follows:

1. Define the exception in the declaration section of the PL/SQL block: EXCEPTION

2 、 RAISE

B. The sample code updates the salary of the specified employee number: DECLARE v_empno emp.empno%TYPE: = & empno; e_no_result EXCEPTION;BEGIN UPDATE emp SET sal = sal + 100WHERE empno = vested empno; IF SQL%NOTFOUND THEN RAISE estrangnostatic result; ELSE COMMIT; END IF;EXCEPTION WHEN e_no_result THEN dbms_output.put_line ('data update failed!') ; WHEN OTHERS THEN dbms_output.put_line ('other errors'); END;5, SQLCODE and SQLERRMa, exception handlers, exception handlers are used to obtain Oracle error numbers and error messages, where the function SQLCODE is used to obtain error numbers and SQLERRM is used to obtain error messages. When writing PL/SQL blocks, you can get unexpected Oracle errors by referencing the functions SQLCODE and SQLERRM in the exception handling section. In addition, by using the built-in procedure RAISE_APPLICATION_ERROR, you can customize the error number and error message when creating subroutines (procedures, functions, packages). B. Sample code for exception handling function inserts employee information (including employee number, employee name and employee department number): DECLARE v_empno emp.empno%TYPE: = & empno; v_ename emp.ename%TYPE: ='& ename'; v_deptno emp.deptno%TYPE: = & deptno;BEGIN INSERT INTO emp (empno, ename, deptno) VALUES (v_empno, v_ename, v_deptno) IF SQL%FOUND THEN DBMS_OUTPUT.put_line ('data inserted successfully'); COMMIT; END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('error number:' | | SQLCODE); DBMS_OUTPUT.put_line ('error message:' | | SQLERRM); END; c, RAISE_APPLICATION_ERROR this procedure is used to customize error messages in the PL/ SQL subroutine. The syntax format is: raise_application_error (error_number, message); error_number: used to define the error number (- 20000 ink 20999).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.