Home
Education

Working with PL/SQL – Blocks, Transactions, and Cursors Explained

Saksham

💻 Working with PL/SQL

PL/SQL (Procedural Language for SQL) is Oracle’s extension to SQL that adds programming features like variables, loops, conditions, cursors, and exception handling. It allows developers to write blocks of code that can interact with databases efficiently and securely.

📦 Generic PL/SQL Block Structure

A PL/SQL program is divided into logical sections called blocks. Each block may contain declarations, executable statements, and exception handling parts.


DECLARE
   -- Declarations (variables, cursors, constants)
BEGIN
   -- Executable statements (SQL and procedural logic)
EXCEPTION
   -- Error handling section
END;
/

Explanation:

  • DECLARE: Optional section to declare variables or cursors.
  • BEGIN: Mandatory section that holds executable SQL and PL/SQL statements.
  • EXCEPTION: Optional section that handles runtime errors.
  • END: Marks the end of the block.

Example:


DECLARE
   v_name VARCHAR2(30) := 'Saksham';
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello ' || v_name);
END;
/

✅ Output: Hello Saksham

🔁 SQL Transactions in PL/SQL

A transaction in PL/SQL is a sequence of SQL statements that Oracle treats as a single unit of work. Transactions ensure data integrity using the principles of COMMIT and ROLLBACK.

  • COMMIT: Saves all changes permanently.
  • ROLLBACK: Cancels uncommitted changes.
  • SAVEPOINT: Creates a checkpoint to roll back partially.

Example:


BEGIN
   INSERT INTO students VALUES (101, 'Rahul', 'MCA');
   INSERT INTO students VALUES (102, 'Ananya', 'BCA');
   COMMIT;
END;
/

Example with ROLLBACK:


BEGIN
   UPDATE students SET course='MBA' WHERE id=102;
   ROLLBACK; -- Undo the change
END;
/

⚠️ Error Handling in PL/SQL

Errors in PL/SQL are called exceptions. These can be system-defined or user-defined.

1️⃣ System Defined Exceptions

Oracle automatically raises these for common runtime errors.

  • NO_DATA_FOUND – When SELECT returns no rows
  • ZERO_DIVIDE – Division by zero
  • TOO_MANY_ROWS – SELECT returns more than one row

Example:


DECLARE
   v_name students.name%TYPE;
BEGIN
   SELECT name INTO v_name FROM students WHERE id=999;
   DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No record found!');
END;
/

2️⃣ User Defined Exceptions

Developers can define custom exceptions for specific logic.

Example:


DECLARE
   ex_invalid_id EXCEPTION;
   v_id NUMBER := -5;
BEGIN
   IF v_id < 0 THEN
      RAISE ex_invalid_id;
   END IF;
EXCEPTION
   WHEN ex_invalid_id THEN
      DBMS_OUTPUT.PUT_LINE('Invalid Student ID!');
END;
/

🎯 Cursors in PL/SQL

A cursor is a pointer that allows row-by-row processing of query results. When SQL statements return multiple rows, cursors help process them one at a time.

Types of Cursors

  • Implicit Cursor: Automatically created by Oracle for SQL statements like SELECT INTO, INSERT, UPDATE, DELETE.
  • Explicit Cursor: Defined by the programmer for queries returning multiple rows.

🔹 Implicit Cursor Example


BEGIN
   UPDATE students SET course='MCA' WHERE id=101;
   IF SQL%ROWCOUNT > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Record Updated');
   END IF;
END;
/

🔹 Explicit Cursor Example


DECLARE
   CURSOR c_student IS SELECT id, name FROM students;
   v_id students.id%TYPE;
   v_name students.name%TYPE;
BEGIN
   OPEN c_student;
   LOOP
      FETCH c_student INTO v_id, v_name;
      EXIT WHEN c_student%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
   END LOOP;
   CLOSE c_student;
END;
/

✅ The cursor retrieves all student records one by one.

🔁 Cursor with FOR Loop

A FOR loop automatically opens, fetches, and closes the cursor — making code simpler.


BEGIN
   FOR rec IN (SELECT id, name FROM students) LOOP
      DBMS_OUTPUT.PUT_LINE(rec.id || ' : ' || rec.name);
   END LOOP;
END;
/

📨 Parameterized Cursor

Parameterized cursors allow passing values at runtime, making them more flexible.

Example:


DECLARE
   CURSOR c_course(c_name VARCHAR2) IS
      SELECT id, name FROM students WHERE course = c_name;
BEGIN
   FOR rec IN c_course('MCA') LOOP
      DBMS_OUTPUT.PUT_LINE(rec.id || ' - ' || rec.name);
   END LOOP;
END;
/

✅ Displays all students enrolled in MCA.

🧩 Nested Cursor

A nested cursor is one cursor inside another — often used when one query depends on another.

Example:


DECLARE
   CURSOR c_dept IS SELECT dept_id, dept_name FROM department;
   CURSOR c_emp(d_id NUMBER) IS SELECT emp_name FROM employee WHERE dept_id = d_id;
BEGIN
   FOR d IN c_dept LOOP
      DBMS_OUTPUT.PUT_LINE('Department: ' || d.dept_name);
      FOR e IN c_emp(d.dept_id) LOOP
         DBMS_OUTPUT.PUT_LINE('   Employee: ' || e.emp_name);
      END LOOP;
   END LOOP;
END;
/

✅ The inner cursor retrieves employees for each department from the outer cursor.

💡 Key Points Summary

  • PL/SQL adds procedural capabilities to SQL.
  • A PL/SQL block consists of DECLARE, BEGIN, EXCEPTION, and END sections.
  • Transactions ensure data consistency with COMMIT and ROLLBACK.
  • Exceptions (system and user-defined) handle runtime errors gracefully.
  • Cursors enable row-by-row processing of query results.
  • Parameterized and nested cursors enhance flexibility and performance.

🧠 Practice Exercise

Write a PL/SQL program to display all employees of a given department using a parameterized cursor. Add exception handling for invalid department IDs.

Written by Saksham Shekher — MCA student and web developer, sharing simple and practical explanations for core computer science topics.

Post a Comment