Oracle cursor deletion and update operations

The following articles mainly introduce Oracle cursor deletion and UPDATE. We all know that in PL/SQL, UPDATE and DELETE statements can be used to UPDATE or DELETE related data rows. An explicit Oracle cursor is used only when multiple rows of data are required.

PL/SQL allows you to delete or update records only using Oracle cursors.

The where current of substring in the UPDATE or DELETE statement is used to process the most recent data retrieved from the table for the UPDATE or DELETE operation.

To use this method, you must use the for update substring when declaring the cursor. When you use the for update substring to open an Oracle cursor, all data rows in the return set will be in ROW-LEVEL) Exclusive locking. Other objects can only query these data rows and cannot perform UPDATE, DELETE, or SELECT... for update operation.

Syntax:

 
 

  
  
FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..  
  
  
[nowait]  
 
 

In multi-table queries, the "OF" clause is used to lock a specific table. If the "OF" clause is ignored, all selected data rows in the table are locked. If these data rows have been locked by other sessions, ORACLE will normally wait until the data row is unlocked.

The syntax for using where current of substring in UPDATE and DELETE is as follows:

 
 

  
  
WHERE{CURRENT OF cursor_name|search_condition}  
 
 

Example:

 
 

  
  
DELCARE  
  
  
CURSOR c1 IS SELECT empno,salary  
  
  
FROM emp  
  
  
WHERE comm IS NULL  
  
  
FOR UPDATE OF comm;  
  
  
v_comm NUMBER(10,2);  
  
  
BEGIN  
  
  
FOR r1 IN c1 LOOP  
  
  
IF r1.salary<500 THEN  
  
  
v_comm:=r1.salary*0.25;  
  
  
ELSEIF r1.salary<1000 THEN  
  
  
v_comm:=r1.salary*0.20;  
  
  
ELSEIF r1.salary<3000 THEN  
  
  
v_comm:=r1.salary*0.15;  
  
  
ELSE  
  
  
v_comm:=r1.salary*0.12;  
  
  
END IF;  
  
  
UPDATE emp;  
  
  
SET comm=v_comm 
  
  
WHERE CURRENT OF c1l;  
  
  
END LOOP;  
  
  
END   
 
 

The above content is an introduction to the update and deletion of Oracle cursors. I hope you will find some gains.

Article by: http://www.programbbs.com/doc/class10-3.htm