CREATE OR REPLACE PROCEDURE flood_fill_blpus IS

TYPE typ_pb# IS TABLE OF pd_blpus.pb#%TYPE
INDEX BY BINARY_INTEGER;

TYPE typ_uprn IS TABLE OF pd_blpus.uprn%TYPE
INDEX BY BINARY_INTEGER;


CURSOR c_gb_pk(p_uprn NUMBER) IS
  SELECT gb.gb_pk
  FROM gh_blpus gb
  WHERE gb.uprn = p_uprn;

t_typ_pb# 			typ_pb#;
t_typ_uprn			typ_uprn;

BEGIN
SELECT pb.PB#,
       pb.UPRN
 BULK COLLECT INTO 
    t_typ_pb#, 
    t_typ_uprn 
FROM bs7666.pd_blpus pb;

FORALL rec IN t_typ_pb#.FIRST..t_typ_pb#.LAST 

  -- Do we have this record already in gh_blpus
  -- Use uprn to find out
  -- If so then update that record

  OPEN c_gb_pk(t_typ_uprn(rec)); 
    FETCH c_gb_pk INTO v_gb_pk;
  CLOSE c_gb_pk;

  IF v_gb_pk IS NOT NULL THEN
    UPDATE ....
  ELSE
    INSERT ...
  END IF;

END;