PL/SQL |
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation s proprietary server-based procedural extension to the SQL database programming language. (Some other SQL database management systems offer languages similar to PL/SQL.) Its syntax strongly resembles that of the Ada programming language.
=Functionality=
PL/SQL supports Variables, conditional statements, arrays, and exception handlings. Implementations from version 8 of the Oracle RDBMS onwards have placed an emphasis on object orientation.
The underlying SQL functions as a Declarative programming. Standard SQL—unlike some functional programming languages—does not require implementations to convert tail recursion to jumps. SQL does not readily provide first row and rest of table accessors, and it cannot easily perform some constructs such as program 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 (function (programming)s, 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 statement 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), DDL statements such as CREATE TABLE/DROP INDEX etc require the use of Dynamic SQL . Earlier versions of Oracle 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 Oracle 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 has several pre-defined packages for specific purposes.Some of the most important PL/SQL packages include: DBMS_OUTPUT - for output operations DBMS_JOBS - for running specific procedures/functions at a particular time i.e scheduling DBMS_XPLAN -for formatting Explain Plan output DBMS_SESSION DBMS_METADATA
With each release of the Oracle DBMS, Oracle Corporation adds more packages.
= Basic code structure =
PL/SQL programs consist of block (programming)s. Blocks take the general form: :
:/* Sample comment spanning
:multiple lines... */
:--Single-line comment
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 an SQL Error Number and SQL Error Message associated with it. Programmers can access these by using the SQLCODE and SQLERRM functions.
= Variables =
The DECLARE section defines and (optionally) initialises variables. If not initialised specifically they default to 0 or to null.
For example:
:
The symbol := functions as an assignment operator to store a value in a variable.
The major Datatypes in PL/SQL include NUMBER, INTEGER, VARCHAR2, DATE, TIMESTAMP etc.
== 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: :dec, decimal, double precision, integer, int, numeric, real, smallint, binary_integer, pls_integer
== Character variables ==
variable_name varchar(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 varaiables include: :varchar, char, long, raw, long raw, nchar, nchar2
== Boolean variables ==
variable_name boolean:= true;
Boolean variables can convey TRUE, FALSE or NULL.
== Date variables ==
variable_name date := 01-Jan-2005 ;
Programmers define date variables by appending the datatype code DATE to a variable name. The Oracle DBMS provides the function to_date 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 ))
To convert dates into text 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.
== User-defined datatypes ==
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:
:
This sample program defines its own datatype, called t_address , which contains the fields name, street, street_number und 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 ;
=Similar languages=
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 ([http://publib.boulder.ibm.com/infocenter/db2help/index.jsptopic=/com.ibm.db2.udb.doc/ad/c0011916.htm SQL PL]).
PL/SQL code closely resembles Pascal in numerous aspects. The structure of a PL/SQL package closely resembles the basic Pascal programming language 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 Borland Delphi, C plus plus and Java programming language. PL/SQL refers to a class as an Advance 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 Advance Data Type are written in PL/SQL. The resulting Advance 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 control and Lock (computer science)ing.
=References=
|
|