Cursors 1 SQL Cursor A cursor is a

Cursors 1

SQL Cursor A cursor is a private SQL work area. n There are two types of cursors: n n Implicit cursors n Explicit cursors The Oracle Server uses implicit cursors to parse and execute your SQL statements. n Explicit cursors are explicitly declared by the programmer. n 2

Cursors n Pointer to memory location on database server n n Use to: n n DBMS uses to process a SQL query Retrieve and manipulate database data in PL/SQL programs Types: Implicit n Explicit n 3

Database Server Memory Cursor Context Area Number of rows processed Parsed command statement active set 4

Implicit Cursors n Context area Contains information about query n Created by INSERT, UPDATE, DELETE, or SELECT n n Active set n n Set of data rows that query retrieves Implicit cursor n Pointer to context area 5

Implicit Cursors (continued) n Use to assign output of SELECT query to PL/SQL program variables n When query will return only one record 6

Implicit Cursors (continued) n Useful to use %TYPE reference data type n n To declare variables used with implicit cursors Error “ORA-01422: exact fetch returns more than requested number of rows” n Implicit cursor query tried to retrieve multiple records 7

SQL Cursor Attributes n Using SQL cursor attributes, you can test the outcome of your SQL statements. SQL%ROWCOUNT Number of rows affected by the most recent SQL statement (an integer value) SQL%FOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement affects one or more rows SQL%NOTFOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows SQL%ISOPEN Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed 8

SQL Cursor Attributes Delete rows that have the specified order number from the ITEM table. Print the number of rows deleted. n Example VARIABLE rows_deleted VARCHAR 2(30) n DECLARE v_ordid NUMBER : = 605; BEGIN DELETE FROM item WHERE ordid = v_ordid; : rows_deleted : = (SQL%ROWCOUNT || ' rows deleted. '); END; / PRINT rows_deleted 9

Implicit Cursor SQL%ROWCOUNT Example SQL> SET SERVEROUTPUT ON; SQL> DECLARE r NUMBER; BEGIN DELETE FROM emp WHERE empno=7900; r: =SQL%ROWCOUNT; DBMS_OUTPUT. PUT_LINE(r); END; / 1 10

Implicit Cursor SQL%FOUND Example SQL> DECLARE r BOOLEAN; BEGIN DELETE FROM emp WHERE empno=1000; r: =SQL%FOUND; IF r THEN DBMS_OUTPUT. PUT_LINE('Rows are founded'); ELSE DBMS_OUTPUT. PUT_LINE('No Rows are founded'); END IF; END; / No Rows are founded 11

Implicit Cursor SQL%ISOPEN Example SQL> DECLARE r BOOLEAN; BEGIN UPDATE emp SET sal=1000 WHERE empno>7900; r: =SQL%ISOPEN; IF r THEN DBMS_OUTPUT. PUT_LINE('The cursor is opened'); ELSE DBMS_OUTPUT. PUT_LINE('The cursor is closed'); END IF; END; / The cursor is closed 12

About Cursors n 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 n Explicit cursors: Declared and named by the programmer n 13

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

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

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

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

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

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

Fetching Data from the Cursor n Syntax FETCH cursor_name INTO [variable 1, variable 2, . . . ] | record_name]; n n n 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. The FETCH statement performs the following operations: 1. Advances the pointer to the next row in the active set. 2. Reads the data for the current row into the output PL/SQL variables. 20

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

Closing the Cursor n Syntax CLOSE cursor_name; Close the cursor after completing the processing of the rows. n Reopen the cursor, if required. n Do not attempt to fetch data from a cursor once it has been closed. n The CLOSE statement releases the context area. n 22

Explicit Cursor Example SQL> DECLARE v_num empno%TYPE; v_name emp. ename%TYPE; CURSOR my_cursor IS SELECT empno, ename FROM emp WHERE empno>7900; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO v_num, v_name; EXIT WHEN my_cursor%NOTFOUND; DBMS_OUTPUT. PUT_LINE(v_num || ' has the name ' ||v_name); END LOOP; CLOSE my_cursor; END; / 7902 has the name FORD 7934 has the name MILLER 23

Explicit Cursor Attributes n 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 24

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

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

The %NOTFOUND and %ROWCOUNT Attributes Use the %ROWCOUNT cursor attribute to retrieve an exact number of rows. n The value of %ROWCOUNT before fetching any row is NULL. n Use the %NOTFOUND cursor attribute to determine when to exit the loop. n 27

Explicit Cursor %ISOPEN Example SQL> DECLARE v_num empno%TYPE; v_name emp. ename%TYPE; r BOOLEAN; CURSOR my_cursor IS SELECT empno, ename FROM emp WHERE empno>7900; BEGIN OPEN my_cursor; r: =my_cursor%ISOPEN; IF r THEN DBMS_OUTPUT. PUT_LINE('The Cursor is opened after the open statement'); ELSE DBMS_OUTPUT. PUT_LINE('The Cursor is closed after the open statement'); END IF; 28

Explicit Cursor %ISOPEN Example Cont. LOOP FETCH my_cursor INTO v_num, v_name; EXIT WHEN my_cursor%NOTFOUND; END LOOP; CLOSE my_cursor; r: =my_cursor%ISOPEN; IF r THEN DBMS_OUTPUT. PUT_LINE('The Cursor is opened after the close statement'); ELSE DBMS_OUTPUT. PUT_LINE('The Cursor is closed after the close statement'); END IF; END; / The Cursor is opened after the open statement The Cursor is closed after the close statement 29

Explicit Cursor %ROWCOUNT Example SQL> DECLARE v_name emp. ename%TYPE; r NUMBER; c NUMBER: =1; CURSOR my_cursor IS SELECT ename FROM emp WHERE empno>7900; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO v_name; EXIT WHEN my_cursor%NOTFOUND; r: =my_cursor%ROWCOUNT; DBMS_OUTPUT. PUT_LINE('After fetch number ' || c || ' ROWCOUNT has the value ' || r); c: =c+1; END LOOP; CLOSE my_cursor; END; / After fetch number 1 ROWCOUNT has the value 1 After fetch number 2 ROWCOUNT has the value 2 30

Cursors and Records Process the rows of the active set conveniently by fetching values into a PL/SQL RECORD. n Example DECLARE n 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; . . . 31

Cursor FOR Loops n Syntax FOR record_name IN cursor_name LOOP statement 1; statement 2; . . . END LOOP; n The cursor FOR loop is a shortcut to process explicit cursors. n Implicit open, fetch, and close occur. n The record is implicitly declared. 32

Cursor FOR Loops Retrieve employees one by one until no more are left. n Example n 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; 33

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