Home » SQL & PL/SQL » SQL & PL/SQL » Help the newbie to do my first procedure
Help the newbie to do my first procedure [message #663681] Wed, 14 June 2017 06:21 Go to next message
TonyDu94
Messages: 3
Registered: June 2017
Junior Member
Hi guys,

I haven't done a PL/SQL since UNI and that is ages ago. Today I need this procedure which updates a table1 according to records on table 2.

Another hurdle is that I am on the client database, So I don't really have a test environment. It is taking ages to have it but I just begun to write some lines.

Now I am asking you help to me to see if I am on a good truck :


Table 1 : carrierT ( carrier, spec2, CarrierID)
Table 2 : CarrierGRP ( Carrier, Spec1, CarrierGRPID)

The need is :
1/When the carrier name is found in table 1 and 2 ( the value is in both table) , and sp1 is not null update the sp2 in Carrier ( I agree that this should have been a foreign key )
2/ When the carrier name is found in table 1 and 2 , and sp1 is null update the spe2 and set to null in Carrier ( I agree that this should have been a foreign key )
3/ When the carrier name is not found in table 1 and 2 , update the spe2 and set to null in Carrier ( I agree that this should have been a foreign key )

here is what I just did :

CREATE OR REPLACE
PROCEDURE PROC (CARRIER VARCHAR(10))

AS
CURSOR carrierPol IS SELECT carrier FROM carrierT;
BEGIN
FOR Carrier IN carrierPol
lOOP

If Carrier = (select GR.CarrierT AS GRPCARRIER
from CarrierGRP GR
where carrier = GR.Carrier and GR.Spec41 <> null)
Then
Update carrierT set carrier.SPEC41 = CarrierGRP.SPEC41 WHERE carrier.CARRIER = CARRIER
end if;



If Carrier = (select GR.Carrier from CarrierGRP GR where carrier = GR.Carrier and GR.Spec41 = null)
Then
Update carrierT set carrierT.SPEC41 = NULL WHERE carrierT.cARRIER = CARRIER
end if;



If Carrier not IN ( select GR.Carrier from CarrierGRP GR where carrier = GR.Carrier )
Then
Update carrierT set carrierT.carrier = NULL WHERE carrierT.cARRIER = CARRIER
end if;

END LOOP ;

END PROC;



Thanks a lot
Re: Help the newbie to do my first procedure [message #663683 is a reply to message #663681] Wed, 14 June 2017 07:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Help the newbie to do my first procedure [message #663684 is a reply to message #663681] Wed, 14 June 2017 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have and Post insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result..

Quote:
When the carrier name is found in table 1 and 2 ( the value is in both table)
There is no column names "carrier name"


Quote:
and sp1 is not null update the sp2 in Carrier
There is no colum names sp1 or sp2.
What is "Carrier" we have to update in?

Re: Help the newbie to do my first procedure [message #663687 is a reply to message #663681] Wed, 14 June 2017 08:19 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
it seems to me that the whole requirement can be by two UPDATE statements. No need of PL/SQL at all.

To set CARRIERT.SPEC41 based on the column with the same name in CARRIERGRP.
update carriert
set spec41 = (select gr.spec41 from carriergrp gr where carriert.carrier = gr.carrier)
where carrier in (select gr.carrier from carriergrp gr);
Looks suspicious though - what is the sense of CARRIERT.SPEC41 column? It is just a data integrity breaker as it does not follow 3NF - its value is redudant to CARRIERGRP.SPEC41

To clear (NULL) values of CARRIERT.CARRIER which are not present in the column with the same name in CARRIERGRP.
update carriert
set carrier = null
where carrier not in (select gr.carrier from carriergrp gr where gr.carrier is not null);
Looks reasonable if followed by making primary key on CARRIERGRP.CARRIER and foreign key on CARRIERT.CARRIER You can treat those NULL values accordingly afterwards.

Anyway, as you did not post a test case, I cannot check these statements.
Re: Help the newbie to do my first procedure [message #663693 is a reply to message #663684] Wed, 14 June 2017 09:37 Go to previous messageGo to next message
TonyDu94
Messages: 3
Registered: June 2017
Junior Member
Sorry, I am confusing.

the structure of my table is :
Table 1 : carrierT ( carrier, spec2, CarrierID)
Table 2 : CarrierGRP ( Carrier, Spec1, CarrierGRPID)

When I say Carrier name, i mean carrier. And Sp1 and Sp2 are SPEC1 and SPEC 2

So sorry for that.
Re: Help the newbie to do my first procedure [message #663694 is a reply to message #663687] Wed, 14 June 2017 09:52 Go to previous messageGo to next message
TonyDu94
Messages: 3
Registered: June 2017
Junior Member
Thanks for that.

Well you are absolutely right.But the problem now, is

1/ Is this an iterative process? I have to go through all rows of the tables ( That is why I thought that the loop was usefull )

2/ what about is I don't want to override the existing carrier information that is already set.
If I need to update only few cases. I think this will override it right?


What do you mean by : test case? Because I can provide you any other information that is needed.

many thanks
Re: Help the newbie to do my first procedure [message #663695 is a reply to message #663694] Wed, 14 June 2017 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/Test_case
Re: Help the newbie to do my first procedure [message #663696 is a reply to message #663694] Wed, 14 June 2017 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What do you mean by : test case? Because I can provide you any other information that is needed.
Click on the links I gave you.

Re: Help the newbie to do my first procedure [message #663704 is a reply to message #663694] Thu, 15 June 2017 00:26 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
TonyDu94 wrote on Wed, 14 June 2017 16:52
1/ Is this an iterative process? I have to go through all rows of the tables ( That is why I thought that the loop was usefull )
UPDATE statement is updating all rows which satisfy the conditions in the WHERE clause. There is no need for extra LOOP inside which you update only one row.
Think in sets: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8744181900346696062
Additionally, SQL is not a procedural language, but rather a declarative one. You do not state HOW the result shall be achieved, but WHAT shall the result look like.
SQL introduction: https://en.wikipedia.org/wiki/SQL (just for a starter, as Oracle has its own dialect described e.g. here: http://docs.oracle.com/en/database/)
TonyDu94 wrote on Wed, 14 June 2017 16:52
2/ what about is I don't want to override the existing carrier information that is already set.
If I need to update only few cases. I think this will override it right?
Again, that is exactly what is the WHERE clause for. Just add there another condition to exclude the rows you do not want to UPDATE, e.g.
where <current condition> and <column with the "carrier information"> is not null
TonyDu94 wrote on Wed, 14 June 2017 16:52
What do you mean by : test case? Because I can provide you any other information that is needed.
I do not have access to your tables, so I cannot check my SQL statements for validity and correctness.
Is this the final table state (where did column SPEC41 disappear)? What about column data types (VARCHAR2, NUMBER)? What about primary key/foreign key/check constraints? What about representative sample data and required result for them?
Just follow the link BlackSwan gave you.
Previous Topic: [Error] Execution (64: 64): ORA-32034: unsupported use of WITH clause for materilized view
Next Topic: Locked object in gv$locked_object without transaction in gv$transaction
Goto Forum:
  


Current Time: Fri Mar 29 07:07:36 CDT 2024