If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

PL/SQL block to populate a table

Hello Everyone,

Need help writing pl/sql to insert into table.

1. Write a PL/SQL block which will populate the RESULTS table as described below. Consider performance implications (specifically where would you commit the queries) as we are dealing with millions of records.

The PL/SQL will:

- insert into the RESULTS table each customer id, and the number of unique products purchased by that customer.

- update the recently_purchased column of the customer table to 'Y' (yes if they have purchased a product in the last 12 months) or 'N' (if they have not purchased a product in the last 12 months).

--populate cust_products with custome_id 1 to 10 and dates
Begin
For i in 1..10
Loop
Insert into cust_products
values(10-i,i,add_months('04-nov-09',i));
If mod(i, 10) = 0 then
Commit;
End if;
End loop;
End;

--update customer table as required

Begin
For i in 1..10
Loop
update customer
set recently_purchased = 'N' where (select date_purchased from cust_products where customer_id = i) > '02-Feb-2010' and customer_id =i;

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.