PL/SQL is the Oracle Procedural Language extension of SQL..

A PL/SQL program can have both SQL and statement and procedural statements. In the PL/SQL program, SQL is used to access sets of data stored in a database ,while the procedural statements are used to process individual piece of data and control the program flow.

PL/SQL programs are divided and written in logical blocks of code. Each block consists of three sub-parts −

Sl.No	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 optional section contains exception(s) that handle errors in the program.

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

DECLARE 

   <declarations section> 

BEGIN 

   <executable command(s)>

EXCEPTION 

   <exception handling> 

END;

The 'Hello World' Example

DECLARE 

   message  varchar2(20):= 'Hello, World!'; 

BEGIN 

   dbms_output.put_line(message); 

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

The PL/SQL Comments

Program comments are explanatory statements that can be included in the PL/SQL code that you write and helps anyone reading its source code. All programming languages allow some form of comments.

The PL/SQL supports single-line and multi-line comments. All characters available inside any comment are ignored by the PL/SQL compiler. The PL/SQL single-line comments start with the delimiter -- (double hyphen) and multi-line comments are enclosed by /* and */.

DECLARE 

   -- variable declaration 

   message  varchar2(20):= 'Hello, World!'; 

BEGIN 

   /* 

   *  PL/SQL executable statement(s) 

   */ 

   dbms_output.put_line(message); 

END; 

/

PL/SQL Program Units

A PL/SQL unit is any one of the following −

• PL/SQL block
• Function
• Package
• Package body
• Procedure
• Trigger
• Type
• Type body

PL/SQL Data Types

 Introduction to PL/SQL data types

Each value in PL/SQL such as a constant, variable and parameter has a data type that determines the storage format, valid values, and allowed operations.

Ø  PL/SQL has two kinds of data types: scalar and composite.

Ø  The scalar types are types that store single values such as number, Boolean, character, and DateTime.

Ø  The composite types are types that store multiple values, for example, record and collection.

>  scalar data types 

PL/SQL divides the scalar data types into four families:

·         Number

·         Boolean

·         Character

·         Datetime

A scalar data type may have subtypes. A subtype is a data type that is a subset of another data type, which is its base type. A subtype further defines a base type by restricting the value or size of the base data type.

Note that PL/SQL scalar data types include SQL data types and their own data type such as Boolean.

Numeric data types

Ø  The numeric data types represent real numbers, integers, and floating-point numbers.

Ø  They are stored as NUMBER, IEEE floating-point storage types (BINARY_FLOAT and BINARY_DOUBLE), and PLS_INTEGER.

Ø  The data types NUMBER, BINARY_FLOAT, and BINARY_DOUBLE are SQL data types.

Ø  The PLS_INTEGER datatype is specific to PL/SQL. It represents signed 32 bits integers that range from -2,147,483,648 to 2,147,483,647.

Ø  Because PLS_INTEGER datatype uses hardware arithmetic, they are faster than NUMBER operations, which use software arithmetic.

Ø  In addition, PLS_INTEGER values require less storage than NUMBER. Hence, you should always use PLS_INTEGER values for all calculations in its range to increase the efficiency of programs.

Ø  The PLS_INTEGER datatype has the following predefined subtypes:

 

PLS_INTEGER subtypes	Description
NATURAL	Represents nonnegative PLS_INTEGER values
NATURALN	Represents nonnegative PLS_INTEGER values with NOT NULL constraint
POSITIVE	Represents positive PLS_INTEGER values
POSITIVEN	Represents positive PLS_INTEGER value with NOT NULL constraint
SIGNTYPE	Represents three values -1, 0, or 1, which are useful for tri-state logic programming
SIMPLE_INTEGER	Represents PLS_INTEGER values with NOT NULL constraint.

 

Note that PLS_INTEGER and BINARY_INTEGER data types are identical.

 

Boolean data type

The BOOLEAN datatype has three data values: TRUE, FALSE, and NULL. Boolean values are typically used in control flow structures such as IF-THEN, CASE, and loop statements like LOOP, FOR LOOP, and WHILE LOOP.

SQL does not have the BOOLEAN data type, therefore, you cannot:

·         Assign a BOOLEAN value to a table column.

·         Select the value from a table column into a BOOLEAN variable.

·         Use a BOOLEAN value in a SQL function.

·         Use a BOOLEAN expression in a SQL statement.

·         Use a BOOLEAN value in the DBMS_OUTPUT.PUTLINE and DBMS_OUTPUT.PUT subprograms.

Character data types

The character data types represent alphanumeric text. PL/SQL uses the SQL character data types such as CHAR, VARCHAR2, LONG, RAW, LONG RAW, ROWID, and UROWID.

·          CHAR(n) is a fixed-length character type whose length is from 1 to 32,767 bytes.

·          VARCHAR2(n) is varying length character data from 1 to 32,767 bytes.

Ø  Datetime data types

The DateTime data types represent dates, timestamps with or without time zone, and intervals. PL/SQL DateTime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.

 

Ø  Data type synonyms

Data types have synonyms for compatibility with non-Oracle data sources such as IBM Db2, and SQL Server. And it is not a good practice to use data type synonyms unless you are accessing a non-Oracle Database.

Data Type	Synonyms
NUMBER	DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT
CHAR	CHARACTER, STRING
VARCHAR2	VARCHAR