I don't think it is common practice for DBA's to write stored procedures during the development of a program. Personnaly, I always had to write my own stored procedures, or sql code.
I think that since they know their database product much more than programmers (in theory, and at least it's true for me), they are best used whenever something that needs more analysis needs to be done on the database:
- defining and optimizing table, tablespaces, indexes, etc.
- optimizing stored procedures
- database migration plan
- etc. (DBA's, feel free to complete this list)
"Optimization" is the key I think.
A few years ago, I remember I asked my Oracle DBA about optimizing a procedure that was performing very poorly (like more than 10 seconds for only a few results). He came back to me with a request I did not understand at all containing keywords I've never seen before at that moment in time (
INTERVAL and how to use partitioning are extremely powerful when put at good use) and that was giving the same good results in less than 25ms...
As for programmers, they have to conceptualize the database, provide the scripts and work together with the DBA so that the resulting database schema is most efficient for the required tasks. Programmers and DBA's should work together to identify/resolve bottlenecks occurring between the application and database servers.