HOME

TheInfoList



OR:

PL/SQL (Procedural Language for SQL) is
Oracle Corporation Oracle Corporation is an American Multinational corporation, multinational computer technology company headquartered in Austin, Texas. Co-founded in 1977 in Santa Clara, California, by Larry Ellison, who remains executive chairman, Oracle was ...
's procedural
extension Extension, extend or extended may refer to: Mathematics Logic or set theory * Axiom of extensionality * Extensible cardinal * Extension (model theory) * Extension (proof theory) * Extension (predicate logic), the set of tuples of values that ...
for
SQL Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel") is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
and the Oracle relational database. PL/SQL is available in Oracle Database (since version 6 - stored PL/SQL procedures/functions/packages/triggers since version 7), TimesTen in-memory database (since version 11.2.1), and IBM Db2 (since version 9.7). Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops, and can handle exceptions (run-time errors). It allows the declaration of constants and variables, procedures, functions, packages, types and variables of those types, and triggers.
Array An array is a systematic arrangement of similar objects, usually in rows and columns. Things called an array include: {{TOC right Music * In twelve-tone and serial composition, the presentation of simultaneous twelve-tone sets such that the ...
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 Object-oriented may refer to: * Object-oriented programming, a programming paradigm ** Object-oriented analysis and design ** Object-oriented database ** Object-oriented operating system ** Object-oriented role analysis and modeling *** Object-orie ...
. 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 was in 1995. It implements the ISO
SQL/PSM SQL/PSM (SQL/Persistent Stored Modules) is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92 (ISO/IEC 9075-4:1996, a version sometimes ...
standard.


PL/SQL program unit

The main feature of SQL (non-procedural) is also its drawback: control statements (
decision-making In psychology, decision-making (also spelled decision making and decisionmaking) is regarded as the Cognition, cognitive process resulting in the selection of a belief or a course of action among several possible alternative options. It could be ...
or 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,
function Function or functionality may refer to: Computing * Function key, a type of key on computer keyboards * Function model, a structured representation of processes in a system * Function object or functor or functionoid, a concept of object-orie ...
, 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.


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 Null may refer to: Science, technology, and mathematics Astronomy *Nuller, an optical tool using interferometry to block certain sources of light Computing *Null (SQL) (or NULL), a special marker and keyword in SQL indicating that a data value do ...
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. For example: < The symbol := functions as an
assignment operator Assignment, assign or The Assignment may refer to: * Homework * Sex assignment * The process of sending National Basketball Association players to its development league; see Computing * Assignment (computer science), a type of modification to ...
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 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 A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its c ...
s supplement the built-in functions provided by Oracle Corporation. The PL/SQL function has the form: CREATE OR REPLACE FUNCTION input/output variable declarations)RETURN return_type DEFINER> -- heading part amount number; -- declaration block BEGIN -- executable part RETURN ; xception none RETURN ; END; Pipe-lined table functions return collections and take the form: CREATE OR REPLACE FUNCTION input/output variable declarations)RETURN return_type DEFINER> PIPELINED> eclaration blockBEGIN PIPE ROW ; RETURN; xception exception block PIPE ROW ; RETURN; END; 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. 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: 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; 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 A stored procedure (also termed prc, proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database d ...
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 by reference. PL/SQL also supports external procedures via the Oracle database's standard ext-proc process.


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: # Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support
object-oriented programming Object-oriented programming (OOP) is a programming paradigm based on the concept of '' objects''. Objects can contain data (called fields, attributes or properties) and have actions they can perform (called procedures or methods and impl ...
features like
function overloading In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations. Calls to an overloaded function will run a specific implementation of that f ...
and encapsulation. # Using package variables one can declare session level (scoped) variables since variables declared in the package specification have a session scope.


Trigger

A
database trigger A database trigger is procedural code that is automatically executed in response to certain Event (computing), events on a particular Table (database), table or View (database), view in a database. The trigger is mostly used for maintaining the Dat ...
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, 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 Derive may refer to: *Derive (computer algebra system), a commercial system made by Texas Instruments * ''Dérive'' (magazine), an Austrian science magazine on urbanism *Dérive, a psychogeographical concept *Derived trait, or apomorphy See also ...
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 In computer science and computer programming, a data type (or simply type) is a collection or grouping of data values, usually specified by a set of possible values, a set of allowed operations on these values, and/or a representation of these ...
s in PL/SQL include NUMBER, CHAR, VARCHAR2, DATE and TIMESTAMP.


Numeric variables

variable_name number(
, S The comma is a punctuation mark that appears in several variants in different languages. Some typefaces render it as a small line, slightly curved or straight, but inclined from the vertical; others give it the appearance of a miniature fille ...
:= 0;
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

variable_name varchar2(20) := 'Text'; -- e.g.: address varchar2(20) := 'lake view road'; 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

variable_name date := to_date('01-01-2005 14:20:23', 'DD-MM-YYYY hh24:mi:ss'); 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 TO_DATE 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: to_date('31-12-2004', 'dd-mm-yyyy') or to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American') To convert the dates to strings one uses the function TO_CHAR (date_string, format_string). PL/SQL also supports the use of ANSI date and interval literals. The following clause gives an 18-month range: WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH AND DATE '2004-12-30'


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 RAISE or RAISE_APPLICATION_ERROR commands, in any situation where they determine it is impossible for normal execution to continue. The RAISE command has the syntax: RAISE ; Oracle Corporation has ''predefined'' several exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, ''etc.'' Each exception has an SQL error number and SQL error message associated with it. Programmers can access these by using the SQLCODE and SQLERRM functions.


Datatypes for specific columns

Variable_name Table_name.Column_name%type; This syntax defines a variable of the type of the referenced column on the referenced tables. Programmers specify user-defined datatypes with the syntax: type data_type is record (field_1 type_1 := xyz, field_2 type_2 := xyz, ..., field_n type_n := xyz); For example: 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; 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. 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; The CASE statement simplifies some large IF-THEN-ELSIF-ELSE structures. 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; CASE statement can be used with predefined selector: 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;


Array handling

PL/SQL refers to
array An array is a systematic arrangement of similar objects, usually in rows and columns. Things called an array include: {{TOC right Music * In twelve-tone and serial composition, the presentation of simultaneous twelve-tone sets such that the ...
s as "collections". The language offers three types of collections: #
Associative array In computer science, an associative array, key-value store, map, symbol table, or dictionary is an abstract data type that stores a collection of (key, value) pairs, such that each possible key appears at most once in the collection. In math ...
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 Method (, methodos, from μετά/meta "in pursuit or quest of" + ὁδός/hodos "a method, system; a way or manner" of doing, saying, etc.), literally means a pursuit of knowledge, investigation, mode of prosecuting such inquiry, or system. In re ...
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 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 Java is one of the Greater Sunda Islands in Indonesia. It is bordered by the Indian Ocean to the south and the Java Sea (a part of Pacific Ocean) to the north. With a population of 156.9 million people (including Madura) in mid 2024, proje ...
''map'', which comprises key-value pairs. There is only one dimension and it is unbounded.


Nested tables

With 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 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 holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. A 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 Iteration is the repetition of a process in order to generate a (possibly unbounded) sequence of outcomes. Each repetition of the process is a single iteration, and the outcome of each iteration is then the starting point of the next iteration. ...
constructs, including basic LOOP statements,
WHILE loop In most computer programming languages, a while loop is a control flow Statement (computer science), statement that allows code to be executed repeatedly based on a given Boolean data type, Boolean condition. The ''while'' loop can be thought o ...
s,
FOR loop In computer science, a for-loop or for loop is a control flow Statement (computer science), statement for specifying iteration. Specifically, a for-loop functions by running a section of code repeatedly until a certain condition has been satisfi ...
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

<> LOOP statements <> loop statements exit parent_loop when ; -- Terminates both loops exit when ; -- Returns control to parent_loop end loop child_loop; if then continue; -- continue to next iteration end if; exit when ; END LOOP parent_loop; Loops can be terminated by using the EXIT keyword, or by raising an exception.


FOR loops

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; Output: 0 1 2 3 4 5 6 7 8 9 10 var is null


Cursor FOR loops

FOR RecordIndex IN (SELECT person_code FROM people_table) LOOP DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code); END LOOP; Cursor-for loops automatically open a 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). 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; The concept of the person_code within the FOR-loop gets expressed with dot-notation ("."): RecordIndex.person_code


Dynamic SQL

While programmers can readily embed
Data Manipulation Language A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database. A DML is often a sublanguage of a broader database language such as SQL, with the DML com ...
(DML) statements directly into PL/SQL code using straightforward SQL statements,
Data Definition Language In the context of SQL, data definition or data description language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining d ...
(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 DBMS_SQL package library. More recent versions have however introduced a simpler "Native Dynamic SQL", along with an associated EXECUTE IMMEDIATE syntax.


Similar languages

PL/SQL works analogously to the embedded procedural languages associated with other
relational database A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
s. For example,
Sybase Sybase, Inc. was an enterprise software and services company. The company produced software relating to relational databases, with facilities located in California and Massachusetts. Sybase was acquired by SAP in 2010; SAP ceased using the Syba ...
ASE and
Microsoft Microsoft Corporation is an American multinational corporation and technology company, technology conglomerate headquartered in Redmond, Washington. Founded in 1975, the company became influential in the History of personal computers#The ear ...
SQL Server have
Transact-SQL Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, vari ...
,
PostgreSQL PostgreSQL ( ) also known as Postgres, is a free and open-source software, free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transaction processing, transactions ...
has PL/pgSQL (which emulates PL/SQL to an extent),
MariaDB MariaDB is a community-developed, commercially supported Fork (software development), fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Developm ...
includes a PL/SQL compatibility parser, and IBM Db2 includes SQL Procedural Language, which conforms to the ISO SQL’s
SQL/PSM SQL/PSM (SQL/Persistent Stored Modules) is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92 (ISO/IEC 9075-4:1996, a version sometimes ...
standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have 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 Object Pascal is an extension to the programming language Pascal (programming language), Pascal that provides object-oriented programming (OOP) features such as Class (computer programming), classes and Method (computer programming), methods. T ...
program structure as implemented by a
Borland Delphi Delphi is a general-purpose programming language and a software product that uses the Delphi dialect of the Object Pascal programming language and provides an integrated development environment (IDE) for rapid application development of desktop, ...
or
Free Pascal Free Pascal Compiler (FPC) is a compiler for the closely related programming-language dialects Pascal and Object Pascal. It is free software released under the GNU General Public License, witexception clausesthat allow static linking against it ...
unit. Programmers can define public and private global data-types, constants, and static variables in a PL/SQL package. 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 Object-oriented programming (OOP) is a programming paradigm based on the concept of '' objects''. Objects can contain data (called fields, attributes or properties) and have actions they can perform (called procedures or methods and impl ...
languages like
Object Pascal Object Pascal is an extension to the programming language Pascal (programming language), Pascal that provides object-oriented programming (OOP) features such as Class (computer programming), classes and Method (computer programming), methods. T ...
, C++ and
Java Java is one of the Greater Sunda Islands in Indonesia. It is bordered by the Indian Ocean to the south and the Java Sea (a part of Pacific Ocean) to the north. With a population of 156.9 million people (including Madura) in mid 2024, proje ...
. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an
Oracle An oracle is a person or thing considered to provide insight, wise counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. If done through occultic means, it is a form of divination. Descript ...
SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the
Oracle An oracle is a person or thing considered to provide insight, wise counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. If done through occultic means, it is a form of divination. Descript ...
SQL Engine and the
Oracle An oracle is a person or thing considered to provide insight, wise counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. If done through occultic means, it is a form of divination. Descript ...
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 Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, vari ...
, 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, but also due to the very significant differences in the way Oracle and SQL Server deal with
concurrency Concurrent means happening at the same time. Concurrency, concurrent, or concurrence may refer to: Law * Concurrence, in jurisprudence, the need to prove both ''actus reus'' and ''mens rea'' * Concurring opinion (also called a "concurrence"), a ...
and
lock Lock(s) or Locked may refer to: Common meanings *Lock and key, a mechanical device used to secure items of importance *Lock (water navigation), a device for boats to transit between different levels of water, as in a canal Arts and entertainme ...
ing. The ''StepSqlite'' product is a PL/SQL compiler for the popular small database
SQLite SQLite ( "S-Q-L-ite", "sequel-ite") is a free and open-source relational database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it ...
which supports a subset of PL/SQL syntax. Oracle's
Berkeley DB Berkeley DB (BDB) is an embedded database software library for key/value data, historically significant in open-source software. Berkeley DB is written in C with API bindings for many other programming languages. BDB stores arbitrary key/data ...
11g R2 release added support for
SQL Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel") is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
based on the popular SQLite API by including a version of SQLite in Berkeley DB. Consequently, StepSqlite can also be used as a third-party tool to run PL/SQL code on Berkeley DB.


See also

*
T-SQL Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, variou ...
*
SQL PL SQL PL stands for Structured Query Language Procedural Language and was developed by IBM as a set of commands that extend the use of SQL in the IBM Db2 (DB2 UDB Version 7) database system. It provides procedural programmability in addition to t ...
*
SQL/PSM SQL/PSM (SQL/Persistent Stored Modules) is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92 (ISO/IEC 9075-4:1996, a version sometimes ...
* PL/SQL editors *
Relational database management system A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
s


References


Further reading

* *


External links


Oracle FAQ: PL/SQL



Oracle Tahiti Search Engine



ORACLE-BASE: PL/SQL Articles
{{DEFAULTSORT:Pl Sql Ada programming language family Data-centric programming languages Oracle software SQL