Creating stored functions in MySQL - Quick Revision & Summary

Recall & Review

beginner

What is a stored function in MySQL?

A stored function is a set of SQL statements stored in the database that performs a specific task and returns a single value.

beginner

Which keyword is used to create a stored function in MySQL?

The CREATE FUNCTION keyword is used to define a stored function.

beginner

What is the purpose of the RETURNS clause in a stored function?

The RETURNS clause specifies the data type of the value that the function will return.

intermediate

Why do you need to use DETERMINISTIC or NOT DETERMINISTIC when creating a stored function?

It tells MySQL whether the function always returns the same result for the same input (DETERMINISTIC) or not, which helps with optimization and caching.

beginner

How do you call a stored function in a SQL query?

You call it by using its name followed by parentheses with any required arguments, like a regular function: SELECT function_name(args);

Which statement correctly creates a stored function that returns the square of a number?

A CREATE FUNCTION square(x INT) RETURNS VOID BEGIN RETURN x * x; END;

B CREATE PROCEDURE square(x INT) RETURNS INT BEGIN RETURN x * x; END;

C CREATE FUNCTION square(x INT) RETURNS INT RETURN x * x;

D CREATE FUNCTION square(x INT) RETURNS INT BEGIN RETURN x * x; END;

Option D uses CREATE FUNCTION with RETURNS INT and a BEGIN...END block returning x * x, which is correct syntax.

What does the RETURNS clause specify in a stored function?

A The data type of the returned value

B The name of the function

C The input parameters

D The SQL statements inside the function

RETURNS specifies the data type of the value the function will return.

Which keyword indicates that a stored function always returns the same result for the same inputs?

A IMMUTABLE

B NOT DETERMINISTIC

C DETERMINISTIC

D VOLATILE

DETERMINISTIC means the function always returns the same result for the same inputs.

How do you execute a stored function named 'get_discount' with parameter 100?

A EXEC get_discount(100);

B SELECT get_discount(100);

C CALL get_discount(100);

D RUN get_discount(100);

Stored functions are called using SELECT, e.g., SELECT get_discount(100);

Which of these is NOT allowed inside a stored function in MySQL?

A Modifying database tables with INSERT or UPDATE

B Calling another stored function

C Declaring variables

D Using control flow statements like IF

Stored functions cannot modify database tables; that is allowed in stored procedures only.

Explain the steps to create a stored function in MySQL that returns the length of a string.

Think about the function signature and the return statement.

Describe the difference between a stored function and a stored procedure in MySQL.

Focus on return values and how they are called.

Practice

1. What is the main purpose of creating a stored function in MySQL?

easy

A. To save reusable code that returns a value

B. To create a temporary table

C. To delete data from a table

D. To backup the database

Solution

1. Step 1: Understand stored function purpose

   Stored functions are designed to hold reusable code inside the database.

2. Step 2: Identify key feature

   They must return a value using RETURN, which distinguishes them from procedures.

3. Final Answer:

   To save reusable code that returns a value -> Option A

4. Quick Check:

   Stored function = reusable code + return value [OK]

Hint: Stored functions always return a value [OK]

Common Mistakes:

• Confusing stored functions with procedures
• Thinking stored functions do not return values
• Using stored functions to delete data
• Assuming stored functions create tables

2. Which of the following is the correct way to start defining a stored function in MySQL?

easy

A. CREATE FUNCTION funcName RETURNS INT BEGIN ... END

B. CREATE PROCEDURE funcName() RETURNS INT BEGIN ... END

C. CREATE FUNCTION funcName() RETURNS INT BEGIN ... END

D. CREATE FUNCTION funcName() RETURNS INT { ... }

Solution

1. Step 1: Check syntax for stored function

   MySQL stored functions start with CREATE FUNCTION, include parentheses for parameters, and specify RETURNS type.

2. Step 2: Identify correct syntax

   CREATE FUNCTION funcName() RETURNS INT BEGIN ... END uses correct syntax with parentheses and RETURNS INT, followed by BEGIN ... END block.

3. Final Answer:

   CREATE FUNCTION funcName() RETURNS INT BEGIN ... END -> Option C

4. Quick Check:

   CREATE FUNCTION + RETURNS + BEGIN/END = correct syntax [OK]

Hint: Use parentheses and RETURNS keyword in function definition [OK]

Common Mistakes:

• Using PROCEDURE instead of FUNCTION
• Omitting parentheses after function name
• Using curly braces instead of BEGIN/END
• Missing RETURNS keyword

3. Given the following stored function, what will be the output of SELECT addFive(10);?

DELIMITER $$
CREATE FUNCTION addFive(num INT) RETURNS INT
BEGIN
  RETURN num + 5;
END$$
DELIMITER ;

medium

A. 15

B. 5

C. 10

D. Syntax Error

Solution

1. Step 1: Understand function logic

   The function addFive takes an integer input and returns the input plus 5.

2. Step 2: Calculate output for input 10

   Input 10 plus 5 equals 15, so the function returns 15.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   10 + 5 = 15 [OK]

Hint: Add input value to 5 as per function logic [OK]

Common Mistakes:

• Confusing input and output values
• Ignoring RETURN statement
• Assuming syntax error due to DELIMITER
• Forgetting to add 5

4. Identify the error in the following stored function definition:

DELIMITER $$
CREATE FUNCTION multiplyByTwo(num INT) RETURNS INT
BEGIN
  SET result = num * 2;
  RETURN result;
END$$
DELIMITER ;

medium

A. Function name is invalid

B. Incorrect use of DELIMITER

C. RETURN statement is missing

D. Missing variable declaration for 'result'

Solution

1. Step 1: Check variable usage inside function

   The variable 'result' is used but not declared with DECLARE inside the function.

2. Step 2: Understand MySQL variable rules

   Local variables must be declared before use; missing DECLARE causes error.

3. Final Answer:

   Missing variable declaration for 'result' -> Option D

4. Quick Check:

   Variables must be declared before use [OK]

Hint: Declare variables before using SET inside functions [OK]

Common Mistakes:

• Not declaring variables before SET
• Misusing DELIMITER keyword
• Forgetting RETURN statement
• Using invalid function names

5. You want to create a stored function isEven that returns 1 if a number is even and 0 if odd. Which of the following is the correct complete function definition?

hard

A. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
RETURN num / 2;
END$$
DELIMITER ;

B. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
IF num % 2 = 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;

C. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
RETURN num % 2;
END$$
DELIMITER ;

D. DELIMITER $$
CREATE FUNCTION isEven(num INT) RETURNS INT
BEGIN
IF num / 2 = 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END$$
DELIMITER ;

Solution

1. Step 1: Check logic for even number

   Even numbers have zero remainder when divided by 2, so use num % 2 = 0.

2. Step 2: Verify correct syntax and return values

   Uses IF with correct condition num % 2 = 0 and returns 1 or 0 accordingly, with proper BEGIN/END block, END IF, and DELIMITER.

3. Final Answer:

   DELIMITER $$ CREATE FUNCTION isEven(num INT) RETURNS INT BEGIN IF num % 2 = 0 THEN RETURN 1; ELSE RETURN 0; END IF; END$$ DELIMITER ; -> Option B

4. Quick Check:

   Use modulo operator and IF for even check [OK]

Hint: Use IF and modulo (%) to check even numbers [OK]

Common Mistakes:

• Using division instead of modulo for condition
• Returning wrong data types
• Omitting END IF statement
• Returning num % 2 without comparing to 0