Home » SQL & PL/SQL » SQL & PL/SQL » STORE SQL PROCEDURES (PL/sql)
STORE SQL PROCEDURES [message #672586] Fri, 19 October 2018 02:23 Go to next message
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?
  • Attachment: diagramma.jpg
    (Size: 82.33KB, Downloaded 431 times)
Re: STORE SQL PROCEDURES [message #672589 is a reply to message #672586] Fri, 19 October 2018 03:30 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #672593 is a reply to message #672589] Fri, 19 October 2018 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also you need to learn top format your code.
Do you really think you can debug code with lines that have more than 1000 characters?

Re: STORE SQL PROCEDURES [message #672595 is a reply to message #672593] Fri, 19 October 2018 04:22 Go to previous messageGo to next message
glprobot
Messages: 2
Registered: October 2018
Junior Member
No

how can I solve this problem if nobody can not help me?
Re: STORE SQL PROCEDURES [message #672596 is a reply to message #672595] Fri, 19 October 2018 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

cookiemonster wrote on Fri, 19 October 2018 10:30
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.
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm


Re: STORE SQL PROCEDURES [message #672597 is a reply to message #672596] Fri, 19 October 2018 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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.

Re: STORE SQL PROCEDURES [message #672605 is a reply to message #672595] Fri, 19 October 2018 08:06 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
glprobot wrote on Fri, 19 October 2018 02:22
No

how can I solve this problem if nobody can not help me?

http://lmgtfy.com/?q=BEGINNING+PL%2FSQL+ONLINE+TUTORIAL
Re: STORE SQL PROCEDURES [message #672608 is a reply to message #672597] Fri, 19 October 2018 08:34 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe this is some other language? Some SQL variant but not Oracle?
Re: STORE SQL PROCEDURES [message #672610 is a reply to message #672608] Fri, 19 October 2018 08:46 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd hope it is, but we're not here to do line by line conversions.
Previous Topic: Run dbms_scheduler.create_job with arguments
Next Topic: Written Assignment (+ example code)
Goto Forum:
  


Current Time: Mon Sep 21 02:37:02 CDT 2020