Writing Explicit Cursors

1. Writing Explicit Cursors

2. Objectives • After completing this lesson, you should be able to do the following: • Distinguish between an implicit and an explicit cursor • Use a PL/SQL record variable • Write a cursor FOR loop

3. About Cursors • Every SQL statement executed by the Oracle Server has an individual cursor associated with it: • Implicit cursors: Declared for all DML and PL/SQL SELECT statements • Explicit cursors: Declared and named by the programmer

4. cursor attributes %ISOPEN == returns true if cursor is open, false otherwise %FOUND == returns true if record was fetched successfully, false otherwise %NOTFOUND == returns true if record was not fetched successfully, false otherwise %ROWCOUNT == returns number of records processed from the cursor.

5. EXAMPLE OF SQL%FOUND (IMPLICIT CURSORS) begin update employee set salary=salary *0.15  where emp_code = &emp_code; if sql%found then dbms_output.put_line('employee record modified successfully'); else dbms_output.put_line('employee no does not exist');end if; end;

6. EXAMPLE FOR SQL%NOTFOUND (IMPLICIT CURSORS) begin update employee set salary = salary*0.15 where emp_code = &emp_code; if sql%notfound then dbms_output.put_line('employee no . does not exist'); else dbms_output.put_line('employee record modified successfully'); end if; end;

7. EXAMPLE FOR SQL%ROWCOUNT (IMPLICIT CURSORS)  declare rows_affected char(4); begin update employee set salary = salary*0.15 where job='programmers'; rows_affected := to_char(sql%rowcount); if sql%rowcount > 0 then dbms_output.put_line(rows_affected || 'employee records modified successfully'); else dbms_output.put_line('There are no employees working as programmers'); end if; end;

8. Cursor Explicit Cursor Functions Active set 7369 SMITH CLERK 7566 JONES MANAGER 7788 SCOTT ANALYST 7876 ADAMS CLERK 7902 FORD ANALYST Current row

9. OPEN FETCH CLOSE Yes EMPTY? Controlling Explicit Cursors No DECLARE • Create a named SQL area • Identify the active set • Load the current row into variables • Test for existing rows • Release the active set • Return to FETCH if rows found

10. Pointer Cursor Pointer Cursor Pointer Cursor Controlling Explicit Cursors Open the cursor. Fetch a row from the cursor. Continue until empty. Close the cursor.

11. Declaring the Cursor • Syntax • Do not include the INTO clause in the cursor declaration. • If processing rows in a specific sequence is required, use the ORDER BY clause in the query. CURSOR cursor_name IS select_statement;

12. Declaring the Cursor • Example DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp; CURSOR dept_cursor IS SELECT * FROM dept WHERE deptno = 10; BEGIN ...

13. Opening the Cursor • Syntax • Open the cursor to execute the query and identify the active set. • If the query returns no rows, no exception is raised. • Use cursor attributes to test the outcome after a fetch. OPEN cursor_name;

14. Fetching Data from the Cursor • Syntax • Retrieve the current row values into variables. • Include the same number of variables. • Match each variable to correspond to the columns positionally. • Test to see if the cursor contains rows. FETCH cursor_name INTO[variable1, variable2, ...] | record_name];

15. Fetching Data from the Cursor • Examples FETCH emp_cursor INTO v_empno, v_ename; ... OPEN defined_cursor; LOOP FETCH defined_cursor INTO defined_variables EXIT WHEN ...; ... -- Process the retrieved data ... END;

16. Closing the Cursor • Syntax • Close the cursor after completing the processing of the rows. • Reopen the cursor, if required. • Do not attempt to fetch data from a cursor once it has been closed. CLOSE cursor_name;

17. If you declare a cursor that retrieves the last name, salary, hire date, and job class of an employee, you can use %ROWTYPE to declare a record that stores the same information . • When you execute the FETCH statement, the value in the last_name column of the employees table is assigned to the last_name field of employee_rec, the value in the salary column is assigned to the salary field, and so on.

18. Example Using %ROWTYPE with an Explicit Cursor • DECLARE • CURSOR c1 IS • SELECT last_name, salary, hire_date, job_id FROM employees WHERE employee_id = 120; • -- declare record variable that represents a row fetched from the employees table employee_rec c1%ROWTYPE; • BEGIN • -- open the explicit cursor and use it to fetch data into employee_rec • OPEN c1; • FETCH c1 INTO employee_rec; • DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name); • END;

