I don't understand what's hard about mass-overwriting your previous stored procedures with new ones.
You're right about non-SProc code; just deploy all of it. Do the same thing with SProc code!
What's tough about keeping all your code in files that start with "CREATE OR REPLACE FUNCTION <funcname>", and just firing them all at the DB (within a transaction, if you like)?
I don't actively advocate putting all the code in sprocs, but I can see advantages. I also don't advocate using PHP, and yet people demonstrably build some great websites with it.
Your approach is a bit naive. You will accumulate a lot of crud if you don't drop any function you deleted or renamed. This crud could even set people up for making mistakes, like using a function that shouldn't exist and does stuff that harms the integrity of the data.
You're right about non-SProc code; just deploy all of it. Do the same thing with SProc code!
What's tough about keeping all your code in files that start with "CREATE OR REPLACE FUNCTION <funcname>", and just firing them all at the DB (within a transaction, if you like)?
I don't actively advocate putting all the code in sprocs, but I can see advantages. I also don't advocate using PHP, and yet people demonstrably build some great websites with it.