Advanced PL/SQL

CURSOR

In SQL and PL/SQL, a cursor is a database object that allows you to retrieve and manipulate the result set of a query one row at a time. Cursors are particularly useful when you need to process query results sequentially rather than in a set-based manner.

Real-Life Example: A Book and Bookmark

Imagine you are reading a book, and you want to keep track of where you are so that you can resume reading later.

The Book: This is like the entire data set or result set from a database query. The book contains many pages, just like a query result set contains multiple rows.
Bookmark: A bookmark is used to mark your current position in the book. In our analogy, the bookmark represents a cursor in PL/SQL. It helps you keep track of where you are in the data set.
Opening the Book: You open the book to start reading. This is like opening the cursor, which prepares the result set for reading or processing.
Reading a Page: You read the current page of the book, then move to the next page. In terms of PL/SQL, this is like fetching a row from the cursor and processing it.
Placing the Bookmark: After reading a page, you place the bookmark on that page to remember where you stopped. This is similar to the cursor keeping track of the last row it fetched.
Closing the Book: Once you’re done reading, you close the book. This is equivalent to closing the cursor in PL/SQL, which releases any resources used.

There are two types of Cursors:
1.Implicit Cursors
2.Explicit Cursors

Implicit Cursors in PL/SQL

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed without explicitly defining a cursor. These are used internally by PL/SQL to handle SQL operations like INSERT, UPDATE, DELETE, and SELECT INTO that return a single row.

Characteristics of Implicit Cursors

Automatic Management: Implicit cursors are automatically opened, fetched, and closed by Oracle. You don’t need to explicitly declare, open, fetch, or close them.
Single Row Operations: They are used for SQL statements that return exactly one row. If a SELECT INTO query returns more than one row or no rows, it raises an exception.
Cursor Attributes: Implicit cursors provide several attributes that can be used to obtain information about the SQL operation’s execution (like %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN).

Implicit Cursor Attributes

SQL%FOUND:
- Returns TRUE if the last SQL operation affected one or more rows.
- Returns FALSE if the last SQL operation affected no rows.
SQL%NOTFOUND:
- Returns TRUE if the last SQL operation affected no rows.
- Returns FALSE if the last SQL operation affected one or more rows.
SQL%ROWCOUNT:
- Returns the number of rows affected by the last SQL operation.
SQL%ISOPEN:
- Always returns FALSE because implicit cursors are automatically closed after execution.

Example of Implicit Cursor in Action

Table: `EMPLOYEES`

Suppose you have an EMPLOYEES table that looks like this:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT_ID
100	John	Doe	5000	10
101	Jane	Smith	5500	20
102	Akhil	Kumar	6000	10
103	Yaswanth	Reddy	6500	30
104	Bhanu	Prakash	7000	20

Example 1: Using an Implicit Cursor for `UPDATE`

Let’s say you want to give a raise to all employees in department 10 by updating their salaries.

BEGIN
    -- Update the salaries of employees in department 10
    UPDATE employees
    SET salary = salary + 500
    WHERE department_id = 10;
    
    -- Check how many rows were affected
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Salaries updated for department 10.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found in department 10.');
    END IF;
    
    -- Display the number of rows updated
    DBMS_OUTPUT.PUT_LINE('Number of rows updated: ' || SQL%ROWCOUNT);
END;
/

Explanation

SQL Operation: The UPDATE statement increases the salary of employees in department 10 by 500. Oracle uses an implicit cursor to manage this operation.
SQL%FOUND: This attribute checks if any rows were updated. If SQL%FOUND is TRUE, it means the UPDATE affected at least one row.
SQL%ROWCOUNT: This attribute tells you the number of rows that were updated. If two employees are in department 10, SQL%ROWCOUNT would return 2.

Example 2: Using an Implicit Cursor for `SELECT INTO`

Now, let’s retrieve the salary of an employee with EMPLOYEE_ID = 102 and store it in a variable.

DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    -- Fetch the salary of the employee with ID 102
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 102;
    
    -- Display the salary
    DBMS_OUTPUT.PUT_LINE('Salary of Employee 102: ' || v_salary);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID 102.');
END;
/

Explanation

SQL Operation: The SELECT INTO statement fetches the salary of the employee with EMPLOYEE_ID = 102. An implicit cursor is used to handle this operation.
NO_DATA_FOUND Exception: If no employee with EMPLOYEE_ID = 102 is found, the NO_DATA_FOUND exception is raised. This exception handling ensures that your program doesn’t crash when no data is returned.
SQL%ROWCOUNT (Not Shown Here): If needed, SQL%ROWCOUNT can be used to check how many rows were fetched, though it is typically used with UPDATE, DELETE, or INSERT operations.

Conclusion

In these examples, implicit cursors allow you to efficiently manage single-row operations and provide a straightforward way to handle SQL statements without the overhead of explicit cursor management. This makes them very useful for simple, direct SQL operations within PL/SQL blocks.

Explicit Cursors

