Do people ever upload papers exclusively to arXiv, or do authors simply use arXiv to assist in review while they find a journal to publish in? If people sometimes publish exclusively on arXiv, what's the point? What do they get for their work? A paper on their CV? Is it at all prestigious?
I suppose some senior people may publish exclusively on arXiv but they are a minority and I can't think of any examples right now. Publishing in mathematics takes a long time. Journal backlogs of multiple years are common enough that if you wait for your paper to appear in print it is already old. This is less of a problem in the other subjects that arXiv covers but still...
So what does publishing on arXiv get the author? Primarily it gives the paper a time stamp and makes it publicly available. Putting up a paper is fast, it only takes a few minutes and it gives a reference number so that you can cite it in a talk or in a paper. Normally you would have to wait until you get page numbers from a journal. And if you are giving a talk on your paper when you have just finished the work that might still be years in the future.
One interesting feature of publishing on arXiv is that you can write small notes and put those up. You can write the "user's guide" version of a paper. You can post a supplement to a paper that wouldn't be publishable on its own.
Ultimately what it all boils down to is that people post documents to arXiv that they want other people to see and hopefully read.
As for prestige, no. There really isn't any as far as I know.
@BSteinhurst mentions the advantages of having a time stamp and making it publicly available; another advantage, one that I have come to value a lot, is that all those people who follow the 'new articles on (...)' announcements will see your paper the next day. It gives your paper instant distribution.
One more way in which arXiv can be used is for distributing papers which are not publishable in their own right, but are still important for some reason. Presumably, if the people consider the paper to be interesting, then this earns the author some reputation (although probably not in any quantifiable form).
I am aware of a case of a somewhat accomplished mathematician, call him S., who came up with a brilliant idea for a new proof of an important theorem. However, carrying out the proof was a big undertaking, and in the end he did not manage produce a paper that was rigorous enough for publication. A version of the paper which was rejected from the journals was posted on arXiv, and sparked a lot of interest, also among the leading experts in the field. As far as I know S. no longer works in this area, but I am aware of others who do. If the proof strategy proves successful, this will surely be very good for S.'s reputation. More importantly, the S.'s ideas were passed on to others, which was beneficial for mathematics.
For a well documented example of a similar situation, look at Mochizuki's proof of the ABC Conjecture. To put it briefly, he built a huge theory that very few people really understand (possibly only he understands it fully) which supposedly is able to prove one of the most important conjectures in number theory. However, given how much effort would be required to verify Mochizuki's work, I am in serious doubt if he would be able to publish it in the sort of journal this result deserves. I don't think he published the papers on arXiv, but if he had, I think it would make sense for the reasons mentioned above.
Along with the otherwise excellent answers here, there is something to add:
It depends on what field you're talking about
While in some fields there might be some benefit to publishing primarily on arXiv, especially if you're important enough to have the reasonable belief that even your preprints will be read, there are other fields where the only response you're reasonably going to get is "...where?"
This may seem like something of a self-evident point, but I've found many researchers, especially ones working in interdisciplinary research, forget that their field's conventions are not the conventions of all fields. For example, in my own field, the answer is simply "No."