PL/SQL data development that's something.

PL/SQL development that little thing----->pl/sql exception handling during development

A user-written PL/SQL block inevitably has some errors during execution.

The errors involved are not caused by the syntax errors of the program, but because the data being processed is outside the scope of the processing and the error is raised.
If you give a name to such a mistake, this is the exception .
When a PL/SQL block detects an error during execution, the corresponding exception is thrown.
Such exceptions should be handled in the block, or it will cause the application to stop running.

Exception handlers

Exceptions are typically thrown by the database server when a PL/SQL program executes an error, or explicitly thrown by a programmer under certain conditions in a PL/SQL block.
Regardless of the form of the exception, you can write a program in the Exception handling section of the PL/SQL block, and if you do nothing, the exception is passed to the caller, which is handled uniformly by the caller .

Here's how two different exceptions are handled:

Mode one ( exception handling ):

DECLARE

Defining exceptions

BEGIN

Throw exception

EXCEPTION

　　Catching and handling exceptions

END;

Mode two ( exception pass ):

DECLARE

Defining exceptions

BEGIN

Throw exception

EXCEPTION

　　Do not handle exceptions

END;

Exception is passed to the caller

If you want to handle exceptions in a PL/SQL block, you need to write handlers in the exception handling section.

The exception handler is in the following form:

EXCEPTION

When exception 1 OR exception 2 Then

exception handling Program 1;

When exception 3 OR exception 4 Then

Exception handling program 2;

When OTHERS Then

Exception handler N;

END;

The exception handler starts with the keyword exception and ends with the keyword end.

In this section, multiple exceptions can be handled differently, or the same can be done.
If all exceptions are not listed, you can use the keyword others instead of the other exception, and at the end of the exception handler, add a when others clause to handle any exceptions that were not previously listed.
If a PL/SQL block performs an error, or if it encounters a statement that explicitly throws an exception, the program immediately stops executing and goes to execute the exception handler.
After the exception is processed, the execution of the entire PL/SQL block ends.

So once an exception occurs, in the executable portion of the PL/SQL block, starting at the place where the exception occurred, the future code will no longer execute.
There are three types of exceptions in PL/SQL blocks, that is, predefined exceptions , non-predefined exceptions , and user-defined exceptions.
Here are some examples of how to use these exceptions.

Pre-defined Exceptions

Oracle defines some common errors as named exceptions, which are predefined exceptions.

Oracle has many predefined exceptions that do not need to be defined when it is processed, only the appropriate exception handlers are written.
When a PL/SQL block execution error occurs, the database server automatically throws the corresponding exception and executes the exception handler that is written.

Some of the pre-defined exceptions are listed below.

No_data_found When retrieving data with the Select command, no data is found that satisfies the requirement

too_many_rows Multiple rows of data when retrieving data with the Select command

Dup_val_on_index Write a duplicate value on the primary key column

Cursor_already_open Attempting to open a cursor that is already open when manipulating the cursor

Invalid_number string is not a numeric string when converting a string to a number

login_denied is denied when connecting to an Oracle database, possibly because there is no permission

not_logged_on attempts to access the database without logging in to the database

zero_divide 0 as a divisor when doing arithmetic operations

Program_error An internal error occurred while the PL/SQL block was running

invalid_cursor View operation an invalid cursor

value_error An error occurred while doing data operations

The first two exceptions are the most common.

The following code shows how to handle both exceptions.

DECLARE
Name Emp.ename%type;
BEGIN
SELECT ename into name from EMP WHERE deptno=100; --there's actually no department numbered 100.
EXCEPTION--This will throw a No_data_found exception
When No_data_found Then
Dbms_output.put_line (' data not meeting the criteria ');
When Too_many_rows Then
Dbms_output.put_line (' Too much data ');
END;

Because a department with number 100 does not exist, the PL/SQL program throws a No_data_found exception when it executes to this SELECT statement.
However, if you query an existing department, multiple rows of data may be returned.

For example, if you change the condition of the WHERE clause to "deptno= 10" because the department 10 is more than one employee, then multiple rows of data will be returned,
This causes the too_many_rows exception to be thrown.

