Functions are a great and fun way to implement any functionality in our code. Not only are they easy to code and include almost anywhere we need them, their reusability and the large acceptance of return datatypes make them a coder's superpower.

One of the benefits of user defined functions is that they can be included in sql queries just like any other oracle predefined functions.

The only catch is that the function's parameters should all be in IN mode and the return datatype should be compatible with sql environment. (Here, I would like to add that a user defined function can return virtually any kind of data except exceptions.)

User defined functions could be used in the select list, where condition, connect by, start with, order by and group by clauses, the values clause of insert, the set clause of update etc.(YOU NAME IT!!!)

Of course, you must have execute priveledges on the function before using it in a query.

Following are some examples:

To find out which employee contributed 1000 bucks towards charity:

DECLARE
ename employees.empname%TYPE;
BEGIN
SELECT empname INTO ename
FROM employees
WHERE empid = emp_contri_pkg.emp_donated(1000);
...

Calling a function inside a view declaration(the function returns avg salary against the deptid passed):

CREATE VIEW above_dept_avg
AS
SELECT empid, empname, deptid from employees where salary > dept_avg_sal(1001);

Function as column of a table:

CREATE TABLE box
(
length number(6),
breadth number(6),
height number(6),
volume AS (dimensions_pkg.compute_vol(length, breadth, height))
)

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com