Handling exception in HANA procedures
Before we start this discussion I would like to highlight the basic of exception what is an exception in terms of database procedure and how we handle this exceptions.
In the time of procedure execution if any issue occurs in specified operations like insert, update etc. with the input data then the exception occurred and we need to use some exception handler to handle this exception. This is call implicit exception.
In HANA we will use declare EXIT HANDLER to handle those exception.
There is another way you can create an exception using fulfill of certain declared condition CONDITION Using SIGNAL and RESIGNAL. This exception explicitly created.
1. Lets first discuss about the EXIT HANDLER in HANA –
To demonstrate this first create an table EMPLOYEE with the following code –
Now create a procedure PALLAB_DEMO_EXITHNDLER with declaring EXIT HANDLER which will insert Two data inside the table –
When you run first time with the following value no exception occurs there is no record with same employee id –
But if you try to execute the procedure with same EMP_ID the SQL exception will occur and EXCEPTION_HANDLER will execute the message you set for the exception-
This is all about handling exception which cooccurs in procedures .
In the next session we will disuses how we can create and exception based on a CONDITION and handle it using SIGNAL and RESIGNAL.