Cursors in PL/SQL are used to handle query results one row at a time. They are essential for processing individual rows returned by database queries. This section will cover the basics of cursors, their types, and how to use them effectively in PL/SQL.
What is a Cursor?
A cursor is a pointer to the context area, which is a memory location where the Oracle database processes SQL statements. Cursors allow you to fetch and manipulate query results row by row.
Types of Cursors
There are two main types of cursors in PL/SQL:
- Implicit Cursors: Automatically created by Oracle for single SQL statements.
- Explicit Cursors: Defined by the programmer for queries that return multiple rows.
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed. They are simple to use and are suitable for single-row queries.
Example of Implicit Cursor
DECLARE
v_employee_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_employee_name
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
/Explanation
- DECLARE: Declares a variable
v_employee_nameof the same type as thefirst_namecolumn in theemployeestable. - BEGIN: Starts the execution block.
- SELECT INTO: Uses an implicit cursor to fetch the
first_nameof the employee withemployee_id100 into the variablev_employee_name. - DBMS_OUTPUT.PUT_LINE: Outputs the employee's name.
- END: Ends the execution block.
Explicit Cursors
Explicit cursors are defined by the programmer and provide more control over the context area. They are used for queries that return multiple rows.
Steps to Use Explicit Cursors
- Declare the Cursor: Define the cursor with a
SELECTstatement. - Open the Cursor: Allocate memory for the cursor.
- Fetch the Data: Retrieve data from the cursor into PL/SQL variables.
- Close the Cursor: Release the memory associated with the cursor.
Example of Explicit Cursor
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name FROM employees;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_first_name, v_last_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
END LOOP;
CLOSE emp_cursor;
END;
/Explanation
- DECLARE: Declares a cursor
emp_cursorwith aSELECTstatement and variablesv_first_nameandv_last_name. - OPEN: Opens the cursor
emp_cursor. - LOOP: Starts a loop to fetch data from the cursor.
- FETCH INTO: Retrieves the
first_nameandlast_nameinto the variables. - EXIT WHEN: Exits the loop when there are no more rows to fetch.
- DBMS_OUTPUT.PUT_LINE: Outputs the employee's name.
- CLOSE: Closes the cursor
emp_cursor.
Practical Exercise
Exercise
Write a PL/SQL block that uses an explicit cursor to fetch and display the department_name and location_id from the departments table.
Solution
DECLARE
CURSOR dept_cursor IS
SELECT department_name, location_id FROM departments;
v_department_name departments.department_name%TYPE;
v_location_id departments.location_id%TYPE;
BEGIN
OPEN dept_cursor;
LOOP
FETCH dept_cursor INTO v_department_name, v_location_id;
EXIT WHEN dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department: ' || v_department_name || ', Location ID: ' || v_location_id);
END LOOP;
CLOSE dept_cursor;
END;
/Explanation
- DECLARE: Declares a cursor
dept_cursorwith aSELECTstatement and variablesv_department_nameandv_location_id. - OPEN: Opens the cursor
dept_cursor. - LOOP: Starts a loop to fetch data from the cursor.
- FETCH INTO: Retrieves the
department_nameandlocation_idinto the variables. - EXIT WHEN: Exits the loop when there are no more rows to fetch.
- DBMS_OUTPUT.PUT_LINE: Outputs the department name and location ID.
- CLOSE: Closes the cursor
dept_cursor.
Common Mistakes and Tips
- Not Closing Cursors: Always close your cursors to free up resources.
- Incorrect Data Types: Ensure that the variables used in
FETCH INTOhave the correct data types. - Handling No Data Found: Use
EXIT WHEN cursor_name%NOTFOUNDto handle cases where no data is found.
Conclusion
In this section, you learned about cursors in PL/SQL, including implicit and explicit cursors. You now know how to declare, open, fetch from, and close explicit cursors. Understanding cursors is crucial for handling query results efficiently in PL/SQL. In the next section, we will delve into exception handling to manage errors gracefully in your PL/SQL programs.