Results 1 to 2 of 2

Thread: Procedure Help

  1. #1
    Join Date
    Aug 2015
    Posts
    5

    Unanswered: Procedure Help

    Dear EXPERTS,

    create table temp_TABLE
    (
    id number,
    encr_number number,
    e_name varchar2(20),
    address varchar2(20),
    mobile_num varchar2(20),
    operation varchar2(20),
    bin_num number,
    cvv number,
    Error_log varchar2(40)
    );

    insert into temp values(1,123456,'JOHN','ABC','9999999999','ADD',65 4321,909);
    insert into temp values(2,789101,'JAMES','DEF','8888888888','ADD',8 75688,101);

    insert into temp values(3,895637,'JOSEPH','GHI','7777777777','DEL', 384303,759);
    insert into temp values(4,789345,'JACOB','JKL','6666666666','DEL',1 82380,654);


    insert into temp values(5,845678,'SMITH','MNO','5555555555','UPDATE ',744333,890);
    insert into temp values(6,234789,'SAMUEL','PQR','4444444444','UPDAT E',832234,349);


    insert into temp values(7,213439,'CLARKE DERICK','STU','333333333','UPSERT',894379,430);
    insert into temp values(8,924353,'STEVE','WXY','222222222','UPSERT' ,834384,301);


    create table MAIN_TABLE
    (
    id number,
    encr_number number PRIMARY KEY,
    e_name varchar2(20),
    address varchar2(20),
    mobile_num varchar2(20),
    bin_num number,
    cvv number
    );


    insert into MAIN_TABLE values(2,789101,'JAMES','DEF','8888888888',875688, 101);
    insert into MAIN_TABLE values(4,789345,'JACOB','JKL','6666666666',182380, 654);

    insert into MAIN_TABLE values(5,845678,'SMITHS','RIO','51515151515',74433 3,890);
    insert into MAIN_TABLE values(6,234789,'SAMUEL MARK','TRIO','4444444444',832234,349);

    insert into temp values(7,213439,'CLARKE','KJYG','313131313',894379 ,430);




    Functionality:

    ADD

    if operation is 'ADD' Then check the encr_number In MAIN_TABLE,If Encr_number doesn't exit in main_table then
    insert the entire row in main_table table.if Encr_number is already exists in Main_table
    then raise the exception and insert in temp_table's error_log column called 'ALREADY EXISTS'

    DELETE

    if operation is 'DEL' Then check the encr_number In MAIN_TABLE,If Encr_number exitS in main_table then
    DELETE the entire row in main_table table.if Encr_number is Doesn't exists in Main_table
    then raise the exception and insert in temp_table's error_log column called 'No Record Found'

    UPDATE

    if operation is 'UPDATE' Then check the encr_number In MAIN_TABLE,If Encr_number exitS in main_table then
    UPDATE the entire row in main_table table as it in TEMP_TABLE.if Encr_number is Doesn't exists in Main_table
    then raise the exception and insert in temp_table's error_log column called 'No Record Found'

    UPSERT

    if operation is 'UPSERT' Then check the encr_number In MAIN_TABLE,If Encr_number exitS in main_table then
    UPDATE the entire row in main_table table as it in TEMP_TABLE.if Encr_number is Doesn't exists in Main_table
    Then Insert the entire row in main_table as it is in temp_table
    and raise the exception and insert in temp_table's error_log column called 'No Record Found'







    Desired Output
    --------------


    ID ENCR_NUMBER E_NAME ADDRESS MOBILE_NUM BIN_NUM CVV
    1 123456 JOHN ABC 9999999999 654321 909
    3 895637 JOSEPH GHI 7777777777 384303 759
    5 845678 SMITH MNO 5555555555 744333 890
    6 234789 SAMUEL PQR 4444444444 832234 349
    7 213439 CLARKE DERICK STU 333333333 894379 430
    8 924353 STEVE WXY 222222222 834384 301

  2. #2
    Join Date
    Aug 2015
    Posts
    5

    Procedure Help

    create table temp_TABLE
    (
    id number,
    encr_number number,
    e_name varchar2(20),
    address varchar2(20),
    mobile_num varchar2(20),
    operation varchar2(20),
    bin_num number,
    cvv number,
    Error_log varchar2(40)
    );

    insert into temp values(1,123456,'JOHN','ABC','9999999999','ADD',65 4321,909);
    insert into temp values(2,789101,'JAMES','DEF','8888888888','ADD',8 75688,101);

    insert into temp values(3,895637,'JOSEPH','GHI','7777777777','DEL', 384303,759);
    insert into temp values(4,789345,'JACOB','JKL','6666666666','DEL',1 82380,654);


    insert into temp values(5,845678,'SMITH','MNO','5555555555','UPDATE ',744333,890);
    insert into temp values(6,234789,'SAMUEL','PQR','4444444444','UPDAT E',832234,349);


    insert into temp values(7,213439,'CLARKE DERICK','STU','333333333','UPSERT',894379,430);
    insert into temp values(8,924353,'STEVE','WXY','222222222','UPSERT' ,834384,301);


    create table MAIN_TABLE
    (
    id number,
    encr_number number PRIMARY KEY,
    e_name varchar2(20),
    address varchar2(20),
    mobile_num varchar2(20),
    bin_num number,
    cvv number
    );


    insert into MAIN_TABLE values(2,789101,'JAMES','DEF','8888888888',875688, 101);
    insert into MAIN_TABLE values(4,789345,'JACOB','JKL','6666666666',182380, 654);

    insert into MAIN_TABLE values(5,845678,'SMITHS','RIO','51515151515',74433 3,890);
    insert into MAIN_TABLE values(6,234789,'SAMUEL MARK','TRIO','4444444444',832234,349);

    insert into temp values(7,213439,'CLARKE','KJYG','313131313',894379 ,430);




    Functionality:

    ADD

    if operation is 'ADD' Then check the encr_number In MAIN_TABLE,If Encr_number doesn't exit in main_table then
    insert the entire row in main_table table.if Encr_number is already exists in Main_table
    then raise the exception and insert in temp_table's error_log column called 'ALREADY EXISTS'

    DELETE

    if operation is 'DEL' Then check the encr_number In MAIN_TABLE,If Encr_number exitS in main_table then
    DELETE the entire row in main_table table.if Encr_number is Doesn't exists in Main_table
    then raise the exception and insert in temp_table's error_log column called 'No Record Found'

    UPDATE

    if operation is 'UPDATE' Then check the encr_number In MAIN_TABLE,If Encr_number exitS in main_table then
    UPDATE the entire row in main_table table as it in TEMP_TABLE.if Encr_number is Doesn't exists in Main_table
    then raise the exception and insert in temp_table's error_log column called 'No Record Found'

    UPSERT

    if operation is 'UPSERT' Then check the encr_number In MAIN_TABLE,If Encr_number exitS in main_table then
    UPDATE the entire row in main_table table as it in TEMP_TABLE.if Encr_number is Doesn't exists in Main_table
    Then Insert the entire row in main_table as it is in temp_table
    and raise the exception and insert in temp_table's error_log column called 'No Record Found'







    Desired Output
    --------------


    ID ENCR_NUMBER E_NAME ADDRESS MOBILE_NUM BIN_NUM CVV
    1 123456 JOHN ABC 9999999999 654321 909
    3 895637 JOSEPH GHI 7777777777 384303 759
    5 845678 SMITH MNO 5555555555 744333 890
    6 234789 SAMUEL PQR 4444444444 832234 349
    7 213439 CLARKE DERICK STU 333333333 894379 430
    8 924353 STEVE WXY 222222222 834384 301

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •