Benefits of PL/SQL | Database Management System

BenefitsĀ of PL/SQL | Database Management System

Integration

Benefits of PL/SQL : PL/SQL plays a central role in both the Oracle server (through stored procedures, stored functions, database triggers, and packages) and Oracle development tools (through Oracle Developer Component triggers).

Oracle Forms Developer, Oracle Reports Developer, and Oracle Graphics Developer applications make use of shared libraries that hold code (procedures and functions) and can be accessed locally or remotely. SOL data types can also be used in PL/SQL. Combined with the direct access that SOL provides, these shared data types integrate PL/SOL with the Oracle server data dictionary. PL/SOL bridges the gap between convenient access to database technology and the need for procedural programming capabilities.

Improved Performance

PL/SQL can improve the performance of an application. The benefits differ depending on the execution environment.

  • PL/SQL can be used to group SOL statements together within a single block and to send the entire block to the server in a single call, thereby reducing networking traffic. Without PL/SQL, the SQL statements are sent to the Oracle server one at a time. Each SQL statement results in another call to the Oracle server and higher performance overhead. In a networked environment, the overhead can become significant. As the slide illustrates, if the application is SOL intensive, you can use PL/SQL blocks and subprograms to group SOL statements before sending them to the Oracle server for execution.
  • PL/SQL can also operate with Oracle Server application development tools such as Oracle Forms and Oracle Reports. By adding procedural processing power to these tools, PL/SQL enhances performance.

PL/SQL Block Structure

Every unit of PL/SQL comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units (procedures, functions, and anonymous blocks) that make up a PL/SOL program are logical blocks, which can contain any number of nested subblocks. Therefore, one block can represent a small part of another block, which in turn can be part of the whole unit of code.

Portability

  • Because PL/SQL is native to the Oracle server, you can move programs to any host environment (operating System or platform) that supports the Oracle server and PL/SQL. In other words, PL/SQL programs can run anywhere the Oracle server can run; you do not need to tailor them to each new environment.
  • You can also move code between the Oracle server and your application. You can write portable program packages and create libraries that can be reused in different environments. In PL/SQL you can use identifiers to do the following:
  • Declare variables, cursors, constants, and exceptions and then use them in SOL and procedural statements.

Procedural Language Control Structures:

Procedural Language Control Structures allow you to do the following:

  • Execute a sequence of Statements conditionally.
  • Execute a sequence of statements iteratively in a loop
  • Process individually the rows returned by a multiple-row query with an explicit cursor Errors:

The Error handling functionality in PL/SQL allows you to do the following:

  • Process Oracle server errors with exception-handling routines.
  • Declare user-defined error conditions and process them with exception-handling routines.