Updating table using cursor in oracle
WHERE CURRENT OF & FOR UPDATE The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted.
Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks.Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows: WHERE [CURRENT OF cursor_name | search_condition] The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.You must, of course, use the SQL language to access tables, and each time you do so, you use a to get the job done.
A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE).
Cursor management of DML statements is handled by Oracle Database, but PL/SQL offers several ways to define and manipulate cursors to execute SELECT statements.
The FOR UPDATE clause in the SELECT statement can only be specified in the top level; subqueries cannot have this clause.
Another Example of WHERE CURRENT OF Go through all Maths students and set all GPA’s under 4.0 to 4.0!
DECLARE this Student Student%ROWTYPE; CURSOR Maths_Student IS SELECT * FROM Student WHERE SID IN (SELECT SID FROM Take WHERE CID = ’CS145’) FOR UPDATE; BEGIN OPEN Maths_Student; LOOP FETCH Maths_Student INTO this Student; EXIT WHEN (Maths_Student%NOTFOUND); IF (this Student.
Part 12 in a series of articles on understanding and using PL/SQL The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database.