Explicit cursors in PL/SQL are used to process query results that return multiple rows. Unlike implicit cursors, explicit cursors must be explicitly declared, opened, fetched, and closed by the programmer. They provide more control over the query execution process and are particularly useful when you need to process each row individually.

Steps to Use Explicit Cursors

Declare the Cursor: Define a cursor to hold the query that you want to execute.
Open the Cursor: Execute the query and prepare the result set.
Fetch the Rows: Retrieve each row from the result set one at a time.
Process the Rows: Perform operations on the data fetched from the cursor.
Close the Cursor: Release the memory associated with the cursor.

Structure of an Explicit Cursor

Here’s a detailed look at how to work with explicit cursors in PL/SQL:

1. Declaring a Cursor

You start by declaring a cursor in the DECLARE section of your PL/SQL block. The cursor is associated with a SELECT query that you want to execute.

DECLARE
    CURSOR cursor_name IS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

For example, if you want to retrieve the employee_id, first_name, and salary of all employees in department 10, you would declare the cursor like this:

DECLARE
    CURSOR emp_cursor IS
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = 10;

2. Opening a Cursor

Once the cursor is declared, you need to open it to execute the query and retrieve the result set. This step allocates memory for the cursor and prepares it for fetching rows.

OPEN emp_cursor;

3. Fetching Rows from the Cursor

After opening the cursor, you can fetch rows one by one. The FETCH statement retrieves the next row from the result set and assigns the values to PL/SQL variables.

FETCH emp_cursor INTO variable1, variable2, ...;

For example:

DECLARE
    v_emp_id    employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_salary    employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;

    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_salary;
        
        EXIT WHEN emp_cursor%NOTFOUND; -- Exit loop if no more rows
        
        -- Process each row
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_first_name || ', Salary: ' || v_salary);
    END LOOP;
    
    CLOSE emp_cursor; -- Close the cursor when done
END;
/

4. Closing the Cursor

After processing all the rows, you must close the cursor to release the resources (memory) associated with it.

CLOSE emp_cursor;

Example: Processing Employees with an Explicit Cursor

Let’s assume you have the following employees table:

employee_id	first_name	last_name	department_id	salary	hire_date
100	Bhanu	Kumar	10	5000	2020-01-15
101	Akhil	Sharma	20	5500	2019-11-20
102	Yaswanth	Reddy	10	6000	2021-03-10
103	Priya	Singh	30	7000	2022-06-18
104	John	Doe	20	4500	2020-09-12

You want to retrieve and display the details of all employees in department 10. Here’s how you can use an explicit cursor to do that:

DECLARE
    -- Declare the cursor to fetch employee details from department 10
    CURSOR emp_cursor IS
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = 10;
    
    -- Variables to hold the fetched data
    v_emp_id    employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_salary    employees.salary%TYPE;
BEGIN
    -- Open the cursor (execute the query)
    OPEN emp_cursor;

    -- Loop through all rows fetched by the cursor
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_salary;
        
        -- Exit the loop if no more rows are found
        EXIT WHEN emp_cursor%NOTFOUND;
        
        -- Process each row (display employee details)
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_first_name || ', Salary: ' || v_salary);
    END LOOP;
    
    -- Close the cursor (release resources)
    CLOSE emp_cursor;
END;
/

Explanation of the Example

Cursor Declaration: The cursor emp_cursor is declared with a SELECT query that retrieves the employee_id, first_name, and salary for all employees in department 10.
Opening the Cursor: The OPEN emp_cursor; statement executes the query and prepares the result set.
Fetching Rows: The FETCH emp_cursor INTO ...; statement retrieves each row from the result set into the variables v_emp_id, v_first_name, and v_salary.
Processing the Data: Inside the loop, the fetched data is processed. In this case, it’s simply printed using DBMS_OUTPUT.PUT_LINE.
Exiting the Loop: The loop exits when there are no more rows to fetch (emp_cursor%NOTFOUND).
Closing the Cursor: After processing all rows, the cursor is closed using CLOSE emp_cursor;.

Cursor Attributes

Explicit cursors have the following attributes, similar to implicit cursors:

%FOUND: Returns TRUE if the last fetch returned a row, otherwise FALSE.
%NOTFOUND: Returns TRUE if the last fetch did not return a row, otherwise FALSE.
%ROWCOUNT: Returns the number of rows fetched so far.
%ISOPEN: Returns TRUE if the cursor is open, otherwise FALSE.

Advanced Usage: Cursor FOR Loop

PL/SQL also provides a convenient way to work with explicit cursors using a FOR loop. This loop automatically opens the cursor, fetches each row, and closes the cursor when done.

BEGIN
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.first_name || ', Salary: ' || emp_rec.salary);
    END LOOP;
END;
/

Explanation

The FOR loop automatically handles the cursor operations (opening, fetching, and closing).
The loop variable emp_rec acts as a record that holds the fetched row.

Conclusion

Explicit cursors in PL/SQL offer powerful control over row-by-row processing of query results. They are essential for tasks that require more detailed management of query execution, especially when dealing with multiple rows. The explicit control over cursor operations makes them versatile and useful for complex data processing in PL/SQL.

Trigger

A trigger is a special type of stored procedure in a database that automatically executes or fires when a specified event occurs. Triggers are most commonly used for enforcing business rules, maintaining audit trails, and synchronizing data.

Key Characteristics of Triggers

Automatic Execution: Triggers are automatically executed (fired) in response to certain events on a table or view.
Event-Driven: They are activated by events such as INSERT, UPDATE, or DELETE operations.
Before or After: Triggers can be set to fire before or after the triggering event occurs.
Row-Level or Statement-Level: Triggers can be defined to execute for each affected row (row-level) or once for the entire statement (statement-level).

Real-Life Example: Library Book Borrowing System

Imagine a library where members can borrow books. The library wants to keep track of when books are borrowed or returned to ensure that the inventory is up-to-date and to prevent a book from being borrowed if it’s already out.

In this scenario, you could use triggers to manage the borrowing and returning process.

Scenario Details

Table: books: This table contains information about all the books in the library.book_idtitleauthoravailablelast_borrowed_bylast_borrowed_date1The Great GatsbyF. Scott’Y’NULLNULL21984George Orwell’N’1022024-07-253To Kill a MockingbirdHarper Lee’Y’NULLNULL
Table: borrow_log: This table records each borrowing transaction.log_idbook_idmember_idborrow_datereturn_date121022024-07-25NULL

Trigger to Prevent Double Borrowing

Let’s say we want to create a trigger that prevents a member from borrowing a book if it’s already borrowed by someone else.

CREATE OR REPLACE TRIGGER prevent_double_borrow
BEFORE INSERT ON borrow_log
FOR EACH ROW
BEGIN
    -- Check if the book is available
    IF EXISTS (
        SELECT 1 FROM books WHERE book_id = :NEW.book_id AND available = 'N'
    ) THEN
        -- Raise an exception to prevent the INSERT operation
        RAISE_APPLICATION_ERROR(-20001, 'This book is already borrowed.');
    END IF;
END;
/

Explanation of the Trigger

Trigger Name: The trigger is named prevent_double_borrow.
Triggering Event: This trigger fires before an INSERT operation on the borrow_log table.
FOR EACH ROW: This means the trigger will execute for each row that is inserted.
Condition Check:
- The trigger checks the books table to see if the book being borrowed (:NEW.book_id) is marked as available = 'N'.
- If the book is not available, it raises an exception using RAISE_APPLICATION_ERROR, preventing the insertion into the borrow_log table.

Trigger to Update Book Availability

Another useful trigger could automatically update the books table when a book is borrowed or returned. For example:

CREATE OR REPLACE TRIGGER update_book_status
AFTER INSERT ON borrow_log
FOR EACH ROW
BEGIN
    -- Update the books table to mark the book as borrowed
    UPDATE books
    SET available = 'N',
        last_borrowed_by = :NEW.member_id,
        last_borrowed_date = :NEW.borrow_date
    WHERE book_id = :NEW.book_id;
END;
/

Explanation of the Trigger

Trigger Name: The trigger is named update_book_status.
Triggering Event: This trigger fires after an INSERT operation on the borrow_log table.
FOR EACH ROW: This means the trigger will execute for each row that is inserted.
Updating Book Availability:
- The trigger updates the books table to mark the book as borrowed by setting available = 'N'.
- It also updates last_borrowed_by and last_borrowed_date to reflect the borrowing details.

Trigger to Handle Book Returns

A trigger could also be created to handle book returns by marking the book as available again:

CREATE OR REPLACE TRIGGER handle_book_return
BEFORE UPDATE OF return_date ON borrow_log
FOR EACH ROW
BEGIN
    -- Update the books table to mark the book as available
    UPDATE books
    SET available = 'Y',
        last_borrowed_by = NULL,
        last_borrowed_date = NULL
    WHERE book_id = :OLD.book_id;
END;
/

Explanation of the Trigger

Trigger Name: The trigger is named handle_book_return.
Triggering Event: This trigger fires before the return_date is updated in the borrow_log table.
FOR EACH ROW: This means the trigger will execute for each row that is updated.
Updating Book Availability:
- The trigger updates the books table to mark the book as available by setting available = 'Y'.
- It also clears the last_borrowed_by and last_borrowed_date fields.

Summary

Triggers: Automatically execute when specific database events occur.
Use Cases: Enforcing rules, maintaining data integrity, and automatically updating related tables.
Real-Life Example: Managing a library’s book borrowing system, where triggers ensure that books can’t be double-borrowed and that the system stays up-to-date when books are borrowed or returned.

Triggers are powerful tools for automating tasks and ensuring the consistency and integrity of your data without requiring manual intervention.

Exception Handling in PL/SQL

Exception handling in PL/SQL is a mechanism to handle errors that occur during the execution of a PL/SQL program. When an error occurs, the normal flow of the program is disrupted, and the program stops executing. Exception handling allows you to catch these errors and take appropriate action, ensuring that the program can continue to run smoothly or fail gracefully.

Types of Exceptions

There are two main types of exceptions in PL/SQL:

Predefined Exceptions: These are standard exceptions that PL/SQL defines for common error conditions, such as NO_DATA_FOUND or ZERO_DIVIDE.
User-Defined Exceptions: These are exceptions that you can define in your PL/SQL code to handle specific situations that are not covered by predefined exceptions.

Structure of Exception Handling

In a PL/SQL block, exception handling is done in the EXCEPTION section, which is optional. The general structure is:

DECLARE
   -- Variable declarations
   -- User-defined exceptions
BEGIN
   -- PL/SQL statements
EXCEPTION
   -- Exception handling code
   WHEN exception_name1 THEN
      -- Actions to take when exception_name1 occurs
   WHEN exception_name2 THEN
      -- Actions to take when exception_name2 occurs
   WHEN OTHERS THEN
      -- Actions to take for all other exceptions
END;
/

Real-Life Example: Bank Account Management System

Consider a banking system where you need to transfer money from one account to another. During this process, several things could go wrong, such as:

The source account might not have enough funds.
The account numbers might not exist.
A division by zero error could occur during calculations (e.g., calculating interest rates).

Exception handling helps manage these issues smoothly.

Scenario Details

Let’s assume we have a table accounts that stores the account balances.

`accounts` Table

account_id	account_holder	balance
1	Bhanu	5000
2	Akhil	3000
3	Yaswanth	7000

Example: Transfer Funds with Exception Handling

Let’s write a PL/SQL block to transfer money between two accounts, with exception handling to manage potential errors.

DECLARE
    v_source_account   accounts.account_id%TYPE := 1; -- Source account ID
    v_target_account   accounts.account_id%TYPE := 2; -- Target account ID
    v_transfer_amount  NUMBER := 2000;                -- Amount to transfer
    
    -- User-defined exception for insufficient funds
    insufficient_funds EXCEPTION;
    
    v_source_balance   accounts.balance%TYPE;
BEGIN
    -- Retrieve the source account balance
    SELECT balance INTO v_source_balance
    FROM accounts
    WHERE account_id = v_source_account;

    -- Check if the source account has enough funds
    IF v_source_balance < v_transfer_amount THEN
        RAISE insufficient_funds;
    END IF;

    -- Deduct the amount from the source account
    UPDATE accounts
    SET balance = balance - v_transfer_amount
    WHERE account_id = v_source_account;

    -- Add the amount to the target account
    UPDATE accounts
    SET balance = balance + v_transfer_amount
    WHERE account_id = v_target_account;

    -- Commit the transaction
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Transfer successful!');
    
EXCEPTION
    -- Handle insufficient funds
    WHEN insufficient_funds THEN
        DBMS_OUTPUT.PUT_LINE('Error: Insufficient funds in source account.');

    -- Handle "no data found" error
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Account not found.');

    -- Handle other exceptions
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
        ROLLBACK;  -- Rollback the transaction if any other error occurs
END;
/

Explanation of the Example

Variable Declarations:
- v_source_account and v_target_account: Hold the source and target account IDs.
- v_transfer_amount: The amount to be transferred.
- insufficient_funds: A user-defined exception to handle cases where the source account doesn’t have enough balance.
Main Execution Block:
- The balance of the source account is retrieved.
- A check is performed to ensure that the source account has enough funds. If not, the insufficient_funds exception is raised.
- If there are sufficient funds, the amount is deducted from the source account and added to the target account.
- If everything is successful, the transaction is committed, and a success message is displayed.
Exception Handling Block:
- insufficient_funds: This block catches the custom exception and displays an error message if the source account does not have enough funds.
- NO_DATA_FOUND: This block handles situations where the account IDs provided do not exist in the database.
- OTHERS: This block catches all other exceptions, displaying a generic error message and rolling back the transaction to maintain data integrity.

Common Predefined Exceptions

Here are some common predefined exceptions in PL/SQL:

NO_DATA_FOUND: Raised when a SELECT INTO statement does not return any rows.
TOO_MANY_ROWS: Raised when a SELECT INTO statement returns more than one row.
ZERO_DIVIDE: Raised when there is an attempt to divide a number by zero.
INVALID_NUMBER: Raised when a conversion to a number fails (e.g., trying to convert a string to a number when the string does not represent a valid number).

User-Defined Exceptions

You can also define your own exceptions to handle specific error conditions. For example, in the transfer funds example, the insufficient_funds exception was a user-defined exception. You declare user-defined exceptions in the DECLARE section and raise them using the RAISE statement.

Exception Propagation

If an exception is raised in a block but not handled there, it propagates to the enclosing block. If not handled anywhere, the exception propagates to the host environment (like SQL*Plus or an application), where it might terminate the program.

Example of Nested Blocks

DECLARE
    insufficient_funds EXCEPTION;
    v_balance NUMBER;
