Marilyn Manson has two on his second Concept Album, Mechanical Animals, which contains two characters (which would take several paragraphs to explain). Omēga sings "The Dope Show", which is, in-universe, non-sarcastic but is sarcastic in real life (with a large Misaimed Fandom) and Alpha sings "I Don't Like The Drugs (But The Drugs Like Me)" which is much more anti-drug.
"New Kind of Kick" by The Cramps ("Energine! Barcol! Draino hot shot! Whack attack! Helium! Nitrous oxide! Formaldehyde! Some new kind of kick!")
"Streams Of Whiskey" by The Pogues. ("When the world is too dark and I need the light inside of me / I'll walk into a bar and drink fifteen pints of beer!")
"Got to Get You Into My Life" by The Beatles is a totally sincere (and very subtle) song about Paul McCartney's love of marijuana.
At first listen, the song "Stella" by the pop-punk band All Time Low appears to be a love song to a girl, but then you realize that Stella is actually a beer, Stella Artois...
I remember how you tasted
I've had you so many times, let's face it
Feels like I'm falling in love alone
Stella, won't you take me home?
Skold vs. KMFDM's track, "Alkohol". The title indicates the subject matter.
The devil goes by many names
Lets get basted, shit-faced, wasted
Angelspit's "Elixir" is an interesting case. The general theme is that 'there's a drug for everything nowadays' but it also covers drug addiction - including the chorus, suggested as being from the 'point of view' of the addiction itself. There are a few interesting analyses on Songmeanings.net.
The infamous "Beer" song set to the 1812 Overture/Overture to Carmen..
Oh, what is that malted liquor,
what gets you drunker quicker,
what comes in bottles or in cans?
Buck Tick loves this. Speed (which was originally going to be named Acid with different lyrics, but since Acid and its lyrics were verboten, Sakurai and Imai rewrote it and renamed it to be about an even more dangerous drug) and Heroin, and those are just the too obvious ones. There's plenty more less obvious such as Candy and My Fuckin Valentine and Machine and Jonathan Jet Coaster.
Some considered it in poor taste to include the song on their first album after Bon Scott died of alcohol poisoning, but their response was basically, "Not at all; Bon would have loved this song." (it could also be an homage to him, just like "Hells Bells" is a Grief Song)
The Polish heroicomical poem Monachomachia (literally "war of monks". It describes, in language better suited for describing epic ancient battles, a theological dispute degenerating into a giant brawl) contains a piece praising the "love of the glass" after a cup of wine is used to end the brawl by starting a binge. (as you can guess, the author, himself a priest, didn't think much of the morality in the monasteries of his time)
Franz Ferdinand has "Ulysses", a song about a night full of drugs and partying.
Korpiklaani has a bunch of these, mostly without a hint of irony. "Vodka" is probably the most over-the-top.
Drinking is good for you!
Not anymore lonesome.
Drinking is good for you!
Oh you will feel awesome!
Don't forget Wooden Pints, Bring Us Pints of Beer, Happy Little Boozer, Tequila, Let's Drink, and Beer Beer. Korpiklaani *loves* doing songs like this.
The Irish folk song, "Whiskey, you're the Devil".
Another example is "Seven Drunken Nights": a man comes home drunk each night and finds things that don't belong to him, and his wife calls him a drunken fool — can't he see it's not a horse/coat, it's a sow/blanket her mother sent her? The last two verses are rather explicit, so the Dubliners version cuts off at five nights.
Although Mike Harding's version, set in darkest Lancashire, reinstates the two missing verses.
"Water is alright in Tea" is another example which lists off the virtue of porter compared to water, wine and tea.
Whiskey, Whiskey by Tri Yann is an example of a Breton ballad to the bottle.
Carmina Burana (Orff) features a whole section, In Taverna, dedicated to drinking songs, including a song which lists all those to be found in the pub in question, plus a song from the point of the roasted swan on the spit. The Abbot of Cucany leads the drinkers.
About 99% percent of Tankard's discography is about the greatness of beer. Other 1% is about how being sober sucks.
There's an old song called "Clink, Clink, Another Drink," popularized by comedy musician Spike Jones. Notable for showing the other side of the bottle, personified by Mel Blanc in full Daffy Duck mode.
Cab Calloway had "Reefer Man" and "Smokin' Reefer" that were directly this, though a lot of his other songs are also riddled with drug and alcohol references.
We Are Scientists sang about how great it is to stay out as late as possible and drink in After Hours. The sentiment of the song seems to be about the social side to drinking, and wanting to continue drinking with friends.
Jerrod Niemann's "One More Drinking Song" Lampshades this trope.
"Tubthumping" by Chumbawumba.
"Red Red Wine" and "Cracklin Rosie" by Neil Diamond. And although "Sweet Caroline" isn't about alcohol per se, thanks to sports fans (especially Red Sox Nation), it's probably sung sloshed more often than not.
I've got the worst hangover ever I'm crawlin' to the bathroom again It hurts so bad that I'm never gonna drink again I'll probably never drink again I may not ever drink again At least not 'til next weekend I'm never gonna drink again
Standing Sex and the Jealousy version of Stab Me In The Back by X Japan. Stab Me In The Back is odd though in that the single version (and most of the live versions) are about gaysex - it is only the Jealousy version rewritten to be an Ode to Intoxication, likely due to label pressure - and Yoshiki finding his own way to flip off the people imposing "no gay" by making the song about injecting drugs. IV could also be interpreted in this way.
Swedish tradition has snapsvisor that are sung before you take a sip during holiday celebrations. They are usually about how good it is to drink, though there are many, many variations and parodies.
Classic Swedish trobadour Carl Michael Bellman wrote lots of songs about partying with pretty girls and getting drunk.
Nickelback has Burn It to the Ground and Bottoms Up.
Shelley West sings "Jose Cuervo," a song about how she loves to go out at night, drink tequila and get crazy. When she wakes up with a hangover and a stranger in her bed, she admits to drinking more than she should. However, the last verses imply that she'll be doing this again.
In The Long Ships Orm's oar-mate Khalid wrote one of these, which in the sharia-ruled Almohad Caliphate got him convicted of blasphemy and sentenced to the galleys. He attempts to write an Ode To Sobriety, but the author dryly states that he found praising water and lemon juice difficult.
Referenced in the novel The Long Ships. While serving as a galley slave, Orm's oarmate is an Arabic poet named Khalid, who wrote one. It was considered a critique of Islam, ad he was found guilty of blasphemy and sent to the galleys. He tries to write an Ode To Sobriety while rowing, but he finds it very difficult to find good things to say about water and lemon juice.
"Alcohol" by gypsy punk icons Gogol Bordello. "And you know that I'll pick up every time you call / Just to thank you one more time / Alcohol!"
"Green Leaves", Lazy Smurf's parody of "Greensleeves" and "What Child Is This" that is sung about the joys of smoking smurfnip in the Empath: The Luckiest Smurf story "A Haunted Christmas".
"Drunk In The Spirit" by T-Bone is about a different kind of intoxication based on the spurious claim from Scripture (usually from Acts chapter 2 and Ephesians 5:18) preached by the likes of Benny Hinn, Rodney Howard Browne, and Kenneth E. Hagin.