DB2/Cursor程序在cobol中工作 [英] DB2/Cursor program working in cobol

问题描述

我的要求是从db2表中删除包含重复电话号码(无效电话号码，硬编码的16个电话号码的集合)的数据.应保留一个电话号码，具体取决于序列号(最小值).

My requirement is to delete data from db2 table which contains duplicate phone num(invalid phone num, set of 16 phone numbers which is hardcoded).And one phone num should be retained , that depends on sequence number(minimum value).

考虑主表具有以下数据:PHONE_TAB

consider main table has following data : PHONE_TAB

Client_Id   Phone_num  Seq_num
 1234        45678       15   --- delete
 4444        55555       45
 1234        45678       10   ---should be retained 
 5555        22222       25
 1234        45678       20   ---  delete
 1234        45678       11   ---  delete

注意:同样，在删除以进行备份之前，我还必须将要删除的行移至输出磁带文件.

Note : also i have to move the rows which will be deleted to output tape file before deleting to take backup.

提出了以下逻辑， 声明光标:

Have come up with the below logic, Declaring cursor :

EXEC SQL
  DECLARE CUR1 CURSOR WITH HOLD FOR
   SELECT * FROM PHONE_TAB WHERE PHONE_NUM = 45678
END-EXEC

FETCH-PARA

FETCH-PARA

EXEC SQL
 FETCH CUR1 INTO :DCLGEN_CLIENT_ID
                :DCLGEN_PHONE_NUM
                :DCLGEN_SEQ_NUM
END-EXEC

VALIDATE-PARA

VALIDATE-PARA

EXEC SQL
 SELECT MIN(SEQ_NUM) FROM PHONE_TAB
 WHERE CLIENT_ID = :DCLGEN_CLIENT_ID
       PHONE_NUM = :DCLGEN_PHONE_NUM GROUP BY CLIENT_ID AND PHONE_NUM
       INTO WS_MIN
END-EXEC

EVALUATE TRUE 
 WHEN SEQ_NUM > WS_MIN 
      PERFORM BACKUP-PARA
 WHEN OTHER
      PERFORM FETCH-PARA

最后，DELETE-PARA

Finally , DELETE-PARA

EXEC SQL
  DELETE FROM PHONE_TAB WHERE CURRENT CURSOR
END-EXEC

有人可以告诉我这种逻辑是否正确? 我的疑问是是否会在不备份到磁带文件的情况下进行批量删除?

Can someone tell me whether this logic is right ? My doubt is whether it will do mass deletion without taking backup to tape file?

谢谢

推荐答案

您可以使用以下SQL一次性删除它们

You can delete them all in one go with the following SQL

DELETE FROM PHONE_TAB A
 WHERE EXISTS (SELECT 1 FROM PHONE_TAB B
                WHERE A.PHONE_NUM = B.PHONE_NUM AND
                      A.SEQ_NUM > B.SEQ_NUM)

它的作用是删除存在另一行的所有行，该行具有相同的电话号码和较低的序号.

It works by deleting all rows where another row exists that has the same phone number and a lower sequence number.

您可以先运行select这样的命令来检查它是否删除了正确的行

You can check that it deletes the right rows by first running it with a select, like this

SELECT * FROM PHONE_TAB A
 WHERE EXISTS (SELECT 1 FROM PHONE_TAB B
                WHERE A.PHONE_NUM = B.PHONE_NUM AND
                      A.SEQ_NUM > B.SEQ_NUM)

如果需要备份行，可以运行select然后删除行.

If you need to backup the rows, you can run the select and then delete the rows.

这篇关于DB2/Cursor程序在cobol中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！