Certainly! PL/SQL cursors are used to handle SQL query results in a structured manner, allowing for row-by-row processing of the data fetched from a database. Cursors can be divided into two types: implicit and explicit cursors. Each type has its own attributes that help manage and retrieve information about the execution of SQL statements.
1. Motivation for Using Cursor Attributes
a. Implicit Cursor Attributes
Motivation:
Implicit cursor attributes are used with SQL statements that do not explicitly define a cursor (like a simple SELECT, INSERT, UPDATE, or DELETE statement). They allow the developer to quickly retrieve information after executing a SQL statement without the need to declare or manage a cursor explicitly.
Why Use Implicit Cursor Attributes?
- Simplicity: They simplify code when you are executing single statements that do not require complex row navigation.
- Quick Feedback: You can easily check for errors or get the number of rows affected after executing a SQL statement using attributes like
%ROWCOUNTand%FOUND. - Reduced Overhead: No need for cursor management (open, fetch, close), which is beneficial in performance-sensitive applications.
Common Implicit Cursor Attributes:
%FOUND: Returns TRUE if the last SQL statement affected one or more rows; returns FALSE otherwise.%NOTFOUND: Returns TRUE if the last SQL statement did not affect any rows; returns FALSE otherwise.%ROWCOUNT: Returns the number of rows affected by the last SQL statement.
b. Explicit Cursor Attributes
Motivation: Explicit cursor attributes are associated with cursors that are explicitly declared in PL/SQL code. This provides a more controlled way of handling multiple rows returned by a query.
Why Use Explicit Cursor Attributes?
- Control: Explicit cursors allow for more complex data manipulation, as they can be opened, fetched, and closed as needed.
- Multiple Row Handling: You can process multiple rows at a time, which is useful for bulk operations or when dealing with large volumes of data.
- Advanced Functionality: They permit scrolling through results, retrieving specific rows, and handling data from complex queries.
Common Explicit Cursor Attributes:
%FOUND: Similar to implicit cursors, it checks if the last fetch operation retrieved any rows.%NOTFOUND: Checks if the last fetch operation did not retrieve any rows.%ROWCOUNT: Counts how many fetch operations have been performed on the cursor.
2. Example Code Demonstration
Case Study: Suppose you have a database with an employee table, and you want to fetch employee details based on their department.
Implementing Implicit Cursor Attribute (%ROWCOUNT)
DECLARE
v_department_id NUMBER := 10; -- Assuming we are looking for department ID 10
BEGIN
-- Implicit cursor for SELECT statement
SELECT *
INTO v_employee_name -- Assume there is a variable named v_employee_name
FROM employees
WHERE department_id = v_department_id;
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
ELSE
DBMS_OUTPUT.PUT_LINE('No employees found in department ID ' || v_department_id);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Data Found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
Implementing Explicit Cursor Attribute (%FOUND)
DECLARE
CURSOR emp_cursor IS
SELECT employee_name FROM employees WHERE department_id = 20;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_name;
-- Checking if the row was fetched
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END LOOP;
-- Check if any employee was found
IF emp_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employees found in department ID 20.');
ELSE
DBMS_OUTPUT.PUT_LINE('No employees found in department ID 20.');
END IF;
CLOSE emp_cursor;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
Conclusion
In summary, both implicit and explicit cursor attributes serve unique purposes in PL/SQL. Implicit cursors simplify SQL execution while allowing quick feedback on the results. In contrast, explicit cursors provide enhanced control and flexibility for handling and processing larger sets of data. Utilizing these attributes effectively can improve both the performance and clarity of PL/SQL code.
1 answer