This set of PL/SQL Multiple Choice Questions & Answers (MCQs) focuses on “PL/SQL Function”.
1. Which of the following is the main difference between a function and a procedure in PL/SQL?
a) Function is user-defined, Procedure is pre-defined
b) Function returns a value, Procedure does not return any value
c) Function uses only actual parameters, Procedures uses only formal parameters
d) Function and Procedure do not have any differences
View Answer
Explanation: A function is same as a procedure except that it returns a value. So, all the properties of a procedure are applied on a function in PL/SQL.
2. Which of the following is the correct syntax for creating a function in PL/SQL?
a)
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] TYPE [, ...])] RETURN return_value {IS | AS} BEGIN <function_body> END [function_name];
b)
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] TYPE [, ...]) {IS | AS} BEGIN <function_body> END [function_name];
c)
CREATE [OR REPLACE] FUNCTION [(parameter_name [IN | OUT | IN OUT] TYPE [, ...])] RETURN return_value BEGIN <function_body> END [function_name];
d)
REPLACE FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] TYPE [, ...])] RETURN return_value {IS | AS} BEGIN <function_body> END [function_name];
Explanation: The correct syntax for creating a function in PL/SQL is –
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] TYPE [, ...])] RETURN return_value {IS | AS} BEGIN <function_body> END [function_name];
Here, all the statements written inside the square brackets [ ] are optional and their use depends on the user and its requirement.
3. The “ OR REPLACE” option in the definition of a function in PL/SQL is used to modify an existing function.
a) True
b) False
View Answer
Explanation: The “OR REPLACE” option is an optional part in the definition of a function in PL/SQL. It is used to modify an existing function.
4. The ‘IS’ keyword is used instead of the ‘AS’ keyword when creating a standalone function in PL/SQL.
a) True
b) False
View Answer
Explanation: The ‘AS’ keyword is used instead of the ‘IS’ keyword when creating a standalone function in PL/SQL. A standalone function is a function that performs a specific function and does not take/return a value from/to any other function.
5. In PL/SQL, a function is called inside which of the following code blocks?
a) EXCEPTION
b) END
c) BEGIN
d) DECLARE
View Answer
Explanation: A function is called inside the BEGIN block of a PL/SQL code. For calling a function, we need to pass the required parameters along with the function name and if the function returns a value, then we can store the returned value in any variable of same datatype as of the returned value.
6. Which of the following keyword is a must have when creating a function in PL/SQL?
a) REPLACE
b) IN
c) AS
d) RETURN
View Answer
Explanation: The RETURN keyword is a mandatary keyword while creating a function in PL/SQL. Since functions are made to return any particular value, so a RETURN keyword should always be present to state which value is to be returned.
7. Which of the following statement is true?
a) Actual and Formal arguments must have same names
b) Actual and Formal arguments must have different names
c) Actual parameters are declared in the calling environment and Formal parameters are declared in the function
d) Formal parameters are declared in the calling environment and Actual parameters are declared in the function
View Answer
Explanation: The statement which is true amongst the given statements is –
Actual parameters are declared in the calling environment and Formal parameters are declared in the function.
Actual parameters are the values on which the function has to be performed and they could be provided by the user. Formal parameters are just placeholders for the actual parameters and take their value when a function needs them.
Sanfoundry Global Education & Learning Series – PL/SQL.
To practice all areas of PL/SQL, 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]
