Introduction to PL/SQL 


01. What is PL/SQL?

PL/SQL:

a. Stands for Procedural Language extension to SQL

b. Is Oracle Corporation’s standard data access language for relational databases

c. Seamlessly integrates procedural constructs with SQL

d. Provides a block structure for executable units of code. Maintenance of code is made easier with such a well-defined structure.

e. Provides procedural constructs.


02. Write down the benefits of PL/SQL.

Benefits:

a. Integration of procedural constructs with SQL

b. Improved performance

c. Modularized program development

d. Integration with Oracle tools

e. Portability

f. Exception handling


03. Write down the PL/SQL Block Structure.

Block Structure:

DECLARE (Optional)

Variables, cursors, user-defined exceptions

BEGIN (Mandatory)

SQL statements

PL/SQL statements

EXCEPTION (Optional)

Actions to perform

when errors occur

END; (Mandatory)


04. What are the types of PL/SQL block?

There are three types of blocks that make up a PL/SQL program:

a. Procedures: Is a type of subprogram that performs an action

b. Functions: Is a named PL/SQL block that returns a value

c. Anonymous blocks:

Anonymous blocks are unnamed blocks. They are declared inline at the point in an applicationwhere they are to be executed and are compiled each time the application is executed. Theseblocks are not stored in the database.


05. What is subprogram?

Subprograms are complementary to anonymous blocks. They are named PL/SQL blocks that are stored in the database.