Home » SQL & PL/SQL » SQL & PL/SQL » HELP on How create a STORED PROCEDURE
HELP on How create a STORED PROCEDURE [message #433504] Wed, 02 December 2009 10:24 Go to next message
chirpl
Messages: 3
Registered: December 2009
Location: Rome
Junior Member
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!
Re: HELP on How create a STORED PROCEDURE [message #433513 is a reply to message #433504] Wed, 02 December 2009 10:55 Go to previous messageGo to next message
mortonmorton
Messages: 6
Registered: October 2009
Junior Member
Please check Oracle documentation for creating a procedure. Also look on package.
Re: HELP on How create a STORED PROCEDURE [message #433542 is a reply to message #433504] Wed, 02 December 2009 21:06 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please read Forum guide lines before posting http://www.orafaq.com/forum/t/88153/0/

http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807.pdf


Sriram Smile

[Updated on: Wed, 02 December 2009 21:07]

Report message to a moderator

Previous Topic: Inequality slowing down UPDATE
Next Topic: insert into wrong column sometimes
Goto Forum:
  


Current Time: Sat Dec 03 16:04:51 CST 2016

Total time taken to generate the page: 0.10020 seconds