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.