Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks