For every SQL statement execution certain area in memory is allocated. PL/SQL allow you to name this area. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable.
When you declare a cursor, you get a pointer variable, which does not point any thing. When the cursor is opened, memory is allocated and the cursor structure is created. The cursor variable now points the cursor. When the cursor is closed the memory allocated for the cursor is released.
Cursors allow the programmer to retrieve data from a table and perform actions on that data one row at a time.
There are two types of cursors implicit cursors and explicit cursors.
Implicit cursors:
For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.
Syntax:
SELECT ename, sal INTO ena, esa FROM EMP WHERE EMPNO = 7844;
Note: Ename and sal are columns of the table EMP and ena and esa are the variables used to store ename and sal fetched by the query.
Explicit Cursors:
Explicit cursors are used in queries that return multiple rows. The set of rows fetched by a query is called active set. The size of the active set meets the search criteria in the select statement. Explicit cursor is declared in the DECLARE section of PL/SQL program.
Syntax:
CURSOR cursor_name IS select statement
Sample Code:
DECLARE
CURSOR emp_cur IS SELECT ename FROM EMP;
BEGIN
-------
-------
END;
A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.
You can manipulate cursors using the OPEN, FETCH, and CLOSE statements.
When you need to get information about the current status of your cursor, or the result of the last fetch from a cursor, you will access cursor attributes.
PL/SQL offers a total of six attributes:
- %FOUND- Returns TRUE if record was fetched successfully; FALSE otherwise.
- %NOTFOUND- Returns TRUE is record was not fetched successfully; FALSE otherwise.
- %ROWCOUNT - Returns number of records fetched from cursor at that point in time.
- %ISOPEN- Returns TRUE is cursor is open; FALSE otherwise.
- %BULK_ROWCOUNT - Returns the number of records modified by the FORALL statement for each collection element.
- %BULK_EXCEPTIONS- Returns exception information for rows modified by the FORALL statement for each collection element.
Examples
1. An explicit cursor that selects data.
SQL> declare
2 cursor emps
3 is select *
4 from employees
5 where rownum < 6
6 order by 1;
7
8 emp employees%rowtype;
9 row number := 1;
10 begin
11 open emps;
12 fetch emps into emp;
13
14 loop
15 if emps%FOUND then
16 dbms_output.put_line('Looping over record '
17 ||row|| ' of ' || emps%ROWCOUNT);
18 fetch emps into emp;
19 row := row + 1;
20 elsif emps%NOTFOUND then
21 exit; -- EXIT statement exits the LOOP
22 end if;
23 end loop;
24
25 if emps%ISOPEN then
26 close emps;
27 end if;
28 end;
29 /
Looping over record 1 of 1
Looping over record 2 of 2
Looping over record 3 of 3
Looping over record 4 of 4
Looping over record 5 of 5
PL/SQL procedure successfully completed.
2. An explicit cursor fetch loop.
SQL> DECLARE 2 myLecturerID lecturer.id%TYPE; 3 firstName lecturer.first_name%TYPE; 4 v_LastName lecturer.last_name%TYPE; 5 6 v_Major lecturer.major%TYPE := 'Computer Science'; 7 8 CURSOR cursorValue IS 9 SELECT id, first_name, last_name 10 FROM lecturer 11 WHERE major = v_Major; 12 BEGIN 13 OPEN cursorValue; 14 LOOP 15 FETCH cursorValue INTO myLecturerID, firstName 16 , v_LastName; 17 EXIT WHEN cursorValue%NOTFOUND; 18 END LOOP; 19 CLOSE cursorValue; 20 END; 21 / PL/SQL procedure successfully completed.
3. Use cursor to store the row count.
SQL> declare
2 l_emp_count number;
3 i number; -- We will use this as our counter
4
5 CURSOR cursorValue IS
6 select count(*)
7 from lecturer;
8 begin
9 OPEN cursorValue;
10 FETCH cursorValue INTO l_emp_count;
11
12 FOR i IN 1 .. l_emp_count LOOP
13 dbms_output.put_line('Employee ' || i);
14 END LOOP;
15
16 CLOSE cursorValue;
17 end;
18 /
Employee 1
Employee 2
Employee 3
Employee 4
Employee 5
Employee 6
Employee 7
Employee 8
Employee 9
Employee 10
Employee 11
Employee 12
PL/SQL procedure successfully completed.
4. Cursor with parameter.
SQL> declare
2 cursor cursorValue (p_deptid in number)
3 is select *
4 from employees
5 where department_id = p_deptid;
6
7 l_emp employees%rowtype;
8 begin
9 open cursorValue(30);
10 loop
11 fetch cursorValue into l_emp;
12 exit when cursorValue%notfound;
13 dbms_output.put('Emp id ' || l_emp.employee_id || ' is ');
14 dbms_output.put_line(l_emp.last_name);
15 end loop;
16 close cursorValue;
17
18 dbms_output.put_line('Getting employees for department 90');
19 open cursorValue(90);
20 loop
21 fetch cursorValue into l_emp;
22 exit when cursorValue%notfound;
23 dbms_output.put('Emp id ' || l_emp.employee_id || ' is ');
24 dbms_output.put_line(l_emp.last_name);
25 end loop;
26 close cursorValue;
27 end;
28 /
Getting employees for department 90
PL/SQL procedure successfully completed.
