View source for PL/SQL

{{Short description|Procedural extension for SQL and the Oracle relational database}}
{{multiple issues|
{{More footnotes needed|date=February 2008}}
{{More citations needed|date=January 2008}}
}}

'''PL/SQL''' ('''Procedural Language for SQL''') is [[Oracle Corporation]]'s [[Procedural programming|procedural]] [[programming language|extension]] for [[SQL]] and the [[Oracle Database|Oracle relational database]].  PL/SQL is available in Oracle Database (since version 6 - stored PL/SQL procedures/functions/packages/triggers since version 7), [[TimesTen|TimesTen in-memory database]] (since version 11.2.1), and [[IBM Db2]] (since version 9.7).<ref>{{cite web|author=Serge Rielau (srielau@ca.ibm.com), SQL Architect, STSM, IBM |url=http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/index.html |title=DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows |publisher=Ibm.com |access-date=2012-07-26}}</ref>  Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

PL/SQL includes procedural language elements such as [[Conditional (programming)|condition]]s and [[program loop|loop]]s, and can handle [[exception handling|exception]]s (run-time errors).  It allows the declaration of constants and [[variable (programming)|variable]]s, procedures, functions, packages, types and variables of those types, and triggers. [[Array data type|Array]]s are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.

The first public version of the PL/SQL definition<ref>Steven Feuerstein (1995), "Oracle PL/SQL Programming", 1st, First Edition.</ref> was in 1995. It implements the ISO [[SQL/PSM]] standard.<ref>{{Cite web|url=https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Oracle-Compliance-with-SQLPSM2011.html#GUID-651F9066-1511-407B-A002-C04AB2F2A534|title = Oracle Compliance with SQL/PSM}}</ref>

==PL/SQL program unit==
The main feature of SQL (non-procedural) is also its drawback: control statements ([[Conditional (computer programming)|decision-making]] or [[for loop|iterative control]]) cannot be used if only SQL is to be used. PL/SQL provides the functionality of other procedural programming languages, such as decision making, iteration etc. A PL/SQL program unit is one of the following: PL/SQL anonymous block, [[Procedure (computer science)|procedure]], [[Function (computer science)|function]], [[Modular programming|package]] specification, package body, trigger, type specification, type body, library.  Program units are the PL/SQL source code that is developed, compiled, and ultimately executed on the database.<ref>{{Cite web|url=https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a88856/c02servr.htm|title=Oracle Server Features|access-date=2020-09-21|publisher=Oracle}}</ref>

===PL/SQL anonymous block===
The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements.  A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.  These keywords divide the block into a declarative part, an executable part, and an exception-handling part.  The declaration section is optional and may be used to define and initialize constants and variables.  If a variable is not initialized then it defaults to [[Null (SQL)|NULL]] value.  The optional exception-handling part is used to handle run-time errors.  Only the executable part is required.  A block can have a label.<ref>{{Cite web|url=https://livesql.oracle.com/apex/livesql/file/tutorial_KS0KNKP218J86THKN85XU37.html|title=PL/SQL Anonymous Blocks|access-date=2020-09-21|publisher=Oracle}}</ref>

For example:

<syntaxhighlight lang="PLpgSQL">
<<label>>   -- this is optional
DECLARE
-- this section is optional
  number1 NUMBER(2);
  number2 number1%TYPE := 17;             -- value default
  text1   VARCHAR2(12) := '      Hello world       ';
  text2   DATE         := SYSDATE;        -- current date and time
BEGIN
-- this section is mandatory, must contain at least one executable statement
  SELECT street_number
    INTO number1
    FROM address
    WHERE name = 'INU';
EXCEPTION
-- this section is optional
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode));
     DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm);
END;
</syntaxhighlight>

The symbol <code>:=</code> functions as an [[assignment operator]] to store a value in a variable.

Blocks can be nested – i.e., because a block is an executable statement, it can appear in another block wherever an executable statement is allowed.  A block can be submitted to an interactive tool (such as SQL*Plus) or embedded within an Oracle Precompiler or [[Oracle Call Interface|OCI]] program. The interactive tool or program runs the block once.  The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label).

===Function===
The purpose of a PL/SQL function is generally used to compute and return a single value. This returned value may be a single scalar value (such as a number, date or character string) or a single collection (such as a nested table or array).  [[User-defined function]]s supplement the built-in functions provided by Oracle Corporation.<ref>{{Cite web|url=https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm|title=Database SQL Reference|access-date=2020-09-21|publisher=Oracle}}</ref>

The PL/SQL function has the form:

<syntaxhighlight lang="PLpgSQL">
CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] <IS|AS>   -- heading part
amount number;   -- declaration block
BEGIN   -- executable part
	<PL/SQL block with return statement>
        RETURN <return_value>;
[Exception
	none]
        RETURN <return_value>;
END;
</syntaxhighlight>

Pipe-lined table functions return collections<ref>
{{cite book
| first1 = Arup
| last1 = Nanda
| first2 =  Steven
| last2 = Feuerstein
| title         = Oracle PL/SQL for DBAs
| url           = https://books.google.com/books?id=-wFDRRSxQFMC
| access-date    = 2011-01-11
| series    = O'Reilly Series
| year      = 2005
| publisher = O'Reilly Media, Inc.
| isbn      = 978-0-596-00587-0
| pages      = 122, 429
| quote         = A pipelined table function [...] returns a result set as a collection [...] iteratively. [... A]s each row is ready to be assigned to the collection, it is 'piped out' of the function.
}}
</ref>
and take the form:

<syntaxhighlight lang="PLpgSQL">
CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING>
	[declaration block]
BEGIN
	<PL/SQL block with return statement>
        PIPE ROW <return type>;
        RETURN;
[Exception
	exception block]
        PIPE ROW <return type>;
        RETURN;
END;
</syntaxhighlight>

A function should only use the default IN type of parameter.  The only out value from the function should be the value it returns.

===Procedure===
Procedures resemble functions in that they are named program units that can be invoked repeatedly. The primary difference is that '''functions can be used in a SQL statement whereas procedures cannot'''. Another difference is that the procedure can return multiple values whereas a function should only return a single value.<ref>{{Cite web|url=https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm|title=Database SQL Reference|access-date=2020-09-21|publisher=Oracle}}</ref>

The procedure begins with a mandatory heading part to hold the procedure name and optionally the procedure parameter list. Next come the declarative, executable and exception-handling parts, as in the PL/SQL Anonymous Block. A simple procedure might look like this:

<syntaxhighlight lang="PLpgSQL">
CREATE PROCEDURE create_email_address ( -- Procedure heading part begins
    name1 VARCHAR2,
    name2 VARCHAR2,
    company VARCHAR2,
    email OUT VARCHAR2
) -- Procedure heading part ends
AS
-- Declarative part begins (optional)
error_message VARCHAR2(30) := 'Email address is too long.';
BEGIN -- Executable part begins (mandatory)
    email := name1 || '.' || name2 || '@' || company;
EXCEPTION -- Exception-handling part begins (optional)
WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE(error_message);
END create_email_address;
</syntaxhighlight>

The example above shows a standalone procedure - this type of procedure is created and stored in a database schema using the CREATE PROCEDURE statement.  A procedure may also be created in a PL/SQL package - this is called a Package Procedure.  A procedure created in a PL/SQL anonymous block is called a nested procedure.  The standalone or package procedures, stored in the database, are referred to as "[[stored procedure]]s".

Procedures can have three types of parameters: IN, OUT and IN OUT.

# An IN parameter is used as input only. An IN parameter is passed by reference, though it can be changed by the inactive program.
# An OUT parameter is initially NULL. The program assigns the parameter value and that value is returned to the calling program.
# An IN OUT parameter may or may not have an initial value. That initial value may or may not be modified by the called program. Any changes made to the parameter are returned to the calling program by default by copying but - with the NO-COPY hint - may be passed [[call by reference|by reference]].

PL/SQL also supports external procedures via the Oracle database's standard <code>ext-proc</code> process.<ref>
{{cite book
| last1 = Gupta
| first1 = Saurabh K.
| year = 2012
| chapter = 5: Using Advanced Interface Methods
| title = Advanced Oracle PL/SQL Developer's Guide
| url = https://books.google.com/books?id=Hq1KDAAAQBAJ
| series = Professional experience distilled
| edition = 2
| location = Birmingham
| publisher = Packt Publishing Ltd
| publication-date = 2016
| page = 143
| isbn = 9781785282522
| access-date = 2017-06-08
| quote = Whenever the PL/SQL runtime engine encounters an external procedure call, the Oracle Database starts the extproc process. The database passes on the information received from the call specification to the <code>extproc</code> process, which helps it to locate the external procedure within the library and execute it using the supplied parameters. The <code>extproc</code> process loads the dynamic linked library, executes the external procedure, and returns the result back to the database.
}}
</ref>

