Unit-5: Cursors and Exception Handling:

Cursors and Exception Handling:

Cursors and Exception Handling: 5.1 Concepts of Cursors
5.1.1 Types of cursors (Implicit & Explicit )
5.1.2 Declare, open, fetch and close cursors.
5.2 Cursor Attributes :
(%FOUND,%NOTFOUND,
%ISOPEN,%ROWCOUNT)
5.3 Exception Handling in PL/SQL
5.3.1 Types of Exceptions:
5.3.1.1 Named System Exceptions
5.3.1.2 Unnamed System
Exceptions
5.3.1.3 User-defined Exceptions
5.3.1.4 User Defined Exceptions
5.3.2 Exception Handling

Cursors and Exception Handling in PL/SQL.

5.1 Concepts of Cursors.

A cursor in PL/SQL is a mechanism that allows processing multiple rows from a query one row at a time.
Since SQL queries return a result set, cursors help in handling this data efficiently within PL/SQL blocks.

PL/SQL provides two types of cursors:

Implicit Cursors – Automatically created by Oracle for SELECT, INSERT, UPDATE, or DELETE statements.
Explicit Cursors – Manually defined by the programmer to process multiple rows from a query.

5.1.1 Types of Cursors.

1. Implicit Cursors.

Created automatically when a SQL statement executes inside PL/SQL.
Used for INSERT, UPDATE, DELETE, and SELECT...INTO statements.
No need for explicit declaration, opening, or closing.
Useful when a query returns only one row.

Example of Implicit Cursor:

DECLARE
   emp_name employees.name%TYPE;
BEGIN
   SELECT name INTO emp_name FROM employees WHERE emp_id = 101;
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
END;
/

Here, Oracle creates an implicit cursor for the SELECT statement.
The cursor automatically fetches the data and closes itself.

2. Explicit Cursors.

Used when a query returns multiple rows.
Must be declared, opened, fetched, and closed explicitly.
Provides more control over data retrieval.

Example of Explicit Cursor:

DECLARE
   CURSOR emp_cursor IS SELECT name FROM employees;
   emp_name employees.name%TYPE;
BEGIN
   OPEN emp_cursor;
   FETCH emp_cursor INTO emp_name;
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
   CLOSE emp_cursor;
END;
/

Here, an explicit cursor is created for fetching employee names.
The cursor is manually opened, fetched, and closed.

5.1.2 Cursor Operations (Declare, Open, Fetch, Close)

1. Declaring a Cursor.

A cursor is declared to store a result set of a SQL query.
Declared in the DECLARE section using the CURSOR keyword.
Syntax:

CURSOR cursor_name IS query;

Example:

CURSOR emp_cursor IS SELECT name, salary FROM employees WHERE department = 'IT';

2. Opening a Cursor.

The OPEN statement allocates memory and executes the query.
It does not fetch data yet.
SYNTAX

OPEN cursor_name;

Example:

OPEN emp_cursor;

3. Fetching Data from Cursor.

The FETCH statement retrieves one row at a time.
Each FETCH moves the cursor to the next row.
SYNTAX

FETCH cursor_name INTO variable1, variable2, ...;

Example:

FETCH emp_cursor INTO emp_name, emp_salary;

4. Closing a Cursor.

The CLOSE statement releases memory used by the cursor.
SYNTAX

CLOSE cursor_name;

Example:

CLOSE emp_cursor;

5.2 Cursor Attributes.

Cursor Attribute	Description
%FOUND	Returns TRUE if the last fetch retrieved a row.
%NOTFOUND	Returns TRUE if the last fetch failed (no rows left).
%ISOPEN	Returns TRUE if the cursor is open.
%ROWCOUNT	Returns the number of rows fetched so far.

1. %FOUND Attribute.

Used to check if a row was successfully fetched.
Example :-

DECLARE
   CURSOR emp_cursor IS SELECT name FROM employees;
   emp_name employees.name%TYPE;
BEGIN
   OPEN emp_cursor;
   FETCH emp_cursor INTO emp_name;
   
   IF emp_cursor%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Data found: ' || emp_name);
   ELSE
      DBMS_OUTPUT.PUT_LINE('No data found');
   END IF;
   CLOSE emp_cursor;
END;
/

2. %NOTFOUND Attribute

Used to check if no more rows exist in the result set.

DECLARE
   CURSOR emp_cursor IS SELECT name FROM employees;
   emp_name employees.name%TYPE;
BEGIN
   OPEN emp_cursor;
   FETCH emp_cursor INTO emp_name;
   
   IF emp_cursor%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('No more records');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_name);
   END IF;
   
   CLOSE emp_cursor;
END;
/

3. %ISOPEN Attribute

Checks whether the cursor is open or closed.

DECLARE
   CURSOR emp_cursor IS SELECT name FROM employees;
BEGIN
   OPEN emp_cursor;
   
   IF emp_cursor%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('Cursor is open');
   END IF;
   
   CLOSE emp_cursor;
