Cursors in DBMS
What is a Cursor?
A cursor in DBMS is a database object used to retrieve, process, and manipulate query results row by
row. It is primarily used in procedural database languages like PL/SQL to handle multiple rows
returned by a query.
Types of Cursors in DBMS
1. Implicit Cursor: Automatically created by the database when a SELECT statement returns a
single row or when INSERT, UPDATE, or DELETE operations are performed.
2. Explicit Cursor: Defined explicitly by the user for handling multiple rows in a result set.
Example 1: Explicit Cursor in PL/SQL
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary FROM employees;
v_emp_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_id, v_first_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_first_name || ', Salary: ' ||
v_salary);
END LOOP;
CLOSE emp_cursor;
END;
Example 2: Cursor FOR Loop (Implicit Fetching)
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary FROM employees;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_record.employee_id || ', Name: ' ||
emp_record.first_name || ', Salary: ' || emp_record.salary);
END LOOP;
END;
Example 3: Parameterized Cursor
DECLARE
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, first_name, salary FROM employees WHERE department_id = p_dept_id;
v_emp_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor(10);
LOOP
FETCH emp_cursor INTO v_emp_id, v_first_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_first_name || ', Salary: ' ||
v_salary);
END LOOP;
CLOSE emp_cursor;
END;
Example 4: Cursor with UPDATE Statement
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees WHERE salary < 5000;
BEGIN
FOR emp_record IN emp_cursor LOOP
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = emp_record.employee_id;
DBMS_OUTPUT.PUT_LINE('Updated Salary for Employee ID: ' || emp_record.employee_id);
END LOOP;
COMMIT;
END;
Example 5: Cursor with Exception Handling
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary FROM employees;
v_emp_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
no_data_found_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(no_data_found_exception, -1403);
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_id, v_first_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_first_name || ', Salary: ' ||
v_salary);
END LOOP;
CLOSE emp_cursor;
EXCEPTION
WHEN no_data_found_exception THEN
DBMS_OUTPUT.PUT_LINE('No data found in the employees table.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Conclusion
Cursors in PL/SQL allow efficient row-by-row processing of query results. They are useful for handling
multi-row queries and performing operations such as updates, parameterized queries, and
exception handling. By practicing these examples, you will gain a deeper understanding of how to
use cursors effectively.
End of Document
