A PL/SQL Block is the basic unit of a PL/SQL program in Oracle. It is a group of SQL and PL/SQL statements that are executed together as a single unit.

Structure of a PL/SQL Block

DECLARE
   -- Variable declarations (Optional)

BEGIN
   -- Executable statements (Mandatory)

EXCEPTION
   -- Error handling statements (Optional)

END;
/

Components of a PL/SQL Block

1. DECLARE Section (Optional)
   • Used to declare variables, constants, cursors, and user-defined exceptions.
2. BEGIN Section (Mandatory)
   • Contains executable SQL and PL/SQL statements.
   • Every PL/SQL block must have a BEGIN...END section.
3. EXCEPTION Section (Optional)
   • Handles runtime errors (exceptions) that occur during execution.
4. END
   • Marks the end of the PL/SQL block.
   • / is used in SQL*Plus or SQL Developer to execute the block.

Example 1: Simple PL/SQL Block

BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');
END;
/

Output:

Hello, Oracle!

Example 2: PL/SQL Block with Variables

DECLARE
   v_name VARCHAR2(30) := 'John';
   v_age NUMBER := 25;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Name : ' || v_name);
   DBMS_OUTPUT.PUT_LINE('Age  : ' || v_age);
END;
/

Output:

Name : John
Age  : 25

Example 3: PL/SQL Block Using SQL Statement

Assume the following table:

EMPLOYEE
------------------------
EMP_ID
EMP_NAME
SALARY

DECLARE
   v_name EMPLOYEE.EMP_NAME%TYPE;
BEGIN
   SELECT EMP_NAME
   INTO v_name
   FROM EMPLOYEE
   WHERE EMP_ID = 101;

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

Example 4: PL/SQL Block with Exception Handling

DECLARE
   v_num NUMBER := 10;
   v_result NUMBER;
BEGIN
   v_result := v_num / 0;

   DBMS_OUTPUT.PUT_LINE(v_result);

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero.');
END;
/

Output:

Error: Cannot divide by zero.

Example 5: PL/SQL Block Using IF Statement

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

Output:

Pass

Types of PL/SQL Blocks

1. Anonymous Block

• Does not have a name.
• Executed only once.
• Cannot be stored in the database.

Example:

BEGIN
   DBMS_OUTPUT.PUT_LINE('Anonymous Block');
END;
/

2. Named Block

• Has a name.
• Stored in the Oracle database.
• Can be executed multiple times.

Examples:

• Procedures
• Functions
• Packages
• Triggers

%TYPE Attribute in Oracle PL/SQL

The %TYPE attribute in PL/SQL is used to declare a variable with the same data type as a table column or another variable.

This helps keep your code consistent. If the data type of the table column changes, the PL/SQL variable automatically uses the updated type.

Syntax

variable_name table_name.column_name%TYPE;

or

variable_name another_variable%TYPE;

Example 1: Using a Table Column

Suppose you have the following EMPLOYEE table:

Declare variables:

DECLARE
   v_empid EMPLOYEE.EMP_ID%TYPE;
   v_name  EMPLOYEE.EMP_NAME%TYPE;
   v_sal   EMPLOYEE.SALARY%TYPE;
BEGIN
   v_empid := 101;
   v_name := 'Amit';
   v_sal := 25000;

   DBMS_OUTPUT.PUT_LINE('ID : ' || v_empid);
   DBMS_OUTPUT.PUT_LINE('Name : ' || v_name);
   DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
END;
/

Output

ID : 101
Name : Amit
Salary : 25000

Example 2: Fetching Data from a Table

DECLARE
   v_name EMPLOYEE.EMP_NAME%TYPE;
BEGIN
   SELECT EMP_NAME
   INTO v_name
   FROM EMPLOYEE
   WHERE EMP_ID = 101;

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

Example 3: Using %TYPE with Another Variable

DECLARE
   v_salary NUMBER(8,2);
   v_bonus  v_salary%TYPE;
BEGIN
   v_salary := 30000;
   v_bonus := 5000;

   DBMS_OUTPUT.PUT_LINE('Salary : ' || v_salary);
   DBMS_OUTPUT.PUT_LINE('Bonus : ' || v_bonus);