This shows that the PL/SQL program can only query one row of data through a traditional select command, and throws an exception if querying 0 or more rows of data.
If you want to process 0 or more rows of data, you need to use a cursor.
An exception Dup_val_on_index is thrown when a duplicate value is written to the primary key column of the table.

For example, the column DEPTNO in the departmental table is the primary key column, which requires that the values on this column cannot be duplicated.
If you already have department 10, and then you insert a row of data into this table, and the department number is 10, an exception Dup_val On_index is thrown.
For example:

BEGIN
INSERT into Dept VALUES (' Network ', ' Nowhere ');
EXCEPTION
When Dup_val_on_index Then
Dbms_output.put_line (' Duplicate values on the primary key column ');
END;

In the exception handling section of the PL/SQL block, the code that is booted by when is the exception handler.

Typically, there are multiple exception handlers in a PL/SQL block that are used to handle different exceptions.
However, it is generally possible to execute only one of the exception handlers, because when an exception occurs, the execution of the PL/SQL block is immediately transferred from the executable to the exception handling section, and the execution of the PL/SQL block ends when the exception is processed, and no other exception occurs.
You can write a separate exception handler for an exception, or you can write the same exception handler for multiple exceptions, and you can do the same if different exceptions occur.
This allows you to specify the names of multiple exceptions in the When clause, separated from each other by OR .

When No_data_found OR Too_many_rows Then
Dbms_output.put_line (' SELECT statement Error ');
...

PL/SQL provides two functions, Sqlcode used to return the code for the error that occurred, and SQLERRM is used to return the cause of the error.

With these two functions, you can write a generic exception handler that handles all exceptions.

For example, error codes and error messages are displayed to the user.
For example:

BEGIN
INSERT into Dept VALUES (' Network ', ' Nowhere ');
EXCEPTION
When others then
Dbms_output.put_line (' Error code: ' | | SQLCODE);
Dbms_output.put_line (' Error reason: ' | | SQLERRM);
END;

In the above example of exception handling, we only show the information that has been wrong.

If you want to record all the errors that occur, you can create a table that writes the error to this table in the Exception handling section of PL/SQL and generates the log information.
For example, create a table err_ info in the database, which is structured as follows:

Column name type is empty description
Err_time Date NO error occurred
Err_user VARCHAR (+) YES user who raised an error due to PL/SQL execution
Err_code INTEGER YES Error code
Err_message VARCHAR (+) YES error reason

This allows the exception to be written directly to the table without being displayed to the user when the exception is handled.

If you want to do the same for all exceptions, you do not need to list each exception separately in the exception handling section, as long as you use others instead.
For example:

DECLARE
Name Emp.ename%type;
Err_code integer;
Err_message varchar (100);
BEGIN
SELECT ename into name from EMP WHERE deptno=100;
EXCEPTION--This will throw a No_data_found exception
When OTHERS Then
Err_code:=sqlcode;
ERR_MESSAGE:=SQLERRM;
INSERT into Err_info VALUES (Sysdate,user, Err_code, err_message);
COMMIT;
END;

The result of this block is that the information of the exception is recorded in the table Error_info.
An important reason to throw an exception is that an error occurred while processing the number.

Statistics show that SELECT statements, DML statements, and cursor action statements are more prone to throw exceptions.
The primary purpose of writing PL/SQL blocks is to process the data, and the PL/SQL blocks are logically separate from the data, and programmers simply cannot anticipate the changes in the data.
For example, to query a department 10 of employees, programmers do not know whether there are employees in this department, one or more employees.
So when writing a program, programmers should consider a variety of possible exceptions, in the program to write the exception of the processing code, such a program can withstand a variety of errors test.

Non-pre-defined exception

There are also a class of errors that you will encounter frequently in PL/SQL.

Each error has a corresponding error code and the cause of the error, but because Oracle does not define a name for such an error, the exception cannot be handled directly.
In general, you can only view its error message if the PL/SQL block performs an error.
When you write a PL/SQL program, you should take full account of the various exceptions that may occur and handle them appropriately, so that the program is robust.
For this kind of non-predefined exception, because it is also automatically thrown, it is only necessary to define an exception, the name of the exception is associated with the wrong code, and then you can handle such an exception as a predefined exception.
The process for non-predefined exceptions is as follows.

