In PL/SQL, cursors are used to handle the result set of a query. They allow you to fetch and process rows returned by a SELECT statement. There are two types of cursors in PL/SQL: implicit cursors and explicit cursors.
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.
Key Points:
- Automatically managed by Oracle.
- Used for single-row queries.
- No need to declare or open the cursor explicitly.
Example:
DECLARE
v_employee_name VARCHAR2(50);
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:
- The
SELECT INTOstatement automatically creates an implicit cursor. - The result of the query is stored in the variable
v_employee_name. - The
DBMS_OUTPUT.PUT_LINEprocedure is used to display the result.
Common Mistakes:
- No Data Found Exception: If the query returns no rows, a
NO_DATA_FOUNDexception is raised. - Too Many Rows Exception: If the query returns more than one row, a
TOO_MANY_ROWSexception is raised.
Explicit Cursors
Explicit cursors are defined and controlled by the programmer. They are used for multi-row queries and provide more control over the context area.
Key Points:
- Must be declared, opened, fetched, and closed explicitly.
- Suitable for multi-row queries.
- Provides more control over the query processing.
Steps to Use Explicit Cursors:
- Declare the Cursor: Define the cursor with a SELECT statement.
- Open the Cursor: Allocate memory for the cursor and execute the query.
- Fetch the Data: Retrieve the rows one by one.
- Close the Cursor: Release the memory associated with the cursor.
Example:
DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name
FROM employees
WHERE department_id = 10;
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 the Cursor: The cursor
emp_cursoris declared with a SELECT statement. - Open the Cursor: The cursor is opened using the
OPENstatement. - Fetch the Data: The
FETCHstatement retrieves rows into the variablesv_first_nameandv_last_name. - Close the Cursor: The cursor is closed using the
CLOSEstatement.
Common Mistakes:
- Not Closing the Cursor: Always close the cursor to free up resources.
- Fetching After Cursor is Closed: Ensure that fetch operations are performed only when the cursor is open.
Practical Exercise
Exercise:
Write a PL/SQL block that uses an explicit cursor to fetch and display the names of all employees in the 'Sales' department.
Solution:
DECLARE
CURSOR sales_cursor IS
SELECT first_name, last_name
FROM employees
WHERE department_name = 'Sales';
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
OPEN sales_cursor;
LOOP
FETCH sales_cursor INTO v_first_name, v_last_name;
EXIT WHEN sales_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
END LOOP;
CLOSE sales_cursor;
END;
/Explanation:
- The cursor
sales_cursoris declared to select employees from the 'Sales' department. - The cursor is opened, and rows are fetched in a loop.
- The loop exits when there are no more rows to fetch.
- The cursor is closed after processing all rows.
Conclusion
In this section, we covered the basics of implicit and explicit cursors in PL/SQL. Implicit cursors are automatically managed by Oracle and are suitable for single-row queries, while explicit cursors provide more control and are used for multi-row queries. Understanding how to use both types of cursors effectively is crucial for efficient PL/SQL programming. In the next section, we will delve deeper into bulk operations with cursors to handle large datasets efficiently.