END;
/

4. %ROWCOUNT Attribute.

Returns number of rows fetched so far.

DECLARE
   CURSOR emp_cursor IS SELECT name FROM employees;
   emp_name employees.name%TYPE;
BEGIN
   OPEN emp_cursor;
   
   LOOP
      FETCH emp_cursor INTO emp_name;
      EXIT WHEN emp_cursor%NOTFOUND;
      
      DBMS_OUTPUT.PUT_LINE('Row ' || emp_cursor%ROWCOUNT || ': ' || emp_name);
   END LOOP;
   
   CLOSE emp_cursor;
END;
/

5.3 Exception Handling in PL/SQL.

Exception handling in PL/SQL allows developers to detect and manage errors that occur during program execution.
It ensures that errors are properly handled without abruptly terminating the execution of a PL/SQL block.
When an error occurs, PL/SQL raises an exception, which can be handled using the EXCEPTION block.
Exceptions can be predefined (system-generated) or user-defined.

5.3.1 Types of Exceptions

PL/SQL provides three types of exceptions:

Named System Exceptions (Predefined by Oracle)
Unnamed System Exceptions (Raised by the system but not predefined)
User-Defined Exceptions (Custom exceptions created by the programmer)

5.3.1.1 Named System Exceptions

Oracle provides predefined exceptions for common errors like division by zero or invalid operations.
These exceptions do not need explicit declaration.

Exception Name Error Code Description

NO_DATA_FOUND ORA-01403 Raised when a SELECT INTO statement returns no rows.

TOO_MANY_ROWS ORA-01422 Raised when a SELECT INTO statement returns more than one row.

ZERO_DIVIDE ORA-01476 Raised when attempting to divide a number by zero.

INVALID_NUMBER ORA-01722 Raised when an invalid number is used in an arithmetic operation.

Exception Name	Error Code	Description
NO_DATA_FOUND	ORA-01403	Raised when a SELECT INTO statement returns no rows.
TOO_MANY_ROWS	ORA-01422	Raised when a SELECT INTO statement returns more than one row.
ZERO_DIVIDE	ORA-01476	Raised when attempting to divide a number by zero.
INVALID_NUMBER	ORA-01722	Raised when an invalid number is used in an arithmetic operation.

DECLARE v_result NUMBER; BEGIN v_result := 10 / 0; -- This will cause a division by zero error EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed'); END; /

The ZERO_DIVIDE exception is automatically raised and handled in the EXCEPTION block.

5.3.1.2 Unnamed System Exceptions.
Some exceptions are raised by the system but do not have predefined names.
These can be handled using the WHEN OTHERS clause or by associating them with a custom name using PRAGMA EXCEPTION_INIT.
Example.

DECLARE v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM employees WHERE emp_id = 9999; -- Invalid ID EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; /

The WHEN OTHERS clause catches any unnamed system exception.
SQLERRM retrieves the error message.

User-Defined Exceptions.
User-defined exceptions allow developers to define custom error conditions that are not automatically handled by the system.
To create a user-defined exception, follow these steps:
Declare the exception in the DECLARE section.
Raise the exception using the RAISE statement.
Handle the exception in the EXCEPTION block.

DECLARE insufficient_funds EXCEPTION; -- Step 1: Declare exception v_balance NUMBER := 500; v_withdraw_amount NUMBER := 1000; BEGIN IF v_withdraw_amount > v_balance THEN RAISE insufficient_funds; -- Step 2: Raise exception END IF; v_balance := v_balance - v_withdraw_amount; EXCEPTION WHEN insufficient_funds THEN DBMS_OUTPUT.PUT_LINE('Error: Insufficient funds in the account.'); END; /

Exception Handling in PL/SQL.
PL/SQL handles exceptions using the EXCEPTION block, which consists of WHEN clauses to catch specific exceptions.
Syntax.

BEGIN -- Executable statements EXCEPTION WHEN exception_name1 THEN -- Exception handling code WHEN exception_name2 THEN -- Exception handling code WHEN OTHERS THEN -- Handle all other exceptions END; /

EXAMPLE.

DECLARE v_result NUMBER; v_salary NUMBER; BEGIN v_result := 10 / 0; -- This will cause a division by zero error SELECT salary INTO v_salary FROM employees WHERE emp_id = 9999; -- Invalid ID EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero.'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Error: No record found for given employee ID.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM); END; /

Using RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR is a built-in procedure that allows users to define custom error messages along with an error code (-20000 to -20999).

EXAMPLE.

DECLARE v_balance NUMBER := 500; v_withdraw_amount NUMBER := 1000; BEGIN IF v_withdraw_amount > v_balance THEN RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance in the account'); END IF; v_balance := v_balance - v_withdraw_amount; END; /

Leave a Reply Cancel reply
Your email address will not be published. Required fields are marked *
Comment *
Name *

Email *

Website

Save my name, email, and website in this browser for the next time I comment.

Unit-5: Cursors and Exception Handling:

