Functions and Procedures Flashcards
(16 cards)
What are Functions and Procedures fundamentally, and what is their main purpose?
They are fundamentally the same thing: a set of SQL statements that accomplish a specific task. Their main purpose is abstracting queries by packaging them under a descriptive name for later use.
What is a key security feature of Functions and Procedures that makes them powerful for database administration?
Admins can control who can use them by granting or denying permission to specific users. This provides fine-grained access control over database operations.
What are the two types of functions in MySQL and how do they differ?
- Built-in functions (system functions): Predefined functions that come with MySQL 2. User-defined functions: Functions created by users themselves. Both are used the same way in SQL statements.
What is the fundamental characteristic that distinguishes Functions from Procedures in terms of return values?
Functions MUST always return a single usable output value. Procedures may return many outputs, none at all, or provide data through SELECT statements instead of return values.
Write the complete syntax structure for creating a user-defined function.
DELIMITER // CREATE FUNCTION function_name (parameter1 TYPE, parameter2 TYPE, …) RETURNS data_type_of_value_to_return DETERMINISTIC | NOT DETERMINISTIC BEGIN –statements RETURN something; END // DELIMITER ;
Why do we need to use DELIMITER when creating functions and procedures?
Because functions/procedures contain semicolons in their statement blocks. Without changing the delimiter, MySQL would think the function creation ends at the first semicolon inside the function body, causing syntax errors.
Explain the difference between DETERMINISTIC and NOT DETERMINISTIC, and give an example of each.
DETERMINISTIC: Always returns same output for same input (e.g., SQRT(4) always returns 2). NOT DETERMINISTIC: May return different outputs for same input (e.g., NOW() returns different time each call).
What are the three parameter types for procedures and what does each do?
IN: Input parameter used inside the procedure. OUT: Output parameter where procedure stores calculated results. INOUT: Acts as both input and output - reads initial value, uses it, then updates it.
Write the basic syntax for creating a procedure with all parameter types.
DELIMITER // CREATE PROCEDURE procedure_name ( IN param1 datatype, OUT param2 datatype, INOUT param3 datatype ) BEGIN SQL statements go here END // DELIMITER ;
How do you execute a function vs execute a procedure?
Function: Use directly in expressions like SELECT my_function(5); - it returns a value. Procedure: Use CALL keyword like CALL procedure_name(); - it performs actions but doesn’t return a value for expressions.
You created a function that calculates tax but it’s giving wrong results. How do you remove it safely?
DROP FUNCTION IF EXISTS function_name; - The IF EXISTS prevents errors if the function doesn’t exist. Same syntax for procedures: DROP PROCEDURE IF EXISTS procedure_name;
Why can’t procedures be used as expressions in SELECT statements like functions can?
Because procedures don’t return a single evaluable value. They may return multiple results through SELECT statements or OUT parameters, but they can’t be evaluated to a single value that fits into an expression.
A function takes customer_id as input and should return their total purchase amount. Write the function signature.
CREATE FUNCTION get_customer_total(customer_id INT) RETURNS DECIMAL(10,2) DETERMINISTIC - Takes INT input, returns DECIMAL for money, DETERMINISTIC because same customer should always have same total at any given moment.
What’s the key difference in how functions and procedures provide output to the caller?
Functions: Use RETURN statement to provide a single value that can be used in expressions. Procedures: Use SELECT statements to display results or OUT parameters to store values in variables.
You need to create a procedure that takes a product ID, calculates discount, and returns both original price and discounted price. What parameter types do you need?
IN product_id INT (input), OUT original_price DECIMAL(10,2) (output), OUT discounted_price DECIMAL(10,2) (output) - One input parameter and two output parameters to return multiple values.
What security advantage do stored procedures provide over giving users direct table access?
Users can execute specific business logic through procedures without needing direct table permissions. Admins control exactly what operations users can perform by granting procedure permissions instead of table permissions.
