HELP on How Create a STORED PROCEDURE

[raf]

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','[email protected]', '333345698756', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('00127', '00999', 'SAMMY', 'LUISS', '[email protected]', '97979799333', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('22129', NULL, 'GRACE', 'LUISS', '[email protected]', '3443434343444', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('33129', '76890', 'JOHN', 'WILSON', '[email protected]', '2323456211', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('005678', NULL, 'JOHN', 'SMITH', '[email protected]', '45634562121', '1');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('225678', NULL, 'JOHN', 'SMITH', '[email protected]', '4563445545', '1');
Insert into TABX_TO_TABY(COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('335622', NULL, 'JOHN', 'ROGERS', '[email protected]', '34234343', '2');
Insert into TABX_TO_TABY (COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('234123', NULL, 'TOMMY', 'ROGERS', '[email protected]', '343411111', '2');
Insert into TABX_TO_TABY(COD_ID, CID, NAME_FIRST, NAME_LAST, EMAIL, MOBILE, STAT)
Values ('00888', '78089', 'TOMMY', 'MOODY','[email protected]', '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', '[email protected]', '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', '[email protected]', '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','[email protected]', 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', '[email protected]', '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', '[email protected]', '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', '[email protected]', 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', '[email protected]', 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', '[email protected]', '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', '[email protected]', 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!

[PAVB]

CREATE OR REPLACE PROCEDURE YOUR_PROCEDURE
IS
BEGIN
  -- Insert here your business logic
END;
/

[raf]

CREATE OR REPLACE PROCEDURE YOUR_PROCEDURE
IS
BEGIN
  -- Insert here your business logic
END;
/

my code should be like this:

MERGE INTO taby dst
USING (
WITH cntr AS
(
SELECT 1 AS n FROM dual UNION ALL
SELECT 2 FROM dual
)
SELECT x.*
, x.name_last || '_' ||
SUBSTR (x.name_first, 1, 3) || '_' ||
SUBSTR (x.cod_id, -3) ||
CASE
WHEN c.n = 1
THEN '_99'
END AS pk_id
, CASE
WHEN c.n = 1 THEN cid
ELSE cod_id
END AS new_cod_id
, NVL2 ( x.cid
, c.n
, x.stat
) AS new_stat
, c.n
FROM tabx_to_taby x
JOIN cntr c ON c.n = 2
OR x.cid IS NOT NULL
) src
ON ( src.cod_id = dst.cod_id
AND src.n = 2
)
WHEN MATCHED THEN
UPDATE SET dst.pk_id = src.pk_id
, dst.name_first = src.name_first
, dst.name_last = src.name_last
, dst.email = src.email
, dst.mobile = src.mobile
, dst.stat = src.new_stat
WHERE ( src.cid IS NULL -- Case 3
AND ( src.pk_id != dst.pk_id
OR src.name_first != dst.name_first
OR src.name_last != dst.name_last
OR src.email != dst.email
OR src.mobile != dst.mobile
OR src.stat != dst.stat
)
)
OR src.cid IS NOT NULL -- Case 4
WHEN NOT MATCHED THEN
INSERT ( dst.pk_id, dst.cod_id, dst.name_first, dst.name_last, dst.email,
dst.mobile, dst.stat
)
VALUES ( src.pk_id, src.new_cod_id, src.name_first, src.name_last, src.email,
src.mobile, src.new_stat
)
;

How can I write this code in my stored procedure?

[PAVB]

are you asking how to edit a text file?