Basics of PL/SQL & its block structure

 

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s extension to SQL. While standard SQL is a “declarative” language (you tell the DB what to get), PL/SQL is “procedural” (you tell the DB how to get it using logic).

A PL/SQL block is divided into four distinct sections:

 

  1. Declaration Section (Optional): Starts with the keyword DECLARE. This is where you define variables, constants, cursors, and subprograms that will be used in the block.
  2. Executable Section (Mandatory): Starts with BEGIN and ends with END;. This contains the procedural logic (IF statements, loops) and SQL statements to manipulate data.
  3. Exception Handling Section (Optional): Starts with EXCEPTION. This section catches and handles errors that occur during the execution of the block, preventing the program from crashing.
  4. END; (Mandatory): Marks the end of the block.

 

Core Features of PL/SQL
 
  • Variables: Used to store temporary values (e.g., v_salary NUMBER(10);).
  • Conditional Statements: Use IF-THEN-ELSE for decision-making.
  • Loops: Use LOOP, WHILE, or FOR to repeat actions.
  • Portability: Code written in PL/SQL can run on any OS where Oracle Database is installed.
  • High Performance: Instead of sending 10 separate SQL queries over the network, you send one PL/SQL block, reducing network traffic.
    PreviousNext