What's the problem with source controlled database logic?
Have your statements (including those that create stored procedures on setup, migrations, etc) on text files, and just load those into your Git or whatever.
The problem is ensuring deployment matches up. It's very easy to end up with subtle differences between a newly deployed database instance and a database instance that was deployed with an old version and then updated. For code you would think very hard before deploying each version as a patch to the previous version - it's easy and effective to just deploy the code afresh each time, with a complete artifact built from a specific VCS tag. It's much harder to do that with databases; the tooling just isn't there and it's hard to ensure that you wipe out previous logic but retain previous data, because data and logic are commingled. You could possibly build a system for this kind of thing, but the standardized tooling just isn't there.
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.
Back when I was writing Python and using Django, I found the migrations system provided by django-south was really good for exactly this.
A migration was a way to roll a database forwards or backwards; there were tools to create simple ones, and one was able to write whatever Python & SQL one wished in order to handle more complex cases. One might even archive off a column somewhere when deleting it, and load it up back when restoring it, if one wished.
Since the migrations were all just source code, they were perfectly well-suited to source control.
It was a really powerful system; I'm surprised that it hasn't seen wider acceptance.
You have an export file in your repo, containing all your stored procedures and part of the deployement process is to export the procedures to the db, updating as needed.
Have your statements (including those that create stored procedures on setup, migrations, etc) on text files, and just load those into your Git or whatever.