Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Introducing PL/SQL


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

PL/SQL is the language of choice for data-centric application development in Oracle databases. But like any programming language, it can also be used to write inefficient and
overly resource intensive programs.

The aim of this book is to present necessary tools to increase the quality and efficiency of PL/SQL code.  This is not a “teach yourself to program PL/SQL” book, and as such requires some previous experience of the PL/SQL language.

Where possible, the examples in this book are totally self-contained, but some may require extra privileges not assigned to test users in a normal environment.  For this reason it may be useful to set up a test user specifically for the examples given throughout this book as shown below.

CONN sys/password AS SYSDBA 

CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
ALTER USER test QUOTA UNLIMITED ON users; 

GRANT CONNECT TO test;
GRANT CREATE PROCEDURE TO test;
GRANT CREATE TYPE TO test;
GRANT EXECUTE ON dbms_lock TO test;
GRANT SELECT_CATALOG_ROLE TO test;

Any additional privileges will be described on a case-by-case basis.

The rest of this chapter will focus on an overview of PL/SQL, which will help to put the rest of the book into context.  Hopefully, none of this information will be completely new.

What is PL/SQL and Why Should I use It?

In most programming languages, database work involves connecting to the server, mapping datatypes and manually preparing and processing result sets. PL/SQL is a procedural language that is so tightly integrated with the SQL language that most of these tasks are either eliminated completely or incredibly simple.

The datatypes available in PL/SQL are a superset of those available in SQL, so datatype conversions between SQL and PL/SQL are rarely needed.  As a result PL/SQL allows interaction with both the data and metadata of database objects with greater ease and efficiency than is possible with most other languages. In addition PL/SQL supports dynamic SQL allowing statements to be created at runtime for greater flexibility.

Running application logic as PL/SQL on the database server can increase efficiency by reducing network traffic.  When business logic is processed in a client application, it is often necessary to pass a succession of statements between the application and the database server.  Each request and response involves network traffic, which can greatly affect overall performance. 

Passing a PL/SQL block containing multiple statements to the server can reduce network round trips, thereby improving performance.  Storing application code in the database takes this a step further as application logic is removed from the client layer and precompiled in the database, allowing modification without the need for a redeployment of client software.

The PL/SQL language is available on all platforms supported by Oracle, making it significantly more portable than many programming languages.  When application logic is located within the database, changes in client programming models have a reduced impact, as only presentation of the data is controlled at that level.

Figure 1.1: Using PL/SQL to improve performance.

Centralizing application logic enables a higher degree of security and productivity.  The use of Application Program Interfaces (APIs) can abstract complex data structures and security implementations from client application developers, leaving them free to do what they do best.

Oracle has continued to improve support for object orientated programming in PL/SQL.  This is a great marketing feature, but in reality few client application tools cope natively with Oracle object types.  As a result, the usage of this feature is often limited by the client tools accessing the data.

PL/SQL Architecture

The PL/SQL language is made up of both procedural code and SQL statements.  When valid PL/SQL code is executed, the PL/SQL engine executes all procedural code and sends SQL statements to the SQL engine of the database server.  Figure 1.2 represents this process in action for a PL/SQL block.

Figure 1.2 – PL/SQL Architecture.

The Oracle database contains a PL/SQL engine, which is used to execute all stored procedures, functions, packages, objects and triggers.  This allows application logic to be processed entirely within the database layer. 

Some application development tools, such as Oracle Forms and Oracle Reports, have their own PL/SQL engine, allowing procedural logic to be processed with no reference to the database server.

Overview of PL/SQL Elements

Blocks in PL/SQL

Blocks are the organizational unit for all PL/SQL code, whether it is in the form of an anonymous block, procedure, function, trigger or type.  A PL/SQL block is made up of three sections: declaration, executable and exception. Only the executable section is mandatory.

[DECLARE
  -- delarations]
BEGIN
  -- statements
[EXCEPTION
  -- handlers
END;

Based on this definition, the simplest valid block is shown below, but it does not do anything.

BEGIN
  NULL;
END;

The optional declaration section allows variables, types, procedures and functions do be defined for use within the block.  The scope of these declarations is limited to the code within the block itself, or any nested blocks or procedure calls.  The limited scope of variable declarations is shown by the following two examples. In the first, a variable is declared in the outer block and is referenced successfully in a nested block.  In the second, a variable is declared in a nested block and referenced from the outer block, resulting in an error as the variable is out of scope.

DECLARE
  l_number  NUMBER;
BEGIN
  l_number := 1;

  BEGIN
    l_number := 2;
  END;
END;
/

PL/SQL procedure successfully completed.

BEGIN
  DECLARE
    l_number  NUMBER;
  BEGIN
    l_number := 1;
  END; 

  l_number := 2;
END;
/
  l_number := 2;
  *
ERROR at line 8:
ORA-06550: line 8, column 3:
PLS-00201: identifier 'L_NUMBER' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored 

SQL>

The main work is done in the mandatory executable section of the block, while the optional exception section is where all error processing is placed.  The following two examples demonstrate the usage of exception handlers for trapping error messages.  In the first, there is no exception handler so a query returning no rows results in an error. In the second, the same error is trapped by the exception handler, allowing the code to complete successfully.

DECLARE
  l_date  DATE;
BEGIN
  SELECT SYSDATE
  INTO   l_date
  FROM   dual
  WHERE  1=2; -- For zero rows
END;
/
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4 

DECLARE
  l_date  DATE;
BEGIN
  SELECT SYSDATE
  INTO   l_date
  FROM   dual
  WHERE  1=2; -- For zero rows
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

PL/SQL procedure successfully completed.

SQL>

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational