PL/SQL User's Guide and Reference 10g

Peter Kitson

ISBN : -

Order a printed copy of this book from Amazon --UNAVAILABLE--


Cover Design - PL/SQL User's Guide and Reference 10g
  

For your free electronic copy of this book please verify the numbers below. 

(We need to do this to make sure you're a person and not a malicious script)

Numbers

 



Sample Chapter From PL/SQL User's Guide and Reference 10g
     Copyright © Oracle Corp



Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language
that offers the following advantages:

  • Support for SQL
  • Support for object-oriented programming
  • Better performance
  • Higher productivity
  • Full portability
  • Tight integration with Oracle
  • Tight security

Tight Integration with SQL

The PL/SQL language is tightly integrated with SQL. You do not have to translate
between SQL and PL/SQL datatypes: a NUMBER or VARCHAR2 column in the database
is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you
both learning time and processing time. Special PL/SQL language features let you
work with table columns and rows without specifying the datatypes, saving on
maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a
text file and processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error
conditions, you can write utility programs for database administration that are reliable
and produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You
can write the bodies of triggers and methods for object types in PL/SQL.

Support for SQL

SQL has become the standard database language because it is flexible, powerful, and
easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and
DELETE make it easy to manipulate the data stored in a relational database.

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.
This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also,
PL/SQL fully supports SQL datatypes, reducing the need to convert data passed
between your applications and the database.

PL/SQL also supports dynamic SQL, a programming technique that makes your
applications more flexible and versatile. Your programs can build and process SQL
data definition, data control, and session control statements at run time, without
knowing details such as table names and WHERE clauses in advance.

Better Performance

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. As
Figure 1–1 shows, you can use PL/SQL blocks and subprograms to group SQL
statements before sending them to the database for execution. PL/SQL even 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.