Does the code require that a cord/plug connection, such as extension cord used for holiday lights, be weatherproof if installed in a wet location?

Not specifically.

The code does contain broad requirements that materials and methods be suitable for their environment, and use. So, should a manufacturer submit a product for outdoor use to UL, the UL Lable will say so.

Connectors and such are not required to be "waterproof." Part of the reason for this is that the application -even if outdoors- may arguably place the product in a "damp" location, and not a "wet" one. And even "rain proof" materials are not, by any stretch of the imagination, suitable for submersion.

The "hardware" is but part of the equation...the install is also part. The outdoor circuit is to be GFI protected. Many light sets also contain their own fuses.

<rant on>
If we were being honest we would say "holiday lighting" is the unwritten 90.2(B)(6).
We just seem to hold our nose, sing Jingle Bells and hope these folks take down the lights before the house burns down.
345 days a year we go around insisting on bubble covers, wet location wiring methods, GFCIs and examining equipment for it's suitability for use in wet locations. We try to insure work is done by qualified installers.
Then sometime in December we pinch out nose tight and watch all sorts of total crap being sold and installed by people who have no electrical knowledge and no idea what the hazards they create could do.