Features of PL/SQL
The following are important features of PL/SQL.
PL/SQL is a block-structured language. Each program written in PL/SQL is written as a block. Blocks can also be nested. Each block is meant for a particular task.
Variables and constants
PL/SQL allows you to declare variables and constants. Variables are used to store values temporarily. Variables and constants can be used in SQL and PL/SQL procedural statements just like an expression.
PL/SQL allows control structures like IF statement, FOR loop, WHILE loop to be used in the block. Control structures are most important extension to SQL in PL/SQL. Control structures allow any data process possible in PL/SQL.
PL/SQL allows errors, called as exceptions, to be detected and handled. Whenever there is a predefined error PL/SQL raises an exception automatically. These exceptions can be handled to recover from errors.
PL/SQL allows process to be divided into different modules. Subprograms called as procedures and functions can be defined and invoked using the name. These subprograms can also take parameters.
A cursor is a private SQL area used to execute SQL statements and store processing information. PL/SQL implicitly uses cursors for all DML commands and SELECT command that returns only one row. And it also allows you to define explicit cursor to deal with multiple row queries.
Most of the SQL functions that we have seen so far in SQL are available in PL/SQL. These functions can be used to manipulate variables of PL/SQL.
Advantages of PL/SQL
PL/SQL has the following advantages:
- SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. Dynamic SQL is SQL allows embedding DDL statements in PL/SQL blocks.
- PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.
- PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.
- PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.
- Applications written in PL/SQL are fully portable.
- PL/SQL provides high security level.
- PL/SQL provides access to predefined SQL packages.
- PL/SQL provides support for Object-Oriented Programming.
- PL/SQL provides support for Developing Web Applications and Server Pages.