Lesson 4 : Function

A function in SQL looks very similar to procedure, only unlike procedure it needs to return a value as output. A function could be a sub-routine in a query or a procedure for computations and other task that needs to be automated inside the database management system.

With the table above we could generate a pay slip for each Employee with net amount 59,550.00 and 39,600.00 respectively as both employees has deductions for SSS and GSIS.

By means of creating a pay slip, we could do that by simply creating a sub-query like this:

SELECT e.*, t1.netamount FROM Employee AS e INNER JOIN (SELECT Amount-(SELECT SUM(Amount) FROM Deductions WHERE EmployeeID='16-M89031')AS netamount,EmployeeID FROM salary WHERE EmployeeID='16-M89031') AS t1 ON t1.EmployeeID = e.EmployeeID

And we can even integrate it into creating a payroll for the whole company but what if there is a requirement on your program which will only generate the NetAmount for a specific employee, it will only return the value 59,550.00 or 39,600.00

Then maybe you need to prepare a single function for that option.

Syntax

DELIMITER $$
CREATE FUNCTION NAME() RETURNS DOUBLE
BEGIN
END$$
DELIMITER;

Example

DELIMITER $$
CREATE FUNCTION getNetSalary() RETURNS DOUBLE
BEGIN
RETURN (SELECT Amount-(SELECT SUM(Amount) FROM Deductions WHERE EmployeeID='16-M89031')AS netamount FROM salary WHERE EmployeeID='16-M89031');
END$$
DELIMITER;

The example above could be called on your program using this command

Select getNetSalary();

But it is a custom query that will only return the net salary of employee 16-M89031. To make if more useful, reusable for other employee we will add a parameter on that function to make it dynamic, see example below:

Example

DELIMITER $$
CREATE FUNCTION getNetSalary(EID VARCHAR(20)) RETURNS DOUBLE
BEGIN
RETURN (SELECT Amount-(SELECT SUM(Amount) FROM Deductions WHERE
EmployeeID=EID)AS netamount FROM salary WHERE EmployeeID=EID);
END$$
DELIMITER;

Activity

With the example table above create a simple function which will return the total NETINCOME of each employee for the whole year.

Download PDF File