This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Stored Functions and Procedures”.
1. To produce a stored function, which statement is used?
a) PRODUCE FUNCTION
b) CREATE FUNCTION
c) PRODUCE PROCEDURE
d) CREATE PROCEDURE
Explanation: The ‘CREATE FUNCTION’ statement is used to create a stored function in MySQL. The ‘CREATE PROCEDURE’ statement is used to create a stored procedure instead, from which values are not returned.
2. How many values can be returned from a given stored function?
Explanation: In MySQL, the stored function cannot return multiple values. Instead, multiple stored functions can be written and invoked from within a single statement however. They are different from stored procedures.
3. How many values can be returned from a stored procedure?
Explanation: In MySQL, unlike the stored functions, the stored procedures cannot return values. They can be used to perform calculations or produce the result sets passed back to the clients.
4. Suppose a stored function named PI() is written in the database ‘sampdb’. How would it be called?
Explanation: When a stored function is defined with the same name as a built in function, the function name should be qualified with the database name when it is invoked. The function ‘PI()’ is built in.
5. Which privilege must be given to the database to create a stored function or procedure?
a) CREATE ROUTINE
b) CREATE METHOD
c) CREATE FUNCTION
d) CREATE PROCEDURE
Explanation: The ‘CREATE ROUTINE’ privilege must be given for the database in order to create a stored function or procedure. Stored functions and procedures always belong to a particular database.
6. Which variable is set to zero when automatic privilege granting is not needed?
Explanation: The automatic_sp_privileges system variable is set to zero when the automatic privilege granting is not needed and revocation should not be allowed to occur. It is a crucial system variable.
7. Which procedure parameter enables the caller to pass in a value and get back a value?
Explanation: In an IN parameter, the caller passes a value into the procedure. An OUT parameter is exactly the opposite. The ‘INOUT’ parameter enables the caller to pass in a value and also to get back a value.
8. The IN, OUT and INOUT keywords do not apply to stored functions.
Explanation: The IN, OUT and INOUT keywords do not apply to stored functions, triggers and events. Triggers and events do not have parameters at all. For the stored functions, all parameters are like IN parameters.
9. For the same input parameters, if the stored function returns the same result, it is called _____________
b) non deterministic
Explanation: For the same input parameters, if the stored function returns the same result, it is called a deterministic function. Otherwise, the stored function is called not deterministic.
10. What is abc in the following statement?
DECLARE abc HANDLER FOR def ghi;
b) condition value
Explanation: The ‘DECLARE HANDLER’ statement is used to declare a handler. When a condition whose value matches def, MySQL will execute ghi and either continue or exit the current code block.
Sanfoundry Global Education & Learning Series – MySQL Database.
To practice all areas of MySQL Database, here is complete set of 1000+ Multiple Choice Questions and Answers.