I think it's only natural for people to feel a bit of adrenaline and apprehension when looking over the edge of a cliff or tall building. I'm sure people can become desensitized to it though. I've been a climber for a good few years now, when I started, I used to get visited by Elvis (shaky legs half way up the climb). But since then, I've found myself hanging upside down and seen an RAF Hawk fly underneath me. It didn't scare me, but it was certainly a rush!
As for pilots, well, I'm part way through ATPL training and can safely say that I've never felt any fear of the height I have been at... except when I had only just started and wasn't sure if the plane was going to fall out of the sky when stalling!
It's not the height that scares me, it's the consequence of having that height, and then not... if you see what I mean!