Introduction of Cursors in Oracle PL/SQL:
Oracle PL/SQL provides powerful features for handling and manipulating data. One such feature is the cursor, which allows you to retrieve and process data from the result set of a SQL query. In this blog post, we will explore the concept of cursors in Oracle PL/SQL and provide a code example to demonstrate their usage.
What is a Cursor in Oracle PL/SQL?
A cursor in Oracle PL/SQL is a database object that allows you to retrieve and manipulate data row by row. It provides a way to handle the result set of a query, enabling you to perform various operations on the retrieved data.
Types of Cursors:
Oracle PL/SQL supports two types of cursors: implicit and explicit.
- Implicit Cursors:
Implicit cursors are automatically created by Oracle whenever a SQL statement is executed. They are used to handle single-row queries and are primarily used for simple, ad-hoc operations.
Here’s an example of using an implicit cursor to retrieve data from a table and display it:
DECLARE
emp_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO emp_name
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
END;
/
In this example, the implicit cursor is created for the SELECT statement, and the result is stored in the emp_name
variable. The retrieved value is then displayed using the DBMS_OUTPUT.PUT_LINE
procedure.
Implicit Cursor Attributes: Implicit cursors have several attributes that provide information about the execution of a SQL statement. Let’s discuss the commonly used attributes:
- %FOUND: This attribute returns TRUE if the most recent fetch from the cursor returned a row. Otherwise, it returns FALSE. It can be used in a conditional statement to check if a row was retrieved.
- %NOTFOUND: This attribute returns TRUE if the most recent fetch from the cursor did not return a row. Otherwise, it returns FALSE. It can be used in a conditional statement to check if no rows were retrieved.
- %ROWCOUNT: This attribute returns the number of rows fetched so far from the cursor. It can be useful for tracking the progress of a fetch operation or determining the total number of rows returned.
- %ISOPEN: This attribute returns TRUE if the cursor is currently open. Otherwise, it returns FALSE. It can be used to check if a cursor is open before attempting to fetch data from it.
DECLARE
emp_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO emp_name
FROM employees
WHERE employee_id = 100;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee not found!');
END IF;
DBMS_OUTPUT.PUT_LINE('Total rows fetched: ' || SQL%ROWCOUNT);
IF SQL%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is open.');
ELSE
DBMS_OUTPUT.PUT_LINE('Cursor is not open.');
END IF;
END;
/
In this example, after fetching the first_name
from the employees
table into the emp_name
variable, we use the %FOUND
attribute to check if a row was retrieved. If a row is found, the employee name is displayed; otherwise, a message indicating that the employee was not found is printed. We also display the total number of rows fetched using the %ROWCOUNT
attribute. Finally, we check if the cursor is open using the %ISOPEN
attribute.
- Explicit Cursors:
Explicit cursors are explicitly declared and defined by the programmer. They provide more control and flexibility over the result set processing compared to implicit cursors. Explicit cursors are suitable for handling complex queries or when you need to iterate over multiple rows.
Here’s an example of using an explicit cursor to retrieve and process data from a table:
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name
FROM employees
WHERE department_id = 10;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
-- Process each row
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.first_name || ' ' || emp_record.last_name);
END LOOP;
CLOSE emp_cursor;
END;
/
In this example, an explicit cursor named emp_cursor
is defined to select the first_name
and last_name
columns from the employees
table. The cursor is then opened, and a loop is used to fetch each row into the emp_record
variable. The loop continues until there are no more rows to fetch, and each row is processed by displaying the employee name.
Conclusion:
Cursors play a vital role in Oracle PL/SQL programming by allowing you to retrieve and manipulate data row by row. Implicit cursors are useful for single-row queries and simple operations, while explicit cursors provide more control and flexibility for complex queries and multi-row processing.
Understanding and mastering the concept of cursors is essential for efficient data handling and manipulation in Oracle PL/SQL. By leveraging cursors, you can write more powerful and efficient database programs.
We hope this blog post provided a comprehensive introduction to Oracle PL/SQL cursors and demonstrated their usage through code examples. Experiment with cursors in your own projects to unlock their full potential!
That concludes our blog post on Oracle PL/SQL cursors. We hope you found it informative and useful for your programming journey. Happy coding!
One Comment