skip to content »

Updating plsql

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks.

Here is an example: CREATE TABLE T1( e INTEGER, f INTEGER ); DELETE FROM T1; INSERT INTO T1 VALUES(1, 3); INSERT INTO T1 VALUES(2, 4); /* Above is plain SQL; below is the PL/SQL program.

*/ DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e A cursor is a variable that runs through the tuples of some relation.

Note: The material on triggers that was formerly in this document has been moved to A New Document on Constraints and Triggers. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. All PL/SQL programs are made up of blocks, which can be nested within each other.

Typically, each block performs a logical action in he program.

Another Example of WHERE CURRENT OF Go through all Maths students and set all GPA’s under 4.0 to 4.0!

updating plsql-78

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.

updating plsql-39updating plsql-74updating plsql-57

WHERE CURRENT OF & FOR UPDATE The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.If the relation is stored, we can also update or delete the tuple at the current cursor position. On the other hand, should the previous definition be a different procedure of the same name, you will not be warned, and the old procedure will be lost.There can be any number of parameters, each followed by a mode and a type.The simplest form of program has some declarations followed by an executable section consisting of one or more of the SQL statements with which we are familiar.The major nuance is that the form of the statement in PL/SQL only works if the result of the query contains a single tuple.Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.