PL/SQL Full Form

PL/SQL stands for “Procedural Language extensions to the Structured Query Language.” PL/SQL is Oracle Corporation’s procedural extension for SQL and the Oracle relational database. It is a high-performance, highly integrated database language.

The PL/SQL architecture mainly consists of following three components:

• PL/SQL Block
• PL/SQL Engine
• Database Server

PL/SQL block:

This is the component which has the actual PL/SQL code.

This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors)

It also contains the SQL instruction that used to interact with the database server.

All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.

Following are the different type of PL/SQL units.

• Anonymous Block
• Function
• Library
• Procedure
• Package Body
• Package Specification
• Trigger
• Type
• Type Body

PL/SQL Engine

PL/SQL engine is the component where the actual processing of the codes takes place.

PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below).

The separated PL/SQL units will be handled by the PL/SQL engine itself.

The SQL part will be sent to database server where the actual interaction with database takes place.

It can be installed in both database server and in the application server.

Database Server:

This is the most important component of Pl/SQL unit which stores the data.

The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server.

It consists of SQL executor which parses the input SQL statements and execute the same.

Features & Advantages of PL/SQL

• Better performance, as SQL is executed in bulk rather than a single statement
• High Productivity
• Tight integration with SQL
• Full Portability
• Tight Security
• Supports Object Oriented Programming concepts.
• Scalability and Manageability
• Supports Web Application Development
• Supports Server Page Development

Disadvantages of PL/SQL

• Stored Procedures in PL/SQL uses high memory
• Lacks functionality debugging in stored procedures
• Any change in underlying database requires change in the presentation layer also
• Does not completely separate roles of back-end developer and fron-end developer
• Difficult to separate HTML development with PL/SQL development

Difference between SQL and PL/SQL

Oracle Data Type

• Scalar(Character,NUMBER, DATE, or BOOLEAN)
• Large Object (LOB)(text, graphic images, video clips, and sound waveforms.)
• Composite(collections and records)
• Reference(Pointers to other data items.)

Character data type and subtype:

• CHAR(Fixed-length character string with maximum size of 32,767 bytes)
• VARCHAR2(Variable-length character string with maximum size of 32,767 bytes)
• RAW(Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL)
• NCHAR(Fixed-length national character string with maximum size of 32,767 bytes)
• NVARCHAR2(Variable-length national character string with maximum size of 32,767 bytes)
• LONG(Variable-length character string with maximum size of 32,760 bytes)
• LONG RAW(Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL)
• ROWID(Physical row identifier, the address of a row in an ordinary table)
• UROWID(Universal row identifier (physical, logical, or foreign row identifier))

Numeric data type and subtype:

• PLS_INTEGER(Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits)
• BINARY_INTEGER(Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits)
• BINARY_FLOAT(Single-precision IEEE 754-format floating-point number)
• BINARY_DOUBLE(Double-precision IEEE 754-format floating-point number)
• NUMBER(prec, scale)(Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0)
• DEC(prec, scale)(ANSI specific fixed-point type with maximum precision of 38 decimal digits)
• DECIMAL(prec, scale)(IBM specific fixed-point type with maximum precision of 38 decimal digits)
• NUMERIC(pre, secale)(Floating type with maximum precision of 38 decimal digits)
• DOUBLE PRECISION(ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits))
• FLOAT(ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits))
• INT(ANSI specific integer type with maximum precision of 38 decimal digits)
• INTEGER(ANSI and IBM specific integer type with maximum precision of 38 decimal digits)
• SMALLINT(ANSI and IBM specific integer type with maximum precision of 38 decimal digits)
• REAL(Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits))

Numeric boolean type and subtype:

• TRUE
• FALSE
• NULL

Large Object(LOB) data type and subtype:

• BFILE(Used to store large binary objects in operating system files outside the database.)
• BLOB(Used to store large binary objects in the database.)
• CLOB(Used to store large blocks of character data in the database.)
• NCLOB(Used to store large blocks of NCHAR data in the database.)

User-Defined Subtypes:

A subtype is a subset of another data type, which is called its base type. A subtype has the same valid operations as its base type, but only a subset of its valid values.

• SUBTYPE CHARACTER IS CHAR;
• SUBTYPE INTEGER IS NUMBER(38,0);

Variable declaration :

sales number(10, 2); 
pi CONSTANT double precision := 3.1415; 
name varchar2(25); 
address varchar2(100);
counter binary_integer := 0; 
greetings varchar2(20) DEFAULT 'Have a Good Day';

When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations

You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.

Constants and Literals :

Operators :