BEGIN
    BEGIN
        -- Nested block for a specific transaction
        SELECT balance INTO v_balance FROM accounts WHERE account_id = 1;
        
        IF v_balance < 1000 THEN
            RAISE insufficient_funds;
        END IF;
        
        DBMS_OUTPUT.PUT_LINE('Transaction Approved');
    EXCEPTION
        WHEN insufficient_funds THEN
            DBMS_OUTPUT.PUT_LINE('Nested Block: Insufficient funds.');
    END;
    
    DBMS_OUTPUT.PUT_LINE('Outer Block Continues...');
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Outer Block: An unexpected error occurred.');
END;
/

Explanation of Nested Blocks

The nested block handles the insufficient_funds exception locally, allowing the outer block to continue executing normally.
If the exception were not handled in the nested block, it would propagate to the outer block, where it could be handled or cause the program to terminate.

Conclusion

Exception handling in PL/SQL is a powerful feature that ensures robust and error-tolerant code. It allows developers to manage errors gracefully, providing the opportunity to correct issues, retry operations, or at least inform the user of what went wrong. Whether dealing with predefined exceptions or creating custom ones, proper exception handling is crucial for building reliable and maintainable applications.

Built-In Exceptions

In PL/SQL, built-in exceptions (also known as predefined exceptions) are a set of exceptions that the PL/SQL runtime environment automatically raises when certain standard error conditions occur. These exceptions cover common runtime errors like division by zero, attempting to insert a null value where it’s not allowed, or trying to select a non-existent record.

Common Built-In Exceptions

Here are some of the most frequently used built-in exceptions:

1.NO_DATA_FOUND:

Raised When: A SELECT INTO statement does not return any rows.

Example: When trying to select a record that doesn’t exist in the database

DECLARE
    v_name employees.first_name%TYPE;
BEGIN
    SELECT first_name INTO v_name FROM employees WHERE employee_id = 999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with this ID.');
END;

2.TOO_MANY_ROWS:

Raised When: A SELECT INTO statement returns more than one row.
Example: When a query is expected to return a single row but returns multiple rows

DECLARE
    v_name employees.first_name%TYPE;
BEGIN
    SELECT first_name INTO v_name FROM employees WHERE department_id = 10;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Query returned more than one row.');
END;

3.ZERO_DIVIDE:

Raised When: An attempt is made to divide a number by zero.
Example: When dividing two numbers, and the divisor is zero

DECLARE
    v_result NUMBER;
BEGIN
    v_result := 10 / 0;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Cannot divide by zero.');
END;

4.INVALID_NUMBER:

Raised When: An attempt is made to convert a non-numeric string to a number.
Example: When trying to convert an invalid string to a number.

DECLARE
    v_number NUMBER;
BEGIN
    v_number := TO_NUMBER('ABC');
EXCEPTION
    WHEN INVALID_NUMBER THEN
        DBMS_OUTPUT.PUT_LINE('Invalid number format.');
END;

5.VALUE_ERROR:

Raised When: An arithmetic, conversion, truncation, or size constraint error occurs.
Example: When trying to assign a value to a variable that is out of the acceptable range

DECLARE
    v_number NUMBER(2);
BEGIN
    v_number := 123; -- This will raise a VALUE_ERROR because the number is too large.
EXCEPTION
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Value out of range.');
END;

ACCESS_INTO_NULL:
- Raised When: An attempt is made to assign values to an attribute of an uninitialized object.Example: When trying to access or modify a property of an uninitialized object type.
CASE_NOT_FOUND:
- Raised When: None of the WHEN clauses in a CASE statement are matched, and there is no ELSE clause.Example: When a CASE statement does not handle a particular condition.
CURSOR_ALREADY_OPEN:
- Raised When: An attempt is made to open a cursor that is already open.Example: When trying to open a cursor without first closing it.
DUP_VAL_ON_INDEX:
- Raised When: An attempt is made to insert a duplicate value into a column with a unique index or primary key.Example: When trying to insert a duplicate employee ID into the employees table.
LOGIN_DENIED:
- Raised When: A login attempt is made with an invalid username or password.Example: When trying to connect to the database with incorrect credentials.

Handling Built-In Exceptions

You handle built-in exceptions in the EXCEPTION section of your PL/SQL block. The WHEN clause is used to specify the exception name, followed by the code to execute when that exception is raised.

Example: Handling Multiple Built-In Exceptions

DECLARE
    v_emp_id    employees.employee_id%TYPE := 999; -- Non-existent ID
    v_salary    employees.salary%TYPE;
BEGIN
    -- Attempt to select a non-existent employee
    SELECT salary INTO v_salary FROM employees WHERE employee_id = v_emp_id;
    
    -- Attempt to divide by zero
    v_salary := v_salary / 0;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with this ID.');
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Cannot divide by zero.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/

Summary

Built-in exceptions are predefined in PL/SQL to handle common runtime errors automatically.
Common exceptions include NO_DATA_FOUND, ZERO_DIVIDE, INVALID_NUMBER, and others.
Handling exceptions allows you to manage errors gracefully and maintain the stability of your PL/SQL programs.

