| STORE SQL PROCEDURES [message #672586] |
Fri, 19 October 2018 02:23  |
 |
glprobot
Messages: 2
Registered: October 2018
|
Junior Member |
|
|
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
I have attached the diagram of the relational tables
............................................................
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
..................................................................................................................................... ................
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
DECLARE integer VARIABLES.iddettaglioTemp; --> I have made this variable declaration so that the record is stored when I do the insert into
etc.....
-------------------------------------------------------------------------------------------------------------------
can you help me to correct the code and complete what I want to accomplish?
|
|
|
|
| Re: STORE SQL PROCEDURES [message #672589 is a reply to message #672586] |
Fri, 19 October 2018 03:30  |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need to spend some times reading the online documentation on PL/SQL (a good book on PL/SQL programming wouldn't hurt either).
None of that is valid PL/SQL. We could correct most of it but given how little you obviously know about the language you really need to study it properly first.
|
|
|
|
|
|
|
|
|
|
| Re: STORE SQL PROCEDURES [message #672597 is a reply to message #672596] |
Fri, 19 October 2018 04:59 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
There's having problems with code
And there's having no idea how to write valid PL/SQL what so ever.
You have no idea how to write valid PL/SQL.
You need to study the basics of the language before you try writing any code. And when I say basics - you don't even know what an IF statement should look like.
Go read the documentation on what PL/SQL looks like (Michel has supplied the appropriate link above).
Go read a good book on PL/SQL.
Go on an actual training course.
Do at least one of those before trying to write a single line of PL/SQL
To make your code work we would have to rewrite every single line. We are here to help people who are stuck with particular issues. You are stuck with absolutely everything.
|
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
