zoekAdres.sql

create or replace 
PROCEDURE zoekAdres (
  postcodeIn IN VARCHAR2,
  huisnummer IN NUMBER,
  toevoeging IN VARCHAR2,
  -- Out
  id OUT NUMBER,
  straat OUT VARCHAR2,
  houseNumber OUT NUMBER,
  addition OUT VARCHAR2,
  zipcode OUT VARCHAR2,
  woonplaats OUT VARCHAR2,
  land OUT VARCHAR2
)
IS
  evenOdd VARCHAR2(5);
  parity VARCHAR2(5);
BEGIN
  IF MOD(huisnummer, 2) = 0 THEN
    evenOdd := 'even';
  ELSIF MOD(huisnummer, 2) = 1 THEN
    evenOdd := 'odd';
  END IF;
  
  SELECT numbertype
    INTO parity
    FROM postcode
    WHERE ROWNUM = 1
    AND postcode = postcodeIn
    AND minNumber <= huisnummer
    AND maxNumber >= huisnummer;
    
  --DBMS_OUTPUT.PUT_LINE(parity);  
  --DBMS_OUTPUT.PUT_LINE(evenOdd);
    
  IF parity != 'mixed' THEN
    IF evenOdd != parity THEN
      RAISE NO_DATA_FOUND;
    END IF;
  END IF;
 
  SELECT id, street, postcode, city
    INTO id, straat, zipcode, woonplaats
    FROM postcode p
    WHERE p.postcode = postcodeIn
    AND minNumber <= huisnummer
    AND maxNumber >= huisnummer
    AND ROWNUM = 1;

  houseNumber := huisnummer;
  addition := toevoeging;
  land := 'NL'; -- No country?
  
  --DBMS_OUTPUT.PUT_LINE('id: ' || id);
  --DBMS_OUTPUT.PUT_LINE('straat: ' || straat);
  --DBMS_OUTPUT.PUT_LINE('houseNumber: ' || houseNumber);
  --DBMS_OUTPUT.PUT_LINE('addition: ' || addition);
  --DBMS_OUTPUT.PUT_LINE('zipcode: ' || zipcode);
  --DBMS_OUTPUT.PUT_LINE('woonplaats: ' || woonplaats);
  --DBMS_OUTPUT.PUT_LINE('land: ' || land);
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    DBMS_OUTPUT.PUT_LINE('Er is geen data gevonden.');
END;