Oracle PL SQL Tutorial day 5
In today's lesson we will explore about cursor in PL SQL. Oracle PL/SQL, cursors are a fundamental concept used to retrieve and process multiple rows from a query, one at a time.
The schema used for this practice is HR. If it is not available please make sure the schema or tables available for convince.
In this article, we’ll explore PL/SQL cursors in-depth using practical examples from the HR schema, explain their types and usage, and provide real-world tips for effective use.
What is a Cursor in PL/SQL?
A cursor is a pointer to the result set of a SQL query. In PL/SQL, cursors let you fetch multiple rows from a query and process them row by row.
PL/SQL automatically manages some cursors (called implicit cursors), but you can define your own for greater control—these are known as explicit cursors.
When Do You Need a Cursor?
Use a cursor when:
- A SELECT statement returns more than one row
- You need to process each row individually (e.g., generate reports, calculate values, or perform DML)
Types of Cursors in PL/SQL
- Implicit Cursor
- Explicit Cursor
- Cursor FOR Loop
- Parameterized Cursor
1. Implicit Cursors
Oracle implicitly creates a cursor whenever you run a DML operation or a SELECT INTO statement that returns a single row.
The dbms_output.print_line use to show or print. If you are using oracle SQL tool then white SET SERVEROUTPUT ON before execute the code.
If any other tool used like toad aor pl sql developer check or set appropriate configuration.
Example: Using %FOUND, %NOTFOUND, %ROWCOUNT
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Salary of employee 100: ' || v_salary);
END IF;
DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);
END;
2. Explicit Cursors
Explicit cursors give you complete control over row-by-row processing.
Syntax:
CURSOR cursor_name IS
SELECT_statement;
OPEN cursor_name;
FETCH cursor_name INTO variable1, variable2, ...;
CLOSE cursor_name;
Example: List All Employees
DECLARE
CURSOR emp_cur IS
SELECT employee_id, first_name, last_name, salary FROM employees;
v_id employees.employee_id%TYPE;
v_fname employees.first_name%TYPE;
v_lname employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_id, v_fname, v_lname, v_salary;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_fname || ' ' || v_lname || ', Salary: ' || v_salary);
END LOOP;
CLOSE emp_cur;
END;
3. Cursor FOR Loop
PL/SQL provides a simpler way to handle cursors with a FOR loop. This automates opening, fetching, and closing.
Example: Print All Departments
BEGIN
FOR dept_rec IN (
SELECT department_id, department_name, location_id
FROM departments
) LOOP
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || dept_rec.department_id || ', Name: ' || dept_rec.department_name || ', Location ID: ' || dept_rec.location_id);
END LOOP;
END;
4. Parameterized Cursors
You can pass values into cursors, just like functions. This lets you filter data dynamically.
Example: Get Employees by Department
DECLARE
CURSOR emp_by_dept(p_dept_id NUMBER) IS
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = p_dept_id;
v_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
v_sal employees.salary%TYPE;
BEGIN
OPEN emp_by_dept(50);
LOOP
FETCH emp_by_dept INTO v_id, v_name, v_sal;
EXIT WHEN emp_by_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Emp ID: ' || v_id || ', Name: ' || v_name || ', Salary: ' || v_sal);
END LOOP;
CLOSE emp_by_dept;
END;
Real Scenario: Give Bonus to Low Earners
DECLARE
CURSOR low_salary_emp IS
SELECT employee_id, salary FROM employees WHERE salary < 5000;
v_empid employees.employee_id%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN low_salary_emp;
LOOP
FETCH low_salary_emp INTO v_empid, v_salary;
EXIT WHEN low_salary_emp%NOTFOUND;
UPDATE employees
SET salary = v_salary * 1.10
WHERE employee_id = v_empid;
DBMS_OUTPUT.PUT_LINE('Updated Salary for Employee ' || v_empid);
END LOOP;
CLOSE low_salary_emp;
END;
Cursor Attributes
- %FOUND TRUE if last fetch returned a row
- %NOTFOUND TRUE if last fetch failed
- %ROWCOUNT Number of rows fetched so far
- %ISOPEN TRUE if the cursor is open
Example:
IF emp_cur%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is still open.');
END IF;
Cursor with %ROWTYPE
You can simplify your code using %ROWTYPE.
Example:
DECLARE
CURSOR emp_cur IS
SELECT * FROM employees;
emp_row employees%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_row;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_row.first_name || ', Salary: ' || emp_row.salary);
END LOOP;
CLOSE emp_cur;
END;
Common Mistakes
- Forgetting to close the cursor
- Not using EXIT WHEN properly
- Mismatched variable types in FETCH
Nested Cursors
Sometimes you may need nested loops using multiple cursors.
Example: For Each Department, List Employees
DECLARE
CURSOR dept_cur IS
SELECT department_id, department_name FROM departments;
CURSOR emp_cur(p_dept_id NUMBER) IS
SELECT first_name, last_name FROM employees WHERE department_id = p_dept_id;
v_dept_id departments.department_id%TYPE;
v_dept_name departments.department_name%TYPE;
v_fname employees.first_name%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN dept_cur;
LOOP
FETCH dept_cur INTO v_dept_id, v_dept_name;
EXIT WHEN dept_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name);
OPEN emp_cur(v_dept_id);
LOOP
FETCH emp_cur INTO v_fname, v_lname;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' --> ' || v_fname || ' ' || v_lname);
END LOOP;
CLOSE emp_cur;
END LOOP;
CLOSE dept_cur;
END;
Best Practices
- Use %ROWTYPE for clean code
- Use FOR loops where possible
- Always close cursors
- Use parameterized cursors
- Avoid unnecessary complexity
Conclusion
Cursors are a core part of PL/SQL programming. When used correctly, they help you process multi-row queries with precision and flexibility. With the HR schema as your playground, you can master cursors through practical, real-world scenarios.
Summary:
- Implicit Single-row queries, DML
- Explicit Full control over row processing
- Cursor FOR Loop Clean, readable loops
- Parameterized Reusable and dynamic cursors
Practice Exercises
1. Write a cursor to list employees who joined before 2005.
2. Create a parameterized cursor to list jobs by department ID.
3. Write a nested cursor to show each department and count of its employees.
4. Write a block that uses a cursor to apply conditional bonuses based on job titles.
0 Comments