SQL Functions

In the previous lesson, we learned about Stored Procedures, which execute blocks of SQL logic.

In this lesson, we will explore SQL Functions, which are designed to return a value and can be used directly inside SQL queries.

What is an SQL Function?

An SQL function is a reusable block of logic that performs a calculation or operation and returns a single value.

Functions are commonly used in:

• SELECT statements
• WHERE clauses
• ORDER BY expressions

Functions vs Stored Procedures

Built-in SQL Functions

SQL provides many built-in functions. Some common categories include:

• String functions
• Numeric functions
• Date & time functions
• Aggregate functions

Common String Functions

SELECT UPPER('sql');
SELECT LOWER('SQL');
SELECT LENGTH('Dataplexa');
SELECT CONCAT('Data', ' ', 'Plexa');
  

Numeric Functions

SELECT ROUND(123.456, 2);
SELECT CEILING(4.2);
SELECT FLOOR(4.8);
SELECT ABS(-10);
  

Date & Time Functions

SELECT NOW();
SELECT CURDATE();
SELECT YEAR(NOW());
SELECT DATEDIFF('2025-12-31', '2025-01-01');
  

Using Functions in Queries

Example: Show employee names in uppercase with yearly salary.

SELECT UPPER(name) AS employee_name,
       salary * 12 AS yearly_salary
FROM employees;
  

User-Defined Functions (UDF)

In addition to built-in functions, you can create your own user-defined functions.

UDFs allow you to encapsulate logic and reuse it across queries.

Creating a User-Defined Function

Example: Calculate yearly salary.

DELIMITER $$

CREATE FUNCTION calculate_yearly_salary(monthly_salary INT)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN monthly_salary * 12;
END $$

DELIMITER ;
  

Using a User-Defined Function

SELECT name,
       calculate_yearly_salary(salary) AS yearly_salary
FROM employees;
  

The function behaves like a built-in SQL function.

DETERMINISTIC vs NON-DETERMINISTIC

When creating functions, MySQL requires declaring determinism.

• DETERMINISTIC – Same input → same output
• NOT DETERMINISTIC – Output may change

Example:

• Salary calculation → Deterministic
• NOW() → Not deterministic

Restrictions on SQL Functions

SQL functions:

• Cannot modify data (INSERT/UPDATE/DELETE)
• Cannot use transactions
• Must return a value

These restrictions ensure predictable behavior.

Dropping a Function

DROP FUNCTION IF EXISTS calculate_yearly_salary;
  

Common Beginner Mistakes

• Using procedures instead of functions
• Forgetting RETURN statement
• Not declaring determinism
• Trying to modify data inside functions

When Should You Use Functions?

Use SQL functions when:

• You need reusable calculations
• You want cleaner SELECT queries
• You need consistent logic across queries

What’s Next?

In the next lesson, we will explore Triggers, which automatically execute logic when data changes.