Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL

PL/SQL Block Structure

PL/SQL is block structured language divided into three logical blocks.

BEGIN block and END; keyword are compulsory, and other two block DECLARE and EXCEPTION are optional block. END; is not a block only keyword to end of PL/SQL program.

PL/SQL block structure follows divide-and-conquer approach to solve the problem stepwise.

Figure - PL/SQL block Structure


Variables and constants are declared, initialized within this section.

Variables and Constants : In this block, declare and initialize variables (and constants). You must have to declare variables and constants in declarative block before referencing them in procedural statement.

Declare Variables and Assigning values : You can define variable name, data type of a variable and its size. Date type can be: CHAR, VARCHAR2, DATE, NUMBER, INT or any other.

DECLARE  		-- DECLARE block, declare and initialize values
	designation VARCHAR2(30);
	eno number(5) := 5;
BEGIN  			-- BEGIN block, also assign values  
	designation := UPPER('Web Developer');
	id := TRUE;
	inter := INTERVAL '45' YEAR; 

Declare Constants and Assigning values : Constants are declared same as variable but you have to add the CONSTANT keyword before defining data type. Once you define a constant value you can't change the value.

designation CONSTANT VARCHAR2(30) := 'Web Developer';


BEGIN block is procedural statement block which will implement the actual programming logic. This section contains conditional statements (if...else), looping statements (for, while) and Branching Statements (goto) etc.


PL/SQL easily detects user defined or predefined error condition. PL/SQL is famous for handling errors in smart way by giving suitable user friendly messages. Errors can be rise due to wrong syntax, bad logical or not passing a validation rules.

You can also define exception in your declarative block and later you can execute it by RAISE statement.

	check_exist EXCEPTION; -- declare exception type
	RAISE check_exist;  -- Raise exception
   WHEN check_exist THEN  -- execute raise exception	

Note :

  1. BEGIN block and END; keyword are compulsory of any PL/SQL program.
  2. Where as DECLARE and EXCEPTION block are optional.