When we want to retrieve certain records, we use SELECT statements. They retrieve all the matching records for the filter conditions in WHERE clause. But when this SELECT statement used in the PL/SQL block is restricted to return only one row. This is not always welcome in a program. There will be situations where we need to select multiple rows by using SELECT query. In order do this; we use cursors where we write a SELECT query to retrieve multiple rows. These cursors are the named SELECT query and the result of which will be traversed one by one in the program using loops.
There are two types of cursors – Implicit and Explicit Cursors
These are the cursors which are automatically created and used by the DBMS. These cursors are created when we use DML statements like INSERT, DELETE or UPDATE or when we use SELECT query. Even for single row SELECT statement DBMS creates implicit cursors. It allocates some space in the memory to hold the data. Even when we fire DML statements, the implicit cursor is fired to select the matching records and the space is reserved for those records in the memory.
Even though these implicit cursors are internal and users/developers cannot control their execution, developers can view the details of these cursors using its attributes. We can access this implicit cursor using ‘SQL’ which refers to the cursor SQL. Its attributes are described below:
- %ISOPEN – It returns TRUE or FALSE depending on the cursor is still open for execution or not. It returns always FALSE for implicit cursor because DBMS would have executed and closed the cursor automatically.
- %FOUND – It returns TRUE if the DML statement affects any of the rows or the SELECT statement retrieves one or more row. Otherwise it returns FALSE. If the statements are not executed then it returns NULL.
- %NOTFOUND – It is opposite of %FOUND. It returns TRUE if the DML statement does not affect any of the rows or the SELECT statement does not retrieve any row. Otherwise it returns FALSE.
- %ROWCOUNT – It returns the number of rows affected by the DML statements or the number of rows retrieved by the SELECT statement.
These implicit cursor attributes are called as below:
SQL%ISOPEN SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND
DECLARE n_count NUMBER; BEGIN UPDATE EMPLOYEE SET SALARY = SALARY + (SALARY*0.1) – 10% increment in salary for DEPT_ID = 10 employees WHERE DEPT_ID = 10; IF SQL%FOUND THEN – checks if any row is updated n_count: = SQL%ROWCOUNT; -- Number of rows updated are counted DBMS_OUTPUT.PUT_LINE (‘Total Number of employees who got increment: ‘|| n_count); ELSE DBMS_OUTPUT.PUT_LINE (‘None of the employees got increment’); END IF; END;
These cursors are created by the developer in his program. In this type of cursors, developer will have the control on it. Explicit cursors have to be declared in the DECLARE section and can be used as any other variable, but it will hold one or more rows of data.
Any explicit cursor created in the PL/SQL block will have four basic steps:
- DECLARE the Cursor – This step declares the cursor with its name and defines the SELECT statement for the cursor. The general syntax for declaring explicit cursor is as below:
CURSOR cursor_name IS SELECT STATEMENT; E.g.: CURSOR cursor_Dept10Emp IS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10;
- OPEN the Cursor – Cursor is opened for reading the records from it. When we open the cursor it gets executed and memory space is allocated for the records.
OPEN cursor_name OPEN cursor_Dept10Emp;
- FETCH the records – This step will start traversing the records in the cursor. Rows will be fetched one at a time from this cursor. We can perform any calculation or manipulation on these fetched records.
FETCH cursor_Dept10Emp INTO n_empID, v_empName;
- CLOSE the Cursor – Once the cursor is used and no more required in the program, close the cursor. This will release the allocated memory for this cursor.
DECLARE CURSOR cursor_Dept10Emp IS -- Declare the cursor SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10; n_empID EMPLOYEE.EMP_ID%TYPE; v_empName EMPLOYEE.EMP_NAME%TYPE; BEGIN OPEN cursor_Dept10Emp; -- Open the cursor LOOP FETCH cursor_Dept10Emp INTO n_empID, v_empName; -- Fetch cursor records EXIT WHEN cursor_Dept10Em%NOTFOUND; DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘|| n_empID|| ‘Employee Name:’|| v_empName); END LOOP; CLOSE cursor_Dept10Emp; -- Close the Cursor END;
In the above four steps, FETCH step will retrieve only one record at a time and it will assign individual column values into respective variables. Also a separate loop has to be created to iterate through all the records. If we use For loop for cursors, then we can iterate and traverse the whole records (all the columns) of cursor query using the for loop variable. This is useful when all the records of a table need to be used in the program. The cursor will automatically be opened when it is called in the FOR loop and closed when for loop ends. No need to explicitly OPEN or CLOSE the cursor. Below example shows how cursor for loop is different from above four steps.
DECLARE CURSOR cursor_Dept10Emp IS -- Declare the cursor SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = 10; rec_emp EMPLOYEE%ROWTYPE BEGIN FOR rec_emp IN cursor_Dept10Emp -- implicitly opens cursor and assigns cursor variable rec_emp LOOP DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘||rec_emp.EMP_ID|| ‘Employee Name:’|| rec_emp.EMP_NAME); END LOOP; END;
We can even pass parameters to the cursors like we do in procedures and functions. These parameters are used in the cursor query to retrieve the data. The scope of the cursor parameter is localized to cursor alone.
DECLARE CURSOR cursor_Dept10Emp (deptID EMPLOYEE.DEPT_ID%TYPE) IS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE DEPT_ID = deptID; -- deptID is the cursor parameter rec_emp EMPLOYEE%ROWTYPE BEGIN FOR rec_emp IN cursor_Dept10Emp (10) – Pass the cursor parameter as 10 LOOP DBMS_OUTPUT.PUT_LINE (‘Employee ID: ‘||rec_emp.EMP_ID|| ‘Employee Name:’|| rec_emp.EMP_NAME); END LOOP; END;
These are the cursor variables used to reference and access the static cursor. We can pass this variable to procedures / functions and get the values from function as a refcursor. In short it acts a variable, but reference to the query defined at the runtime.
We can see the difference between cursor and refcursor in below program.
DECLARE TYPE rc_cursor is ref cursor; CURSOR c_course IS SELECT * FROM COURSE; l_cursor rc_cursor; n_ID NUMBER; BEGIN IF n_ID = 10 THEN -- Dynamically opens the cursor for student ids less than 10 Open l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID<= 10’; ELSE -- Dynamically opens the cursor for student ids greater than 10 OPEN l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID > 10’; END IF; -- Opens static cursor c_course OPEN c_course; END;