Assuming the system is sufficiently programmable, it might be better to use a recorded voice to give a specific warning and command, something like "TORNADO WARNING TAKE IMMEDIATE COVER" and have it repeat several times.

The problem I envision with different sounds for different warnings is the learning curve/confusion factor among people, e.g. "that's a horn---does that mean evacuate or shelter in place? Does anyone remember?"

From the fire drills I've been in, its hard enough to get people to know that it (the bell or whatever) means that they are supposed to leave. I always saw a lot of people standing around looking confused. . .