DB2 conditional processors play an irreplaceable role in stored procedures. In DB2, SQL stored procedures can use the DB2 conditional processor Condition Handler) to handle SQL errors in the stored procedure running SQLERROR), SQL warning SQLWARNING) and no data NOT FOUND) you can use three condition processors, including EXIT, CONTINUE), and UNDO, as well as the trigger conditions you have defined.
If SQL error, SQLWARNING, and not found occur during SQL stored procedures, the SQL stored proced
Document directory
Criteria 2.10. Conditions and handling procedures
Criteria 2.10. Conditions and handling procedures
Limit 2.10.1. Declare Condition
Listen 2.10.2. Declare Handler
Specific conditions must be specific. These conditions can be associated with errors and general process control in subprograms.Limit 2.10.1. Declare Condition
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
|
handle exceptions or directly return them to the caller. Here, condition is translated as an exception for readers to understand. In fact, some exceptions (condition) are not caused by errors, which will be described in detail below.
When the sqlstate returned by the statement in the Stored Procedure exceeds 00000, it indicates that an exception (condition) is generated in the stored procedure ), it indicates that an error occurs, data is not found
Error Handler ExamplesHere are some examples of handler declarations:If any error condition arises (other than a not FOUND), continue execution after setting L_error=1:DECLARE CONTINUE HANDLER for SQLEXCEPTIONSET l_error=1;If any error condition arises ("other than a not FOUND"), exit the current block or stored program after issuing a ROLLBACK Statement and issuing an error message:DECLARE EXIT HANDLER for SQLEXCEPTIONBEGINROLLBACK;SELECT ' Error occurred–terminating ';End;If MySQL Error 1062 (
Oracle error ldquo; ORA-19809: limitexceededforrecoveryfilesrdquo; Solution
Oracle error ldquo; ORA-19809: limit exceeded for recovery filesrdquo; Solution
When running the archive command, the database reports the following error:
The reason for this error is that the size of the recovery file exceeds the limit. There are two solutions: one is to delete the old file and the other is to expand the space limit.1. Delete the old file
This method is used to access RMAN and delete part of t
);
2) two types (before/after );
3) two levels (row-level/statement-level );
So a total of 3X2X2 = 12
4. Trigger restrictions
A: 1) transaction control statements should not be used;
2) No LONG or long raw variables can be declared;
3) tables that can be accessed are limited.
5. Tables accessible to the trigger body
A: 1) You cannot read or modify any change table (the table being modified by the DML Statement );
2) You cannot read or modify the primary keys, uniqu
Error | Address Today field staff report in imp a DMP file times exceeds the maximum number of cursors for errors, ora-01000:maximum open cursors exceeded.
Asked about the basic situation, on the Metalink check, IMP did not mention any of the ORA-01000 error, this is a little puzzled. At this time, through the mail received DMP file, their own simulation test, found that the cause of the mistake is not ORA-
Problems encountered in the project
I. Spring transaction problems
1. Description: a in service1 calls B, B calls c in service2, and c calls d in service3
Expectation: When d throws an exception (the SQL exception is thrown in my real project), d and c will be rolled back, while a and B will not be rolled back.
Test: Considering the self-tuning of Spring transactions and the spring transaction configuration under the cglib dynamic proxy. Added
Demo:
Custom exception:Public class MyException ext
, @ pcount );
This stored procedure is to grant the value to the variable by select;
The table does not have the depid = 101 and name = 'Chen 'record.
III. conditions
The function of a condition is generally used to process a specified condition. for example, what should we do if we encounter a primary key repeat error.
Define conditions
Defining a condition is to define the name of an error state or SQL state in advance, and then you can reference the condition name for conditional processin
;SQL1035NThedatabaseiscurrentlyinuse.SQLSTATE=57019
=>db2backupdatabasedbnameto/xx/xxcompress
> SQL1035N The database is currently in use. SQLSTATE = 57019 solution:
SQL code
=>db2stopforce
=>db2start
=>db2stopforce
=>db2start
5. When creating a tablespace, the system reports the error 54047. For example:
SQL code
=>createregulartablespacespace1pagesize4kmanagedbydatabaseusing(device'/dev/dd'25000000)prefetchsizeautoma
--The article content through the network search combination,MySQL exception, you can customize the exception, and then apply. You can also use the system default exception to capture the app.First, the exception definition:DECLARE condition_name condition for [condition_type];The condition_name parameter represents the name of the exception;condition_type Parameter exception typeCondition_type consists of SQLState [VALUE] Sqlstate_value|mysql_error_c
. Java is run twice, the following error message is returned:
SQLException: There is already an object named 'COFFEES'in the database.Severity 16, State 1, Line 1
This example shows how to output the Message parts of the sqlexception object. In most cases, this is enough to indicate an error.
However, this object actually has three parts. To fully understand the cause of the error, You can output all of them. The following code snippet shows the catch blocks completed in two ways. T
variable;
There is no record of depid=101 and Name= ' Chen ' in the table.
Third, the conditions
The role of the condition is generally used in the processing of the specified conditions, such as we encounter the primary key repeated error after how to deal with.
Define criteria
Defining a condition is to define a certain error state or the name of the SQL state beforehand, and then you can refer to the condition name to do the conditional processing, the definition condition generally u
automatically close after the declared Begin...end statement block executes;
The cursor declare is used to declare a data collection that corresponds to a cursor and a specified cursor, typically a data collection that is a SELECT statement. DECLARE cursor_name CURSOR FOR select_statement Cursor fetch is used to get the next row of data for the specified data set of the cursor and assign each field value to the subsequent variable FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name]
Java. SQL. SQLException: ORA-00604: recursive SQL Level 1 error, recursive SQL Level 1 error
The following error is reported in the background:
Caused by: java. SQL. SQLException: ORA-00604: recursive SQL Level 1 error
ORA-01000: exceeds the maximum number of opened cursors
ORA-00604: recursive SQL Level 1 error
ORA-01000: exceeds the maximum number of opened cursors
ORA-
PC
Update brandproductkey as bpk,
(Select bp. brandproductid, bp. brandproductenname, bp. brandid
From brandproduct as bp
Inner join (select brandid, brandproductid from brandproductdriverrelation group by brandid, brandproductid) as bpr
On bp. brandid = bpr. brandid and bp. brandproductid = bpr. brandproductid and bp. brandid = 36
) As bpp
Set bpk. brandproductid = bpp. brandproductid
Where bpk. brandproductid = 0
-- And bpk. computertype = 2 -- 0
And bpk. brandid = bpp. brandid
And bpk. brand
above is our program. The first statement declare exit handler is used to handle exceptions. It means that if error 1215 occurs, this program inserts a row in the error record table. EXIT means to EXIT the compound statement after the action is successfully submitted.5. Sample Problem: Log Of ErrorsCALL p22 (5 )//Calling this stored procedure will fail, which is normal because the value 5 does not appear in the primary key table. However, no error message is returned because the error processin
handler_type handler for condition_value [,...] sp_statement handler_type: CONTINUE | EXIT condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | not found | SQLEXCEPTION | mysql_error_code Handlers type: 1, EXIT: exit the current code block when an error occurs (may be a subcode block or main code block) 2. CONTINUE: CONTINUE to execute the subsequent code condition_value: condition_value when an error is sent. standard
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.