Help the newbie to do my first procedure [message #663681] |
Wed, 14 June 2017 06:21 |
|
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 #663687 is a reply to message #663681] |
Wed, 14 June 2017 08:19 |
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 #663704 is a reply to message #663694] |
Thu, 15 June 2017 00:26 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
TonyDu94 wrote on Wed, 14 June 2017 16:521/ 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:522/ 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:52What 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.
|
|
|