The Chocolate Watch Band and The Electric Prunes are up there. I used to think Love had a stupid name, but once you listen to the lyrics it has a great ironic/melancholic resonance that I think must have been deliberate as Arthur Lee never did anything by accident.
I know it's a joke, but I never liked the name "Condo Fucks." And there's Blue Öyster Cult, which might veer over into camp, but their early psych stuff is pretty fantastic. "that dog." [sic] is a pretty twee name for a pretty great band. Also not crazy about "The Apples in Stereo," though I love their music.
My Bloody Valentine, definitely. I avoided them in the early 90s because I thought they were an industrial/goth band. And I think we all agree on Jay Reatard, who was an incredible songwriter and musician, but hoo boy.
As for great bands with great names, I vote for The Detroit Cobras and The Ettes.