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.
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.
"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.
Asylum Street Spankers have their jaunty, not at all sarcastic ode to Beer, rattling off and dismissing pretty much every other alternative.
Dr. Dre's debut solo album, "The Chronic", was named after Marijuana and references smoking weed several times.
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.
Brazil has quite a few songs about cachaça, such as "Marvada Pinga" ("evil cachaça"), "Pinga Ni Mim" ("drops on me" - Double Entendre meaning both raindrops and beverage drops) and Pato Fu's "Pinga".
"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.
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 Mikael 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,