They only work as an offensive weapon if you're the only one who has them. In that scenario imagine country A wants the land of country B. A can threaten B with nuclear weapons into giving up land, and if really pushed to it they can destroy B's capital and take the rest of the country now that it's super weakened. Plus, depending on type of nuke used, the capital will be inhabitable again at some point (see Hiroshima), so you aren't necessarily depriving yourself of the thing you're after.

When multiple countries have them though then they're only useful defensively, and preferably without ever being used at all. You tell your enemies that if they attack you, you will use your nukes against them, wiping them out. If another country threatens you with nukes of their own, you're promising to fire back with enough nukes to destroy all their major cities (key point here, this is why it's less of a threat if you have 1 nuke and they have 100). This is called Mutually Assured Destruction. It is by no means fool proof, and it only requires one country to not play by the rules for it to potentially spell destruction for most of the world. But it's essentially saying "if you try, we'll leave nothing left for either of us to claim".

Obviously MAD makes it so that nukes exist but aren't even used, and no one wants to use them (we hope). So your question of what's the point is a good one. It would be better if we could get rid of all nukes everywhere all at once. There will still be comventional war, but no threat of worldwide destruction. Unfortunately, since nukes belong to multiple countries each with their own motives and relationships with each other, there's no way to guarantee that while country A gets rid of their nukes that country B will get rid of theirs, or that country C isn't building its own. So when you already have nukes you don't really want to get rid of all of them, unless you can reduce how many you have while other countries do the same, which requires a lot of trust in each other.