===Package===
Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors, etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification.
Two advantages of packages are:<ref>{{Cite web|url=https://docs.oracle.com/cd/B19306_01/B14251_01/adfns_packages.htm#ADFNS009|title=Coding PL/SQL Procedures and Packages|access-date=2020-09-21|publisher=Oracle}}</ref>
# Modular approach, encapsulation/hiding of [[business logic]], security, performance improvement, re-usability. They support [[object-oriented programming]] features like [[function overloading]] and encapsulation.
# Using package variables one can declare session level (scoped) variables since variables declared in the package specification have a session scope.

===Trigger===
{{main|Database trigger}}
A [[database trigger]] is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs.  It is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it—that is, the trigger fires—whenever its triggering event occurs. While a trigger is disabled, it does not fire.

You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements, and the item they act on. The trigger is said to be created on or defined on the item—which is either a table, a [[view (database)|view]], a schema, or the database. You also specify the timing point, which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects.

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger. If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger.

An INSTEAD OF trigger is either: A DML trigger created on a view or a system trigger defined on a CREATE statement.  The database fires the INSTEAD OF trigger instead of running the triggering statement.

====Purpose of triggers====
Triggers can be written for the following purposes:

* Generating some [[derived column]] values automatically
* Enforcing referential integrity
* Event logging and storing information on table access
* Auditing
* Synchronous replication of tables
* Imposing security authorizations
* Preventing invalid transactions

== Data types ==
The major [[datatype]]s in PL/SQL include NUMBER, CHAR, VARCHAR2, DATE and TIMESTAMP.

=== Numeric variables ===
<syntaxhighlight lang="plpgsql">variable_name number([P, S]) := 0;</syntaxhighlight>

To define a numeric variable, the programmer appends the variable type '''NUMBER''' to the name definition.
To specify the (optional) precision (P) and the (optional) scale (S), one can further append these in round brackets, separated by a comma. ("Precision" in this context refers to the number of digits the variable can hold, and "scale" refers to the number of digits that can follow the decimal point.)

A selection of other data-types for numeric variables would include:
binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, small-int, binary_integer.

=== Character variables ===
<syntaxhighlight lang="plpgsql">
variable_name varchar2(20) := 'Text';

-- e.g.: 
address varchar2(20) := 'lake view road';
</syntaxhighlight>
To define a character variable, the programmer normally appends the variable type VARCHAR2 to the name definition. There follows in brackets the maximum number of characters the variable can store.

Other datatypes for character variables include: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob, and bfile.

=== Date variables===
<syntaxhighlight lang="plpgsql">variable_name date := to_date('01-01-2005 14:20:23', 'DD-MM-YYYY hh24:mi:ss');</syntaxhighlight>

Date variables can contain date and time. The time may be left out, but there is no way to define a variable that only contains the time. There is no DATETIME type. And there is a TIME type. But there is no TIMESTAMP type that can contain fine-grained timestamp up to millisecond or nanosecond.
The <code>TO_DATE</code> function can be used to convert strings to date values. The function converts the first quoted string into a date, using as a definition the second quoted string, for example:

<syntaxhighlight lang="plpgsql">
 to_date('31-12-2004', 'dd-mm-yyyy')
</syntaxhighlight>
or

<syntaxhighlight lang="plpgsql">
 to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')
</syntaxhighlight>

To convert the dates to strings one uses the function <code>TO_CHAR (date_string, format_string)</code>.

PL/SQL also supports the use of ANSI date and interval literals.<ref name=OraDTLit>
{{cite web
|url= http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#sthref365
|access-date= 2009-03-20
|title= Literals
|work= Oracle iSQL Reference 10g Release 2 (10.2)
|publisher= Oracle
}}</ref> The following clause gives an 18-month range:

<syntaxhighlight lang="plpgsql">
WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH
    AND DATE '2004-12-30'
</syntaxhighlight>

=== Exceptions ===
Exceptions—errors during code execution—are of two types: user-defined and predefined.

''User-defined'' exceptions are always raised explicitly by the programmers, using the <code>RAISE</code> or <code>RAISE_APPLICATION_ERROR</code> commands, in any situation where they determine it is impossible for normal execution to continue. The <code>RAISE</code> command has the syntax:
<syntaxhighlight lang="plpgsql">RAISE <exception name>;</syntaxhighlight>
Oracle Corporation has ''predefined'' several exceptions like <code>NO_DATA_FOUND</code>, <code>TOO_MANY_ROWS</code>, ''etc.''
Each exception has an SQL error number and SQL error message associated with it. Programmers can access these by using the <code>SQLCODE</code> and <code>SQLERRM</code> functions.

=== Datatypes for specific columns ===
 Variable_name <span style="color:#B8860B">Table_name</span><span style="color:#00f;">.</span>Column_name<span style="color:#00f;">%type;</span>

This syntax defines a variable of the type of the referenced column on the referenced tables.

Programmers specify user-defined datatypes with the syntax:

<syntaxhighlight lang="plpgsql">type data_type is record (field_1 type_1 := xyz, field_2 type_2 := xyz, ..., field_n type_n := xyz);</syntaxhighlight>

For example:

<syntaxhighlight lang="plpgsql">
declare
    type t_address is record (
        name address.name%type,
        street address.street%type,
        street_number address.street_number%type,
        postcode address.postcode%type);
    v_address t_address;
begin
    select name, street, street_number, postcode into v_address from address where rownum = 1;
end;
</syntaxhighlight>

This sample program defines its own datatype, called ''t_address'', which contains the fields ''name, street, street_number'' and ''postcode''.

So according to the example, we are able to copy the data from the database to the fields in the program.

Using this datatype the programmer has defined a variable called ''v_address'' and loaded it with data from the ADDRESS table.

Programmers can address individual attributes in such a structure by means of the dot-notation, thus:
 v_address.street := 'High Street';

==Conditional statements==
The following code segment shows the IF-THEN-ELSIF-ELSE construct. The ELSIF and ELSE parts are optional so it is possible to create simpler IF-THEN or, IF-THEN-ELSE constructs.

<syntaxhighlight lang="PLpgSQL">
IF x = 1 THEN
   sequence_of_statements_1;
ELSIF x = 2 THEN
   sequence_of_statements_2;
ELSIF x = 3 THEN
   sequence_of_statements_3;
ELSIF x = 4 THEN
   sequence_of_statements_4;
ELSIF x = 5 THEN
   sequence_of_statements_5;
ELSE
   sequence_of_statements_N;
END IF;
</syntaxhighlight>

The CASE statement simplifies some large IF-THEN-ELSIF-ELSE structures.

<syntaxhighlight lang="PLpgSQL">
CASE
   WHEN x = 1 THEN sequence_of_statements_1;
   WHEN x = 2 THEN sequence_of_statements_2;
   WHEN x = 3 THEN sequence_of_statements_3;
   WHEN x = 4 THEN sequence_of_statements_4;
   WHEN x = 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;
</syntaxhighlight>

CASE statement can be used with predefined selector:

<syntaxhighlight lang="PLpgSQL">
CASE x
   WHEN 1 THEN sequence_of_statements_1;
   WHEN 2 THEN sequence_of_statements_2;
   WHEN 3 THEN sequence_of_statements_3;
   WHEN 4 THEN sequence_of_statements_4;
   WHEN 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;
</syntaxhighlight>

==Array handling==
PL/SQL refers to [[Array data type|array]]s as "collections". The language offers three types of collections:
# [[Associative array]]s (Index-by tables)
# Nested tables
# Varrays (variable-size arrays)

Programmers must specify an upper limit for varrays, but need not for index-by tables or for nested tables. The language includes several collection [[method (computer science)|method]]s used to manipulate collection elements: for example FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc. Index-by tables can be used to simulate associative arrays, as in this [[Ackermann function#cite note-10|example of a memo function for Ackermann's function in PL/SQL]].

===Associative arrays (index-by tables)===

With index-by tables, the array can be indexed by numbers or strings. It parallels a [[Java (programming language)|Java]] ''map'', which comprises key-value pairs. There is only one dimension and it is unbounded.

===Nested tables===

With [[Nested SQL|nested tables]] the programmer needs to understand what is nested. Here, a new type is created that may be composed of a number of components. That type can then be used to make a column in a table, and nested within that column are those components.

===Varrays (variable-size arrays)===

With Varrays you need to understand that the word "variable" in the phrase "variable-size arrays" doesn't apply to the size of the array in the way you might think that it would. The size the array is declared with is in fact fixed. The number of elements in the array is variable up to the declared size. Arguably then, variable-sized arrays aren't that variable in size.

==Cursors==
A [[Cursor (databases)|cursor]] is a pointer to a private SQL area that stores information coming from a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). A [[Cursor (databases)|cursor]] holds the rows (one or more) returned by a SQL statement. 
The set of rows the [[Cursor (databases)|cursor]] holds is referred to as the active set.<ref>{{cite web|url=http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html|title=Working with Cursors|first=Steven|last=Feuerstein|website=oracle.com}}</ref>

A [[Cursor (databases)|cursor]] can be explicit or implicit. In a FOR loop, an explicit cursor shall be used if the query will be reused, otherwise an implicit cursor is preferred. If using a cursor inside a loop, use a FETCH is recommended when needing to bulk collect or when needing dynamic SQL.

==Looping==
As a procedural language by definition, PL/SQL provides several [[iteration]] constructs, including basic LOOP statements, [[while loop|WHILE loop]]s, [[for loop|FOR loop]]s, and Cursor FOR loops. Since Oracle 7.3 the REF CURSOR type was introduced to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types.

===LOOP statements===

<syntaxhighlight lang="PLpgSQL">
<<parent_loop>>
LOOP
	statements

	<<child_loop>>
	loop
		statements
		exit parent_loop when <condition>; -- Terminates both loops
		exit when <condition>; -- Returns control to parent_loop
	end loop child_loop;
        if <condition> then
           continue; -- continue to next iteration
        end if;

	exit when <condition>;
END LOOP parent_loop;
</syntaxhighlight><ref>{{cite web|url=http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/controlstructures.htm#sthref921|title=Database PL/SQL User's Guide and Reference|website=download.oracle.com}}</ref>
Loops can be terminated by using the <code>EXIT</code> [[Keyword (computer programming)|keyword]], or by raising an [[Exception handling|exception]].

===FOR loops===
<syntaxhighlight lang="plpgsql">
DECLARE
    var NUMBER;
BEGIN
    /* N.B. for loop variables in PL/SQL are new declarations, with scope only inside the loop */
    FOR var IN 0 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;

    IF var IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('var is null');
    ELSE
        DBMS_OUTPUT.PUT_LINE('var is not null');
    END IF;
END;
</syntaxhighlight>
Output:
  0
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  var is null

===Cursor FOR loops===
<syntaxhighlight lang="plpgsql">
FOR RecordIndex IN (SELECT person_code FROM people_table)
LOOP
  DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;
</syntaxhighlight>

Cursor-for loops automatically open a [[Cursor (databases)|cursor]], read in their data and close the cursor again.

As an alternative, the PL/SQL programmer can pre-define the cursor's SELECT-statement in advance to (for example) allow re-use or make the code more understandable (especially useful in the case of long or complex queries).

<syntaxhighlight lang="plpgsql">
DECLARE
  CURSOR cursor_person IS
    SELECT person_code FROM people_table;
BEGIN
  FOR RecordIndex IN cursor_person
  LOOP
    DBMS_OUTPUT.PUT_LINE(recordIndex.person_code);
  END LOOP;
END;
</syntaxhighlight>

The concept of the person_code within the FOR-loop gets expressed with dot-notation ("."):

<syntaxhighlight lang="plpgsql">
RecordIndex.person_code
</syntaxhighlight>

==Dynamic SQL==
While programmers can readily embed [[Data Manipulation Language]] (DML) statements directly into PL/SQL code using straightforward SQL statements, [[Data Definition Language]] (DDL) requires more complex "Dynamic SQL" statements in the PL/SQL code. However, DML statements underpin the majority of PL/SQL code in typical software applications.

In the case of PL/SQL dynamic SQL, early versions of the Oracle Database required the use of a complicated Oracle <code>DBMS_SQL</code> package library. More recent versions have however introduced a simpler "Native Dynamic SQL", along with an associated <code>EXECUTE IMMEDIATE</code> syntax.

==Similar languages==
PL/SQL works analogously to the embedded procedural languages associated with other [[relational database]]s. For example, [[Sybase]] [[Adaptive Server Enterprise|ASE]] and [[Microsoft]] [[Microsoft SQL Server|SQL Server]] have [[Transact-SQL]], [[PostgreSQL]] has [[PL/pgSQL]] (which emulates PL/SQL to an extent), [[MariaDB]] includes a PL/SQL compatibility parser,<ref>{{cite web|url=https://mariadb.org/wp-content/uploads/2018/02/Whats-New-in-MariaDB-10.3-Unconference.pdf |title=What's New in MariaDB Server 10.3 |publisher=MariaDB|access-date=2018-08-21}}</ref> and [[IBM Db2]] includes SQL Procedural Language,<ref>{{cite web|url=http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011916.htm |title=SQL PL |publisher=Publib.boulder.ibm.com |access-date=2012-07-26}}</ref> 
which conforms to the [[SQL|ISO SQL]]’s [[SQL/PSM]] standard.

The designers of PL/SQL modeled its syntax on that of [[Ada programming language|Ada]]. Both Ada and PL/SQL have [[Pascal programming language|Pascal]] as a common ancestor, and so PL/SQL also resembles Pascal in most aspects. However, the structure of a PL/SQL package does not resemble the basic [[Object Pascal]] program structure as implemented by a [[Borland Delphi]] or [[Free Pascal]] unit. Programmers can define public and private global data-types, constants, and static variables in a PL/SQL package.<ref>Oracle documentation [http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/packages.htm#LNPLS00913 "Private and public items in PL/SQL"]</ref>

PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in [[object-oriented programming]] languages like [[Object Pascal]], [[C++]] and [[Java (programming language)|Java]]. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an [[Oracle database|Oracle]] SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the [[Oracle database|Oracle]] SQL Engine and the [[Oracle database|Oracle]] PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables.

PL/SQL is fundamentally distinct from [[Transact-SQL]], despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,<ref>{{Cite web|url=http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm|title=Migrating from Oracle to SQL Server - T-SQL, PL/SQL differences: Narayana Vyas Kondreddi's home page|website=vyaskn.tripod.com}}</ref> but also due to the very significant differences in the way Oracle and SQL Server deal with [[Concurrency control|concurrency]] and [[Lock (computer science)|lock]]ing.

The ''StepSqlite'' product is a PL/SQL compiler for the popular small database [[SQLite]] which supports a subset of PL/SQL syntax. Oracle's [[Berkeley DB]] 11g R2 release added support for [[SQL]] based on the popular SQLite API by including a version of SQLite in Berkeley DB.<ref>{{Cite web|url=https://twitter.com/gregburd/statuses/10979336891|title=@humanications We didn't re-implement the SQLite API, we include a version of SQLite which uses Berkeley DB for storage (replacing btree.c).|first=Gregory|last=Burd|date=March 24, 2010}}</ref> Consequently, StepSqlite can also be used as a third-party tool to run PL/SQL code on Berkeley DB.<ref>{{cite web|title=Official Berkeley DB FAQ|url=http://www.oracle.com/technetwork/database/berkeleydb/db-faq-095848.html#DoesBerkeleyDBsupportPLSQL|publisher=[[Oracle Corporation]] |quote=Does Berkeley DB support PL/SQL? |access-date=March 30, 2010}}</ref>

==See also==
* [[Transact-SQL|T-SQL]]
* [[SQL PL]]
* [[SQL/PSM]]
* [[:Category:PL/SQL editors|PL/SQL editors]]
* [[Relational database management system]]s

== References ==
{{Reflist}}

==Further reading==
* {{cite book |last= Feuerstein|first= Steven|author-link= Steven Feuerstein|author2=Bill Pribyl |title= Oracle PL/SQL Programming|edition= 6th|year= 2014|publisher= [[O'Reilly & Associates]]|isbn= 978-1449324452}}
*{{cite web
| url = http://www.orafaq.com/faqplsql.htm
| title = Oracle PL/SQL FAQ rev 2.08
| last = Naudé
| first = Frank
| date = June 9, 2005
}}

== External links ==
{{Wikibooks|Oracle Programming|SQL Cheatsheet}}
<!-- NOTE
This is not the place for links to each and every website that may be helpful
to a PL/SQL programmer!
Spam links WILL be removed. Thanks!
-->
* [http://www.orafaq.com/wiki/PL/SQL_FAQ Oracle FAQ: PL/SQL]
* [http://www.oracle.com/technology/tech/pl_sql/index.html Oracle Technology Center]
* [http://tahiti.oracle.com Oracle Tahiti Search Engine]
* [http://www.morganslibrary.org/library.html Morgan's Library]
* [http://www.oracle-base.com/articles/plsql/ArticlesPLSQL.php ORACLE-BASE: PL/SQL Articles]

{{Oracle}}

{{Authority control}}

{{DEFAULTSORT:Pl Sql}}
[[Category:Ada programming language family]]
[[Category:Data-centric programming languages]]
[[Category:Oracle software]]
[[Category:SQL]]