DECLARE
   V_CDCLIENTE VARCHAR2(21);
BEGIN
   FOR C_FUNCIONARIO IN (SELECT FO.CDFORNECED AS CODIGO, FO.NMRAZSOCFORN AS NOME, FO.DTCADAFORN AS DATA
                         FROM FORNECEDOR FO, FUNCIONARIO F
                         WHERE FO.CDFORNECED = 'F'||F.NRCPFFUNC 
                           AND DTDESLFUNC IS NULL) LOOP
      BEGIN
         SELECT CDCLIENTE
         INTO V_CDCLIENTE
         FROM CLIENTE
         WHERE CDCLIENTE = C_FUNCIONARIO.CODIGO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN V_CDCLIENTE := NULL;
      END;

      IF V_CDCLIENTE IS NULL THEN
         INSERT INTO CLIENTE(CDCLIENTE, IDTPIJURCLIE, NRINSJURCLIE, NMRAZSOCCLIE, NMFANTCLIE, DTCADACLIE)
         VALUES (C_FUNCIONARIO.CODIGO, 'F', SUBSTR(C_FUNCIONARIO.CODIGO, 2, 21), C_FUNCIONARIO.NOME,
                 C_FUNCIONARIO.NOME, C_FUNCIONARIO.DATA);
      END IF;
  END LOOP;
  COMMIT;
END;
/