19. Example Using the %ROWTYPE Attribute • The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable. • DECLARE • -- %ROWTYPE can include all the columns in a table... • emp_rec employees%ROWTYPE; • -- ...or a subset of the columns, based on a cursor. • CURSOR c1 IS SELECT department_id, department_name FROM departments; • dept_rec c1%ROWTYPE; • -- Could even make a %ROWTYPE with columns from multiple tables. • CURSOR c2 IS SELECT employee_id, email, employees.manager_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id; • join_rec c2%ROWTYPE; • BEGIN • -- We know EMP_REC can hold a row from the EMPLOYEES table. • SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2; • -- We can refer to the fields of EMP_REC using column names • -- from the EMPLOYEES table. • IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN • emp_rec.salary := emp_rec.salary * 1.15; • END IF; • END; • /

20. Example Assigning Values to a Record With a %ROWTYPE Declaration • Although a %ROWTYPE declaration cannot include an initialization clause, there are ways to assign values to all fields in a record at once. You can assign one record to another if their declarations refer to the same table or cursor. • DECLARE • dept_rec1 departments%ROWTYPE; • dept_rec2 departments%ROWTYPE; • CURSOR c1 IS SELECT department_id, location_id FROM departments; • dept_rec3 c1%ROWTYPE; • BEGIN • dept_rec1 := dept_rec2; -- allowed • -- dept_rec2 refers to a table, dept_rec3 refers to a cursor • -- dept_rec2 := dept_rec3; -- not allowed END;

21. Example Fetching With a Cursor • DECLARE • v_jobid employees.job_id%TYPE; -- variable for job_id • v_lastname employees.last_name%TYPE; -- variable for last_name • CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK'); • v_employees employees%ROWTYPE; -- record variable for row • CURSOR c2 is SELECT * FROM employees WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]'); BEGIN OPEN c1; -- open the cursor before fetching • LOOP FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables • EXIT WHEN c1%NOTFOUND; • DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid ); • END LOOP; • CLOSE c1; • DBMS_OUTPUT.PUT_LINE( '-------------------------------------' ); • OPEN c2; • LOOP • FETCH c2 INTO v_employees; -- fetches entire row into the v_employees record • EXIT WHEN c2%NOTFOUND; • DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id ); • END LOOP; • CLOSE c2; • END;

22. The query can reference PL/SQL variables within its scope. • Any variables in the query are evaluated only when the cursor is opened. • Next example shows each retrieved salary is multiplied by 2, even though factor is incremented after every fetch. • DECLARE • my_sal employees.salary%TYPE; • my_job employees.job_id%TYPE; • factor INTEGER := 2; • CURSOR c1 IS SELECT factor*salary FROM employees WHERE job_id = my_job; • BEGIN • OPEN c1; -- factor initially equals 2 • LOOP FETCH c1 INTO my_sal; • EXIT WHEN c1%NOTFOUND; • factor := factor + 1; -- does not affect FETCH • END LOOP; • CLOSe c1; • END;

23. Fetching Bulk Data with a Cursor • The BULK COLLECT clause lets you fetch all rows from the result set at once. • In next slide, you bulk-fetch from a cursor into two collections. • DECLARE • TYPE IdsTab IS TABLE OF employees.employee_id%TYPE; • TYPE NameTab IS TABLE OF employees.last_name%TYPE; • ids IdsTab; • names NameTab; • CURSOR c1 IS SELECT employee_id, last_name FROM employees WHERE job_id = 'ST_CLERK'; • BEGIN • OPEN c1; • FETCH c1 BULK COLLECT INTO ids, names; • CLOsE c1; • -- Here is where you process the elements in the collections • FOR i IN ids.FIRST .. ids.LAST LOOP • IF ids(i) > 140 THEN • DBMS_OUTPUT.PUT_LINE( ids(i) ); • END IF; • END LOOP; • FOR i IN names.FIRST .. names.LAST LOOP • IF names(i) LIKE '%Ma%' THEN • DBMS_OUTPUT.PUT_LINE( names(i) ); • END IF; • END LOOP; • END;

28. Explicit Cursor Attributes • Obtain status information about a cursor. Attribute Type Description %ISOPEN Boolean Evaluates to TRUE if the cursor is open %NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row %FOUND Boolean Evaluates to TRUE if the most recent fetch returns a row; complement of %NOTFOUND %ROWCOUNT Number Evaluates to the total number of rows returned so far

29. Controlling Multiple Fetches • Process several rows from an explicit cursor using a loop. • Fetch a row with each iteration. • Use the %NOTFOUND attribute to write a test for an unsuccessful fetch. • Use explicit cursor attributes to test the success of each fetch.

30. The %ISOPEN Attribute • Fetch rows only when the cursor is open. • Use the %ISOPEN cursor attribute before performing a fetch to test whether the cursor is open. • Example IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; END IF; LOOP FETCH emp_cursor...

