At times, things may happen within stored procedures that are out of the ordinary. The status variables can be a major help in determining what happened. However, at times, the same error handling logic might be needed multiple times within the same procedure. When this is the case, a Handler routine may be written to "handle" the condition. This is an alternative to coding the same IF statements multiple times.
DECLARE HANDLER Statement
The DECLARE is also used to establish a Handler as well as a variable. A Handler is a section of logic that executes automatically based on error conditions that may occur during the execution of a stored procedure.
This is important because the procedure is executing without interaction from the user. It does not return until it is finished. So, rather than having an error occur and returning a code to the user to manually determine the next step, the proper action is coded as part of the procedure. It is programmed to "handle" the error condition and perform the necessary processing. Therefore, it is easier for the user and provides less opportunity for human errors and inconsistent results.
The syntax for DECLARE HANDLER:
The Handler type is one of these:
The Condition Value list is a comparison using one or more of the status variables.
The Handler action is the user logic written using SPL and DML necessary to handle the condition.
The next procedure contains two handler routines, the first one is a CONTINUE type and the second is an EXIT:
Every time an SQL error occurs that is a 41020 or 52100, a row is inserted into My_err_tbl with the error code and "Error Handled" and continues to execute. However, if any other error occurs, the SQLEXCEPTION picks it up and inserts the error code and "Unknown Error" into My_err_tbl. But, since it is an EXIT, the procedure ends. Granted, inserting a row is not much of a way to handle an error. Therefore, something more substantial is probably needed for your Handler routines. This is just a quick example of the technique involved in writing handler routines.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.