Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead. With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application.
- You can use PL/SQL blocks and subprograms to group SQL statements before sending them to the database for execution.
- PL/SQL also has language features to further speed up SQL statements that are issued inside a loop.
- PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient.
- Because stored procedures execute in the database server, a single call over the network can start a large job.
- This division of work reduces network traffic and improves response times.
- Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.
PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as Perl can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as
- exception handling,
- data hiding, and
- object-oriented datatypes.
PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language constructs to build applications.
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. After you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.