User-defined Functions

In the world of SQL, functions are essential tools that allow us to package specific actions or calculations, apply conditional rules, and get results based on predefined conditions. SQL functions provide an organized approach to breaking down and reusing code. In this topic, we are going to explore how the instrument of functions works and how it can be applied in real-world cases.

Understanding SQL functions

SQL functions are procedures that accept parameters, perform calculations or operations, and return a single value. They serve as a convenient way to package logic, making it modular, reusable, and simpler to handle. SQL functions can be classified into two primary types:

1. Pre-defined functions: These are built-in functions provided by the database management system (DBMS). Examples include mathematical functions (e.g., SUM, AVG), date and time functions (e.g., DATE, NOW), and string manipulation functions (e.g., CONCAT, UPPER).

2. User-defined functions: These functions are created by users to meet specific requirements not covered by pre-defined functions. User-defined functions can be written in SQL or other programming languages supported by the DBMS.

In this topic, we are going to focus on user-defined functions in order to understand how they work.

User-defined functions syntax

If we want to create our custom SQL function, we will use the following pattern:

CREATE FUNCTION function_name ([parameter1 datatype, parameter2 datatype, ...])
RETURNS return_datatype
BEGIN
    DECLARE variable_name datatype; -- Declare local variables
    SET variable_name = initial_value; -- Set initial values for variables

    -- Function logic resides here, utilizing declared variables
END;

• function_name: The name of the function you're creating.
• parameter1, parameter2, ...: Input parameters that the function accepts.
• return_datatype: The data type of the value the function will return.
• BEGIN and END: Delimit the function's body, where you write the logic.
• DECLARE variable_name datatype;: This is used to declare local variables within the function. Local variables are placeholders used to store and manipulate data within the function's logic.
• SET variable_name = initial_value;: This statement is used to initialize the declared variables with an initial value. These variables can then be used for calculations, conditional statements, or any other operations within the function's logic.

While creating a user-defined function, we should keep in mind one important thing. The typical delimiter in SQL statements is the semicolon (;). However, when you create more complex database objects like stored procedures, functions, or triggers, they can contain multiple SQL statements. In such cases, using the standard semicolon as the delimiter between statements can cause conflicts.

To resolve this, we can change the delimiter temporarily using the DELIMITER command. After changing the delimiter, you can define your custom delimiter, such as // or $$, and then write your multi-statement SQL code. When you're done, you change the delimiter back to the standard semicolon.

Let's see the code below:

DELIMITER //
CREATE FUNCTION my_function()
RETURNS return_datatype
BEGIN
  -- SQL statements here
END //
DELIMITER ;

In the above example, we temporarily changed the delimiter to // before creating the function and then changed it back to the semicolon at the end.

Practical example

Let's explore a real-world use case for SQL functions. Imagine, that we want to create a function, that calculates sales tax based on a given purchase amount. Tax rates will differ depending on the purchase amount. The code will look as follows:

DELIMITER //

CREATE FUNCTION CalculateSalesTax(purchase_amount DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE tax_rate DECIMAL(4, 2);
    DECLARE sales_tax DECIMAL(10, 2);
    
    -- Determine the tax rate based on the purchase amount
    IF purchase_amount <= 100.00 THEN
        SET tax_rate = 0.05; -- 5% sales tax for purchases up to $100
    ELSE
        SET tax_rate = 0.08; -- 8% sales tax for purchases over $100
    END IF;
    
    SET sales_tax = purchase_amount * tax_rate;
    
    RETURN sales_tax;
END;
//
DELIMITER ;

• We start with the input parameter, purchase_amount, which represents the total purchase amount.
• We declare two variables, tax_rate and sales_tax, to store the tax rate and the calculated sales tax, respectively.

Inside the function body:

• We use an IF statement to determine the appropriate tax rate based on the purchase_amount. If the purchase amount is less than or equal to $100.00, we set a tax rate of 5%; otherwise, we set a tax rate of 8%.

• We then calculate the sales tax by multiplying the purchase_amount by the determined tax_rate.

• Finally, we use the RETURN statement to return the calculated sales_tax.

The CALL statement

So after we created a user-defined function, how to make it work? Here comes the CALL statement, which is used to invoke a user-defined function in SQL. It allows you to pass arguments to the function and retrieve its return value.

Here is how we are going to call the function we created above:

CALL CalculateSalesTax(500.00);

1. We use the CALL statement to invoke the CalculateSalesTax function with a purchase amount of 500.00.

2. We have two options to capture the result:

   • Option 1: Declare a variable (@sales_tax) and use the INTO clause to store the result in the variable. Then, we can select and display the value stored in the variable.
   • Option 2: Use a SELECT statement directly to call the function and display the result as a column named SalesTax.

-- Option 1: Capture the result in a variable
DECLARE @sales_tax DECIMAL(10, 2);
CALL CalculateSalesTax(500.00) INTO @sales_tax;
SELECT @sales_tax AS SalesTax;

-- Option 2: Use a SELECT statement directly
SELECT CalculateSalesTax(500.00) AS SalesTax;

Conclusion

In this topic, we went over SQL functions – quite useful tools for data management. We've explored their types, syntax, real-world applications, and how to invoke them. With the help of SQL functions, we can encapsulate actions, apply conditions, and deliver results, enhancing code organization and reusability. Now it's high time to practice what we've learned, in order to become an SQL function expert.