Here I need to create a procedure to insert values into a newly added column. This table has two primary keys. company_no and part_no.This table has 10 records and I need to update the newly added column as shown below.

 new_col = quantity * price;

This quantity and the price fields are columns which included in the same table. I already added the new column into the table and having a trouble in creating a procedure to update the newly added column. Do I need to use cursors and loops here? If yes could you please tell me how to do this.

CodePudding user response：

Basically, you don't need any procedure as everything can be done with a single update statement:

update that_table set
  new_col = quantity * price;

On the other hand, storing such a value is bad practice. You can

• always calculate it if needed (in a select statement), or
• create a view (which contains calculated value), or
• add a virtual column to the table

If it has to be a procedure:

create or replace procedure p_set_col is
begin
  update that_table set
    new_col = quantity * price;
end;
/

and run it as

begin
  p_set_col;
end;
/