Andreas Weber has, amongst other things, some nice macro photos (super-closeup).
Normal cameras can only go so close, like maybe fill the frame with a book or so. Funny enough, many pocket cameras can do macro quite well.
Otherwise most system cameras have a dedicated macro lens. Not the cheapest lens in the range usually, of course, so one can use macro rings to put between the lens and camera*, or lenses to put in front (just a high-quality magnifying glass). But with lowered quality (less sharp), since most lenses are not optimized for very close range.
Macro lenses tend to be not very "fast" (not so big maximum aperture for the light), F:2.8 is typical. But they do tend to be excellent optically, both at close range and at far. Sometimes exceptionally excellent. Pentax' 35mm F:2.8 is a good example. It's unusual for a macro lens to be this wide (50mm-equivalent on a Digital Pentax SLR), but it has been called a "paragon" by lens connoisseur Mike Johnston. And Andreas has revealed that one of his lenses is the Leica 45mm F:2.8 for the Micro Four Thirds system (90mm-equivalent), this is an outstanding lens, which is reflected in the price, about $800.
*This is what the camera does usually to focus: move the lens away from the sensor.