A repeat offender would eventually be barred from any participation in traffic other than walking. Basically a form of restraining order, with the same limited effectivity.

Fortunately the law is still acknowledging that drunk cycling is much less bad than drunk driving (and often the only practical alternative besides sobriety), so you'd have to go pretty wild to ever get more than maybe a fine. Unfortunately, this view is increasingly under pressure from the kind of "enemy of the good" perfectionists who always ignore the big picture.

Depends on the state. In Washington(state) riding a bike while drunk is legal. The police can stop you an offer you a ride to your destination. If the drunk cyclist declines the offer, they're free to go unless the officer believes they are a hazard to the public, in which case the bicycle can be impounded if necessary. But the cyclist won't get a DUI for it.

I suspect the main reason is to ensure the driver of a car is not liable if they hit a drunk cyclist.

Unlikely unless you accidently kill /injure some one and then only if the family has a lot of $ for a private prosecution.

The original post stated cycling was illegal in japan.