If I set an alarm and I turn off my iPhone, the alarm doesn't ring.
Correct, since off means off. Some phones include two off settings - completely off and partially off, which is a lower power saving setting. The partially off setting allows for an alarm to sound. The completely off setting does not. The iPhone includes one off setting - completely off.
Is it possible to have on iPhone an app that allows this function ?
Not with a 3rd party app. 3rd party apps do not have access to private iPhone APIs which such an app would require.
You want the iPhone powered off during the night I assume for what purpose - to not receive calls, SMS/MMS, or hear any alert sounds such as when new email has been received? If so, you can accomplish the same by turning on Airplane mode instead of turning your iPhone off. This is a very low power saving mode as well since all iPhone radios are off - no calls can be received, the same for SMS/MMS, and the same for email, but an alarm will sound.