I have seen a lot of programmers implementing their complete logic as a single procedure.After getting a small overview of what has to be done,they will simply create a procedure and dump all their selects,inserts,updates and merges with in a single BEGIN and END block with a single EXCEPTION block that too some time with WHEN OTHERS NULL which will literally eat the occurred exception.
I have seen code (written by programmers with 2 years of experience) with 730 odd lines of code with single exception block.If any one select fails in the procedure then we wont be able to troubleshoot where the ERROR has occurred and which statement caused the error.As PL/SQL guru said SQL is as bad as hard coding we should avoid SQL inside our begin end block.Basically we are spending time on writing more code and less time on design which is a bad approach in development.
The ideal way of implementing a logic should be
- Breaking the requirement in to logical blocks and implementing them as packages.
- Providing Interface only to the required procedures in the package and hiding the reset.
- Making all selects as a Functions (Table APIs would be better).
- Repeated activity as procedure inside a package.
- Maximum use of bind variables
- Tuning the query before making it live.
I would like to stress the point of implementing the selects as functions.Because these frequently used functions will be there in the cache memory which will avoid hard parsing there by time to execute the query.If we follow these simple steps we will get better performing and maintainable code which will give our user a better feeling and the support team a better life.
The point to learn from this is
We should spent more time on designing and validating the design and less time on coding the same.