PL/SQL

PL/SQL is Procedural Language/ structured Query Language provided by Oracle. It is an extension of SQL and bridges the gap between the application programs, the Oracle database system and the database we are accessing. Using PL/SQL we can manipulate data in the database, insert data into database and also retrieve data. Also we can group PL/SQL codes logically and this group of PL/SQL code is called a block. If a block is not given a name it is called on anonymous block.

Performance of SQL:

Only one SQL statement is executed by the DBA of the Oracle at one time. Consider a multi-user environment when more users are issuing SQL statements simultaneously, it causes more traffic and hence the data processing is considerably slowed down. But PL/SQL, logically packs a set of SQL statement and when it is sent to the Oracle server, it executes all the SQL statements at one time and returns the result set to the user. Thus PL/SQL reduces the traffic between Oracle server and the user’s application program.

Performance Improvement:

To improve the performance of DBA, we put all related statements within a block and send it to the DBA. This process is faster than the execution of SQL statements sending one by one. PL/SQL can be used in SQL* FORMS as triggers,

The reactions by Oracle for your action in the form PL/SQL is used to create procedures, functions etc.

Portability:

Applications written in PL/SQL are portable to any other operating system on which Oracle is installed. This is really an advantage of Oracle too.

PL/SQL has the following functionalities and flexibilities:

• Using SQL data manipulation languages in PL/SQL, data in the tables can be manipulated.
• Using the data transaction languages in PL/SQL, changes of data in a table can be made or can be undone as a whole.
• All sorts of SQL functions can be used in PL/SQL.
• All operators that we are using in SQL statements can also be used in PL/SQL.
• We can declare variables and constants in PL/SQL to store results of a query during the process, which can be used later in PL/SQL block.

PL/SQL in Oracle RDBMS:

PL/SQL engine may reside either within the Oracle RDBMS or in a tool outside. But the SQL statement executor is always inside the server. Hence, if the PL/SQL engine is placed in the Oracle RDBMS (server), then the execution of PL/SQL will be more efficient.