PL/SQL block to populate a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL/SQL block to populate a table

Hybrid View

  1. #1
    Join Date
    Jul 2009
    Posts
    2

    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).



    Listed below are the tables & definitions:

    table: CUSTOMER

    columns:

    customer_id NUMBER,

    customer_name VARCHAR2(100),

    recently_purchased VARCHAR2(1) -- 'Y' or 'N'



    table: CUST_PRODUCTS

    columns:

    product_id NUMBER,

    customer_id NUMBER,

    date_purchased DATE



    table: RESULTS

    columns: customer_id NUMBER, product_count NUMBER

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    well seeing as this is your homework - what have you come up with so far

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    here's what i'm doing

    --create customer table

    create table customer (
    customer_id number(6),
    customer_name varchar2(100),
    recently_purchased varchar2(1));

    -- describe to see if it is created as required
    desc customer

    --populate customer table with random values

    Begin
    For i in 1..10
    Loop
    Insert into customer(customer_id,customer_name)
    values(i, dbms_random.string('U',5));
    If mod(i, 10) = 0 then
    Commit;
    End if;
    End loop;
    End;

    --create cust_products table

    create table cust_products
    (product_id number (6),
    customer_id number(6),
    date_purchased date);

    --describe to see if table is created as required

    desc cust_products

    --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;

    End loop;
    End;

    i'm worried this doesn't look professional..any suggestions please..the logic is working though.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Good job.
    I would probably rework the update statement - I see no reason to execute update statement ten times; a single execution of update statement should solve it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width