1. IF Condition

The IF statement is used to execute a block of code when a specified condition is true.

Syntax

IF condition THEN
   statements;
END IF;

Example

DECLARE
    num NUMBER := 15;
BEGIN
    IF num > 10 THEN
        DBMS_OUTPUT.PUT_LINE('Number is greater than 10');
    END IF;
END;
/

Output

Number is greater than 10

Description

• IF checks the condition.
• If the condition is TRUE, the statements inside THEN are executed.

2. IF-ELSE Condition

Syntax

IF condition THEN
   statements1;
ELSE
   statements2;
END IF;

Example

DECLARE
    marks NUMBER := 45;
BEGIN
    IF marks >= 35 THEN
        DBMS_OUTPUT.PUT_LINE('Pass');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Fail');
    END IF;
END;
/

Output

Pass

Description

• Executes one block if the condition is true.
• Executes the ELSE block if the condition is false.

3. IF-ELSIF-ELSE Condition

Syntax

IF condition1 THEN
    statements1;
ELSIF condition2 THEN
    statements2;
ELSE
    statements3;
END IF;

Example

DECLARE
    marks NUMBER := 82;
BEGIN
    IF marks >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('Grade A+');
    ELSIF marks >= 75 THEN
        DBMS_OUTPUT.PUT_LINE('Grade A');
    ELSIF marks >= 60 THEN
        DBMS_OUTPUT.PUT_LINE('Grade B');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Grade C');
    END IF;
END;
/

Output

Grade A

Description

• Checks multiple conditions.
• The first true condition is executed.

4. CASE Statement (Switch Case)

Oracle PL/SQL does not have switch; it uses the CASE statement.

Syntax

CASE expression
    WHEN value1 THEN
        statements;
    WHEN value2 THEN
        statements;
    ELSE
        statements;
END CASE;

Example

DECLARE
    day_no NUMBER := 3;
BEGIN
    CASE day_no
        WHEN 1 THEN
            DBMS_OUTPUT.PUT_LINE('Monday');
        WHEN 2 THEN
            DBMS_OUTPUT.PUT_LINE('Tuesday');
        WHEN 3 THEN
            DBMS_OUTPUT.PUT_LINE('Wednesday');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Invalid Day');
    END CASE;
END;
/

Output

Wednesday

Description

• Compares one expression with multiple values.
• Executes the matching block.

5. Looping Structures

A. Simple LOOP

Syntax

LOOP
   statements;
   EXIT WHEN condition;
END LOOP;

Example

DECLARE
    i NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(i);
        i := i + 1;
        EXIT WHEN i > 5;
    END LOOP;
END;
/

Output

1
2
3
4
5

Description

• Executes repeatedly until EXIT WHEN becomes true.

B. WHILE LOOP

Syntax

WHILE condition LOOP
    statements;
END LOOP;

Example

DECLARE
    i NUMBER := 1;
BEGIN
    WHILE i <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
        i := i + 1;
    END LOOP;
END;
/

Output

1
2
3
4
5

Description

• Checks the condition before each iteration.
• Executes while the condition is true.

C. FOR LOOP

Syntax

FOR variable IN start..end LOOP
    statements;
END LOOP;

Example

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

Output

1
2
3
4
5

Description

• Automatically initializes and increments the loop variable.
• No need to declare or increment the loop variable.

D. REVERSE FOR LOOP

Example

BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

Output

5
4
3
2
1

Description

• Loops from the ending value down to the starting value.

6. Operators in PL/SQL

A. Arithmetic Operators

Example

DECLARE
    a NUMBER := 20;
    b NUMBER := 5;
BEGIN
    DBMS_OUTPUT.PUT_LINE(a+b);
    DBMS_OUTPUT.PUT_LINE(a-b);
    DBMS_OUTPUT.PUT_LINE(a*b);
    DBMS_OUTPUT.PUT_LINE(a/b);
END;
/

B. Comparison (Relational) Operators

Example

DECLARE
    a NUMBER := 10;
    b NUMBER := 20;
BEGIN
    IF a < b THEN
        DBMS_OUTPUT.PUT_LINE('a is smaller');
    END IF;
END;
/

C. Logical Operators

Example

DECLARE
    age NUMBER := 22;
BEGIN
    IF age >= 18 AND age <= 60 THEN
        DBMS_OUTPUT.PUT_LINE('Eligible');
    END IF;
END;
/

D. Assignment Operator

Example

DECLARE
    name VARCHAR2(20);
BEGIN
    name := 'Oracle';
    DBMS_OUTPUT.PUT_LINE(name);
END;
/

7. SELECT INTO Statement

The SELECT INTO statement retrieves a single row from a table and stores the values into PL/SQL variables.

Syntax

SELECT column1, column2
INTO variable1, variable2
FROM table_name
WHERE condition;

Example Table

CREATE TABLE employee(
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(30),
    salary NUMBER
);

INSERT INTO employee VALUES (101,'Rahul',30000);
INSERT INTO employee VALUES (102,'Priya',45000);
COMMIT;

Example 1: Fetch One Record

DECLARE
    v_name employee.emp_name%TYPE;
    v_salary employee.salary%TYPE;
BEGIN
    SELECT emp_name, salary
    INTO v_name, v_salary
    FROM employee
    WHERE emp_id = 101;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

Output

Employee: Rahul
Salary: 30000

Description

• SELECT INTO retrieves exactly one row.
• The selected values are stored in PL/SQL variables.
• If no row is found, Oracle raises NO_DATA_FOUND.
• If more than one row is returned, Oracle raises TOO_MANY_ROWS.

Example 2: SELECT INTO with Exception Handling

DECLARE
    v_name employee.emp_name%TYPE;
BEGIN
    SELECT emp_name
    INTO v_name
    FROM employee
    WHERE emp_id = 500;

    DBMS_OUTPUT.PUT_LINE(v_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one employee found.');
END;
/

Output

Employee not found.

Summary Table