importWinkel.sql

create or replace 
PROCEDURE importWinkel
IS
  file UTL_FILE.FILE_TYPE;

  counter INT := 0;
  
  name VARCHAR2(128);
  street VARCHAR2(128);
  
  houseNumberWithAddition VARCHAR2(128);
  houseNumber INT;
  houseNumberAddition VARCHAR2(128);
  
  city VARCHAR2(128);
  country VARCHAR2(128);
  zipcode VARCHAR2(128);
  zipcodeAscii VARCHAR2(128);
  phone VARCHAR2(128);
  
  zipcodeHouseNumber INT;
  evenOdd VARCHAR2(128);
  
  zipcodeException EXCEPTION;
  zipcodeHouseNumberException EXCEPTION;
  storeException EXCEPTION;
  
  postcodeResult postcode%ROWTYPE;
BEGIN
  file := UTL_FILE.FOPEN('C_DIR', 'Winkels Dominos NL.txt', 'r');

  WHILE counter < 7
  LOOP
    counter := counter + 1;
    
    IF counter = 1 THEN
      UTL_FILE.GET_LINE(file, name);
      
      -- Als er commentaartekst in het bestand staat of een lege regel zal deze overgeslagen worden
      IF name is null OR SUBSTR(name, 0, 2) = '--' THEN
        counter := counter - 1;
        
        CONTINUE;
      END IF;
      
      DBMS_OUTPUT.PUT_LINE('name: ' || name);
    ELSIF counter = 2 THEN
      UTL_FILE.GET_LINE(file, street);
      
      IF street is null OR SUBSTR(street, 0, 2) = '--' THEN
        counter := counter - 1;
        
        CONTINUE;
      END IF;
      
      street := RTRIM(street);
      
      DBMS_OUTPUT.PUT_LINE('street: ' || street);
    ELSIF counter = 3 THEN
      UTL_FILE.GET_LINE(file, houseNumberWithAddition);
      
      IF houseNumberWithAddition is null OR SUBSTR(houseNumberWithAddition, 0, 2) = '--' THEN
        counter := counter - 1;
        
        CONTINUE;
      END IF;
      
      houseNumberAddition := REGEXP_SUBSTR(houseNumberWithAddition, '[^   ]+', 1, 2);
      houseNumber := REGEXP_SUBSTR(REGEXP_SUBSTR(houseNumberWithAddition, '[^-OR/]+', 1, 1), '[^   ]+', 1, 1);
      
      DBMS_OUTPUT.PUT_LINE('house number: ' || houseNumber || ' addition: ' || houseNumberAddition);
    ELSIF counter = 4 THEN
      UTL_FILE.GET_LINE(file, city);
      
      IF city is null OR SUBSTR(city, 0, 2) = '--' THEN
        counter := counter - 1;
        
        CONTINUE;
      END IF;
      
      -- De spatie aan het einde van de regel weghalen
      city := RTRIM(city);
      
      -- Des (s-) voor de naam
      IF SUBSTR(city, 0, 2) = 's-' THEN
        city := '''s' || INITCAP(SUBSTR(city, 2, LENGTH(city) - 1));
      ELSE
        city := INITCAP(city);
      END IF;
      
      DBMS_OUTPUT.PUT_LINE('city: ' || city);
    ELSIF counter = 5 THEN
      UTL_FILE.GET_LINE(file, country);
      
      IF country is null OR SUBSTR(country, 0, 2) = '--' THEN
        counter := counter - 1;
        
        CONTINUE;
      END IF;
      
      DBMS_OUTPUT.PUT_LINE('country: ' || country);
    ELSIF counter = 6 THEN
      UTL_FILE.GET_LINE(file, zipcode);
      
      IF zipcode is null OR SUBSTR(zipcode, 0, 2) = '--' THEN
        counter := counter - 1;
        
        CONTINUE;
      END IF;
      
      zipcode := REPLACE(zipcode, ' ', '');
      
      DBMS_OUTPUT.PUT_LINE('zipcode: ' || zipcode);
    ELSIF counter = 7 THEN
      UTL_FILE.GET_LINE(file, phone);
      
      IF phone is null OR SUBSTR(phone, 0, 2) = '--' THEN
        counter := counter - 1;
        
        CONTINUE;
      END IF;
      
      -- Remove spaces
      phone := REPLACE(phone, ' ', '');
      -- Remove any - in phone
      phone := REPLACE(phone, '-', '');
      
      DBMS_OUTPUT.PUT_LINE('phone: ' || phone);
    END IF;
    
    -- Alle regels uitgelezen?
    IF (counter >= 7) THEN
      -- De counter weer op 0 zetten
      counter := 0;
    
      -- Controleren of het huis nummer even of oneven is
      IF MOD(houseNumber, 2) = 0 THEN
        evenOdd := 'even';
      ELSIF MOD(houseNumber, 2) = 1 THEN
        evenOdd := 'odd';
      END IF;
    
      -- De nodige gegevens uit de postcode tabel halen
      DECLARE
        CURSOR postcodeCursor IS 
          SELECT *
            FROM postcode
            WHERE postcode = zipcode
            AND street = street
            AND minNumber <= houseNumber
            AND maxNumber >= houseNumber;
      BEGIN
        FOR res IN postcodeCursor LOOP
          -- Als het type 'mixed' is, dan klopt de combinatie wel
          IF res.numberType = 'mixed' THEN
            EXIT;
            IF res.numberType = evenOdd THEN
              EXIT;
            END IF;
          END IF;
          
          IF res.city = city THEN
            EXIT;
          END IF;
          
          EXIT WHEN postcodeCursor%NOTFOUND;
        END LOOP;
      END;
      
      -- Alle letters individueel omzetten naar ASCII
      zipcodeAscii := SUBSTR(zipcode, 1, 4) || convertToNumbers(SUBSTR(zipcode, 4, 2));
      
      -- De query uitvoeren
      INSERT INTO winkel
      (naam, postcode, postcode_id, huisnr, toevoeging, telNr)
      VALUES
      (name, zipcode, zipcodeAscii, houseNumber, houseNumberAddition, phone);
      
      COMMIT;
    END IF;
  END LOOP;
  
  -- Bestand sluiten
  UTL_FILE.FCLOSE(file);
EXCEPTION
  WHEN zipcodeHouseNumberException THEN
    DBMS_OUTPUT.PUT_LINE('De combinatie van postcode en huisnummer bestaat niet.');
  WHEN storeException THEN
    DBMS_OUTPUT.PUT_LINE('Deze gegevens komen niet overeen met de winkel.');
END;
/

CREATE OR REPLACE FUNCTION convertToNumbers(
  stringToConvert VARCHAR2
)
RETURN NUMBER
AS
  convertedNumbers NUMBER;
  countLoops NUMBER := 0;
  charToConvert VARCHAR2(50);
BEGIN
  WHILE countLoops < LENGTH(stringToConvert)
  LOOP
    charToConvert := SUBSTR(UPPER(stringToConvert), countLoops + 1, 1);
    
    convertedNumbers := convertedNumbers || ASCII(charToConvert);
    
    countLoops := countLoops + 1;
  END LOOP;
  
  RETURN convertedNumbers;
END;
/