-- 1. Insert data using variable declared as ROWTYPE
--    The first selected row is updated (ROWNUM = 1)

DECLARE
 sales_row   ods_sales_detail%ROWTYPE;
 idx         SIMPLE_INTEGER :=1;

BEGIN
 FOR idx IN 1..3 LOOP
   sales_row.loc_id := 100;
   sales_row.cust_id := 1100;
   sales_row.invoice_num := 'A10000'||idx;
   sales_row.invoice_date := TO_DATE(20120801,'yyyymmdd'); 
   sales_row.sales_amt := ROUND(1234.50*idx,2);
   sales_row.tax_amt := ROUND(1234.50*idx*0.05,2);
   sales_row.freight_amt := ROUND(12.50*idx,2);
   sales_row.discount_amt := ROUND(1234.50*idx*0.05,2);
   sales_row.created_date := SYSDATE;

   INSERT INTO ods_sales_detail VALUES sales_row;
 END LOOP;
 COMMIT;

 UPDATE ods_sales_detail
   SET  sales_amt = 5678.90,
        tax_amt = ROUND(5678.90*0.05,2),
        discount_amt = ROUND(5678.90*0.05,2)
 WHERE  invoice_num = sales_row.invoice_num
 AND    ROWNUM = 1;
 COMMIT;

END;
/

=> Oracle Data Insert Using Rowtype

-- 2. Update data using ROW = sales_row
--    The third invoice is updated as shown below

DECLARE
 sales_row   ods_sales_detail%ROWTYPE;

BEGIN
 sales_row.loc_id := 101;
 sales_row.cust_id := 1100;
 sales_row.invoice_num := 'A1000021';
 sales_row.invoice_date := TO_DATE(20120801,'yyyymmdd');
 sales_row.sales_amt := 1122.90;
 sales_row.tax_amt := ROUND(1122.90*0.05,2);
 sales_row.freight_amt := 75.75;
 sales_row.discount_amt := ROUND(1122.90*0.04,2);
 sales_row.created_date := SYSDATE;

 UPDATE  ods_sales_detail
   SET ROW = sales_row
 WHERE  invoice_num = 'A100003';
 COMMIT;

END;
/

=> Oracle Data Update Using Rowtype