||Oracle Concepts by Burleson
The Evolution and Features of PL/SQL
We begin our theme with a discussion of the
evolution and features of PL/SQL by investigating programming within
database applications. We will introduce how PL/SQL allows for stored
programming and the ability of PL/SQL to be executed independently
from a client-side application. We also introduce the origins of the
PL/SQL language and take a look at how the PL/SQL language has evolved
over the past decade into a robust database programming language. We
also introduce the concepts of modular construction of PL/SQL code and
look at support for the object-oriented model with than PL/SQL.
We will also describe how PL/SQL can be placed
into anonymous code snippets, stored procedures, and functions within
the domain of the database engine. We also then take a look at how
PL/SQL stored procedures and functions can be encapsulated into PL/SQL
packages, which can then be cached inside the database and executed at
very high speeds.
Oracle has a commitment to continually improve
the functionality, and PL/SQL has become a very powerful procedural
language. Of all of the features of PL/SQL, the most important are:
1 – Tight coupling with the database –
Unlike other procedural languages such as C++, PL/SQL has direct
interfaces into the database. In PL/SQL, you can place SQL directly
inside the PL/SQL program without the burden of working with an
Application Programming Interface (API).
2 – Code is stored inside the database –
Because PL/SQL is stored inside the Oracle data dictionary, the code
can be cached inside Oracle for very fast execution.
3 – Code independence – Oracle PL/SQL code
can be encapsulated into stored procedures and functions that can be
easily invoked from external programs.
4 – Package constructs – PL/SQL stored
procedures and functions can be placed directly inside packages.
We should now have a complete understanding of
the properties of SQL and PL/SQL in a Oracle database environment. We
have now covered the basics of the interaction of SQL with the
relational database and learned important techniques for monitoring
and changing the behavior of SQL statements. We also examined the
main features of PL/SQL and learned why PL/SQL is a popular choice for
programming Oracle-based applications.
The main points of this tutorial include:
- SQL is far easier to use than navigational
database languages because the SQL optimizer automatically
determines the access path to the data.
- The SQL optimizer determines the optimal
execution plan by looking at table and index statistics inside the
- The goals of SQL tuning are to search for
inefficient SQL: inside the library cache and tune the SQL by adding
indexes, using hints, of re-writing the query.
- The PL/SQL language is a powerful
Oracle-centric language that has become very popular because if its
tight-coupling with the Oracle database and built-in API. PL/SQL is
extremely popular because the procedural code can be stored directly
inside the Oracle database. Once a PL/SQL procedure is defined to
the database, external programs can call the procedure and invoke
the PL/SQL code.
We are now ready to move on to the next tutorial
titled “Basic SQL data access”. In this tutorial we will understand
some advanced SQL query features and learn the internal mechanisms for
processing SQL inside the Oracle database.
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA