I went off to the Red Sea five years ago for a diving holiday (photos here), and I did a PADI course to learn the skills. This involved watching some videos and reading through a textbook (with questions at the end of each chapter) before I went out there, and then two days of training in the pool/sea. Well, actually it took me three days because I found it difficult to breathe without my mask on, but most people only need two. Anyway, after that I got my Open Water certification, which qualifies me to dive in the sea. There's another organisation called BSAC who also do dive training, and their training is a lot more thorough; this means that it takes longer/costs more, but it also prepares you to handle extra situations.
What I've observed is that there's a certain amount of snobbery (if not outright hostility) from some BSAC divers to PADI divers, with comments like "PADI stands for 'Pay And Die Instantly'". By contrast, I've never heard any PADI divers criticising BSAC (although it may have happened). I think that the training you need does depend on what type of diving you want to do. I enjoyed diving in the Red Sea, where the water was warm and clear, so there was typically about 25m of visibility. By contrast, I have no interest in diving in the North Sea, where it's cold, and you can only see about 1m. I remember a BSAC diver telling me that it's quite an intimate experience to dive in low visibility, when it feels like you and your buddy are the only two people in the world; I can understand that. Similarly, there's the argument that when you have high visibility, that means that you see a coral reef, swim over to it, and then see it again, i.e. there's nothing new, whereas when you have low visibility that means that you get more new experiences. Personally, I'd prefer not to have things (e.g. moray eels) looming out of the darkness right in front of me! For that matter, I remember going to the Brighton Sea Life Centre a few years ago, where one feature is that you can walk through a glass tunnel at the bottom of the tank, i.e. you can see the fish and turtles swimming around you. I found it very relaxing, and stayed there for about half an hour watching them; in fact, I would happily have stayed longer. So, it's not a problem for me if I'm out at sea, and swim over to take a closer look at something.
Anyway, I'd agree that if you're doing more advanced diving (e.g. wreck dives) then there is more that can go wrong, so it makes sense to get extra training. However, if you're only doing basic diving, then I don't think that the extra training is really necessary. There's no harm in doing it, but it's not essential. (I also dispute the idea that it prepares you for absolutely everything, taking the scene in "Tomorrow Never Dies" as a counter-example - Bond gets trapped in a wreck when he knocks over a rack and lots of torpedoes block his exit.)
Some people would argue that it makes sense to do the extra training anyway, even if you don't expect to use it, on the principle of "safety first". I think that there's a good analogy between diving and driving. For instance, there are two types of transmissions in cars (i.e. two ways to change gear): manual and automatic. If you pass your test in a car with a manual transmission then you can drive both types of car afterwards, whereas if you pass your test in an automatic then you can only drive automatics (until/unless you subsequently retake the test in a manual car). As I understand it, the vast majority of British cars are manual, whereas the vast majority of US cars are automatic. This means that everyone I know in England who's taken their driving test has done so in a car with a manual tranmission (I certainly did). Question: if you were living in America, and you had no plans to drive a car with a gearstick, would you bother learning to use it? Or would you do your lessons/test in an automatic, and then learn the manual method later if need be? Particularly bearing in mind that you could save time/money by only doing the automatic method, as well as being more likely to pass the test.
When I've asked (English) people this question, the general response has been "Yes, do it the manual way", but I think this may be skewed by the fact that they've already done it that way (in a parallel with the fact that BSAC divers have done the extra training). So, extending this further, how about motorbike lessons? It's an extra option on the same licence, rather than being a completely separate qualification, and there's certainly a lot of overlap between cars and bikes (e.g. recognising road signs). I also think that each time you learn to use a new form of transport that makes you better at the types you already know. For instance, one difference between a bicycle and a motorcycle is that a pushbike doesn't (usually) have mirrors on the handlebars. When I ride my motorbike I rely on the mirrors quite heavily, to maintain an awareness of the traffic around me. When I then switched back to cycling for a while, I definitely noticed the absence of these mirrors, so I think that I look over my shoulder a lot more often than the average cyclist. Similarly, people who cycle will hopefully be more diligent about checking for cyclists when they're driving a car. However, the only people I know who've taken motorbike lessons are the people who specifically intend to ride a motorbike.
Or if this seems like too much of a stretch, how about the advanced driving licence? I've been thinking about doing that for my motorbike, and it's now a requirement for people who want to drive SJA vehicles. However, I'd guess that there are lots of BSAC divers who don't have it. And that's ok - people can make their own assessment about how much training they want to do.
So, in conclusion, if you want to do the extra training then great, go for it, and I hope you enjoy it. And I agree that it's necessary in some cases. I just don't think it's essential for everyone, and it's reasonable to start out with the basics and maybe work up from there.
As a side-note, one of my colleagues at work mentioned that she went diving from the same boat as me (the Sodfa) a couple of years ago - small world syndrome strikes again! I haven't been diving since, mainly due to the cost of jetting across the world, but I think I may go off again next year, particularly if I can get a cheapo last minute deal.