按候选键分组并更新记录以进行批处理

Question

create table SCHEDULED_TIMER_EVENTS(
STATUS varchar(50),
EVENT_NAME varchar(50),
CUSOTOMER_ID varchar(50),
PROD_CODE varchar(50),              
FILE_WORKITEM_ID varchar(50),
PROCESSING_BATCH_ID varchar(50)
);

insert into SCHEDULED_TIMER_EVENTS values ('PENDING','ALERT1',1,1,1,1);
insert into SCHEDULED_TIMER_EVENTS values ('PENDING','ALERT1',2,2,2,2);
insert into SCHEDULED_TIMER_EVENTS values ('PENDING','ALERT1',1,1,1,3);
insert into SCHEDULED_TIMER_EVENTS values ('PENDING','ALERT1',1,1,1,4);
insert into SCHEDULED_TIMER_EVENTS values ('PENDING','ALERT1',1,1,1,5);
select * from SCHEDULED_TIMER_EVENTS;

我如何创建一个唯一的PROCESSING_BATCH_ID进一步处理根据customer_id的候选键，prod_code

Answer 1

一个序列，只要在insert时省略该列就可以了

SQL> create sequence PROCESSING_BATCH_ID_SEQ;
Sequence created.
SQL>
SQL> create table SCHEDULED_TIMER_EVENTS(
2               STATUS varchar(50),
3                EVENT_NAME varchar(50),
4                CUSOTOMER_ID varchar(50),
5                PROD_CODE varchar(50),
6                FILE_WORKITEM_ID varchar(50),
7               PROCESSING_BATCH_ID varchar(50)
8  );
Table created.
SQL>
SQL> alter table SCHEDULED_TIMER_EVENTS
2  modify PROCESSING_BATCH_ID default PROCESSING_BATCH_ID_SEQ.nextval;
Table altered.
SQL>
SQL> insert into SCHEDULED_TIMER_EVENTS ( STATUS,EVENT_NAME,CUSOTOMER_ID,PROD_CODE,FILE_WORKITEM_ID) values ('PENDING','ALERT1',1,1,1);
1 row created.
SQL> insert into SCHEDULED_TIMER_EVENTS ( STATUS,EVENT_NAME,CUSOTOMER_ID,PROD_CODE,FILE_WORKITEM_ID) values ('PENDING','ALERT1',2,2,2);
1 row created.
SQL> insert into SCHEDULED_TIMER_EVENTS ( STATUS,EVENT_NAME,CUSOTOMER_ID,PROD_CODE,FILE_WORKITEM_ID) values ('PENDING','ALERT1',1,1,1);
1 row created.
SQL> insert into SCHEDULED_TIMER_EVENTS ( STATUS,EVENT_NAME,CUSOTOMER_ID,PROD_CODE,FILE_WORKITEM_ID) values ('PENDING','ALERT1',1,1,1);
1 row created.
SQL> insert into SCHEDULED_TIMER_EVENTS ( STATUS,EVENT_NAME,CUSOTOMER_ID,PROD_CODE,FILE_WORKITEM_ID) values ('PENDING','ALERT1',1,1,1);
1 row created.