[SQL / PLSQL] Introducing PL/SQL

 

Introducing PL/SQL

 

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.

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;

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.

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 serverThe 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.