-- vendor2.sql

DECLARE
  curr_partno product.vendor_part_number%TYPE;
  new_partno curr_partno%TYPE;
  vend_id product.vendor_id%TYPE;
BEGIN
  SELECT vendor_id, vendor_part_number
    INTO vend_id, curr_partno
    FROM product
   WHERE product_id = 'BROD0000020';

  new_partno :=  vend_id || '-' || curr_partno;

  UPDATE product
     SET vendor_part_number = new_partno
   WHERE product_id = 'BROD0000020';
END;
/
