A cursor a temporary memory which holds the records of a sql statement.
A cursor is a pointer that points to a result of a query.
A cursor is used to referred to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:
The implicit cursors are automatically generated by Oracle while an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE is executed, if you don't use an explicit cursor for the statement.
Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor's information and statuses such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.
The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.
When you execute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement. It will return an error if there no data is selected.
Attribute | Description |
---|---|
%FOUND | Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at least one row or more rows or a SELECT INTO statement returned one or more rows. Otherwise it returns FALSE. |
%NOTFOUND | Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise it returns FALSE. It is a just opposite of %FOUND. |
%ISOPEN | It always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements. |
%ROWCOUNT | It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement. |
DECLARE total_records NUMBER(2); BEGIN UPDATE emp SET sal = sal + 1000; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('No employee updated'); ELSIF SQL%FOUND THEN total_records := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE( total_records || ' employees updated '); END IF; END;
An explicit cursor is an SELECT statement declared explicitly in the declaration section of the current block or a package specification. For an explicit cursor, you have the control over its execution cycle from OPEN, FETCH, and CLOSE.
CURSOR cursor_name IS query;
OPEN cursor_name;
When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement. Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.
The FETCH statement places the contents of current row into variables. The syntax of FETCH statement is as follows:
FETCH cursor_name INTO variable_list;
CLOSE cursor_name;
Closing a cursor instructs Oracle to release allocated memory at an appropriate time.
If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of the block, procedure, and function ends.
However, you must explicitly close package-based cursors. If you close a cursor that is not opened, Oracle will raise an INVALID_CURSOR exception.
DECLARE e_empno emp.empno%TYPE; e_name emp.ename%TYPE; e_hiredt emp.hiredate%TYPE; CURSOR c_emp_dtl IS SELECT empno, ename, hiredate FROM emp; BEGIN OPEN c_emp_dtl; LOOP FETCH c_emp_dtl INTO e_empno,e_name,e_hiredt; EXIT WHEN c_emp_dtl%NOTFOUND; dbms_output.put_line('ename :' || e_empno||' , empid :' || e_empno || ' , hiredate :'|| e_hiredt); END LOOP; CLOSE c_emp_dtl; END;
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.