STORE PROCEDURE SQL
-
Good morning,
STORE SQL PROCEDURES
I have three tables:
the first table has the file name:FILE table:
fileid
first name
DATE
OUTCOMEthe second table has the name of the detail:
DETAIL table:
IDDETTAGLIO
FIRST NAME
SURNAME
OUTCOME
FILE_FKthe third table has the public name:
PUBLIC table
IDPUBBLICA
FIRST NAME
SURNAMEI 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 tableIn the public table, having the record called the result
all those who were successful posito ie ok does the insert in the PUBLIC tableI hope I explained myself better
How you do it?
Thank you
-
Hi
Where do you get the data used in inserting the records? Does it come from parameters to the stored procedure?
How do you tell if all Detail records have been input? Are there a set number?
Or are you talking about the inserts causing an error, & trapping the error? -
There are no entry parameters in the procedure.
Since there are three tables, as I wrote in detail, the only thing that has to do this procedure is to verify that the result is OK is to write in the PUBLISCHED table
instead if the result is KO does not have to write in the PUBLISCHED table it means that in the detail table there is some record that is not filled null.see code, if I'm wrong something kindly can you correct me?
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 -
What language is this? It doesn't look like TSQL...
- fra.ang - Thursday, October 18, 2018 6:07 AM
This is clearly some language that allows SQL to be embedded in it. I've never seen it before, and there's a pretty good chance no one else has, either. What, exactly, is it ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
store procedure pl sql
I have the table detail and file -> report 1 to n
I have the detail table and publish -> 1 to 1 report1) Check whether all records are entered in the detail table
2) If in the detail table the records are all filled up, insert into the public table
3) in the public table and detail table, the state name record becomes OK and the file name status record becomes OK
4) If in the detail table some records have a null value or empty field it does not insert into the public table
5) update a new status of the file table is the status becomes KOthe detail table that was successful ok after a check of all the records goes to the published table
who has not passed the control of all the records that only a single null record is enough does not make the insert in the published table
you can help me with the code sql that I'm having difficulty since I do not know well -
This is not an Oracle forum. It's a SQL Server forum. There are a few folks here that ARE familiar with Oracle, but not necessarily at a level high enough to be able to help you. Additionally, solutions written in T-SQL for SQL Server don't always have a direct translation into PL/SQL, and it contains other constructs not available in T-SQL that might do a better job of accomplishing your task in PL/SQL, and that is why you would be far better off in an Oracle forum than here.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply