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

PL/SQL (Procedural Language/SQL, procedural language / SQL) is an extension language that combines Oracel procedural language and structured query language (SQL).

Advantages:

The main results are as follows: (1) PL/SQL has the characteristics of programming language, it can put a group of SQL statements into a module, which makes it more modular.

(2) PL/SQL can adopt the structure of procedural language control program.

(3) PL/SQL has an automatic exception handling mechanism.

(4) the PL/SQL program block has better portability and can be transplanted to another Oracle database.

(5) the PL/SQL program reduces the interaction of the network and helps to improve the program performance.

The exception handling part of exception is a very important part of the OraclePL/SQL statement block, which determines that when an exception occurs in the executable part of the PL/SQL statement block, the program is friendly to prompt: the program encounters some errors and cannot be executed, or throws a bunch of incomprehensible Oracle internal error codes.

This article only introduces three advanced forms of three kinds of PL/SQL exceptions, which are used to solve the problem that there are too few built-in exceptions in Oracle, which often can not meet the actual needs.

1,RAISE_APPLICATION_ERROR

 -A special built-in procedure provided by Oracle that allows programmers to create meaningful error messages for specific programs, suitable for user-defined exceptions.

 -grammatical structure

   RAISE_APPLICATION_ERROR (error_number,error_message); or

   RAISE_APPLICATION_ERROR (error_number,error_message,keep_errors)

  -error_number is the error number associated with a specific error message, and Oracle reserves-20999-20000 specifically for programmers to customize the error code.

  -error_message is the error message text and contains up to 2048 characters.

  -keep_errors is an optional Boolean parameter. The default is FALSE. If it is TRUE, the newly thrown error will be added to the list of thrown errors, which is called the error stack. If FALSE, the new error will replace the thrown error stack.

 -applies to unnamed user-defined exceptions and is responsible for associating the error number with the error message. The user defines the exception but does not define the name of the error

 -using the RAISE_APPLICATION_ERROR procedure, programmers can return error messages in the same way as Oracle.

-sample code

Declare v_id number: = & pairid; v_name varchar2 (20); v_sal number;begin if v_id > 0 then select ename,sal into vprincipal from emp where empno = vuploid; dbms_output.put_line (chr (10) | | v_name | |'| v_sal); else raise_application_error (- 20001jure employee id can not be negative.'); end if Exception when NO_DATA_FOUND then dbms_output.put_line (chr (10) | | 'There is no such employee id is' | | v_id); end;/Enter value for p_id: 40old 2: v_id number: = & v_id number: = 40 is no such employee id is 40PL/SQL procedure successfully completed./Enter value for p_id there is no such employee id is 40PL/SQL procedure successfully completed./Enter value for p_id:-90old 2: v_id number: = & v_id number: =-90 Declare*ERROR at line 1:ORA-20001: Employee id can not be negative.ORA-06512: at line 11

-example parsing: the PL/SQL code queries the employee's name and salary based on the employee Id entered by the user. When we enter the existing employee number, the program can return the result normally; if there is no ID in the input, the select into statement throws a row that does not return, thus causing the program to enter the exception handling part (this part is divided into examples), and the program is also executed successfully. When entering a negative number, the if conditional statement will enter the raise_application_error part. Due to the error occurred in the executable part, the execution focus will immediately shift to the exception handling part, while the exception handling part has no handling about the exception, so the program reports an error and returns to the user interface.

-Yes raise_application_error, programmers can make programs implement error messages like those generated by Oracle systems.

-in fact, just use raise_application_error, because there is no exception name, and if you want to handle exceptions, you can only use others (described below).

2,EXCEPTION_INIT

 -using the EXCEPTION_INIT compilation instruction, you can associate a user-defined Oracle error number with a user-defined error name, which is equivalent to a combination of user-defined errors and RAISE_APPLICATION_ERROR.

 -EXCEPTION_INIT appears in the declaration section of the statement block:  

Exception_name exception;    pragma exception_init (exception_name,error_code)

-consider the following code:

