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

Question: 11

What is a subprogram? What are its advantages?

A named procedural Language/structured Query Language (PL/SQL) block is called a subprogram. It has three sections: declarative, executable, and exception handling (Optional). The main advantage of a subprogram is that it can be complied and stored in a database. In addition, it has the following advantages.

a. Modularization Refers to a property, which allows you to break the large program into smaller modules and make the program easy to write and debug.

b. Easy maintenance Refers to a property, which allows you to make the modifications in a code because the code is modular and written in one place.

c. Reusability Refers to a property, which allows you to reuse the same subprogram within any application. The code of the subprogram need not be re-written each time.

d. Data integrity and security Refers to a property, which prevents unauthorized users from accessing the subprogram without the proper rights.

e. Better performance Helps in avoiding repeated parsing on subsequent calls to the subprogram as the code is complied, parsed, and available in the shared structured Query Language (SQL) area. This reduces the number of calls to the database; and therefore, increases the network performance.

f. Code clarity Allows you to give proper names to the PL/SQL blocks. As a result, the code becomes simpler to understand and gets better clarity.

Question: 12

Can the size of the parameter passed to the procedure be restricted in the procedure definition?

No, the size of the parameter passed to the procedure cannot be restricted in the procedure declaration. The data type for the parameter has to be specified only.

Question: 13

What is the meaning of disabling a trigger?

When a trigger is disabled, it does not mean that it is deleted. The code of the trigger is still stored in the data dictionary but the trigger will not have any effect on the table.

Question: 14

What are the different parameter modes, which can be used in the procedure specification?

IN- Refers to a constant value, which is assigned to this parameter from the calling environment. The IN parameter value cannot be modified within a procedure. It is a default mode. OUT-Refers to a return value, which is passed procedure back to the calling environment.

IN Out- Refers to a value, which is assigned from the calling environment to this parameter and a same or different value is passed from the procedure to the calling environment using this parameter.