PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's proprietary procedural extension to the SQL database language, used in the Oracle database. Some other SQL database management systems offer similar extensions to the SQL language.PL/SQL's syntaxstrongly resembles that of Ada,and just like Ada compilers of the 1980sthe PL/SQL runtime system uses Diana as intermediate representation.
The key strength of PL/SQL is its tight integration with the Oracle database.
PL/SQL is one of three languages embedded in the Oracle Database, the other two being SQL and Java.
History
PL/SQL made its first appearance in Oracle Forms v3. A few years later, it was included in the Oracle Database server v7 (as database procedures, functions, packages, triggers and anonymous blocks) followed by Oracle Reports v2.
Functionality
PL/SQL supports the following : variables, conditions, arrays, and exceptions. Implementations from version 8 of Oracle Database onwards have included features associated with 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. The open standard 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 Data Manipulation Language (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.
While programmers can readily incorporate SQL DML statements into PL/SQL (as cursor definitions, for example, or using the SELECT ... INTO syntax), Data Definition Language (DDL) statements such as CREATE TABLE/DROP INDEX etc require the use of "Dynamic SQL". Earlier versions of Oracle Database required the use of a complex built-in DBMS_SQL package for Dynamic SQL where the system needed to explicitly parse and execute an SQL statement. Later versions have included an EXECUTE IMMEDIATE syntax called "Native Dynamic SQL" which considerably simplifies matters. Any use of DDL in an Oracle database will result in an implicit commit. Programmers can also use Dynamic SQL to execute DML where they do not know the exact content of the statement in advance.
PL/SQL offers several pre-defined packages for specific purposes. Such PL/SQL packages include: * DBMS_OUTPUT - for output operations to non-database destinations * DBMS_JOB - for running specific procedures/functions at a particular time (i.e. scheduling) * DBMS_XPLAN - for formatting "Explain Plan" output * DBMS_SESSION - provides access to SQL ALTER SESSION and SET ROLE statements, and other session information. * DBMS_METADATA - for extracting meta data from the data dictionary (such as DDL statements) * UTL_FILE - for reading and writing files on disk * UTL_HTTP - for making requests to web servers from the database * UTL_SMTP - for sending mail from the database (via an SMTP server)
Oracle Corporation customarily adds more packages and/or extends package functionality with each successive release of Oracle Database.
Basic code structure
PL/SQL programs consist of procedures, functions, and anonymous blocks. Each of these is made up of the basic PL/SQL unit which is the block. Blocks take the general form:
The DECLARE section specifies the datatypes of variables, constants, collections, and user-defined types.
The block between BEGIN and END specifies executable procedural code.
Exceptions, errors which arise during the execution of the code, have one of two types: # pre-defined exceptions # user-defined exceptions.Programmers have to raise user-defined exceptions explicitly. They can do this by using the RAISE command, with the syntax: RAISE Oracle Corporation has pre-defined several exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, etc.Each exception has a SQL Error Number and SQL Error Message associated with it. Programmers can access these by using the SQLCODE and SQLERRM functions.
The DECLARE section defines and (optionally) initialises variables. If not initialised specifically they default to NULL.
For example: declare number1 number(2); number2 number(2) := 17; text1 varchar2(12) := 'Hello world'; text2 date := SYSDATE; -- current date and timebegin SELECT street_number into number1 from address where name = 'Smith';end;
The symbol := functions as an assignment operator to store a value in a variable.
The major datatypes in PL/SQL include NUMBER, INTEGER, CHAR, VARCHAR2, DATE, TIMESTAMP, TEXT etc.
Functions
Functions in PL/SQL are a collection of SQL and PL/SQL statements that perform a task and should return a value to the calling environment.
create or replace function IS/AS{Variable declaration}{constant declaration}Return return_type
begin
Pl/sql Block;
Exception
Exception Block;
End;
Procedures
To Be Decided
Anonymous Blocks
Anonymous PL/SQL blocks can be embedded in an Oracle Precompiler or OCI program. At run time, the program, lacking a local PL/SQL engine, sends these blocks to the Oracle server, where they are compiled and executed. Likewise, interactive tools such as SQL*Plus and Enterprise Manager, lacking a local PL/SQL engine, must send anonymous blocks to Oracle.
Packages
To Be Decided
Numeric variables
variable_name number(P [,S] ) := value;
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 which the variable can hold, "scale" refers to the number of digits which can follow the decimal point.)
A selection of other datatypes for numeric variables would include::binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, smallint, binary_integer
Character variables
variable_name varchar2(L) := 'Text';
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 which the variable can store.
Other datatypes for character variables include: :varchar, char, long, raw, long raw, nchar, nchar2, clob, blob, bfile
Date variables
variable_name date := '01-Jan-2005';
Oracle provides a number of data types that can store dates (DATE, DATETIME, TIMESTAMP etc), however DATE is most commonly used.
Programmers define date variables by appending the datatype code "DATE" to a variable name.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 convert the dates to strings one uses the function TO_CHAR (date_string, format_string).
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 table.
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".
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 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-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 arrays as "collections". The language offers three types of collections: # Index-by tables (associative arrays) # 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 methods 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.
Looping
As a procedural language by definition, PL/SQL provides several iteration constructs, including basic LOOP statements, WHILE loops, FOR loops, and Cursor FOR loops.
LOOP statements
Syntax: LOOP statement1; statement2; END LOOP;
Loops can be terminated by using the EXITkeyword, or by raising an exception.
WHILE loops
Syntax: WHILE condition LOOP ...do something... END LOOP;
FOR loops
FOR loops, also called "numerical loops", operate a certain (counted) number of times.
FOR IN [REVERSE] .. LOOP .... ..... END LOOP;
The REVERSE keyword implements looping in reverse order.
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 in order (for example) to allow re-use or to 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
Example
declare var number; /* this "var" is not in the same scope as the for loop "var" a reference to "var" after the "end loop;" would find its value to be null */ 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; end;
Output: 0 1 2 3 4 5 6 7 8 9 10
imilar languages
PL/SQL functions analogously to the embedded procedural languages associated with other relational databases. SybaseASE and MicrosoftSQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which tries to emulate PL/SQL to an extent), and IBMDB2 includes SQL Procedural Language, [ [http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011916.htm SQL PL] ] which conforms to the ISO SQL’s SQL/PSM standard.
The designers of PL/SQL modelled 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 numerous aspects. The structure of a PL/SQL package closely resembles the basic Pascal's program structure, or a Borland Delphi unit. Programmers can define global data-types, constants and static variables, public and private, 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 usages in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Advanced Data Type" (ADT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Advanced Data Type are written in PL/SQL. The resulting Advanced 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 does not resemble Transact-SQL, despite superficial similarities due to the use of both as embedded database languages. 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 and locking.
SQL — ist eine Datenbanksprache zur Definition, Abfrage und Manipulation von Daten in relationalen Datenbanken. SQL ist von ANSI und ISO standardisiert und wird von fast allen gängigen Datenbanksystemen unterstützt. Die Bezeichnung SQL (offizielle… … Deutsch Wikipedia
SQL-92 — SQL (das Kürzel für Structured Query Language; offizielle Aussprache [ɛskjuːˈɛl], häufig auch [ˈsiːkwəl] →SEQUEL), ist eine Datenbanksprache zur Definition, Abfrage und Manipulation von Daten in relationalen Datenbanken. SQL ist von ANSI und ISO… … Deutsch Wikipedia
SQL-99 — SQL (das Kürzel für Structured Query Language; offizielle Aussprache [ɛskjuːˈɛl], häufig auch [ˈsiːkwəl] →SEQUEL), ist eine Datenbanksprache zur Definition, Abfrage und Manipulation von Daten in relationalen Datenbanken. SQL ist von ANSI und ISO… … Deutsch Wikipedia
SQL — Класс языка: Мультипарадигмальный Появился в: 1974 Автор(ы): Дональд Чэмбэрлин Рэймонд Бойс Релиз: SQL:2008 (2008) Типизация данных … Википедия
SQL — Desarrollador(es) IBM ISO/IEC 9075 1:2008 Información general Paradigma Multiparadigma … Wikipedia Español
SQL-Injection — (dt. SQL Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei, über die… … Deutsch Wikipedia
SQL Server Compact — Filename extension .sdf Developed by Microsoft Type of format Relational database Microsoft SQL Server Compact (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the… … Wikipedia
SQL-Injektion — SQL Injection (dt. SQL Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei … Deutsch Wikipedia
SQL Injection — (dt. SQL Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei, über die… … Deutsch Wikipedia
SQL Injektion — SQL Injection (dt. SQL Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei … Deutsch Wikipedia
SQL-92 — SQL 92 третья версия языка запросов к базам данных SQL. В отличие от стандарта SQL 89, третья версия стала весьма существенным обновлением языка. За исключением некоторых мелких противоречий стандарт SQL 89 практически полностью совместим… … Википедия
