 |
| Creating Procedures |
01. Write down the process of creating a Layered Subprogram Design.
Create subprogram layers for your application.
a. Data access subprogram layer with SQL logic
b. Business logic subprogram layer, which may or may not use the data access layer
02. What is Modularization?
Modularization converts large blocks of code into smaller groups of code called modules. After modularization, the modules can be reused by the same program or shared with other programs.
03. What are the benefits of using modular program constructs?
The benefits of using modular program constructs are:
a. Easy maintenance.
b. Improved data security and integrity.
c. Improved performance.
d. Improved code clarity.
04. What Are PL/SQL Subprograms?
PL/SQL Subprograms:
a. A PL/SQL subprogram is a named PL/SQL block that can be called with a set of parameters.
b. You can declare and define a subprogram within either a PL/SQL block or another subprogram.
c. A subprogram consists of a specification and a body.
d. A subprogram can be a procedure or a function.
e. Typically, you use a procedure to perform an action and a function to compute and return a value.
f. Subprograms can be grouped into PL/SQL packages.
05. Write down the Benefits of Using PL/SQL Subprograms.
Benefits:
a. Benefits of Subprograms.
b. Easy maintenance.
c. Improved data security.
d. Data integrity.
e. Improved performance.
f. Improved code.
06. Write down the differences between Anonymous Blocks and Subprograms.
Differences:
07. What are Procedures?
Procedures:
a. Are types of subprogram that perform an action.
b. Can be stored in the database as a schema object.
c. Promote reusability and maintainability.
08. Write down the use of CREATE OR REPLACE Statement with Creating Procedures.
CREATE OR REPLACE Statement:
a. Use the CREATE clause to create a stand-alone procedure that is stored in the Oracle database.
b. Use the OR REPLACE option to overwrite an existing procedure.
09. What are Parameters and Parameter Modes?
Parameters:
a. Are declared after the subprogram name in the PL/SQL header.
b. Pass or communicate data between the calling environment and the subprogram.
Parameter Modes:
Are used like local variables but are dependent on their parameter-passing mode:
a. An IN parameter mode (the default) provides values for a subprogram to process.
b. An OUT parameter mode returns a value to the caller.
c. An IN OUT parameter mode supplies an input value, which may be returned (output) as a modified value.
10. What are Formal and Actual Parameters?
Formal Parameters:
Local variables declared in the parameter list of a subprogram specification.
Actual Parameters (or Arguments):
Literal values, variables, and expressions used in the parameter list of the calling subprogram.
11. Write down the Comparing the Parameter Modes.
Comparing the Parameter Modes:
12. Write down the Available Notations for Passing Actual Parameters.
When calling a subprogram, you can write the actual parameters using the following notations:
a. Positional: Lists the actual parameters in the same order as the formal parameters.
b. Named: Lists the actual parameters in arbitrary order and uses the association operator (=>) to associate a named formal parameter with its actual parameter.
c. Mixed: Lists some of the actual parameters as positional and some as named.
13. Write down the process of Calling Procedures.
Calling Procedures:
a. You can call procedures using anonymous blocks, another procedure, or packages.
b. You must own the procedure or have the EXECUTE privilege.
0 Comments