Some cheap microprocessors do that, usually in devices where power consumption matters they have a small companion microprocessor (a micromicroprocessor so to speak) that will power down the big one if necessary and enter a sleep state itself to severely cut down on power consumption (some devices can go as low as microamps).
Generally, most modern CPUs support turning off when not needed, however, this is generally referred to as power-on-standby (S3, IIRC). The CPU is off, most things are off, RAM is on.
The CPU itself has to continue to run because there is almost no timeperiod larger than a few seconds in which there is truly nothing to do and shutting down CPU cores and clocking the remaining one is efficient enough.
Another good example are the javascript microcontroller boards. Because of the event-loop model of the Js engine, they can simply see there's no code to run and shift into power saving modes without the dev having to do anything special.
Generally, most modern CPUs support turning off when not needed, however, this is generally referred to as power-on-standby (S3, IIRC). The CPU is off, most things are off, RAM is on.
The CPU itself has to continue to run because there is almost no timeperiod larger than a few seconds in which there is truly nothing to do and shutting down CPU cores and clocking the remaining one is efficient enough.