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