UDF with exception handler possible ?

N

Hi all,
I am wondering if there is a way to
create a UDF with exception handler.
I've been trying to find an example in the
doc and online without any lucks. What
I found is that I will need to create an
SPL with an OUT parameter and CALL
it. But what I need is something that acts
like a function so that I can use it in the
SELECT statement etc. Here is an example.
------------------
CREATE PROCEDURE db2.get_type_id (v_type CHAR(2),
OUT RETURN_VAL INTEGER)
LANGUAGE SQL
BEGIN
DECLARE v_cat_type INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,

SQLWARNING,
NOT
FOUND
BEGIN
SET v_cat_type = 7;
END;
IF v_type <> '' THEN
SET v_cat_type = DOUBLE(v_type);
ELSE
SET v_cat_type = 7;
END IF;
SET RETURN_VAL = v_cat_type;
RETURN 0;
END
--------------------
It would be great if I could create the above SPL as a
FUNCTION instead (with HANDLER). What I would like to
be able to do is being able to :

SELECT
col_id,
db2.get_type_id (col_type_id)
FROM
db2.table_name;

Thanks for any helps/inputs anyone can give!!!

N.

Nov 12 '05 #1

Serge Rielau

N wrote:

db2 -td%

CREATE PROCEDURE db2.get_type_id (v_type CHAR(2),
OUT RETURN_VAL INTEGER)
LANGUAGE SQL CONTAINS SQL BEGIN
DECLARE v_cat_type INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,

SQLWARNING,
NOT FOUND
BEGIN
SET v_cat_type = 7;
END;
IF v_type <> '' THEN
SET v_cat_type = DOUBLE(v_type);
ELSE
SET v_cat_type = 7;
END IF;
SET RETURN_VAL = v_cat_type;
RETURN 0;
END %

CREATE FUNCTION db2.get_type_id (v_type CHAR(2)
RETURNS INTEGER
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE RETURN_VAL INTEGER;
CALL db2.get_type_id (v_type, RETURN_VAL);
RETURN RETURN_VAL;
END
% SELECT
col_id,
db2.get_type_id (col_type_id)
FROM
db2.table_name

%

Cheers
Serge

Nov 12 '05 #2

N

Thank you Serge,
Is this the only way out ? So I have to have an SPL
for every functions I have ? We have well over 300
functions....

Thank you!

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2t*************@uni-berlin.de...

N wrote:

db2 -td%

CREATE PROCEDURE db2.get_type_id (v_type CHAR(2),
OUT RETURN_VAL INTEGER)
LANGUAGE SQL

CONTAINS SQL

BEGIN
DECLARE v_cat_type INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,

SQLWARNING,
NOT FOUND
BEGIN
SET v_cat_type = 7;
END;
IF v_type <> '' THEN
SET v_cat_type = DOUBLE(v_type);
ELSE
SET v_cat_type = 7;
END IF;
SET RETURN_VAL = v_cat_type;
RETURN 0;
END

%

CREATE FUNCTION db2.get_type_id (v_type CHAR(2)
RETURNS INTEGER
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE RETURN_VAL INTEGER;
CALL db2.get_type_id (v_type, RETURN_VAL);
RETURN RETURN_VAL;
END
%

SELECT
col_id,
db2.get_type_id (col_type_id)
FROM
db2.table_name

%

Cheers
Serge

Nov 12 '05 #3

Serge Rielau

Are all these 300 functions so complex that they need condition handlers?
SQL Functions are supposed to be simple since they are meant to execute
very often (e.g. in a WHERE clause)....

As soon as you use a feature that is not supported in inline SQL PL you
need to call out to a stored procedure.
In some future the goal is to let the user choose between inline SQL PL
and regular SQL PL in the CREATE FUNCTION statement.
But we are not quite there yet ...

Cheers
Serge

Nov 12 '05 #4