31. Using %FOUND • After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row. • DECLARE • CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; • my_ename employees.last_name%TYPE; • my_salary employees.salary%TYPE; • BEGIN • OPEN c1; • LOOP • FETCH c1 INTO my_ename, my_salary; • IF c1%FOUND THEN -- fetch succeeded • DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary); • ELSE -- fetch failed, so exit loop • EXIT; • END IF; • END LOOP; • END; • If a cursor or cursor variable is not open, referencing it with %FOUND raises the predefined exception INVALID_CURSOR.

32. The %NOTFOUND and %ROWCOUNT Attributes • Use the %ROWCOUNT cursor attribute to retrieve an exact number of rows. • Use the %NOTFOUND cursor attribute to determine when to exit the loop.

33. %ISOPEN Attribute • Is the Cursor Open? • %ISOPEN returns TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN returns FALSE. • DECLARE • CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; • the_name employees.last_name%TYPE; the_salary employees.salary%TYPE; • BEGIN • IF c1%ISOPEN = FALSE THEN -- cursor was not already open • OPEN c1; • END IF; • FETCH c1 INTO the_name, the_salary; • CLOSE c1; • END;

34. %NOTFOUND Attribute • Has a Fetch Failed? • %NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. In the example below, you use %NOTFOUND to exit a loop when FETCH fails to return a row. • DECLARE • CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; • my_ename employees.last_name%TYPE; • my_salary employees.salary%TYPE; • BEGIN OPEN c1; • LOOP FETCH c1 INTO my_ename, my_salary; • IF c1%NOTFOUND THEN -- fetch failed, so exit loop • -- Another form of this test is “ • EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;" • EXIT; • ELSE -- fetch succeeded • DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary); • END IF; • END LOOP; END;

35. Before the first fetch, %NOTFOUND returns NULL. If FETCH never executes successfully, the loop is never exited, because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, you might want to use the following EXIT statement instead: • EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; • If a cursor or cursor variable is not open, referencing it with %NOTFOUND raises an INVALID_CURSOR exception.

36. %ROWCOUNT Attribute • How Many Rows Fetched So Far? • When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. • Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. The following example uses %ROWCOUNT to test if more than ten rows have been fetched. • DECLARE • CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11; • name employees.last_name%TYPE; • BEGIN • OPEN c1; • LOOP FETCH c1 INTO name; • EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name); • IF c1%ROWCOUNT = 5 THEN DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---'); • END IF; • END LOOP; • CLOSE c1; • END;

37. Cursors and Records • Process the rows of the active set conveniently by fetching values into a PL/SQL RECORD. • Example DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp; emp_record emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; ...

38. Cursor FOR Loops • Syntax • The cursor FOR loop is a shortcut to process explicit cursors. • Implicit open, fetch, and close occur. • The record is implicitly declared. FOR record_name IN cursor_name LOOP statement1; statement2; . . . END LOOP;

39. Cursor FOR Loops • Retrieve employees one by one until no more are left. • Example DECLARE CURSOR emp_cursor IS SELECT ename, deptno FROM emp; BEGIN FOR emp_record IN emp_cursor LOOP -- implicit open and implicit fetch occur IF emp_record.deptno = 30 THEN ... END LOOP; -- implicit close occurs END;

40. Cursor FOR Loops Using Subqueries • No need to declare the cursor. • Example BEGIN FOR emp_record IN (SELECT ename, deptno FROM emp) LOOP -- implicit open and implicit fetch occur IF emp_record.deptno = 30 THEN ... END LOOP; -- implicit close occurs END;

41. Passing Parameters to Explicit Cursors • DECLARE • emp_job employees.job_id%TYPE := 'ST_CLERK'; emp_salary employees.salary%TYPE := 3000; • my_record employees%ROWTYPE; • CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE job_id = job and salary > max_wage; • BEGIN -- Any of the following statements opens the cursor: • -- OPEN c1('ST_CLERK', 3000); • OPEN c1('ST_CLERK', emp_salary); • -- OPEN c1(emp_job, 3000); • OPEN c1(emp_job, emp_salary); • OPEN c1(emp_job, emp_salary); • LOOP FETCH c1 INTO my_record; • EXIT WHEN c1%NOTFOUND; -- process data record DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.last_name || ', salary = ' || my_record.salary || ', Job Id = ' || my_record.job_id ); • END LOOP; • END;

42. Summary • Cursor types: • Implicit cursors: Used for all DML statements and single-row queries. • Explicit cursors: Used for queries of zero, one, or more rows. • You can manipulate explicit cursors. • You can evaluate the cursor status by using cursor attributes. • You can use cursor FOR loops.

43. Practice Overview • Declaring and using explicit cursors to query rows of a table • Using a cursor FOR loop • Applying cursor attributes to test the cursor status