Defining exceptions associating exceptions with error codes to handle exceptions

PL/SQL declares partial PL/SQL Exception handling section

Process for non-predefined exceptions

The definition of an exception is made in the Declarations section of the PL/SQL block , defined in the following format:

Exception name EXCEPTION

Where the exception name is the user's own definition of-a name, at this time it is just a symbol, no meaning.

This exception represents this error only when the name is associated with an error code.
The format for associating the name of the exception with the error code is:

PRAGMA Exception_init (exception name, error code)

This association is also done in the Declarations section of the PL/SQL block.

This exception's name represents this particular error, and when the PL/SQL program executes this error, the exception is automatically thrown, and then it can be processed.
For example, error code-02292 means that the association integrity is violated.

If two tables are associated with a primary key and a foreign key, deleting a row from the primary table may violate the association integrity between them.
For example, the Employee table EMP and the Department Table Dept Establish an association relationship through column DEPTNO, if you want to delete a row from the Department table dept, you must ensure that the EMP table does not have employees in the Department, otherwise it violates the association integrity between them.
The following code shows an error that occurs when trying to delete department 10 data because there is a department 10 employee in the table emp.

Delete from dept where deptno=10;

ORA-02292: Violation of the full constraint (SCOTT. FK_DEPTNO)-Child records found

If an exception is defined at this point, the exception is associated with the error code 02292, which can be processed in the exception handling section of the PL/SQL block.
The following is the code that handles this exception.

DECLARE
Reference_err EXCEPTION;
PRAGMA Exception_init (reference_err,-02292);
BEGIN
DELETE from dept WHERE deptno=10;
EXCEPTION
When Reference_err Then
Dbms_output.put_line (' The action you have performed violates the integrity of the association ');
END;

User-defined exceptions

In addition to the two types of exceptions defined by Oracle, you can customize exceptions in PL/SQL.

Programmers can define some specific states as exceptions.
Such exceptions are usually determined by programmers themselves, thrown under certain conditions, and then handled by the exception mechanism of PL/SQL.
There are two ways to handle user-defined exceptions.

The first method is to define an exception, throw it at the appropriate time, and then handle it in the exception handling section of the PL/SQL block.
User-defined exceptions are usually thrown under certain conditions, so this condition becomes the cause of the exception.
The second method is to return a custom error code and an error message to the caller.
The first method is described here first.

The definition of an exception is made in the Declarations section of the PL/SQL block, defined in the following format:

Exception name EXCEPTION

The exception name is only a symbol at this point and is only meaningful if it is thrown under certain conditions.

The command that throws the exception is raise, and the exception is thrown in the executable portion of the PL/SQL block.
The format of the Raise command is:

RAISE Exception Name

Exceptions are generally thrown under certain conditions, so the raise statement is usually followed by a conditional judgment, which associates the exception with the condition.
The exception may be thrown because of a data error or a custom condition that satisfies a custom exception in the same way that the first two exceptions are handled.
For example, write a PL/SQL program that asks for 1+2+3 + ... A value of 100.

If the result is found to exceed 1000 during the summation, an exception is thrown and the sum is stopped.
The code for this block is as follows:

DECLARE
Out_of_range EXCEPTION; --Defining exceptions
Result integer: = 0;
BEGIN
For I in 1.. Loop
Result: = result + I;
If result >
RAISE Out_of_range; --Throws an exception
END if;
END Loop;
EXCEPTION
When Out_of_range then-handling exceptions
Dbms_output. Put_Line (' Current calculation result is ' | | result| | ', already out of range ');
END;

Use the raise command to not only throw a custom exception, but also to throw a pre-defined exception and a non-predefined exception.
For example, in the example above, an exception value_error can be thrown when the result of the calculation exceeds 1000.

The modified PL/SQL block code is as follows:

DECLARE
Result integer: = 0;
BEGIN
For I in 1.. Loop
Result: = result + I;
If result >
RAISE Value_error; --throws a pre-defined exception
END if;
END Loop;
EXCEPTION
When Value_error then-handling exceptions
Dbms_output. Put_Line (' Current calculation result is ' | | result| | ', already out of range ');
END;

Now let's introduce the second method of custom exception handling.

