Home » SQL & PL/SQL » SQL & PL/SQL » Needed PL/SQL Procedure for this 2nd Table.(and others merged 5) (Oracle 10g)
Needed PL/SQL Procedure for this 2nd Table.(and others merged 5) [message #435184] Tue, 15 December 2009 00:46 Go to next message
lokeshorafaq
Messages: 9
Registered: December 2009
Location: NJ
Junior Member
I Just need code for 2nd Table Only.

Table 2 Contains: 3 Columns.
(Name, CardNum, Exp)


Table 1. CARS
Table 2. CARIDS
Table 3. REGISTRATIONS
Table 4. AUTHORIZATIONS



Please write PL/SQL procedures that will copy those records for the above tables in the M_OWNER schema that have values in column PTLASTUPDATE that are younger that a specified date (which will be an input parameter to your procedure), and insert or update the corresponding record in M_ADMIN.M_MCARS, M_MCARIDS, M_MREGISTRATIONS and M_MAUTHORIZATIONS. Do not delete from the M_TABLES if the record already exists, but update all the columns instead. Please return the number of records inserted and updated in two output parameters. That is, please write the following procedures:

Update_MCars(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)
Update_MCarIds(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)
Update_MRegistrations(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)
Update_MAuthorizations(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)

Thanks,
Re: Needed PL/SQL Procedure for this 2nd Table. [message #435185 is a reply to message #435184] Tue, 15 December 2009 00:56 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
All other members are not here to write code for you. They are here just to help in solving problems faced.
So you have to do it own your own, and come back if you have any problem, syntactical or logical.

And if you have already tried something, then show us what you have done before.

Moreover, describe your problem clearly providing sufficient data.

regards,
Delna
This PL/SQL Procedure is Correct...or anything wrong with that [message #435306 is a reply to message #435185] Tue, 15 December 2009 08:26 Go to previous messageGo to next message
lokeshorafaq
Messages: 9
Registered: December 2009
Location: NJ
Junior Member
Question Details:
--------------------
Needed PL/SQL Procedure for this 2nd Table.

I Just need PL/SQL Procedure for 2nd Table Only.

Table 2 Contains: 3 Columns.
(Name, CardNum, Exp)


Table 1. CARS
Table 2. CARIDS
Table 3. REGISTRATIONS
Table 4. AUTHORIZATIONS


Please write PL/SQL procedures that will copy those records for the above tables in the M_OWNER schema that have values in column PTLASTUPDATE that are younger that a specified date (which will be an input parameter to your procedure), and insert or update the corresponding record in M_ADMIN.M_MCARS, M_MCARIDS, M_MREGISTRATIONS and M_MAUTHORIZATIONS. Do not delete from the M_TABLES if the record already exists, but update all the columns instead. Please return the number of records inserted and updated in two output parameters. That is, please write the following procedures:

Update_MCars(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)
Update_MCarIds(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)
Update_MRegistrations(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)
Update_MAuthorizations(p_start_datetime IN date, p_num_inserts OUT number, p_num_updates OUT number)

Thanks,

create or replace procedure Update_MCarIds (
p_start_datetime IN date ,
p_num_inserts OUT number ,
p_num_updates OUT number )
AS
-- Michael McGrew
BEGIN

select count(1) into p_num_inserts
from (select cardnum
from M_OWNER.CARIDS a
where a.PTLASTUPDATE < p_start_datetime
minus
select cardnum from M_ADMIN.M_MCARIDS b
where b.PTLASTUPDATE < p_start_datetime);

select count(1) into p_num_updates
from M_OWNER.CARIDS a
join M_ADMIN.M_MCARIDS using (CardNum)
where a.PTLASTUPDATE < p_start_datetime;

merge into M_ADMIN.M_MCARIDS a
USING M_OWNER.CARIDS b
ON (a.CardNum = b.CardNum)
WHEN MATCHED THEN
UPDATE SET a.Name = b.Name,
a.Exp = b.Exp,
a.PTLASTUPDATE = sysdate
WHERE PTLASTUPDATE < p_start_datetime
WHEN NOT MATCHED THEN
INSERT (Name, CardNum, Exp, PTLASTUPDATE)
VALUES (b.Name, b.CardNum, b.Exp, sysdate);

commit;

END Update_MCarIds;
/
show errors ;

/*
var out_inserts number
var out_updates number
execute Update_MCarIds(sysdate, :out_inserts, :out_updates)
print
*/


Re: Hi I am new to Pl/SQL Please write the code for this... (merged 3) [message #435329 is a reply to message #435184] Tue, 15 December 2009 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
We don't do homework assignments.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Needed PL/SQL Procedure for this 2nd Table.(and others merged 5) [message #435330 is a reply to message #435184] Tue, 15 December 2009 11:06 Go to previous message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to repeat the same question over and over: they will be deleted.
In addition, it is counterproductive as you will irritate those that may help you (like me, I hate waste my time to manage undisciplined posters).

Regards
Michel
Previous Topic: Write image BLOB's to files using PL/SQL
Next Topic: Oracle External Table
Goto Forum:
  


Current Time: Sat Oct 01 07:35:44 CDT 2016

Total time taken to generate the page: 0.07991 seconds