Introduction to PL/SQL

Introduction to PL/SQL

The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database. Following are notable facts about PL/SQL:

Ø  PL/SQL is a completely portable, high-performance transaction-processing language.

Ø  PL/SQL provides a built-in interpreted and OS independent programming environment.

Ø  PL/SQL can also directly be called from the command-line SQL*Plus interface.

Ø  Direct call can also be made from external programming language calls to database.

Ø  PL/SQL's general syntax is based on that of ADA and Pascal programming language.

Ø  Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.

Features of PL/SQL

·        PL/SQL is tightly integrated with SQL.

·        It offers extensive error checking.

·        It offers numerous data types.

·        It offers a variety of programming structures.

·        It supports structured programming through functions and procedures.

·        It supports object-oriented programming.

·        It supports developing web applications and server pages.

Advantages of PL/SQL

·        SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. Dynamic SQL is SQL allows embedding DDL statements in PL/SQL blocks.

·        PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.

·        PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.

·        PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.

·        Applications written in PL/SQL are fully portable.

·        PL/SQL provides high security level.

·        PL/SQL provides access to predefined SQL packages.

·        PL/SQL provides support for Object-Oriented Programming.

·        PL/SQL provides support for Developing Web Applications and Server Pages.

·         PL/SQL is a block-structured language, meaning that PL/SQL programs are divided and written in logical blocks of code. Each block consists of three sub-parts:

S.N.	Sections & Description
1	Declarations This section starts with the keyword DECLARE. It is an optional section and defines all variables, cursors, subprograms, and other elements to be used in the program.
2	Executable Commands This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It consists of the executable PL/SQL statements of the program. It should have at least one executable line of code, which may be just a NULL command to indicate that nothing should be executed.
3	Exception Handling This section starts with the keyword EXCEPTION. This section is again optional and contains exception(s) that handle errors in the program.

 Structure of PL/SQL block

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END. Here is the basic structure of a PL/SQL block:

·         DECLARE

·            <declarations section>

·         BEGIN

·            <executable command(s)>

·         EXCEPTION

·            <exception handling>

·         END;

The PL/SQL Identifiers

PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words. The identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters.

By default, identifiers are not case-sensitive. So you can use integer or INTEGER to represent a numeric value. You cannot use a reserved keyword as an identifier.

 The PL/SQL Delimiters

A delimiter is a symbol with a special meaning. Following is the list of delimiters in PL/SQL:

Delimiter	Description
+, -, *, /	Addition, subtraction/negation, multiplication, division
%	Attribute indicator
'	Character string delimiter
.	Component selector
(,)	Expression or list delimiter
:	Host variable indicator
,	Item separator
"	Quoted identifier delimiter
=	Relational operator
@	Remote access indicator
;	Statement terminator
:=	Assignment operator
=>	Association operator
||	Concatenation operator
**	Exponentiation operator
<<, >>	Label delimiter (begin and end)
/*, */	Multi-line comment delimiter (begin and end)
--	Single-line comment indicator
..	Range operator
<, >, <=, >=	Relational operators
<>, '=, ~=, ^=	Different versions of NOT EQUAL