MCQs on Stored Procedures and Functions | Reusable Code

9. Stored Procedures and Functions

Creating and Executing Stored Procedures

1. What is a stored procedure in SQL?
   • A. A function that performs a specific task
   • B. A precompiled set of one or more SQL statements
   • C. A temporary table used in SQL queries
   • D. A query to retrieve data from a table
2. Which of the following is the correct syntax to create a stored procedure?
   • A. CREATE PROCEDURE procedure_name AS SELECT * FROM table;
   • B. CREATE PROCEDURE procedure_name (parameters) BEGIN SQL_statements END;
   • C. CREATE PROCEDURE procedure_name SELECT * FROM table;
   • D. CREATE PROCEDURE procedure_name USING table_name;
3. How do you execute a stored procedure in SQL?
   • A. EXEC procedure_name;
   • B. CALL procedure_name;
   • C. RUN procedure_name;
   • D. EXECUTE procedure_name;
4. Which of the following statements can be used to execute a stored procedure in Oracle SQL?
   • A. CALL procedure_name;
   • B. EXEC procedure_name;
   • C. RUN procedure_name;
   • D. EXECUTE procedure_name;
5. What does the keyword BEGIN indicate when creating a stored procedure?
   • A. The start of the procedure’s parameters
   • B. The beginning of the SQL block
   • C. The termination of the procedure
   • D. The end of the SQL statements
6. In which scenario would you use a stored procedure in SQL?
   • A. For performing repetitive tasks with one or more SQL queries
   • B. To retrieve data from a database
   • C. To insert a single row into a table
   • D. To update a table in a single transaction
7. What is the advantage of using stored procedures over regular SQL queries?
   • A. Improved performance due to precompilation
   • B. Easier for beginners to learn
   • C. More flexible with database migrations
   • D. They allow for automatic indexing

Writing and Using Functions

8. What is the key difference between a stored procedure and a function in SQL?
   • A. A function does not return a value, while a stored procedure does
   • B. A function returns a value, while a stored procedure does not
   • C. A function can only be executed within a SELECT statement
   • D. A stored procedure can only be executed once
9. Which of the following is true for functions in SQL?
   • A. Functions cannot return values
   • B. Functions are always called with EXEC
   • C. Functions must return a value
   • D. Functions cannot accept parameters
10. Which SQL keyword is used to define a function?

• A. CREATE FUNCTION
• B. CREATE PROCEDURE
• C. DEFINE FUNCTION
• D. DEFINE PROCEDURE

11. When would you use a function in SQL?

• A. To perform a task that does not return a result
• B. When you need to execute a set of SQL queries
• C. When you need to return a result or compute a value
• D. When you need to update data in the database

12. In SQL, what does the RETURNS clause specify when creating a function?

• A. The parameters the function will accept
• B. The result type that the function will return
• C. The source of the data used by the function
• D. The security level for executing the function

13. Which of the following statements is true about SQL functions?

• A. They must always perform data manipulation operations
• B. They return only a single row of data
• C. They can be used in a SELECT statement
• D. They are stored outside the database

IN, OUT, and INOUT Parameters

14. What is an IN parameter in a stored procedure or function?

• A. A parameter that returns data to the caller
• B. A parameter used to pass values into the procedure or function
• C. A parameter that stores multiple values
• D. A parameter that accepts values from the procedure

15. What is an OUT parameter used for in a stored procedure or function?

• A. To accept input from the caller
• B. To pass a result back to the caller
• C. To process input data without returning results
• D. To define an internal value within the procedure

16. Which of the following is true for INOUT parameters?

• A. They only pass values into the procedure or function
• B. They can be used to send values into and receive values from the procedure
• C. They only pass results back to the caller
• D. They cannot be used in stored procedures

17. How do you specify that a parameter is INOUT in a stored procedure?

• A. INOUT parameter_name
• B. IN parameter_name OUT
• C. OUT parameter_name IN
• D. INOUT parameter_name

18. Which parameter mode is used to pass values from the procedure back to the caller?

• A. IN
• B. OUT
• C. INOUT
• D. BOTH

19. Can an INOUT parameter be used to pass multiple values between the caller and procedure?

• A. Yes, INOUT parameters can accept multiple values
• B. No, INOUT parameters only pass a single value at a time
• C. Yes, but only in a function
• D. No, only OUT parameters can pass multiple values

