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.
- 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 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)
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.