Functions & Procedures Flashcards
What are functions and procedures in SQL?
They are a set of SQL statements that accomplish a specific task, packaged under a name for reuse.
Why are functions and procedures useful?
They abstract logic, promote reuse, and can be permission-controlled by the database admin.
What is the main role of a function in SQL?
To operate on input data and return a single usable output without affecting the table directly.
What are the two types of functions in MySQL?
Built-in (system) functions and User-defined functions.
What is a built-in function in SQL?
A predefined function provided by MySQL, such as COUNT(), AVG(), NOW(), etc.
What is a user-defined function?
A function created by the user to operate on inputs and return a result.
What is the syntax to define a user-defined function in MySQL?
DELIMITER // CREATE FUNCTION name (…) RETURNS type BEGIN … RETURN …; END // DELIMITER ;
Why must every SQL function return something?
Because functions are designed to return a single value as output.
What is the DELIMITER keyword used for in MySQL?
To change the statement terminator so SQL doesn’t misinterpret the function body.
Why change the delimiter when creating a function?
So that semicolons inside the function body are not treated as the end of the entire function declaration.
What does deterministic mean for a function?
It always returns the same output for the same input.
What does non-deterministic mean for a function?
It may return different outputs for the same input (e.g., using NOW()).
What is a procedure in SQL?
A block of SQL statements that may return none, one, or many outputs, usually executed using CALL.
What is the difference between a function and a procedure?
A function returns a value and is used in expressions, while a procedure performs actions and uses SELECT or OUT parameters for output.
Can a procedure be used as an expression in another SQL statement?
No, only functions can be used as expressions.
How do you define a procedure in SQL?
Using CREATE PROCEDURE with optional IN, OUT, and INOUT parameters.
What is the syntax to create a procedure in MySQL?
DELIMITER // CREATE PROCEDURE name ([params]) BEGIN … END // DELIMITER ;
What does IN mean in procedure parameters?
The variable is used inside the procedure (input only).
What does OUT mean in procedure parameters?
The variable is used to return data from the procedure (output only).
What does INOUT mean in procedure parameters?
The variable is both input and output.
How do you execute a procedure?
Using the CALL keyword, e.g., CALL procedure_name();
What command deletes a function?
DROP FUNCTION IF EXISTS function_name;
What command deletes a procedure?
DROP PROCEDURE IF EXISTS procedure_name;
If you want to reuse a calculation logic across queries, what should you use?
A user-defined function.
If you want to perform multiple actions and possibly return multiple results, what should you use?
A procedure.
Spot the mistake: ‘CREATE FUNCTION myfunc() BEGIN RETURN 1; END;’
Missing RETURN type and proper delimiter syntax.
Spot the mistake: ‘CALL myfunc();’
CALL is used for procedures, not functions.
Explain when you’d choose a function over a procedure.
Use a function when you need a single return value that can be embedded in an expression; use a procedure for complex workflows.
Describe the role of DELIMITER in function and procedure definitions.
It prevents early termination of SQL statements inside BEGIN…END blocks by changing the SQL statement terminator.
