PL/SQL Overview

PL/SQL bridges the gap between database technology and procedural programming languages. PL/SQL allows the use of SQL statements to manipulate Oracle data and flow-of-control statements to process the data. You can also:

• declare constants and variables
• define procedures and functions
• trap runtime errors.

A PL/SQL program is made up of logical blocks, containing any number of nested sub-blocks that group logically related declarations, statements, and exception handling together. 


And example PL/SQL script is given below (taken from Oracle docs):

DECLARE
   qty_on_hand  NUMBER(5);
BEGIN
   SELECT quantity INTO qty_on_hand FROM inventory
      WHERE product = 'TENNIS RACKET'
      FOR UPDATE OF quantity;
   IF qty_on_hand > 0 THEN  -- check quantity
      UPDATE inventory SET quantity = quantity - 1
         WHERE product = 'TENNIS RACKET';
      INSERT INTO purchase_record
         VALUES ('Tennis racket purchased', SYSDATE);
   ELSE
      INSERT INTO purchase_record
         VALUES ('Out of tennis rackets', SYSDATE);
   END IF;
   COMMIT;
END;

Control structures are the most important PL/SQL extension to SQL. PL/SQL provides the following control structures:

• if-then-else
• case
• for loop
• while loop
• exit when
• goto

Oracle uses work areas to execute SQL statements and store processing information. A 
PL/SQL cursor is a construct that 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. For example (taken from Oracle docs):

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

    ...
BEGIN
   FOR emp_rec IN c1 LOOP
      ...
      salary_total :=  salary_total + emp_rec.sal;
   END LOOP;

The query returns a result set and the cursor allows you to process one row at the time.