PL / SQL


PL / SQL (short for Procedural Language / Structured Query Language ) is a language based on the programming paradigms procedural and structured . It is proprietary , created by Oracle and used as part of relational databases . The general syntax resembles that of languages Pascal and Ada .

PL / SQL is available in Oracle Database (since version 7), TimesTen In-Memory Database (since version 11.2.1) and IBM DB2 (since version 9.7).

It allows to combine SQL queries and procedural instructions (loops, conditions …), with the aim of creating complex processes to be stored on the database server (server objects), such as stored procedures or Triggers .

The latest developments proposed by Oracle are based on an engine to create and manage objects containing methods and properties.

Basically , PL / SQL is an interpreted language , but since the 9i RC1 version, code can be compiled into machine code 1 . In version 9i of Oracle database, the code is converted to C and must be compiled into shared libraries ( DLL under Windows); in version 10g the machine code is stored in the catalog 2 and since version 11g is stored in the tablespace system after direct compilation 3 .

Comparison with other databases

MySQL and Mimer SQL propose an analogous language in the principle but more limited, the SQL / PSM of the standard SQL: 2003, and several DBMS use a derivative (IBM DB2 with SQL-PL, PostgreSQL with PL / pgSQL and PL / pgPSM ). As for Microsoft and Sybase , they use a competitor developed by Sybase , the Transact-SQL (T-SQL) .

Concepts

Blocks

Any PL / SQL program must be in blocks. Here is the general shape of a block.

<< Label >> - Optional
DECLARE - Optional Section
d é declarations
BEGIN - Mandatory Section, must contain at least one executable statement
implementations é supply
EXCEPTION - Optional Section
management of exceptions
END ;

A PL / SQL block includes a part dedicated to the definition of all the variables used in the block. It begins with the DECLARE statement and is optional. The main section begins with the BEGIN statement . It contains the instructions of the program. If a variable is used in this section when it has not been declared in the declaration section, the compiler will generate an error. Finally, the last section is called if there is an error in the previous section. It begins with the EXCEPTION statement and treats all the exceptions raised in the main section.

Structure of the code

PL / SQL allows you to group the instructions in procedures and functions , these terms have the same meaning as in Pascal : A function is a block of code taking settings and performs processing for a returned result, a procedure covers The same concept except that a procedure does not return a result. The arguments passed to procedures and functions can be given read-only ( IN ), write ( OUT ) or read-write ( IN OUT ) The developer can also choose the passage by value (normal behavior) or by reference ( NO COPY ).

Functions and procedures can be grouped together in units named packages , which are then accessible under the same namespace. In a manner similar to Pascal’s modules, creating a package requires writing a specification outlining the prototype of the package’s functions and procedures, as well as public variables and types . Secondly, the implementation of the package is done separately; It contains the code of procedures and functions as well as the declaration of private elements.

Version 8i introduced the concept of object and implementations from 9i support the object-oriented programming including the legacy . A PL / SQL class is described as a type. It has a structure similar to that of a PL / SQL package: first the declaration of the methods, then their implementation in the body .

Variables

The first types of variables available are SQL types supported by the Oracle server ( INTEGER , VARCHAR , …), but it is possible to define custom types.

Notes and references

  1. ↑ http://www.dbspecialists.com/files/presentations/native_plsql.html  [ archive ]
  2. ↑ http://www.scribd.com/doc/16654335/Plsql-New-in-11gr1  [ archive ]
  3. ↑ http://swervedba.wordpress.com/2011/07/  [ archive ]