 |
|
ss
Oracle Tips by Burleson |
Introduction to PL/SQL
Databases have been in use long before the
personal computer arrived on the scene. IBM developed the
Structured Query Language standard (dubbed SQL, and
pronounced “See-Quel”) over 30 years ago as a way to retrieve data
from their new “relational” database. A decade later, Oracle released
the first commercial relational database that used SQL, and SQL has
become the de-facto query language for the vast majority of popular
database products.
Even though SQL
is the standard language for
interacting with any modern database, it does not mean that SQL is
without limitations. If we want to retrieve a set of records from the
database and modify them according to a set of rules, (updating some
and returning to the calling program others), we can’t do this with a
single SQL call to the database. Complex processing requires the
ability to compare values (often called Boolean logic)
and implement programmatic flow control. In other words, some type
of programming language was required to process the returned rows and
implement the program rules. To achieve this capability, Oracle
introduced the Procedural Language extensions to the Structured Query
Language or PL/SQL.
Oracle PL/SQL
was based on the ADA programming
language which was developed by the Department of Defense to be used
on mission critical systems. Although not a “sexy” language like Java or C, ADA is
still being develop and used for applications such as aircraft control
systems. ADA is a highly structured, strongly typed programming
language that uses natural language constructs to make it easy to
understand. The PL/SQL language inherited these attributes making
PL/SQL easier to read and maintain than more cryptic languages such as
C. For example, below are two loops, one in PL/SQL and the other in a
programming language called C.
for x in v_start..v_finish --PL/SQL
loop
v_int := v_int +1;
end loop;
As opposed to
for (x = str; x< fin; x++)
{i++} --C
As we see, the PL/SQL statement is
more verbose but also easier to understand.
PL/SQL
is also portable within the Oracle
database family and runs on all supported Oracle platforms including
Oracle10g grid database. Even more important is platform
independence, where programs developed in PL/SQL on a Windows Oracle
database will load and run in a UNIX Oracle database. With each
release of the Oracle database, Oracle Corporation
enhances the capabilities and performance of PL/SQL. Remember,
PL/SQL is an Oracle only product, and no other database management
system will run PL/SQL.
Unlike other languages that execute externally,
PL/SQL executes inside the database. This means that you can
take advantage of PL/SQL’s exceptional ability to manipulate data in
the database without paying the network penalty of retrieving the data
out of the database and them updating it back to the database.
Because PL/SQL runs inside the database it takes advantage of the
capabilities and capacity of the database server.
Traditionally, PL/SQL has been a
compiled/interpreted language similar to Java. When
PL/SQL code is loaded into the database it is compiled into an
intermediate form, similar to the way Java is compiled into
byte-code. This intermediate code is portable across Oracle
databases. Later versions of Oracle (Oracle9i and 10g)
will compile PL/SQL into native code for over 60 hardware platforms.
This natively compiled code runs more efficiently, but it loses the
ability to move to other Oracle databases without recompiling.
Lastly, placing the code that interacts with the
database in PL/SQL makes better use of the database resources. PL/SQL
packages are loaded as a package so as your program calls for
data, the procedures and functions are already in cached memory.
Using PL/SQL will also result in your application using bind
variables. The only way not to use bind variables in PL/SQL is to
implement dynamic SQL
(discussed in Chapter 5). The
Database Administrator (DBA) also benefits when developers place their
SQL inside PL/SQL because they have access to the statements for
tuning. For example the DBA can tune the SQL (adding hints,
reordering the WHERE clause
) without impacting the existing
application. Placing SQL inside PL/SQL also allows the code to be
protected by the recovery capabilities of the Oracle database.
PL/SQL
is the most common language for
Oracle the world, and developers are realizing the benefits in both
application performance and database performance by implementing the
database interaction in PL/SQL. There are even websites built
entirely using PL/SQL. For example, Oracle’s HTML-DB product
is installed in the database and consists primarily of PL/SQL packages
and Javascripts.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|