When the execution of a PL/SQL block satisfies a certain condition, you can return an error code and an error message to the PL/SQL program.
The range of error codes is 20000 to-20999, and the code for this range is reserved by Oracle and has no meaning in itself.
If a program associates an error code with a condition, the system throws an error when the condition is met.
Of course, this is a human-made error, does not mean that the program or data is really wrong.
PL/SQL provides a procedure for returning an error code and an error message to the PL/SQL program.

This process is raise_application_error , and the procedure is called in the following format:

Raise_application_error (Error code, error message)

For example, to modify the above summation example, the PL/SQL program gets an error code-20001 and an error message when the result is greater than 1000.
The modified code is as follows:

DECLARE
Result integer: = 0;
BEGIN
For I in 1.. Loop
Result: = result + I;
If result >
Raise_application_error (-20001, ' Current calculation result is ' | | result| | ', already out of range ');
END if;
END Loop;
END;

Judging from the running results of the program, the execution of the program does occur incorrectly and returns the specified error code and error message.
At this point, the user-defined exception is similar to the non-predefined exception.

Only non-predefined exceptions are automatically thrown by the database server, and error codes and error messages are specified by the database server, and user-defined exceptions are thrown by programmers, and error codes and error messages are specified by the programmer.
When dealing with non-predefined exceptions, we specify an exception name for each error code, and then we can do exception handling based on that name.
Since user-defined exceptions can also return error codes and error messages to callers, we can also handle such exceptions in the same way.
Define an exception first, and then associate the exception with an error code.

Both steps are made in the Declarations section of the PL/SQL block.
The exception is then thrown in the executable part of the PL/SQL program according to certain conditions.

Finally, this named exception is captured and handled in the exception handling section of the PL/SQL block.
For example, to re-process the exception in the above summation example with this kind of square, the code is as follows:

DECLARE
Result integer: = 0;
Out_of_range EXCEPTION;
PRAGMA Exception_init (Out_of_range,-20001);
BEGIN
For I in 1.. Loop
Result: = result + I;
If result >
Raise_application_error (-20001, ' Current calculation result is ' | | result| | ', already out of range ');
END if;
END Loop;
EXCEPTION
When Out_of_range Then
Dbms_output.put_line (' Error code: ' | | Sqlcode);
Dbms_output.put_line (' Error message: ' | | SQLERRM);
END;

As can be seen from the PL/SQL block above, we first define an exception Out_of_range in the Declaration section, and then associate the exception with the error code-20001, once the program is in the process of running the error, is to throw an exception out_of_range.
In the executable part of the block, if the value of the variable result exceeds 1000 during the cumulative process, the error code-20001 and the corresponding error message are returned.
This allows the exception out_of_range to be captured and handled in the Exception handling section.
When dealing with user-defined exceptions, you can also use functions Sqlcode and SQLERRM, which are used to return the specified error code and error message, respectively.
As you can see from the running results of the program, the two functions do return the specified error code and error message.
Such error codes and error messages are specified in the executable section through the procedure raise application error.

Delivery of exceptions

If the PL/SQL program has an error during execution, it goes to execute the appropriate exception handler and then ends the execution of the block.
If no corresponding exception handler is defined, the PL/SQL program returns information about the error to the caller, that is, passing the exception to the caller of the program, and then ending the execution of the program.
If the program is executed in Sql*plus, then the exception is passed to the Sql*plus environment, which displays the error message on the screen.
For example, the following block throws a Too_many _rows exception when retrieving data and passes the exception to Sql*plus.

DECLARE
Name Emp.ename%type;
BEGIN
SELECT ename into name from EMP WHERE deptno=10;
EXCEPTION--This will throw a Too_many_rows exception
When No_data_found Then
Dbms_output.put_line (' data not meeting the criteria ');
END;

As can be seen from the execution results of the program, this exception is passed to the caller of the program-sql*plus because the exception too_many_rows is not handled in the program.
In a PL/SQL block, you can define subroutines in the form of procedures, functions, etc., and you can define exception handlers separately in each subroutine.
This way, when the subroutine executes an error, it goes to execute the appropriate exception handler.

