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
View Answer
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?
a) 0
b) 1
c) 2
d) 3
View Answer
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?
a) 0
b) 1
c) 2
d) 3
View Answer
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?
a) PI()
b) sampdb.PI()
c) MySQL.PI()
d) db.PI()
View Answer
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
View Answer
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?
a) automatic_sp_privileges
b) automatic_ps_privileges
c) automatic_pg_privileges
d) automatic_gp_privileges
View Answer
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?
a) IN
b) OUT
c) INOUT
d) GETINOUT
View Answer
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.
a) True
b) False
View Answer
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 _____________
a) deterministic
b) non deterministic
c) regular
d) monotonous
View Answer
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 MySQL statement?
DECLARE abc HANDLER FOR def ghi;
a) action
b) condition value
c) statement
d) null
View Answer
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.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
- Check MySQL Books
- Apply for Programming Internship
- Practice Programming MCQs
- Check Information Technology Books