DB2: User-defined Function Syntax to Create, Drop

UDF also called a user-defined function. In this post, I want to share with you an interesting UDF example and how to create and delete it. Here delete means- how to drop the UDF.

Rules of UDF.

UDF will deliver or the output of it will be three kinds of result.

  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)

Scalar function means, it returns a SINGLE value.

Example: How to Create Scalar 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.

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.

SQL Procedure Vs UDF

A Procedure returns multiple values.

A Function returns one or One Set of Values – One Time.

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 Delete (Drop) a UDF.

drop function emp_vote_results;

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

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.