Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing
language that offers the following advantages:
Full Support for SQL
PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction
control commands, as well as all the SQL functions, operators, and pseudocolumns.
So, you can manipulate Oracle Database data flexibly and safely. PL/SQL fully
supports SQL datatypes, reducing conversions as data is passed between
applications and the database.
Dynamic SQL is a programming technique that lets you build and process SQL
statements "on the fly" at run time. It gives PL/SQL flexibility comparable to
scripting languages such as Perl, Korn shell, and Tcl.
Tight Integration with Oracle Database
PL/SQL supports all the SQL datatypes. Combined with the direct access that SQL
provides, these shared datatypes integrate PL/SQL with the Oracle Database data
dictionary.
The %TYPE and %ROWTYPE attributes let your code adapt as table definitions
change. For example, the %TYPE attribute declares a variable based on the type of a
database column. If the column's type changes, your variable uses the correct type
at run time. This provides data independence and reduces maintenance costs.
Better Performance
If your application is database intensive, you can use PL/SQL blocks to group SQL
statements before sending them to Oracle Database for execution. This can
drastically reduce the communication overhead between your application and
Oracle Database.
PL/SQL stored procedures are compiled once and stored in executable form, so
procedure calls are quick and efficient. A single call can start a compute-intensive
stored procedure, reducing network traffic and improving round-trip response
times. Executable code is automatically cached and shared among users, lowering
memory requirements and invocation overhead.

Higher Productivity
PL/SQL adds procedural capabilities, such as Oracle Forms and Oracle Reports. For
example, you can use an entire PL/SQL block in an Oracle Forms trigger instead of
multiple trigger steps, macros, or user exits.
PL/SQL is the same in all environments. As soon as you master PL/SQL with one
Oracle tool, you can transfer your knowledge to others, and so multiply the
productivity gains. For example, scripts written with one tool can be used by other
tools.
Scalability
PL/SQL stored procedures increase scalability by centralizing application
processing on the server. Automatic dependency tracking helps you develop
scalable applications.
The shared memory facilities of the shared server (formerly known as
Multi-Threaded Server or MTS) enable Oracle Database to support many thousands
of concurrent users on a single node. For more scalability, you can use the Oracle
Connection Manager to multiplex network connections.
Maintainability
Once validated, a PL/SQL stored procedure can be used with confidence in any
number of applications. If its definition changes, only the procedure is affected, not
the applications that call it. This simplifies maintenance and enhancement. Also,
maintaining a procedure on the server is easier than maintaining copies on various
client machines.

PL/SQL Support for Object-Oriented Programming
Object Types An object type is a user-defined composite datatype that encapsulates a
data structure along with the functions and procedures needed to manipulate the
data. The variables that form the data structure are called attributes. The functions
and procedures that characterize the behavior of the object type are called methods,
which you can implement in PL/SQL.
Object types are an ideal object-oriented modeling tool, which you can use to
reduce the cost and time required to build complex applications. Besides allowing
you to create software components that are modular, maintainable, and reusable,
object types allow different teams of programmers to develop software components
concurrently.

Collections A collection is an ordered group of elements, all of the same type (for
example, the grades for a class of students). Each element has a unique subscript
that determines its position in the collection. PL/SQL offers two kinds of
collections: nested tables and varrays (short for variable-size arrays).
Collections work like the set, queue, stack, and hash table data structures found in
most third-generation programming languages. Collections can store instances of an
object type and can also be attributes of an object type. Collections can be passed as
parameters. So, you can use them to move columns of data into and out of database
tables or between client-side applications and stored subprograms. You can define
collection types in a PL/SQL package, then use the same types across many
applications.
Portability
Applications written in PL/SQL can run on any operating system and hardware
platform where Oracle Database runs. You can write portable program libraries and
reuse them in different environments.
Security
PL/SQL stored procedures let you divide application logic between the client and
the server, to prevent client applications from manipulating sensitive Oracle
Database data. Database triggers written in PL/SQL can prevent applications from
making certain updates, and can audit user queries.
You can restrict access to Oracle Database data by allowing users to manipulate it
only through stored procedures that have a restricted set of privileges. For example,
you can grant users access to a procedure that updates a table, but not grant them
access to the table itself.

Built-In Packages for Application Development
DBMS_PIPE is used to communicate between sessions.
DBMS_ALERT is used to broadcast alerts to users.
DBMS_LOCK and DBMS_TRANSACTION are used for lock and transaction
management.
DBMS_AQ is used for Advanced Queuing.
DBMS_LOB is used to manipulate large objects.

DBMS_ROWID is used for employing ROWID values.
UTL_RAW is the RAW facility.
UTL_REF is for work with REF values.
Built-In Packages for Server Management
DBMS_SESSION is for session management by DBAs.
DBMS_SPACE and DBMS_SHARED_POOL provide space information and
reserve shared pool resources.
DBMS_JOB is used to schedule jobs in the server.
Built-In Packages for Distributed Database Access
These provide access to snapshots, advanced replication, conflict resolution,
deferred transactions, and remote procedure calls.