PL/SQL TUTORIALS – CONCEPTS

PL/SQL Tutorials – CONCEPTS

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: