[Ad-Oracle-Sql] 7. Cursors and Exception Handling

📋 This is my note-taking from what I learned in the class “Advanced Database Concepts”

Objective

• Manipulating data with cursors
• Using bulk-processing features
• Managing errors with exception handlers
• Addressing exception-handling issues, such as RAISE_APPLICATION_ERROR and propagation
• Documenting code with comments

Brewbean’s Challenge

Processing multiple data rows

Cursors

• Work area in which SQL statement is processed
• Implicit cursor – declared automatically for DML and SELECT statements
• Explicit cursor – declared and managed programmatically to handle a set of rows returned by a SELECT statement
• Cursor variable – reference or pointer to a work area or cursor

Cursor Attributes

Attribute Name	Data Type	Description
%ROWCOUNT	Number	Number of rows affected by the SQL statement
%FOUND	Boolean	TRUE if at least one row is affected by the SQL statement, otherwise FALSE
%NOTFOUND	Boolean	TRUE if no rows are affected by the SQL statement, otherwise FALSE

Implicit Cursor

Explicit Cursor

Cursor FOR Loop

• Handles tasks automatically for processing each row returned by a cursor (record declaration, fetch, ending loop)
• Use FOR UPDATE and WHERE CURRENT OF clauses for record locking

DECLARE
   -- Declare a cursor to select products to update
   CURSOR cur_prod IS
      SELECT type, price
      FROM bb_product
      WHERE active = 1
      FOR UPDATE NOWAIT;

   -- Declare a variable to store the calculated sale price
   lv_sale bb_product.saleprice%TYPE;
BEGIN
   -- Loop through the products selected by the cursor
   FOR rec_prod IN cur_prod LOOP
      -- Calculate the sale price based on product type
      IF rec_prod.type = 'C' THEN
         lv_sale := rec_prod.price * 0.9;
      ELSIF rec_prod.type = 'E' THEN
         lv_sale := rec_prod.price * 0.95;
      END IF;

      -- Update the sale price for the current product
      UPDATE bb_product
      SET saleprice = lv_sale
      WHERE CURRENT OF cur_prod;
   END LOOP;

   -- Commit the changes
   COMMIT;
END;

Cursors with Parameters

• Use parameters to make dynamic
• Parameters are values passed to the cursor when it is opened
• Enables the cursor to retrieve different data based on the input values

DECLARE
   -- Declare a cursor to retrieve basket items for a given basket
   CURSOR cur_order (p_basket NUMBER) IS
      SELECT idBasket, idProduct, price, quantity
      FROM bb_basketitem
      WHERE idBasket = p_basket;

   -- Declare variables for basket IDs
   lv_bask1_num bb_basket.idbasket%TYPE := 6;
   lv_bask2_num bb_basket.idbasket%TYPE := 10;
BEGIN
   -- Loop through basket items for the first basket
   FOR rec_order IN cur_order(lv_bask1_num) LOOP
      DBMS_OUTPUT.PUT_LINE(
         'Basket: ' || rec_order.idBasket ||
         ' - Product: ' || rec_order.idProduct ||
         ' - Price: ' || rec_order.price
      );
   END LOOP;

   -- Loop through basket items for the second basket
   FOR rec_order IN cur_order(lv_bask2_num) LOOP
      DBMS_OUTPUT.PUT_LINE(
         'Basket: ' || rec_order.idBasket ||
         ' - Product: ' || rec_order.idProduct ||
         ' - Price: ' || rec_order.price
      );
   END LOOP;
END;

Cursor Variable

• More efficiently handles data returned by query by returning a pointer to the work area rather than the actual result set
• The same cursor variable can be used for different query statements

DECLARE
   -- Declare a cursor
   cv_prod SYS_REFCURSOR;

   -- Declare record variables for table rows
   rec_item bb_basketitem%ROWTYPE;
   rec_status bb_basketstatus%ROWTYPE;

   -- Declare input variables
   lv_input1_num NUMBER(2) := 2;
   lv_input2_num NUMBER(2) := 3;
BEGIN
   IF lv_input1_num = 1 THEN
      -- Open the cursor for selecting basket items
      OPEN cv_prod FOR SELECT * FROM bb_basketitem WHERE idBasket = lv_input2_num;

      -- Loop through the cursor results for basket items
      LOOP
         FETCH cv_prod INTO rec_item;
         EXIT WHEN cv_prod%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('Product ID: ' || rec_item.idProduct);
      END LOOP;
   ELSIF lv_input1_num = 2 THEN
      -- Open the cursor for selecting basket statuses
      OPEN cv_prod FOR SELECT * FROM bb_basketstatus WHERE idBasket = lv_input2_num;

      -- Loop through the cursor results for basket statuses
      LOOP
         FETCH cv_prod INTO rec_status;
         EXIT WHEN cv_prod%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('Stage ID: ' || rec_status.idStage || ' - Stage Date: ' || rec_status.dtstage);
      END LOOP;
   END IF;
END;

Bulk-processing

• Improve performance of multi-row queries and DML statements
• Processes groups of rows without context switching between the SQL and PL/SQL processing engine
• Use in FETCH with LIMIT clause
• FORALL option with DML activity

Bulk-processing (Query)

DECLARE
   -- Declare a cursor to select all records from bb_basketitem
   CURSOR cur_item IS
      SELECT *
      FROM bb_basketitem;

   -- Define a custom collection type for the cursor results
   TYPE type_item IS TABLE OF cur_item%ROWTYPE INDEX BY PLS_INTEGER;

   -- Declare a collection to store the cursor results
   tbl_item type_item;
BEGIN
   -- Open the cursor
   OPEN cur_item;

   -- Loop through the cursor results in chunks of 1000 records
   LOOP
      -- Fetch 1000 records at a time into the collection
      FETCH cur_item BULK COLLECT INTO tbl_item LIMIT 1000;

      -- Iterate through the collection and process the records
      FOR i IN 1..tbl_item.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE('Basket Item ID: ' || tbl_item(i).idBasketitem || ' - Product ID: ' || tbl_item(i).idProduct);
      END LOOP;

      -- Exit the loop when no more records are found
      EXIT WHEN cur_item%NOTFOUND;
   END LOOP;

   -- Close the cursor
   CLOSE cur_item;
END;

Bulk-processing (DML)

DECLARE
   -- Define a custom collection type for employee IDs
   TYPE emp_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

   -- Declare a collection to store employee IDs
   emp_tbl emp_type;
BEGIN
   -- Fetch employee IDs into the collection
   SELECT empID
   BULK COLLECT INTO emp_tbl
   FROM employees
   WHERE classtype = '100';

   -- Update salaries for employees in the collection
   FORALL i IN emp_tbl.FIRST .. emp_tbl.LAST
      UPDATE employees
      SET raise = salary * 0.06
      WHERE empID = emp_tbl(i);

   -- Commit the updates
   COMMIT;
END;

Exception Handlers

• Used to capture error conditions and handle the processing to allow the application to continue
• Placed in the EXCEPTION section of a PL/SQL block

• Two types of errors

  1. Oracle errors (Predefined and Non-Predefined)

  1. User-defined errors

• RAISE_APPLICATION_ERROR

Predefined Oracle Errors

Exception Name	Description
NO_DATA_FOUND	A SELECT statement in a PL/SQL block retrieves no rows or a nonexistent row of an index-by table is referenced
TOO_MANY_ROWS	A SELECT statement in a PL/SQL block retrieves more than one row
CASE_NOT_FOUND	No WHEN clause in the CASE statement is processed
ZERO_DIVIDE	Attempted division by zero
DUP_VAL_ON_INDEX	Attempted violation of a unique or primary key column constraint

Predefined Error Example

Undefined Error

Identify possible errors for statements in a block

Handler Added

User-Defined Exception

• No system error is raised
• Raise errors to enforce business rules
• Once error is raised, the remaining statements in the executable sections are not executed
• Processing moves to the exception area of the block

User-Defined Exception Example

Additional Exception Concepts

• WHEN OTHERS – traps all errors not specifically addressed by an exception handler and used for handling unanticipated errors
• SQLCODE and SQLERRM – functions used to identify the error code and message, especially in application, testing to identify unanticipated errors

Exception Propagation

• Exception handling in nested blocks
• Exception raised in a block will first look for handler in the exception section of that block, if no handler found, execution will move to the exception section of the enclosing block
• Error in DECLARE section propagates directly to exception section of the enclosing block
• Error in exception handler propagates to exception section of the enclosing block

Commenting Code

• Add comments within code to identify code purpose and processing steps
• Use /* */ to enclose a multiline comment
• Use -- to add a single or partial line comment

DECLARE
   ex_prod_update EXCEPTION;  -- Exception for UPDATE of no rows

BEGIN
   /* This block is used to update product descriptions
      Constructed to support the Prod_desc.frm app screen
      Exception raised if no rows updated  */

   -- Update the product description for a specific product ID
   UPDATE bb_product
   SET description = 'Mill grinder with 5 grind settings!'
   WHERE idProduct = 30;

   -- Check if any rows were updated
   IF SQL%NOTFOUND THEN
      -- Raise the custom exception if no rows were updated
      RAISE ex_prod_update;
   END IF;

EXCEPTION
   WHEN ex_prod_update THEN
      -- Handle the exception by displaying an error message
      DBMS_OUTPUT.PUT_LINE('Invalid product id entered');
END;

Summary

• Implicit cursors are automatically created for SQL statements
• Explicit cursors are declared
• Cursors allow the processing of a group of rows
• CURSOR FOR Loops simplify cursor coding
• Parameters make cursors more dynamic
• A REF CURSOR acts like a pointer
• BULK processing options can improve performance for queries and DML activity
• Add error handlers in the EXCEPTION area to manage Oracle and user-defined errors
• Exception propagation is the flow of error handling processing
• Use comments in code for documentation

---

Back to Top

See other articles in Category Ad-Oracle-Sql