User-defined exceptions in PL/SQL

User-defined exceptions in PL/SQL allow you to define your own error conditions, which can be specific to your application’s business logic. These exceptions give you more control over how to handle certain situations that aren’t covered by PL/SQL’s predefined exceptions.

Steps to Create and Use User-Defined Exceptions

Declare the Exception: In the DECLARE section of your PL/SQL block or package, declare a variable of type EXCEPTION.
Raise the Exception: Use the RAISE statement to trigger the exception when a certain condition is met.
Handle the Exception: In the EXCEPTION section, use a WHEN clause to specify what should happen when the exception is raised.

Real-Life Example: Order Processing System

Imagine an order processing system where customers place orders for products. The system needs to ensure that:

The ordered quantity doesn’t exceed the available stock.
The customer has sufficient credit to place the order.

If either of these conditions isn’t met, the system should raise an error and handle it appropriately.

Scenario Details

Table: products: Stores product details and available stock.product_idproduct_nameavailable_stock1Laptop102Smartphone53Headphones20
Table: customers: Stores customer details and credit limits.customer_idcustomer_namecredit_limit101Bhanu50000102Akhil30000103Yaswanth10000

User-Defined Exceptions Example

Let’s create a PL/SQL block to handle these business rules using user-defined exceptions.

DECLARE
    v_product_id      products.product_id%TYPE := 1; -- Product to be ordered
    v_order_quantity  NUMBER := 15;                  -- Quantity to order
    v_customer_id     customers.customer_id%TYPE := 103; -- Customer placing the order
    v_product_stock   products.available_stock%TYPE;
    v_customer_credit customers.credit_limit%TYPE;
    
    -- User-defined exceptions
    insufficient_stock EXCEPTION;
    insufficient_credit EXCEPTION;
    
BEGIN
    -- Check product stock
    SELECT available_stock INTO v_product_stock
    FROM products
    WHERE product_id = v_product_id;
    
    IF v_order_quantity > v_product_stock THEN
        RAISE insufficient_stock;
    END IF;
    
    -- Check customer credit
    SELECT credit_limit INTO v_customer_credit
    FROM customers
    WHERE customer_id = v_customer_id;
    
    IF v_order_quantity * 1000 > v_customer_credit THEN -- Assuming price per unit is 1000
        RAISE insufficient_credit;
    END IF;
    
    -- If all checks pass, process the order
    UPDATE products
    SET available_stock = available_stock - v_order_quantity
    WHERE product_id = v_product_id;
    
    DBMS_OUTPUT.PUT_LINE('Order processed successfully!');
    
