Oracle updating cursor

06-Mar-2018 11:56 by 9 Comments

Oracle updating cursor - pros cons dating friend

For transparent understanding, it is a handle or gateway adopted by Oracle to execute a SQL query.The memory location to which it points is known as Context area.

Oracle 11g identifies a different categorization basis of implicit cursors.

It is one of the core database programming concepts, which forms a basic unit of execution of SQL statement.

A cursor is a pointer, which points towards a pre allocated memory location in the SGA.

Oracle server processes every SQL statement in a PL/SQL block as an implicit cursor.

All the DML statements (INSERT, UPDATE or DELETE) and SELECT query with INTO or BULK COLLECT clauses are candidates for implicit cursors.

Check the exception situations in the below screen dumps.

Explicit cursors are best suited in situations where number of records in the result set is not known.Since, it returns a single row output, it demonstrates the construction of single row implicit cursor.[box]DECLAREL_SAL NUMBER; BEGINSELECT SALARYINTO L_SALFROM EMPLOYEESWHERE EMPLOYEE_ID=110; END;[/box]Example Code [3]: In the PL/SQL block below, UPDATE statement recalculates the salary of all employees in the company.As the DML statement affects multiple rows, it demonstrates a multiple row implicit cursor.[box]BEGINUPDATE EMPLOYEESSET SALARY = SALARY (COMM*SALARY); END;[/box]These cursors are explicitly declared in the DECLARE section of the block.But, implicit cursor runs serious threat to the application, when it returns zero or multiple rows.Zero rows returning implicit cursor raises NO_DATA_FOUND exception, while multiple rows one raises TOO_MANY_ROWS exception.Oracle associates every SELECT statement with a cursor to hold the query information in this context area.