By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,227 Members | 1,025 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,227 IT Pros & Developers. It's quick & easy.

UDF with exception handler possible ?

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.