Cursors (Implicit and Explicit)

20. What is a cursor in SQL?

• A. A pointer to a single row in a result set
• B. A type of database transaction
• C. A special table used to store intermediate results
• D. A mechanism for iterating over multiple rows of a query result

21. What is the difference between an implicit cursor and an explicit cursor in SQL?

• A. An explicit cursor is automatically created by SQL, while an implicit cursor requires a DECLARE statement
• B. An implicit cursor is automatically created by SQL, while an explicit cursor must be declared manually
• C. An explicit cursor cannot be used to iterate over rows
• D. Implicit cursors require the FETCH command to retrieve data

22. Which statement is true about implicit cursors?

• A. They are always declared by the user
• B. They are used for single-row queries only
• C. They are automatically created by SQL for DML statements
• D. They are created for complex queries only

23. What is the main advantage of using explicit cursors?

• A. They allow you to fetch data from a single row
• B. They provide more control over how rows are retrieved and processed
• C. They are faster than implicit cursors
• D. They do not require any programming logic

24. What are the key operations involved with cursors in SQL?

• A. DECLARE, OPEN, FETCH, CLOSE
• B. SELECT, UPDATE, DELETE, INSERT
• C. CREATE, EXECUTE, RUN, CLOSE
• D. BEGIN, COMMIT, ROLLBACK

25. Which SQL statement is used to fetch the next row from an explicit cursor?

• A. GET NEXT
• B. SELECT NEXT
• C. FETCH NEXT
• D. RETRIEVE NEXT

Performance Considerations for Procedures and Functions

26. How do stored procedures improve performance in SQL?

• A. By reducing the need for multiple round trips between the application and database
• B. By ensuring that data is encrypted before being queried
• C. By storing data in a compressed format
• D. By reducing the size of the database

27. What is the impact of using a function with a large result set in SQL performance?

• A. It may slow down the performance due to the extra overhead of returning the result
• B. It will improve the performance because functions are precompiled
• C. It does not affect performance significantly
• D. It will speed up the query execution

28. Which of the following is a recommended practice for optimizing stored procedures?

• A. Minimize the use of complex queries and loops inside stored procedures
• B. Use stored procedures only for read operations, not for updates
• C. Avoid using indexes when creating stored procedures
• D. Write all queries inside a single stored procedure for better performance

29. Which of the following can negatively impact the performance of functions and stored procedures?

• A. Use of cursors and loops
• B. Use of INOUT parameters
• C. Use of the RETURN statement
• D. Use of the DECLARE statement

30. How can you optimize the performance of functions in SQL?

• A. By minimizing the number of nested queries inside the function
• B. By using multiple RETURN statements
• C. By increasing the number of parameters in the function
• D. By using functions only for large data manipulation tasks

---

Answers

QNo	Answer (Option with text)
1	B. A precompiled set of one or more SQL statements
2	B. CREATE PROCEDURE procedure_name (parameters) BEGIN SQL_statements END;
3	B. CALL procedure_name;
4	B. EXEC procedure_name;
5	B. The beginning of the SQL block
6	A. For performing repetitive tasks with one or more SQL queries
7	A. Improved performance due to precompilation
8	B. A function returns a value, while a stored procedure does not
9	C. Functions must return a value
10	A. CREATE FUNCTION
11	C. When you need to return a result or compute a value
12	B. The result type that the function will return
13	C. They can be used in a SELECT statement
14	B. A parameter used to pass values into the procedure or function
15	B. To pass a result back to the caller
16	B. They can be used to send values into and receive values from the procedure
17	A. INOUT parameter_name
18	B. OUT
19	A. Yes, INOUT parameters can accept multiple values
20	D. A mechanism for iterating over multiple rows of a query result
21	B. An implicit cursor is automatically created by SQL, while an explicit cursor must be declared manually
22	C. They are automatically created by SQL for DML statements
23	B. They provide more control over how rows are retrieved and processed
24	A. DECLARE, OPEN, FETCH, CLOSE
25	C. FETCH NEXT
26	A. By reducing the need for multiple round trips between the application and database
27	A. It may slow down the performance due to the extra overhead of returning the result
28	A. Minimize the use of complex queries and loops inside stored procedures
29	A. Use of cursors and loops
30	A. By minimizing the number of nested queries inside the function