EXCEPTION
    WHEN insufficient_stock THEN
        DBMS_OUTPUT.PUT_LINE('Error: Insufficient stock for the product.');
    WHEN insufficient_credit THEN
        DBMS_OUTPUT.PUT_LINE('Error: Customer has insufficient credit.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/

Explanation of the Example

Declare the Exception:
- insufficient_stock and insufficient_credit are declared as user-defined exceptions.
Main Execution Block:
- The available_stock of the product is checked. If the v_order_quantity exceeds v_product_stock, the insufficient_stock exception is raised.
- The customer’s credit_limit is checked. If the cost of the order (assumed to be v_order_quantity * 1000) exceeds the customer’s credit limit, the insufficient_credit exception is raised.
- If both checks pass, the product stock is updated to reflect the order, and a success message is displayed.
Exception Handling Block:
- insufficient_stock: This block handles cases where the ordered quantity exceeds the available stock, displaying an appropriate error message.
- insufficient_credit: This block handles cases where the customer doesn’t have enough credit, displaying an error message.
- OTHERS: This block catches all other exceptions, ensuring that the program handles any unexpected errors gracefully.

Raising User-Defined Exceptions in Functions or Procedures

User-defined exceptions can also be raised within functions or procedures. Here’s an example using a procedure:

CREATE OR REPLACE PROCEDURE process_order(
    p_product_id      IN products.product_id%TYPE,
    p_order_quantity  IN NUMBER,
    p_customer_id     IN customers.customer_id%TYPE
) IS
    v_product_stock   products.available_stock%TYPE;
    v_customer_credit customers.credit_limit%TYPE;
    
    -- User-defined exceptions
    insufficient_stock EXCEPTION;
    insufficient_credit EXCEPTION;
    
BEGIN
    -- Check product stock
    SELECT available_stock INTO v_product_stock
    FROM products
    WHERE product_id = p_product_id;
    
    IF p_order_quantity > v_product_stock THEN
        RAISE insufficient_stock;
    END IF;
    
    -- Check customer credit
    SELECT credit_limit INTO v_customer_credit
    FROM customers
    WHERE customer_id = p_customer_id;
    
    IF p_order_quantity * 1000 > v_customer_credit THEN
        RAISE insufficient_credit;
    END IF;
    
    -- Process the order
    UPDATE products
    SET available_stock = available_stock - p_order_quantity
    WHERE product_id = p_product_id;
    
    DBMS_OUTPUT.PUT_LINE('Order processed successfully!');
    
EXCEPTION
    WHEN insufficient_stock THEN
        DBMS_OUTPUT.PUT_LINE('Error: Insufficient stock for the product.');
    WHEN insufficient_credit THEN
        DBMS_OUTPUT.PUT_LINE('Error: Customer has insufficient credit.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END process_order;
/

Explanation of Procedure

Procedure Declaration: The procedure process_order takes three parameters: p_product_id, p_order_quantity, and p_customer_id.
Exception Handling: Similar to the block example, the procedure raises and handles user-defined exceptions (insufficient_stock and insufficient_credit).

Benefits of User-Defined Exceptions

Custom Error Handling: You can tailor error handling to specific business rules or application needs.
Improved Code Readability: By using meaningful exception names, the code becomes easier to understand.
Centralized Error Management: Handling exceptions in a single place can make maintenance easier and reduce code duplication.

Conclusion

User-defined exceptions are powerful tools that allow developers to handle specific error conditions that aren’t covered by predefined exceptions. They enable more precise error handling and improve the robustness and maintainability of PL/SQL programs.

Propagation and Re-raising of Exceptions

In PL/SQL, exception propagation and re-raising of exceptions are mechanisms that allow errors to be handled and passed up through nested blocks or to higher levels in the calling chain. This ensures that exceptions are managed appropriately, even if they occur deep within the program.

Exception Propagation

Exception propagation refers to the process where an exception, if not handled in the current block, is passed (or propagated) to the enclosing block. This continues until the exception is either handled or it propagates out of the topmost block, causing the program to terminate.

Real-Life Example: Banking System

Imagine a banking system where a customer is trying to withdraw money from an ATM. The process involves multiple steps, each potentially containing nested blocks:

Check the account balance.
Process the withdrawal.
Update the account balance.
Record the transaction.

Each of these steps might be enclosed within its own block or procedure. If an exception occurs (e.g., insufficient funds), it might propagate up the call chain until it’s handled.

Example: Exception Propagation

Let’s create a PL/SQL block that mimics this scenario:

DECLARE
    v_account_balance NUMBER := 5000; -- Example account balance
    v_withdrawal_amount NUMBER := 6000; -- Amount to withdraw

    -- Custom exception
    insufficient_funds EXCEPTION;
    
    -- Procedure to record transaction
    PROCEDURE record_transaction IS
    BEGIN
        -- Simulate a potential error during transaction recording
        RAISE NO_DATA_FOUND; -- Assume no data found in some lookup
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Error recording transaction: Data not found.');
            -- Let the exception propagate to the calling block
            RAISE;
    END;

BEGIN
    -- Check if there are sufficient funds
    IF v_withdrawal_amount > v_account_balance THEN
        RAISE insufficient_funds;
    END IF;
    
    -- Process the withdrawal
    v_account_balance := v_account_balance - v_withdrawal_amount;
    
    -- Attempt to record the transaction
    record_transaction;
    
    DBMS_OUTPUT.PUT_LINE('Withdrawal successful!');
    
EXCEPTION
    WHEN insufficient_funds THEN
        DBMS_OUTPUT.PUT_LINE('Error: Insufficient funds.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/

Explanation of Propagation in Example:

Withdrawal Process: The block checks if the withdrawal amount exceeds the account balance. If it does, the insufficient_funds exception is raised.
Transaction Recording: The record_transaction procedure raises a NO_DATA_FOUND exception, simulating a scenario where something goes wrong during transaction recording.
Exception Handling:
- The record_transaction procedure catches the NO_DATA_FOUND exception, prints a message, and then re-raises the exception using RAISE.
- The re-raised exception propagates back to the main block, which catches it under WHEN OTHERS and prints a general error message.
- If the insufficient_funds exception is raised, it’s caught in the main block and handled appropriately.

Re-Raising Exceptions

Re-raising an exception involves capturing an exception within a block and then explicitly raising it again to propagate it further up the call chain. This is useful when you need to perform some cleanup or logging before passing the exception on.

Real-Life Example: E-commerce Order Processing

Consider an e-commerce platform where an order needs to be processed. The steps include:

Verify inventory.
Process payment.
Update inventory.
Confirm order.

If any step fails, the exception should be logged, and the error should be propagated back to the caller to handle it at a higher level, perhaps by notifying the user.

Example: Re-Raising an Exception

Here’s how this could be implemented in PL/SQL:

DECLARE
    v_product_id      NUMBER := 101; -- Product to be ordered
    v_order_quantity  NUMBER := 2;   -- Quantity to order
    v_available_stock NUMBER := 1;   -- Available stock
    v_payment_status  VARCHAR2(20);  -- Payment status

    -- Custom exceptions
    insufficient_stock EXCEPTION;
    payment_failed EXCEPTION;
    
    -- Procedure to process payment
    PROCEDURE process_payment IS
    BEGIN
        -- Simulate a payment failure
        v_payment_status := 'FAILED';
        
        IF v_payment_status = 'FAILED' THEN
            RAISE payment_failed;
        END IF;
    EXCEPTION
        WHEN payment_failed THEN
            DBMS_OUTPUT.PUT_LINE('Payment failed: Logging the error.');
            -- Re-raise the exception to propagate it to the calling block
            RAISE;
    END;
    
BEGIN
    -- Check if there is sufficient stock
    IF v_order_quantity > v_available_stock THEN
        RAISE insufficient_stock;
    END IF;
    
    -- Attempt to process the payment
    process_payment;
    
    -- If payment succeeds, update inventory
    v_available_stock := v_available_stock - v_order_quantity;
    
    DBMS_OUTPUT.PUT_LINE('Order processed successfully!');
    
EXCEPTION
    WHEN insufficient_stock THEN
        DBMS_OUTPUT.PUT_LINE('Error: Insufficient stock for the product.');
    WHEN payment_failed THEN
        DBMS_OUTPUT.PUT_LINE('Order failed due to payment issues.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
/

Explanation of Re-Raising in Example:

Stock Check: The main block checks if the ordered quantity exceeds the available stock. If it does, the insufficient_stock exception is raised.
Payment Processing: The process_payment procedure raises a payment_failed exception if the payment status is FAILED.
Exception Handling:
- The process_payment procedure catches the payment_failed exception, logs an error message, and then re-raises the exception.
- The re-raised exception is caught in the main block under the WHEN payment_failed clause, where it is handled by printing an appropriate message.
- If an insufficient_stock exception is raised, it is caught in the main block and handled accordingly.

Key Points on Exception Propagation and Re-Raising

Propagation: If an exception is not handled in a block, it automatically propagates to the enclosing block. This continues until the exception is either handled or it propagates out of all blocks, leading to program termination.
Re-Raising: Capturing an exception and then re-raising it allows for intermediate actions (like logging) before passing the error to a higher level.
Selective Handling: By selectively handling specific exceptions and allowing others to propagate, you can create robust error management strategies that maintain application stability.

Summary

Exception Propagation: Allows unhandled exceptions to be passed up the call chain, ensuring that they are managed at the appropriate level.
Re-Raising Exceptions: Provides flexibility by allowing an exception to be caught, processed (e.g., logged), and then passed on to a higher-level block for further handling.
Real-Life Scenarios: Examples from banking, e-commerce, and other domains demonstrate how propagation and re-raising can be used to manage errors effectively in complex systems.

Advanced PL/SQL

CURSOR

Real-Life Example: A Book and Bookmark

Implicit Cursors in PL/SQL

Characteristics of Implicit Cursors

Implicit Cursor Attributes

Example of Implicit Cursor in Action

Table: EMPLOYEES

Example 1: Using an Implicit Cursor for UPDATE

Explanation

Example 2: Using an Implicit Cursor for SELECT INTO

Explanation

Conclusion

Explicit Cursors

Steps to Use Explicit Cursors

Structure of an Explicit Cursor

1. Declaring a Cursor

2. Opening a Cursor

3. Fetching Rows from the Cursor

4. Closing the Cursor

Example: Processing Employees with an Explicit Cursor

Explanation of the Example

Cursor Attributes

Advanced Usage: Cursor FOR Loop

Explanation

Conclusion

Trigger

Key Characteristics of Triggers

Real-Life Example: Library Book Borrowing System

Scenario Details

Trigger to Prevent Double Borrowing

Explanation of the Trigger

Trigger to Update Book Availability

Explanation of the Trigger

Trigger to Handle Book Returns

Explanation of the Trigger

Summary

Exception Handling in PL/SQL

Types of Exceptions

Structure of Exception Handling

Real-Life Example: Bank Account Management System

Scenario Details

accounts Table

Example: Transfer Funds with Exception Handling

Explanation of the Example

Common Predefined Exceptions

User-Defined Exceptions

Exception Propagation

Example of Nested Blocks

Explanation of Nested Blocks

Conclusion

Built-In Exceptions

Common Built-In Exceptions

Handling Built-In Exceptions

Example: Handling Multiple Built-In Exceptions

Summary

User-defined exceptions in PL/SQL

Steps to Create and Use User-Defined Exceptions

Real-Life Example: Order Processing System

Scenario Details

User-Defined Exceptions Example

Explanation of the Example

Raising User-Defined Exceptions in Functions or Procedures

Explanation of Procedure

Benefits of User-Defined Exceptions

Conclusion

Propagation and Re-raising of Exceptions

Exception Propagation

Real-Life Example: Banking System

Example: Exception Propagation

Explanation of Propagation in Example:

Re-Raising Exceptions

Real-Life Example: E-commerce Order Processing

Example: Re-Raising an Exception

Explanation of Re-Raising in Example:

Key Points on Exception Propagation and Re-Raising

Summary

Leave a Reply Cancel reply

Table: `EMPLOYEES`

Example 1: Using an Implicit Cursor for `UPDATE`

Example 2: Using an Implicit Cursor for `SELECT INTO`

`accounts` Table