Dbms_output.put_line (chr (10) | | 'The department id is' | | v_no | | 'has been deleted'); end;/Enter value for p_no: 20old 2: v_no number: = & paired notress new 2: v_no number: = 20 poster error error at line 1:ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated-child record foundORA-06512: at line 4

-the delete department failed due to a violation of the foreign key constraint. But the error thrown is not easy to understand.

-We can use EXCEPTION_INIT to handle this error. First we need to know the Oracle error code "ORA-02292" that violates the foreign key constraint.

-use EXCEPTION_INIT

Declare v_no number: = & pawnol; e_dept_exist exception; pragma exception_init (estranged deptabilityLaoyu 02292); begin delete from dept where deptno = viciono; dbms_output.put_line (chr (10) | | 'The department id is' | | v_no | | 'has been deleted'); exception when e_dept_exist then dbms_output.put_line (chr (10) | |' There are some employees in this deptartment, if you want delete this deptartment, please delete these employees in the department first.'); end / Enter value for p_no: 20old 2: v_no number: = & v_no number: = 20 there are some employees in this deptartment, if you want delete this deptartment, please delete these employees in the department first.PL/SQL procedure successfully completed.

This makes it much easier to understand the mistakes thrown. First we define an exception called e_dept_exist, and then associate this exception with the Oracle error code-02292. Enter the exception handling section when the program executes an error, where we redefine the error message.

3SQLCODE and SQLERRM

 -in exception handling, when the name of the exception is unknown (such as RAISE_APPLICATION_ERROR in 1 above), others can be used to catch the exception

 -because the exceptions caught by others are unknown (or known, but not enumerated in the program), you need to use the two built-in functions SQLCODE and SQLERRM provided by Oracle to handle others exceptions:

 -SQLCODE returns the error number of Oracle

 -SQLERRM, which returns an error message

-example 1, handle the error returned by the Oracle system:

Declare v_no number: = & pairnos; error_code number; error_msg varchar2 (500); begin delete from dept where deptno = vroomnos; dbms_output.put_line (chr (10) | | 'The department id is' | v_no | | 'has been deleted'); exception when others then error_code: = sqlcode; error_msg: = sqlerrm; dbms_output.put_line (chr (10) | |' Error code is:'| | error_code) Dbms_output.put_line (chr (10) | | 'Error message is:' | | error_msg); end;Enter value for p_no: 10old 2: v_no number: = & paired error code is:-2292Error message is: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated-child record foundPL/SQL procedure successfully completed.

-pay attention to the exception exception handling section, in which we use the two variables defined in the declaration section, and error_code is used to store SQLCODE,error_msg and SQLERRM. Then print out the values of the two variables.

-example 2, handling user-defined exceptions:

Declare v_id number: = & pairid; v_name varchar2 (20); v_sal number;begin if v_id > 0 then select ename,sal into vprincipal from emp where empno = vuploid; dbms_output.put_line (chr (10) | | v_name | |'| v_sal); else raise_application_error (- 20001jure employee id can not be negative.'); end if Exception when NO_DATA_FOUND then dbms_output.put_line (chr (10) | | 'There is no such employee id is' | | v_id); when others then declare error_code number; error_msg varchar2 (500); begin error_code: = sqlcode; error_msg: = sqlerrm; dbms_output.put_line (chr (10) | | 'Error code is:' | | error_code); dbms_output.put_line (chr (10) | | 'Error message is:' | error_msg); end;end / Enter value for p_id:-90old 2: v_id number: = & 20001Error message is: ORA-20001: Employee id can not be negative.PL/SQL procedure successfully completed. 2: v_id number: =-90 world error code is:-20001Error message is: Employee id can not be negative.PL/SQL procedure successfully completed.

-raise_application_error is used in this code. Due to the simple use of raise_application_error, only others can be used for capture. In the exception handling section, we use a block of PL/SQL statements to handle this error, declare two variables, and assign SQLCODE and SQLERRM to them literally.

Summary

The above is the example analysis of the abnormal advanced features in Oracle PL/SQL introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

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.