What is PL/SQL?

It is a procedural extension language (PL) for the structured query language (SQL) and the Oracle relational database. It provides a built-in, interpreted and OS independent programming environment.

Apart from Oracle it also available Times Ten in-memory database and IBM DB2.

The Syntax of PL/SQL is based on ADA and Pascal programming languages. PL/SQL is tightly integrated with SQL.

PL/SQL offers the following −

• Extensive error checking.
• Numerous data types.
• Variety of programming structures.

PL/SQL supports the following −

• Structured programming through functions.

• Procedures and object-oriented programming.

• Development of web applications and server pages.

PL/SQL block structure

It defines the type of block (procedure, function, anonymous) and the way it is called, it declares any variables used in the block, it uses variables and other PL/SQL objects to perform actions and handle any problems that arise during execution of the block.

PL/SQL is not case sensitive. It uses the same data-types as SQL like Boolean, record, table, array and LOB. It allows reference data-types %type and %row type.

Comments

• /* and */ for multiline.
• -- for a single line.

Uses

• = is used for assignment.
• ; is used to indicate end of line (instruction).

Example

Given below is an example for the PL/SQL −

DECLARE
   current_bldg_code VARCHAR2(5);
   CURSOR location_cursor IS
      SELECT room, capacity
      FROM location
      WHERE bldg_code = current_bldg_code
      FOR UPDATE of capacity;
   location_row location_cursor%rowtype;
BEGIN
   current_bldg_code := ‘LIB’;
   FOR location_row IN location_cursor LOOP
      UPDATE location
      Set capacity = capacity +1
      WHERE CURRENT OF location_cursor;
   END LOOP;
   COMMIT;
END;