PL/SQL
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's proprietary server-based procedural extension to the SQL database language. In its syntax it strongly resembles the Ada programming language.
Functionality
PL/SQL supports variables, conditions, arrays, and exceptions. Implementations from version 8 of the Oracle RDBMS onwards have emphasised object-orientation.
The underlying SQL functions as a declarative language. Standard SQL—unlike some functional programming languages—does not require implementations to convert tail calls to jumps. SQL does not readily provide "first row" and "rest of table" accessors, and it cannot easily perform some constructs such as loops. PL/SQL, however, as a Turing-complete procedural language which fills in these gaps, allows Oracle database developers to interface with the underlying relational database in an imperative manner. SQL statements can make explicit in-line calls to PL/SQL functions, or can cause PL/SQL triggers to fire upon pre-defined DML events.
PL/SQL stored procedures (functions, procedures, packages, and triggers) which perform DML get compiled into an Oracle database: to this extent their SQL code can undergo syntax-checking. Programmers working in an Oracle database environment can construct PL/SQL blocks of such functionality to serve as procedures, functions; or they can write in-line segments of PL/SQL within SQL*Plus scripts.
"Native Dynamic SQL" calls (or PL/SQL packages such as DBMS_SQL), in turn, allow the execution of SQL commands, including DDL, from within PL/SQL code.
Basic code structure
PL/SQL programs consist of blocks. Blocks take the general form:
declare -- Declaration block (optional) begin -- Program proper exception -- Exception-handling (optional) end;
- /* Sample comment spanning
- multiple lines... */
- --Single-line comment
Analogies
PL/SQL functions analogously to the embedded procedural languages associated with other relational databases. Sybase and its derivative Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which tries to emulate PL/SQL to an extent), and DB2 includes SQL Procedural Language (SQL PL).
Aside from some proprietary functions (notably Oracle's UTL_FILE package) PL/SQL code closely resembles Transact-SQL, allowing trivial conversion if the code does not use proprietary features. For non-trivial code conversion becomes much more complicated. Oracle Corporation does not provides tools to port from PL/SQL to Transact-SQL.
As an alternative to (and alongside) PL/SQL, Oracle programmers can also use methods written in Java.
References
- PL/pgSQL - SQL Procedural Language
- Steven Feuerstein (2002). Oracle PL/SQL Programming, 3rd ed. O'Reilly & Associates. ISBN 0596003811.
(Many consider this book the "bible" of PL/SQL development) - Oracle PL/SQL FAQ