Then the execution of the subroutine ends, and the PL/SQL block is then executed from the next statement at the subroutine call.
If the subroutine processes the exception that occurs, it can be assumed that the execution of the subroutine ends normally.
For example, consider this example of subroutine overloading.
Two overloaded process increase_salary are defined in this block, which is used to increase wages for employees.

The first procedure has two parameters, namely, the department number and the increased quota, which is used to increase the salary for the employees of the assigned department.
The second process has a parameter, the increased amount, that is used to raise wages for all employees.

The procedure for handling exception No_data_found is added in the first procedure, and a SELECT statement is added.
If a non-existent department is specified when the procedure is called, the No_data_found exception is thrown when the department information is queried, and the process of executing the procedure goes to the Exception handling section.

DECLARE
Procedure Increase_salary (d_no Emp.deptno%type, amount float)
Is
D_name Dept.dname%type;
BEGIN
SELECT dname into D_name from dept-in fact, department 100 does not exist
WHERE Deptno=d_no;
UPDATE EMP Set Sal=sal+amount WHERE deptno=d_no;
EXCEPTION
When No_data_found Then
Dbms_output.put_line (' This department does not exist ');
END;
Procedure Increase_salary (amount float)
Is
BEGIN
UPDATE EMP Set Sal=sal+amount;
END;
BEGIN
Increase_salary (100,100.50); --Call the first increase_salary procedure
Increase_salary (200); --Call a second increase_salary procedure
END;

As can be seen from the execution of the block, when the first overloaded procedure is called, a No_data_found exception is thrown because a nonexistent department number is passed.
This process ends after handling an exception, and the PL/SQL block then executes the second invocation statement, invoking the second procedure.
The first procedure, because it handles the exception that occurs, can be thought of as a normal end, and it does not affect the execution of other statements in the entire program of a block.
The handling of exceptions should be guided by the principle of "non-proliferation".

Errors that occur in the subroutine should be handled in the subroutine and not spread to the main program.
Similarly, errors that occur in the executable portion of a PL/SQL block should be handled in the block and not spread to the sql*plus or application that called the block.
What happens if the error is not handled in the subroutine?

Consider the example above and cancel the exception handling part of the first increase_salary process.
To facilitate testing, an output statement was added to the middle of the two call statements.
The modified code is as follows:

DECLARE
Procedure Increase_salary (d_no Emp.deptno%type, amount float)
Is
D_name Dept.dname%type;
BEGIN
SELECT dname into D_name from dept-in fact, department 100 does not exist
WHERE Deptno=d_no;
UPDATE EMP Set Sal=sal+amount WHERE deptno=d_no;
END;
Procedure Increase_salary (amount float)
Is
BEGIN
UPDATE EMP Set Sal=sal+amount;
END;
BEGIN
Increase_salary (100,100.50); --Call the first increase_salary procedure
Dbms_output.put_line (' The first process is over ');
Increase_salary (200); --Call a second increase_salary procedure
END;

An exception was thrown when the first increase_salary procedure was called because a non-existent department number was specified
No_data_found. The exception is not handled in the subroutine, so the process is not finished properly. From the execution node of the program
As you can see, the output we specify does not produce, and it can be concluded that all statements below the first invocation statement have no
Be executed.
If the subroutine does not handle the error that occurred, then the exception is passed to its caller, the PL/SQL main program,
Errors can also be generated in the main program. So the main program will stop executing where the subroutine is called and handle the
Often. But because the main program also does not define an exception handler, this exception is passed to the caller of the block
Sql*plus, which displays an error message on the screen.
The exception that is passed from the subroutine to the PL/SQL main program, can it be handled in the main program? The answer is yes.
Of If an exception handler is defined in the main program, then the exception is passed from the subroutine to the main program, as if it were in the main
Process in the same way as an exception generated in the program. This allows us to write a uniform exception handler in the main program, regardless of the different
is often thrown in the main program or thrown in a subroutine, you can get the same treatment. This makes the land although it is possible to
But it does not conform to the "non-proliferation" principle. If the program has an exception, it is not easy to determine where the error occurred
The exception generated by different parts of the program can be handled separately. For example, the first one in the above example
The exception handling for the increase_salary process is placed in the PL/SQL block. If an exception is thrown when the procedure is called, you can
Acting

PL/SQL data development that's something.