Procedures & Functions – ORACLE PL/SQL Chapter Wise Interview Questions

Procedures & Functions – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
What are the constructs of a procedure, function or a package?
Answer:
The constructs of a procedure, function or a package are:

1. variables and constants
2. cursors
3. exceptions

Question 2:
What is the advantage of a Stored Procedure over a Database Trigger?
Answer:
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

Question 3:
What is the difference between Anonymous Blocks and Stored Procedures?
Answer:

Question 4:
What are the advantages of Procedures?
Answer:

1. Loaded once and used many times
2. Performance better coz all SQL statements are sent in one go from the application to the database
3. Security ( no object privileges are given directly )
4. Invoker’s rights possible
5. Data integrity, productivity

Question 5:
What is syntax for dropping a procedure and a function? Are these operations possible?
Answer:
Drop Procedure/Function; yes, if they are standalone procedures or functions. If they are a part of a package then one has to remove it from the package definition and body and recompile the package.

Question 6:
What is the difference between a Function and a Procedure?
Answer:

Question 7:
What is a Stored Procedure?
Answer:
A Stored Procedure or in simple a Proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.

Question 8:
What are the procedure parameter types?
Answer:
We can pass parameters to procedures in three ways.

1. IN-parameters
2. OUT-parameters
3. IN OUT-parameters

Question 9:
How to execute a Stored Procedure?
Answer:
There are two ways to execute a procedure.

1. From the SQL prompt.
   EXECUTE [or EXEC] procedure_name;
2. Within another procedure – simply use the procedure name. procedure_name;

Question 10:
What is a Function in PL/SQL?
Answer:
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
The General Syntax to create a function is:

CREATE [OR REPLACE] FUNCTION function_name
[parametersl RETURN return_datatype;
IS Declaration_section
BEGIN Execution_section Return return_variable;
EXCEPTION exception-section 
Return return_variable;
END;

Question 11:
How to execute a PL/SQL Function?
Answer:
A function can be executed in the following ways.

1. Since a function returns a value we can assign it to a variable.
   employee _name := employer_details_func;If ’employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.
2. As a part of a SELECT statement
   SELECT employer_detail_func FROM dual;
3. In a PL/SQL Statements like,
   dbms_output.put line(employer_details_fune);

This line displays the value returned by the function.

Question 12:
What types of Parameters are used in Procedure and Functions? Or
How to pass parameters to Procedures and Functions in PL/SQL?
Answer:
In PL/SQL, we can pass parameters to procedures and functions in three ways.

1. IN type parameter: These types of para meters are used to send values to stored procedures.
2. OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3. IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.

Question 13:
Explain the parameter type: IN parameter.
Answer:
This is similar to passing parameters in programming languages. We can pass values to the stored procedure through these parameters or variables. This type of parameter is a read only parameter. We can assign the value of IN type parameter to a variable or use it in a query, but we cannot change its value inside the procedure.
The General syntax to pass a IN parameter is

CREATE [OR REPLACE] PROCEDURE procedure_name 
( param_name1 IN datatype, param_namel2 IN datatype...) 
param_name1, param_name2... are unique parameter names. 
Datatype - defines the datatype of the variable.
IN - is optional, by default it is a IN type parameter.

Question 14:
Explain the parameter type OUT parameter.
Answer:
The OUT parameters are used to send the OUTPUT from a procedure or a function. This is a write-only parameter i.e., we cannot pass values to OUT parameters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can receive this output value.
The General syntax to create an OUT parameter is

CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)

The parameter should be explicitly declared as OUT parameter.

Question 15:
Explain the parameter type IN OUT parameter.
Answer:
The IN OUT parameter allows us to pass values into a procedure and get output values from the procedure. This parameter is used if the value of the IN parameter can be changed in the calling program.
By using IN OUT parameter we can pass values into a parameter and return a value to the calling program using the same parameter. But this is possible only if the value passed to the procedure and output value have a same datatype. This parameter is used if the value of the parameter will be changed in the procedure.
The General syntax to create an IN OUT parameter is

CREATE [OR REPLACE[ PROCEDURE proc3 (param_name IN OUT datatype)