MySQL Questions and Answers – Stored Functions and Procedures

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

Answer: b
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

Answer: b
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

Answer: a
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.
advertisement
advertisement

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

Answer: b
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

Answer: a
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

Answer: a
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

Answer: c
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.
advertisement

8. The IN, OUT and INOUT keywords do not apply to stored functions.
a) True
b) False
View Answer

Answer: a
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

Answer: a
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.
advertisement

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

Answer: a
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]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.