Special September 2017 - Exception handling in stored procedures |
---|
Category : User |
Level : Advanced |
Back to Overview |
Database stored procedures are used to encapsulate database oriented algorithms and to store it into the database. Often this results in more efficient and smaller code instead of an implementation in the upper application. Also I like to use stored procedures to keep the application database independent. All database specific operations can be hidden in the stored procedure.
To get the full power of stored procedures, it is important to understand the concept of exception handling. This also helps to keep the above described advantages. To get in touch, we start with a very simple procedure, which just throws an expcetion.
drop if exists procedure excepCheck001; @ create procedure excepCheck001 ( ) begin throw 'Ouups, an exception ..'; end; @ |
As expected, calling this procedure results in an exception
CGCLT > call excepCheck001(); Error : Procedure exception : 'Ouups, an exception ..' Query failed |
Now we modify the implementation in a way, that we add a return value to the procedure and catch the user thrown exception. In case of an exception, a value of 1 is returned by the procedure now.
drop if exists procedure excepCheck002; @ create procedure excepCheck002 ( ) return int begin throw 'Ouups, an exception ..'; return 0; exception when other then return 1; end; @ |
After the procedure has been loaded, it can be checked by executing it with the following commands
CGCLT > :r = call excepCheck002(); Procedure executed ok ( 0.002 s ) CGCLT > print :r; 1 ok ( 0.000 s ) |
If you want to provide the exception message to the calling application, this can be done by adding an out parameter to the procedure. If an exception is thrown, the exception message is stored in the special variable excep_info. In the exception block, this value is assigned to the out parameter
drop if exists procedure excepCheck003; @ create procedure excepCheck003 ( r out string(20) ) return int begin throw 'Ouups, an exception ..'; return 0; exception when other then :r = :excep_info; return 1; end; @ |
And here is, how to check it
CGCLT > :r = call excepCheck003(:s); Procedure executed ok ( 0.002 s ) CGCLT > print :r; 1 ok ( 0.000 s ) CGCLT > print :s; 'Ouups, an exception ..' ok ( 0.000 s ) |
Exception also can be nested .....
drop if exists procedure excepCheck004; @ create procedure excepCheck004 ( i in int, r out string(20) ) return int begin if :i = 1 then begin throw 'A nested exception ..'; exception when other then :r = 'Exception for 1'; end; else throw 'Ouups, an exception in the else path ..'; end; return 0; exception when other then :r = :excep_info; return 1; end; @ :r = call excepCheck004(1, :s); print :r; print :s; :r = call excepCheck004(2, :s); print :r; print :s; |
And here ist the expected output of the batch
$ ./chkclt --batchfile=p4.sql Procedure excepCheck004 dropped ok ( 0.000 s ) Procedure excepCheck004 created ok ( 0.000 s ) Procedure executed ok ( 0.003 s ) 0 ok ( 0.000 s ) Exception for 1 ok ( 0.000 s ) Procedure executed ok ( 0.000 s ) 1 ok ( 0.000 s ) 'Ouups, an exception in the else path ..' ok ( 0.000 s ) |