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.
- Scalar – It means – returns a single value.
- Single Row – returns a single row.
- Return Table – returns the whole Table as a result of function defined by the user (UDF)
Scalar function returns SINGLE value.
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)
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 can return multiple values.
- A Function returns Single 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 Drop an UDF
drop function emp_vote_results;
With drop statement, your UDF will be no longer available.