PROCESSING A PL/SQL BLOCK

A PL/SQL block can be run in one of two modes:

Ø  Batch processing wherein records are gathered in a table and at regular intervals manipulated

Ø  Real Time processing wherein records are manipulated as they are created (in real time)

Batch Processing is a PL/SQL block run at the SQL prompt at regular intervals to process table data.

A technique that Oracle provides for manipulating table data in batch processing mode is the use of Cursors.

Oracle And The Processing of SQL Statements

Whenever an SQL statement is executed, Oracle engine performs the following tasks:

Ø  Reserves a private SQL area in memory

Ø  Populates (Occupy) this area with the data requested in the SQL sentence

Ø  Processes the data in this memory area as required

Ø  Frees the memory area when the processing of data is complete

Example :

An SQL statement that will display the employee number (EMP_NO), employee name (ENAME) and Department (DEPT) from EMP_MSTR table in the ascending order of employee name will be as follows:

SELECT EMP_NO, ENAME, DEPT FROM EMP_MSTR ORDER BY ENAME;

To execute the above statement, Oracle will reserve an area in memory and populate (occupy) it with the records from EMP_MSTR table. These records are then sorted in the ascending order of employee name and displayed. When all the records from the EMP_MSTR table are displayed, Oracle will free the memory area used for retrieving and sorting the data.