END;
/

Advantages of %TYPE

• Automatically uses the same data type as a table column.
• No need to specify the data type manually.
• Reduces maintenance when table definitions change.
• Prevents data type mismatch errors.
• Makes code easier to read and maintain.

%TYPE vs Manual Data Type

Interview/Exam Questions

Q1. What is %TYPE in PL/SQL?
Answer: %TYPE is an attribute used to declare a variable with the same data type as a database column or another variable.

Q2. Why is %TYPE used?
Answer: It avoids manually specifying data types and automatically reflects changes made to the referenced column or variable.

Q3. Can %TYPE be used with another variable?
Answer: Yes. Example:

DECLARE
   a NUMBER;
   b a%TYPE;
BEGIN
   b := 100;
END;
/

Note: %TYPE copies only the data type of the referenced column or variable. It does not copy constraints such as NOT NULL or PRIMARY KEY.

%ROWTYPE Attribute in Oracle PL/SQL

The %ROWTYPE attribute in PL/SQL is used to declare a record variable that has the same structure (all columns) as a table or a cursor.

Instead of declaring one variable for each column, %ROWTYPE creates a single record containing all the columns of the table.

Syntax

record_name table_name%ROWTYPE;

or

record_name cursor_name%ROWTYPE;

Example 1: Using %ROWTYPE with a Table

Suppose the EMPLOYEE table contains:

DECLARE
   emp_rec EMPLOYEE%ROWTYPE;
BEGIN
   SELECT *
   INTO emp_rec
   FROM EMPLOYEE
   WHERE EMP_ID = 101;

   DBMS_OUTPUT.PUT_LINE('ID        : ' || emp_rec.EMP_ID);
   DBMS_OUTPUT.PUT_LINE('Name      : ' || emp_rec.EMP_NAME);
   DBMS_OUTPUT.PUT_LINE('Salary    : ' || emp_rec.SALARY);
   DBMS_OUTPUT.PUT_LINE('Department: ' || emp_rec.DEPARTMENT);
END;
/

Output

ID        : 101
Name      : Amit
Salary    : 25000
Department: IT

Example 2: Inserting Values Using %ROWTYPE

DECLARE
   emp_rec EMPLOYEE%ROWTYPE;
BEGIN
   emp_rec.EMP_ID := 102;
   emp_rec.EMP_NAME := 'Rahul';
   emp_rec.SALARY := 30000;
   emp_rec.DEPARTMENT := 'HR';

   INSERT INTO EMPLOYEE
   VALUES (emp_rec.EMP_ID,
           emp_rec.EMP_NAME,
           emp_rec.SALARY,
           emp_rec.DEPARTMENT);

   COMMIT;
END;
/

Example 3: Using %ROWTYPE with a Cursor

DECLARE
   CURSOR c_emp IS
      SELECT EMP_ID, EMP_NAME, SALARY
      FROM EMPLOYEE;

   emp_rec c_emp%ROWTYPE;
BEGIN
   OPEN c_emp;

   FETCH c_emp INTO emp_rec;

   DBMS_OUTPUT.PUT_LINE(emp_rec.EMP_NAME);

   CLOSE c_emp;
END;
/

Advantages of %ROWTYPE

• Declares all table columns with a single statement.
• Automatically reflects changes in the table structure.
• Reduces coding effort.
• Prevents data type mismatch errors.
• Makes programs easier to maintain.

Difference Between %TYPE and %ROWTYPE

Interview/Exam Questions

Q1. What is %ROWTYPE?
Answer: %ROWTYPE is a PL/SQL attribute that declares a record variable with the same structure as a table or cursor.

Q2. What is the advantage of %ROWTYPE?
Answer: It automatically includes all columns of a table or cursor, reducing code and automatically adapting to table structure changes.

Q3. How do you access a field in a %ROWTYPE record?
Answer: Use dot notation.

Example:

DBMS_OUTPUT.PUT_LINE(emp_rec.EMP_NAME);

Quick Memory Tip

• %TYPE → One column → One variable
• %ROWTYPE → Whole row → One record with multiple fields