Cursors and Exception Handling:

Cursors and Exception Handling in PL/SQL.

5.1 Concepts of Cursors.

A cursor in PL/SQL is a mechanism that allows processing multiple rows from a query one row at a time.

Since SQL queries return a result set, cursors help in handling this data efficiently within PL/SQL blocks.

PL/SQL provides two types of cursors:

Implicit Cursors – Automatically created by Oracle for SELECT, INSERT, UPDATE, or DELETE statements.

Explicit Cursors – Manually defined by the programmer to process multiple rows from a query.

5.1.1 Types of Cursors.

1. Implicit Cursors.

Created automatically when a SQL statement executes inside PL/SQL.

Used for INSERT, UPDATE, DELETE, and SELECT...INTO statements.

No need for explicit declaration, opening, or closing.

Useful when a query returns only one row.

Example of Implicit Cursor:

Here, Oracle creates an implicit cursor for the SELECT statement.

The cursor automatically fetches the data and closes itself.

2. Explicit Cursors.

Used when a query returns multiple rows.

Must be declared, opened, fetched, and closed explicitly.

Provides more control over data retrieval.

Example of Explicit Cursor:

Here, an explicit cursor is created for fetching employee names.

The cursor is manually opened, fetched, and closed.

5.1.2 Cursor Operations (Declare, Open, Fetch, Close)

1. Declaring a Cursor.

A cursor is declared to store a result set of a SQL query.

Declared in the DECLARE section using the CURSOR keyword.

Syntax:

Example:

2. Opening a Cursor.

The OPEN statement allocates memory and executes the query.

It does not fetch data yet.

SYNTAX

Example:

3. Fetching Data from Cursor.

The FETCH statement retrieves one row at a time.

Each FETCH moves the cursor to the next row.

SYNTAX

Example:

4. Closing a Cursor.

The CLOSE statement releases memory used by the cursor.

SYNTAX

Example:

5.2 Cursor Attributes.

1. %FOUND Attribute.

Used to check if a row was successfully fetched.

Example :-

2. %NOTFOUND Attribute

Used to check if no more rows exist in the result set.

3. %ISOPEN Attribute

Checks whether the cursor is open or closed.

4. %ROWCOUNT Attribute.

Returns number of rows fetched so far.

5.3 Exception Handling in PL/SQL.

Exception handling in PL/SQL allows developers to detect and manage errors that occur during program execution.

It ensures that errors are properly handled without abruptly terminating the execution of a PL/SQL block.

When an error occurs, PL/SQL raises an exception, which can be handled using the EXCEPTION block.

Exceptions can be predefined (system-generated) or user-defined.

5.3.1 Types of Exceptions

PL/SQL provides three types of exceptions:

Named System Exceptions (Predefined by Oracle)

Unnamed System Exceptions (Raised by the system but not predefined)

User-Defined Exceptions (Custom exceptions created by the programmer)

5.3.1.1 Named System Exceptions

Oracle provides predefined exceptions for common errors like division by zero or invalid operations.

These exceptions do not need explicit declaration.

The ZERO_DIVIDE exception is automatically raised and handled in the EXCEPTION block.

5.3.1.2 Unnamed System Exceptions.

Some exceptions are raised by the system but do not have predefined names.

These can be handled using the WHEN OTHERS clause or by associating them with a custom name using PRAGMA EXCEPTION_INIT.

Example.

The WHEN OTHERS clause catches any unnamed system exception.

SQLERRM retrieves the error message.

User-Defined Exceptions.

User-defined exceptions allow developers to define custom error conditions that are not automatically handled by the system.

To create a user-defined exception, follow these steps:

Declare the exception in the DECLARE section.

Raise the exception using the RAISE statement.

Implicit Cursors – Automatically created by Oracle for `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statements.

Used for `INSERT`, `UPDATE`, `DELETE`, and `SELECT...INTO` statements.

Here, Oracle creates an implicit cursor for the `SELECT` statement.

Declared in the DECLARE section using the `CURSOR` keyword.

The `OPEN` statement allocates memory and executes the query.

The `FETCH` statement retrieves one row at a time.

Each `FETCH` moves the cursor to the next row.

The `CLOSE` statement releases memory used by the cursor.

When an error occurs, PL/SQL raises an exception, which can be handled using the `EXCEPTION` block.

These can be handled using the `WHEN OTHERS` clause or by associating them with a custom name using PRAGMA EXCEPTION_INIT.

The `WHEN OTHERS` clause catches any unnamed system exception.

`SQLERRM` retrieves the error message.

Declare the exception in the `DECLARE` section.

Raise the exception using the `RAISE` statement.

Handle the exception in the `EXCEPTION` block.

PL/SQL handles exceptions using the `EXCEPTION` block, which consists of `WHEN` clauses to catch specific exceptions.

`RAISE_APPLICATION_ERROR` is a built-in procedure that allows users to define custom error messages along with an error code (`-20000` to `-20999`).