Creating a function is fully within the user’s control—and with User-Defined Functions (UDFs), the process becomes much simpler. UDFs can be written in various programming languages such as C, PL/I, COBOL, and Assembler, allowing users to extend SQL’s capabilities with custom logic.
User-defined functions
Table of contents
What is UDF stands for?
SQL allows the creation of user-defined functions. This capability extends the database’s functionality. Users can create custom functions in SQL, known as User-Defined Functions (UDFs), and use them just like built-in functions. These functions can be seamlessly integrated into SELECT, INSERT, UPDATE, and DELETE statements, as well as within other functions and stored procedures.
SQL supports several types of user-defined functions:
- Scalar Functions: Return a single value. Ideal for performing calculations or manipulating individual data points within a SQL query.
- Table-Valued Functions: Return an entire table as output. Useful for returning query results or performing complex data transformations.
- Aggregate Functions: Operate on a set of values and return a single aggregated result—commonly used for operations like
SUM,AVG, orCOUNTover groups of rows.
User-defined functions add significant flexibility and reusability to your SQL code, enabling custom logic tailored to your specific data processing needs. However, it’s essential to validate input parameters and implement proper security measures to prevent issues such as SQL injection attacks.
UDF Code Examples
Scalar Function
CREATE FUNCTION getFullName(firstName VARCHAR(50), lastName VARCHAR(50))
RETURNS VARCHAR(100)
BEGIN
DECLARE fullName VARCHAR(100);
SET fullName = CONCAT(firstName, ' ', lastName);
RETURN fullName;
END;
This function takes a first name and a last name as parameters and returns the full name by concatenating them.
Table-Valued Function
CREATE FUNCTION getEmployeesByDepartment(departmentId INT)
RETURNS TABLE (
employeeId INT,
firstName VARCHAR(50),
lastName VARCHAR(50),
departmentId INT
)
BEGIN
RETURN (SELECT * FROM employees WHERE departmentId = departmentId);
END;
This function takes a department ID as a parameter and returns a table of employees belonging to that department.
Aggregate Function
CREATE FUNCTION getAvgSalaryByDepartment(departmentId INT)
RETURNS DECIMAL(10, 2)
BEGIN
DECLARE avgSalary DECIMAL(10, 2);
SELECT AVG(salary) INTO avgSalary FROM employees WHERE departmentId = departmentId;
RETURN avgSalary;
END;
This function calculates and returns the average salary for employees in a specific department.
Conclusion
These examples show the flexibility and power of user-defined functions in SQL. You can create functions tailored to your specific needs, allowing for custom calculations, data manipulation, and more.
Srinimf 