How to CREATE and DROP User-defined Function in DB2

UDF also called a user-defined function. Here are the SQL queries to create or Drop a function in DB2.

3 rules to create UDF

The output of UDF will be of three kinds.

1. Scalar – It means – returns a single value.
2. Single Row – returns a single row.
3. Return Table – returns the whole Table as a result of function defined by the user (UDF)

Example: How to create an UDF

create function emp_current_votes
(employee_id char(6))
returns integer
specific emp_curr_votes_int
not deterministic
language sql
return select count(*)from emp_votes where empno = employee_id

In the above example, when you supply employee id, it returns the count of votes. This is the best example of Scalar value.

Download DB2 Complex Interview Questions.

How to Call UDF from the Called Program.

select firstnme, lastname, emp_current_votes(empno) 
from 
employee;

Example: How to Create Row-type UDF.

create function emp_vote_results()
returns table(empno char(6), votecount integer)
specific emp_results
not deterministic
language sql
called on null input
  return select empno, count(*) votes
  from emp_votes
  group by empno

In the above example, in return you are getting more than one value. So you can call this as row-type UDF.

Example: How to Create Table type UDF.

In fact, after creating either scalar or row type UDFs, you need to call UDF from the called program as a Table.

Model:1
***
select * 
from table(emp_vote_results()) as t;  
Model:2
***
select * 
from table(emp_vote_results()) as t where t.empno = '555666';
Model:3
***
select emp_current_votes(t.empno) 
from table(emp_vote_results()) as t;

Example: How to Drop an UDF

drop function emp_vote_results;

With drop statement, your UDF will be no longer available.

Related Posts