Yes, Hogwarts teachers would occasionally get drunk in public settings, where students could see them. Usually Hagrid.
Hagrid (and possibly McGonnagal) gets drunk at the Christmas feast in PS
Harry watched Hagrid getting redder and redder in the face as he called for more wine, finally kissing Professor McGonagall on the cheek, who, to Harry’s amazement, giggled and blushed, her top hat lopsided
It's also implied that Hagrid spikes his drinks during the Christmas feast in CoS
Dumbledore led them in a few of his favorite carols, Hagrid booming more and more loudly with every goblet of eggnog he consumed.
Hagrid drinks four entire pints at once in the Three Broomsticks during a Hogsmeade weekend
‘Four pints of mulled mead –’
‘Ta, Rosmerta,’ said Hagrid.
(It's unclear how drunk that would get Hagrid, given his size and heritage. But he gets very emotional during their conversation about Harry's parents. And Slughorn actually manages to outdrink Hagrid in HBP.)
Sybil Trelawney literally wanders the halls of Hogwarts drunk on several occasions.
To many instances to cite here, probably.
Since you asked about public intoxication, I've left off the many more instances of Hogwarts professors drinking privately with their students (Slughorn and Dumbledore both drink with Harry in HBP). In the UK, generally speaking, it's perfect legal for older teenagers to drink under adult supervision.