Oracle PL/SQL Difference between Procedures, Functions, Packages - 1

Question: 1

What is the difference between procedure and function?

A procedure is executed as a PL/SQL statement.

It can accept more than one parameter as an input from the calling environment and may return none, one, or more than one value.

A function is invoked as a part of expression.

It can have more than one parameter as an input from the calling environment and it should return a single value to the calling environment using the RETURN statement.

Question: 2

What is a procedure? Can it be stored in the database?

A procedure is a named PL/SQL block (Sub program) that can have more than one parameter as an input from the calling environment and it may return none, one, or more than one value.

Yes, it can be compiled and stored in the database as a schema object and used for execution at any point of time.

Question: 3

What are the different methods for passing parameter to a procedure?

There are following three different methods to pass values to a procedure:

Positional-Refers to a method in which actual parameter of a procedure are passed in the same order as the formal parameters.

Named-Refers to a method in which actual parameters of a procedure are passed in any arbitrary order but are associated with their corresponding formal parameters.

Combination-Refers to a method in which some parameters are passed as positional and some are passed as named parameters.

Question: 4

Which data types can be used for parameters within a procedure?

Explicit data types, %TYPE and %ROW TYPE, without size specification can be used for parameters in a procedure.

Question: 5

What are the two different types of subprogram in PL/SQL?

The two different types of subprograms in PL/SQL are procedures and functions. Both can be called from an external environment or application. In addition, they can accept parameters.