I've these tables:
CREATE TABLE TABX_TO_TABY
(
COD_ID VARCHAR2(32),
CID VARCHAR2(32),
NAME_FIRST VARCHAR2(32),
NAME_LAST VARCHAR2(32),
EMAIL VARCHAR2(32),
MOBILE VARCHAR2(32),
STAT VARCHAR2(32)
);
ALTER TABLE TABX_TO_TABY
ADD (CONSTRAINT PK_COD_ID PRIMARY KEY (COD_ID));
CREATE TABLE TABY
(
PK_ID VARCHAR2(32),
COD_ID VARCHAR2(32),
NAME_FIRST VARCHAR2(32),
NAME_LAST VARCHAR2(32),
EMAIL VARCHAR2(32),
CITY VARCHAR2(32),
MOBILE VARCHAR2(32),
STAT VARCHAR2(32)
);
ALTER TABLE TABY
ADD (CONSTRAINT PK_PK_ID PRIMARY KEY (PK_ID));
The primary key PK_ID comes from the TABX_TO_TABY table linked in this way:
name_last||'_'||substr(name_first,1,3)||'_'||substr(cod_id,-3)
TABX_TO_TABY has more 10000 records
TABY has more 5000 records
Below is a subset of data:
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('00123', NULL, 'TOMMY', 'MAL','mal2@bb.com', '333345698756', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('00127', '00999', 'SAMMY', 'LUISS', 'cccc@bb.com', '97979799333', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('22129', NULL, 'GRACE', 'LUISS', 'mmmm@bb.com', '3443434343444', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('33129', '76890', 'JOHN', 'WILSON', 'ssss@bb.com', '2323456211', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('005678', NULL, 'JOHN', 'SMITH', 'smith@bb.com', '45634562121', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('225678', NULL, 'JOHN', 'SMITH', 'aaaa@bb.com', '4563445545', '1');
Insert into TABX_TO_TABY(COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('335622', NULL, 'JOHN', 'ROGERS', 'rrrttt@bb.com', '34234343', '2');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('234123', NULL, 'TOMMY', 'ROGERS', 'rogers@bb.com', '343411111', '2');
Insert into TABX_TO_TABY(COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('00888', '78089', 'TOMMY', 'MOODY','mody@bb.com', '345234999', '1');
COMMIT;
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('MAL_TOM_123', '00123', 'TOMMY', 'MAL', 'mal@bb.com', 'ROME', '333345698756', '1');
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('LUISS_SAM_127', '00127', 'SAMMY', 'LUISS', 'cccc@bb.com', 'LONDON', '97979799333', '1');
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('WILSON_JOH_129', '33129', 'JOHN', 'WILSON','ssss@bb.com', NULL, '2323456211', '1');
COMMIT;
PROBLEM:
I'd like to insert (with a STORED PROCEDURE) from TABX_TO_TABY into TABY with these condition:
CASE1
If TABX_TO_TABY.COD_ID NOT present into TABY and CID is NULL I must insert into TABY a new record
CASE2
If TABX_TO_TABY.COD_ID NOT present into TABY and CID is NOT NULL I must insert into TABY 2 new record:
first with COD_ID=CID STAT=1
second with COD_ID=old COD_ID STAT=2
CASE3
If TABX_TO_TABY.COD_ID present into TABY and CID is NULL I must check if it has the same values
If the values are equal than makes no change
If the values are changed then I have to update these values
CASE4
If TABX_TO_TABY.COD_ID present into TABY and CID is NOT NULL I must insert into TABY a new record with TABY.COD_ID=TABX.CID
and I must update the old COD_ID with STAT=2 and the new COD_ID (=CID) must have the same values of the old COD_ID (NAME_FIRST,NAME_LAST, EMAIL ,CITY, MOBILE)
FOR EXAMPLE (with my data):
TABX_TO_TABY.COD_ID = 22129 (CASE1)
TABX_TO_TABY.COD_ID = 33129 (CASE2)
To avoid duplication of PK_ID value add '_99' at old COD_ID
NAME_FIRST,NAME_LAST, EMAIL ,CITY, MOBILE must have same value of the old COD_ID
- TABX_TO_TABY.COD_ID=00123 (CASE3)
-TABX_TO_TABY.COD_ID=00127 (CASE4)
To avoid duplication of PK_ID value add '_99' at old COD_ID
NAME_FIRST,NAME_LAST, EMAIL ,CITY, MOBILE must have same value of the old COD_ID
my output looks like this;
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('MAL_TOM_123', '00123', 'TOMMY', 'MAL', 'mal@bb.com', 'ROME', '333345698756', '1');
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('LUISS_SAM_127_99', '00127', 'SAMMY', 'LUISS', 'cccc@bb.com', 'LONDON', '97979799333', '2');
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('WILSON_JOH_129', '76890', 'JOHN', 'WILSON', 'ssss@bb.com', NULL, '2323456211', '1');
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('WILSON_JOH_129_99', '33129', 'JOHN', 'WILSON', 'ssss@bb.com', NULL, '2323456211', '2');
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('LUISS_SAM_127', '00999', 'SAMMY', 'LUISS', 'cccc@bb.com', 'LONDON', '97979799333', '1');
Insert into TABY (PK_ID, COD_ID, NAME_FIRST, NAME_LAST, EMAIL, CITY, MOBILE, STAT)
Values ('LUISS_GRA_129', '22129', 'GRACE', 'LUISS', 'mmmm@bb.com', NULL, '3443434343444', '1');
COMMIT;
How can I put these cases in a STORED PROCEDURE whereas TABX_TO_TABY has more 10000 records and TABY has more 5000 records?
Thanks in advance!
|