Functions and Mysql Stored Procedures

Functions in MySQL are routines that accept parameters, perform an action or calculation, and return the result of that action as a value. Depending on the Function, the return value can be either a single value or a result set. There are many MySQL functions as we will discuss below.

On the other hand, MySQL procedure is a set of Structured Query Language (SQL) statements that are saved in a relational database management system (RDBMS) and can be reused by multiple programs. When you don't want to repeat writing a query that will execute the same, you write one then save it and call it whenever you want to use it again.

Numeric Functions

Numeric functions are used to perform calculations and return a single result or a set of results.

Some common numeric functions include aggregate functions such as SUM, COUNT, AVG, and MODE. Additionally, functions like ROUND can be used to return a number with a specified number of decimal places.

Using our movies database, let’s calculate the average revenue per industry (i.e. Bollywood and Hollywood) using the AVG function.

USE moviesdb;

SELECT 
    industry,
    AVG(revenue) AS average_revenue
FROM 
    movies
INNER JOIN 
    financials 
ON 
    movies.movie_id = financials.movie_id
GROUP BY 
    industry;

In the above query, we selected industry and revenue as average, inner-joined them since they are from different tables then used group by clause to section them per industry.

String functions

String Functions in SQL are built-in functions that allow users to manipulate character data in various ways. These functions can perform tasks such as formatting text, extracting substrings, and searching for specific patterns within a string.

Some examples of string functions include; upper (converts string to uppercase), lower, concat(adds two strings together), Insert(adds a string of specified characters within a string), etc. Let's update the names of actors in the movies database actors table to uppercase.

UPDATE actors
SET name= UPPER(name)

Date Functions

Date functions are functions that help to format dates and carry out date-related calculations on your data. Some examples of date functions include checking the current date (CURRENT DATE), calculating date difference (DATEDIFF), formatting a date (DATE FORMAT), etc. Let’s call the current date

SELECT current_date();

Comparison Functions

Comparison Operators, compare values in a database and determine if they are equal (=), not equal (!=, <>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=). Let's return all actors whose birth_year is greater or equal to 1990 in the movies database, actors table.

SELECT * FROM actors WHERE birth_year>=1990

To learn more about numeric, strings and date functions https://www.w3schools.com/mysql/func_mysql_curdate.asp

Control Flow Functions

In any programming language, there are control flow statements. For instance, in Python, we have if and else statements,

a=3
b=4
if b>a:
    print('true')
else:
    print('false')

We want to determine if b is greater than a which is true. Same as in MySQL control flow function evaluates the condition specified in it. The output generated by them can be a true, false, static value, or column expression. We can use the control flow functions in the SELECT, WHERE, ORDER BY, and GROUP BY clause. The most common functions include; The most common functions:

1. The IF functions

The IF function returns the value based on the condition specified within the function. The syntax of the IF function is the following:

SELECT IF(condition, if_condition_true, if_condition_false)

2. The CASE expression

The case expression is used to implement the IF ELSE logic in the query. The CASE expression can be used in the SELECT, WHERE, and ORDER BY clause.

3. The IFNULL function

IFNULL(expression, value) replaces NULL with a specified value.

4. The NULLIF function

NULLIF(expression1, expression2) returns NULL if the two expressions are equal. Otherwise, it returns expression1.

Using our movie database let's create a column name generation then use the existing birth_year column to determine if an actor is a millennial or genx using case expression

/* create column*/
ALTER TABLE actors*/
ADD COLUMN generation VARCHAR(10);

/*control flow statement*/

SELECT 
    name,
    birth_year,
    CASE
        WHEN birth_year >= 1980 THEN 'Millennial'
        ELSE 'GenX'
    END AS generation
FROM actors;

Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. Let us write a stored procedure of a query that determines the average revenue per industry.

USE moviesdb;
Create procedure analysis()

SELECT 
    industry,
    AVG(revenue) AS average_revenue
FROM 
    movies
INNER JOIN 
    financials 
ON 
    movies.movie_id = financials.movie_id
GROUP BY 
    industry;

Calling Stored Procedures

To call a procedure we use keyword call. Let's call our procedure analysis.

Call analysis();

You can follow me on github at https://github.com/JoyKimaiyo