Erm, ok ... I'm going to make a different comment to the general 'yes he is horrendous' one.
First, though - yes, his behaviour is really hard to deal with, it is exhausting, it is sure to have a very depressing effect on you and will eventually (sounds like it already has) start having a deep, long term effect on your happiness and self-esteem, and on your kids. It is not in any way good.
BUT
Nobody here has mentioned what seems screamingly obvious to me - the guy has some kind of depressive tendencies. He is deeply, dependently attached to you. He is very unhappy. He has terrible mood swings and generally he is very bad at dealing appropriately with people. You're a woman and his emotional and sexual home, so he relates to you on the level of sex, but often in a clumsy, over the top way, followed up by guilt about having done it wrong and the strongest declarations of love he can give; people in the street or shops he is rude to, as if he doesn't know how to be just normally polite - he kind of is over the top rude because he'd be self-conscious just being nice, because ... I think he is unsure of HOW to be normally nice.
I think he is one of those (apparently quite common) cases of a guy who in some ways is on the autistic spectrum in terms of social interaction, and is himself deeply tormented. He is kind of play-acting what he thinks he should say, etc, but that's too much to keep up so then mostly you just get this very moody, very uncomfortable interaction.
I'm not in any way at all trying to lessen the effect of this behaviour - OMG no - in fact I recognise it because I've experienced something almost identical, in everything you say. Which is why I'm actually v interested by your post, because it kind of starts to confirm my theory that this behaviour is so pathological, that it is actually a syndrome, or symptom of something that could perhaps be treated. It is depression, it is hormonal fluctuation, it is a social disorder, it is a kind of autism - I don't know what it is, but I know it's utterly unbearable and miserable to live with, as a kind, normal person, and I know as well that somehow the person who does it can't help himself.
It needs sorting out. I don't know what you do, but just saying OMG he's so awful and he should change his behaviour doesn't work ... I do it all the time and I know it's pointless. For years I tried to get him to the doc's. I really think that it could be treated. But Lord knows how you get someone like this to admit there's something wrong. That in itself is very tricky.
I suppose my thoughts are: yes you need to get yourself and the children out of this. But at the same time, think about trying to get him some help. I don't think he can help himself. : (