How to FETCH perticular records from table1 and INSERT into table2?

Question

Image_1 is "table_1:-Product"

Image 2 is "table_2:-User_detail"

Now, i want to select data from table_1 (p_name,p_amount) with use of particular "p_id" with "prompt" and insert into table_2 (purchased_item,amount_total,due_date) at particular select "u_id"

• p_name-> purchased_item
• p_amount->amount_total
• due_date is sysdate

Expected output:- If i am select "p_id=101" from table_1 and "u_id=3676" from table_2 then i get this update in table_2 ---

"row-1"

u_id:-3676
u_name:-Rv
email:-rv@gmail.com
purchased_item:-LED
amount_total:-5000
due_date:-sysdate

please help me out,how this is possible? with procedure or with trigger?

thanx in advanced.

I am new pl/sql learner.

Answer 1

how this is possible? with procedure or with trigger?

Neither, I guess. UPDATE will do.

update user_detail d set
  (d.purchased_item, d.amount_Total, d.due_Date) =
  (select p.p_name, p.p_amount, sysdate
   from product p
   where p.p_id = :par_p_id
  )
where d.u_id = :par_u_id;

---

If it has to be a procedure, then

create or replace procedure p_iud (par_p_id in product.p_id%type,
                                   par_u_id in user_detail.u_id%type
                                  )
as
begin
  update user_detail d set
    (d.purchased_item, d.amount_Total, d.due_Date) =
    (select p.p_name, p.p_amount, sysdate
     from product p
     where p.p_id = par_p_id
    )
  where d.u_id = par_u_id;
end;
/

Call it as

begin
   p_iud(101, 3676);
end;
/

---

If you'd want to pass several products at the same time, one option is to pass them as a string with comma-separated values. The procedure would then be

create or replace procedure p_iud (par_p_id in varchar2,
                                   par_u_id in user_detail.u_id%type
                                  )
as
begin
  update user_detail d set
    (d.purchased_item, d.amount_total, d.due_Date) =
    (select listagg(p.p_name, ',') within group (order by p.p_name), 
            sum(p.p_amount), 
            sysdate
     from product p
     where p.p_id in (select to_number(regexp_substr(par_p_id, '[^,]+', 1, level))
                      from dual
                      connect by level <= regexp_count(par_p_id, ',') + 1
                     )
    )
  where d.u_id = par_u_id;
end;
/

and you'd call it as

begin
   p_iud('101,301', 3676);
end;
/

Possible problem: as LISTAGG concatenates all those product names, it'll fail if such a "long" string is too long for PURCHASED_ITEM column (you could SUBSTR it, if that's an option)