skip to content »

Oracle updating cursor

oracle updating cursor-19

A cursor can hold more than one row, but can process only one row at a time.The set of rows the cursor holds is called the These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed.

oracle updating cursor-51oracle updating cursor-44

Instead of this complex looping construct, I can use the following single SQL statement instead: This single SQL statement should run faster than the same work being done in the loop.If none of the employee’s salary are updated we get a message 'None of the salaries where updated'.Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.There are two types of cursors: The implicit cursors are automatically generated by Oracle while an SQL statement is executed, if you don't use an explicit cursor for the statement.These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. Orcale provides some attributes known as Implicit cursor's attributes to check the status of DML operations.These cursors should be defined in the declaration section of the PL/SQL block.

It is created on a SELECT statement which returns more than one row.

Let's take an example to demonstrate the use of explicit cursor.

In this example, we are using the already created CUSTOMERS table.

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.

Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN.