Summary -

In this topic, we described about the below sections -

The cursor for DELETE is used to delete the rows from the table or result table.

Positioned DELETE is allowed in the cursor for DELETE.

FOR UPDATE/FOR UPDATE OF clause used to declare these cursors.

WHERE CURRENT OF used to delete the row in these cursors using the current row positioning.

All columns can be updated if a FOR UPDATE OF, an ORDER BY, a FOR READ ONLY, or a SCROLL clause without a DYNAMIC clause is not specified.

If the "FOR UPDATE/FOR UPDATE OF" clause is specified while declaring a cursor, DELETE statements might execute on any specific row or all the rows of the result table. However, it is not mandatory to execute DELETE for every FETCH.

Syntax -

Declaring Cursor -

EXEC SQL
	DECLARE cursor-name/cursor-variable-name 
	    CURSOR FOR select-statement
	    FOR UPDATE OF
END-EXEC.

Opening Cursor -

EXEC SQL
	OPEN cursor-name/cursor-variable-name;
END-EXEC.

Fetching Cursor -

EXEC SQL
	FETCH cursor-name/cursor-variable-name
	INTO :hv1,
	     :hv2,
	     .
	     .
	     :hvn;
END-EXEC.

UPDATE Statement -

EXEC SQL
	DELETE FROM table_name
    WHERE CURRENT OF cursor-name/cursor-variable-name
END-EXEC.

Closing Cursor -

EXEC SQL
	CLOSE cursor-name/cursor-variable-name;
END-EXEC.

cursor-name -

  • Cursor-name is the name of the cursor used in an application program to refer to the cursor.
  • The cursor-name should be unique in the entire application program.
  • The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
  • The max length of the cursor-name is 128 characters for the standard cursors.

cursor-variable-name -

  • Cursor-variable-name is a working-storage variable that contains the cursor name used in an application program to refer to the cursor.
  • The cursor-variable-name should be unique in the entire application program.
  • The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
  • The max length of the cursor-name is 128 characters for the standard cursors.

Examples -

Input-

employee_details table

Scenario1 - Declare the cursor for deleting rows that have the designation as "FRESHER" employee_details table.

Code -

DECLARE CURSOR -

   EXEC SQL 
	DECLARE E1 CURSOR FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
		WHERE  DESIGNATION = "FRESHER"
        FOR    UPDATE OF SALARY
   END-EXEC.

DELETE -

   EXEC SQL 
	DELETE FROM EMPLOYEE_DETAILS	   
	WHERE  CURRENT OF E1
   END-EXEC.

Scenario2 - Declare the cursor for deleting all columns of the employee_details table.

Code -

DECLARE CURSOR -

   EXEC SQL 
	DECLARE E2 CURSOR FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION,
               MANAGER_ID,
               DATE_OF_HIRE,
               SALARY,
               DEPT_ID
     	FROM   EMPLOYEE_DETAILS 
     	FOR UPDATE 
   END-EXEC.
   
DELETE -

   EXEC SQL 
	DELETE FROM EMPLOYEE_DETAILS		   
	WHERE  CURRENT OF E2
   END-EXEC.
   

Practical Example -

Scenario - The below example describes how the delete cursor is coded in the COBOL + DB2 program.

Requirement - Delete the rows whose designation is TESTER.

Code -

DELETE CURSOR Program Code
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CDELETE.                                             
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         
                                                                        
           EXEC SQL                                                     
             INCLUDE SQLCA                                              
           END-EXEC.                                                    
                                                                        
           EXEC SQL                                                     
             INCLUDE EMPLDET                                            
           END-EXEC.                                                    
                                                                        
      * DECLARING UPDATE   CURSOR                                       
                                                                        
           EXEC SQL                                                     
                DECLARE CSR1 CURSOR FOR                                 
                SELECT EMP_ID,                                          
                       EMP_NAME,                                        
                       DESIGNATION,                                     
                       MANAGER_ID,                                      
                       SALARY                                           
                FROM   EMPLOYEE_DETAILS                                 
                WHERE  DESIGNATION = 'TESTER'                           
                  AND  DEPT_ID = 2000                                   
                FOR    UPDATE                                           
           END-EXEC.                                                    
                                                                        
        01 WS-VAR.                                                      
           05 WS-DCNT               PIC 9(02) VALUE ZEROES.             
                                                                        
       PROCEDURE DIVISION.                                              
                                                                        
      * OPENING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                OPEN CSR1                                               
           END-EXEC.                                                   
                                                                        
      * FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE                  
                                                                        
           IF SQLCODE EQUAL ZERO                                        
              PERFORM FETCH-EMP-DETAILS                                 
                 THRU FETCH-EMP-DETAILS-EXIT                            
                UNTIL SQLCODE NOT EQUAL 0                               
           END-IF.                                                      
                                                                        
      * CLOSING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                CLOSE CSR1                                              
           END-EXEC.                                                    
                                                                        
           DISPLAY "NO OF ROWS DELETED:  ", WS-DCNT.                    
                                                                        
           STOP RUN.                                                    
                                                                        
       FETCH-EMP-DETAILS.                                               
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH CSR1                                              
                INTO   :EMP-ID,                                         
                       :EMP-NAME,                                       
                       :DESIGNATION,                                    
                       :MANAGER-ID                                      
                       :MANAGER-ID-NI,                                  
                       :SALARY                                          
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    PERFORM DELETE-EMP-DETAILS                          
                       THRU DELETE-EMP-DETAILS-EXIT                     
               WHEN 100                                                 
                    CONTINUE                                            
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       FETCH-EMP-DETAILS-EXIT.                                          
           EXIT.                                                        
                                                                        
       DELETE-EMP-DETAILS.                                              
                                                                        
           EXEC SQL                                                     
                DELETE FROM EMPLOYEE_DETAILS                            
                 WHERE CURRENT OF CSR1                                  
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    COMPUTE WS-DCNT = WS-DCNT + 1                       
               WHEN 100                                                 
                    DISPLAY "DELETING ROW NOT FOUND"                    
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       DELETE-EMP-DETAILS-EXIT.                                         
           EXIT.                                                        
**************************** Bottom of Data ****************************

Output -

DELETE CURSOR Program Output

employee_details table after DELETE -

DELETE CURSOR Program Output