1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Store procedure table in insert into

Discussion in 'SQL PL/SQL' started by francesco, Oct 19, 2018.

  1. francesco

    francesco Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    milano
    Good morning,
    STORE SQL PROCEDURES

    I have three tables:

    the first table has the file name:
    FILE table:
    ..
    ..record

    the second table has the name of the detail:
    DETAIL table:
    ..
    ..record

    the third table has the public name:
    PUBLIC table
    ..
    ..record


    I want to create a store procedure in the following ways:
    the FILE table has a 1 to N relation of the DETAIL table
    in practice, once the data have been entered in the detail table, with insert
    in the file table there is the column called the result, this column if it is ok, checking that all the records are inserted in the detail table, ie the positive result is the insert in the public table
    otherwise in the detail table if the result is negative ie some record has not been inserted into the table this column is ko and does not insert into the public table
    In the public table, having the record called the result
    all those who were successful posito ie ok does the insert in the PUBLIC table
    I hope I explained myself better
    --------------------------------------------------------------------------------------------------------------------------
    For each file

    Check whether all records are entered in the detail table

    If in the detail table the records are all filled up

    insert into the public table

    in the public table and detail table, the state name record becomes OK and the file name status record becomes OK

    If in the detail table some records have a null value or empty field it does not insert into the public table

    update a new status of the file table is the status becomes KO
    -------------------------------------------------------------------------------------------------------------------------------
    Code (SQL):
    BEGIN
        DECLARE INTEGER VARIABLES.iddettaglioTemp;
        DECLARE string VARIABLES.statoTemp;
        DECLARE INTEGER VARIABLES.codhnTemp;
        DECLARE INTEGER VARIABLES.partitaivacfTemp;
        DECLARE string VARIABLES.ragionesocialeTemp;
        DECLARE string VARIABLES.indirizzoTemp;
        DECLARE string VARIABLES.comuneTemp;
        DECLARE string VARIABLES.provTemp;
        DECLARE INTEGER VARIABLES.capTemp;
        DECLARE DOUBLE VARIABLES.latitudineTemp;
        DECLARE DOUBLE VARIABLES.longitudineTemp;
        DECLARE string VARIABLES.regioneTemp;
        DECLARE INTEGER VARIABLES.telefonoTemp;
        DECLARE INTEGER VARIABLES.faxTemp;
        DECLARE string VARIABLES.emailTemp;
        DECLARE string VARIABLES.esitoTemp;
        DECLARE INTEGER VARIABLES.file_fkTemp;
    BEGIN
        SELECT * FROM target.dettaglio tabellaDettaglio INNER JOIN target.file  tabellaFile ON tabellaDettaglio.file_fk = tabellaFile.idfile WHERE tabellaDettaglio.esito = VARIABLES.esitoTemp;
    BEGIN
                    IF(tabellaDettaglio.iddettaglio != IS NULL && tabellaDettaglio.stato != IS NULL && tabellaDettaglio.codhn != IS NULL && tabellaDettaglio.partitaivacf != IS NULL && tabellaDettaglio.ragionesociale != IS NULL && tabellaDettaglio.indirizzo != IS NULL && tabellaDettaglio.comune != IS NULL && tabellaDettaglio.prov != IS NULL && tabellaDettaglio.cap != IS NULL && tabellaDettaglio.latitudine != IS NULL && tabellaDettaglio.longitudine != IS NULL && tabellaDettaglio.regione != IS NULL && tabellaDettaglio.telefono != IS NULL && tabellaDettaglio.fax != IS NULL && tabellaDettaglio.email != IS NULL && tabellaDettaglio.esito != IS NULL && tabellaDettaglio.file_fk != IS NULL)
                IF(VARIABLES.esitoTemp == 'OK')
                    INSERT INTO target.published tabellaPublished (tabellaPublished.idpublished, tabellaPublished.stato,tabellaPublished.codhn,tabellaPublished.partitaivacf,tabellaPublished.ragionesociale,tabellaPublished.indirizzo,tabellaPublished.comune,tabellaPublished.prov,tabellaPublished.cap,tabellaPublished.latitudine,tabellaPublished.longitudine,tabellaPublished.regione,tabellaPublished.telefono,tabellaPublished.fax,tabellaPublished.email) VALUES(VARIABLES.iddettaglioTemp,VARIABLES.statoTemp, VARIABLES.codhnTemp,VARIABLES.partitaivacfTemp,  VARIABLES.ragionesocialeTemp, VARIABLES.indirizzoTemp,  VARIABLES.comuneTemp,  VARIABLES.provTemp, VARIABLES.capTemp,  VARIABLES.latitudineTemp, VARIABLES.longitudineTemp, VARIABLES.regioneTemp, VARIABLES.telefonoTemp,VARIABLES.faxTemp,  VARIABLES.emailTemp );
        END ELSE
         ERROR "tabella published esito KO, IL RECORD ha il VALORE null";
    END
        END
            END
    ------------------------------------------------------------------------------------------------------------------------------
    can you correct the code and complete it?
    since in pl sql I'm not good.
    ----------------------------------------------------------------------------------------
    I am attaching the table diagram
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is your work to do, not ours.

    You need a better description of the work that needs to be done. What does 'all filled up' mean?

    There is much work to do before someone can assist you.