PL/SQL is an acronym to Procedural Language/Structured Query Language.

When the business logic to be attained is not achievable easily using SQL which is 4th Generation Language, Oracle turns to the Procedural Language structures and achieves that business logic and achieves what business is looking for.

In the process of evolution of PL/SQL it has become more robust and multi-faceted.

a. DML to insert, update, delete the data into Oracle Tables
b. DDL to create, alter, drop objects in a schema
c. DCL to create save point and to rollback or commit the work done to a certain desired state. And also enables to take control of the situation in case one or other process within the PLSQL fails and there is a need to rollback to a position where the data integrity maintained and before process state for data is restored.
d. DQL to save data for a given process into variables
e. To call other functions, procedures, packages from within the procedures, functions, packages, triggers.
f. PLSQL owned security mechanism to protect the intrusions
g. To use all the utilities and built in packages as provided by Oracle
h. Triggers, even though are part of PLSQL are of different gene and they are event driven in their nature. They are the backbone of Oracle Forms and they are used in RDBMS to achieve data integrity, data auditing, database auditing, to create a source safe mechanism and many more requirements. But they are the most dangerous creatures that reside within Oracle RDBMS and work behind backs of DBA, users and consumers of data. Some RDBMS have used triggers for data integrity and validation like constraints. In Oracle RDBMS maintaining the data integrity and validation is the job of constraints and using triggers for that purpose leads to performance. There is an issue mutating triggers in Oracle as a result of recursive dependency of one activity on the other recursively. This is an issue with the developers and this, they are to handle carefully.
i. PL/SQL collections, records and object types enable development of Object Oriented Relational Database Management System more comfortably getting out of many procedural issues.

Understanding main features of PLSQL in the words of Oracle:

PLSQL is in Blocks. Block structure look like as under:

declare

begin

conditions
statements

end;
/

The above is an example of an anonymous PLSQL block. This means that this PLSQL has no name and can not be stored in the database as an PLSQL Object.

Between ‘declare’ and ‘begin’ key words the user has been given the opportunity to create all types of variables as permitted by PLSQL.

VARIABLES

Variables are a type of containers that allow you to identify the container with a PLSQL identifiable datatypes, attributes, constants, cursors, also with assigned values. The values can be assigned to these variables with in the BEGIN and END control blocks.

Variable datatypes:
Here is listing of the data types that are identifiable within PLSQL:

DATATYPE
MEMBER
SUB-MEMBER
SIZE/LENGTH
REMARKS
NUMBER
BINARY
-2**31 to 2**31
BINARY_INTEGER
Less storage than NUMBER. Operations are slower than pls_integer
NATURAL
non-negative or positive values only
NATURALN
prevent the assigning of nulls to an integer variable
POSITIVE
non-negative or positive values only
POSITIVEN
prevent the assigning of nulls to an integer variable
SIGNTYPE
values -1, 0, and 1
NUMBER
Its magnitude range is 1E-130 thru 10E1
DEC
38
DECIMAL
38
DOUBLE PRECISION
126 binary digits (roughly 38 digits)
FLOAT
126 binary digits (roughly 38 digits)
INTEGER
INT
NUMERIC
38
REAL
63 binary digits
PLS_INTEGER
values range from 2**31 to 2**31
Less storage than NUMBER.
CHARACTER
Maximum size up to 32767 bytes. This is fixed length
CHAR
maximum width 2000 bytes
LONG and LONG RAW
size of a LONG and LONG RAW value is 32760 bytes
RAW
Max size up to 32767 bytes
VARCHAR2
Max size up to 32767 bytes
STRING
For compatibility with IBM and ANSI
VARCHAR
For compatibility with IBM and ANSI
NATIONAL CHARACTER TYPES
NCHAR
NCHAR maximum size Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the AL16UTF16 encoding, and 32767/3 in the UTF8 encoding.
NVARCHAR2
BFILE
The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 – 1 bytes).
BFILEs are read-only, so you cannot modify them.
BLOB
The size of a BLOB cannot exceed four gigabytes
CLOB
The size of a CLOB cannot exceed four gigabytes.
NCLOB
The size of an NCLOB cannot exceed four gigabytes.
BOOLEAN TYPE
BOOLEAN
Values are TRUE, FALSE, and NULL.
You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a BOOLEAN variable.
DATE and INTERVAL TYPES
DATE
Valid dates range from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

Note:

VARCHAR2

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL pre-allocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2 (2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

ATTRIBUTES for the Variables

Apart from these datatypes, the variables can be declared using ATTRIBUTES

%rowtype which means that an entire row in a table has been identified as a variable

Example emprec scott.emp%rowtype; — declare record variable

%type which means that the variable’s datatype is to be that of the referenced table.column datatype.

Example var_ename scott.emp.ename%type;

CONSTANT variable

credit_limit CONSTANT REAL := 5000.00;

CURSORS in the declare section

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:

DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;


The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria

Cursor FOR Loops

In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. In the following example, the cursor FOR loop implicitly declares emp_rec as a record:

DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;

BEGIN
FOR emp_rec IN c1 LOOP

salary_total:= salary_total + emp_rec.sal;
END LOOP;


To reference individual fields in the record, you use dot notation, in which a dot (.) serves as the component selector.

Cursor Variables

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database. This gives you more flexibility and a convenient way to centralize data retrieval.

Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. The following procedure opens the cursor variable generic_cv for the chosen query:

PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp, choice NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;

END;