I am particularly interested in this part since it seems to be a common theme/argument against BL in DB
You get one level of namespacing, and function names have to be less than 32 characters, so abbreviated names are common. This can be unwieldy, but alleviated by good comments and docs.
You want to code each function to do one thing: have a well-defined input, and a well-defined output. Living inside the database gives us transaction support for free -- atomic operations that either succeed or rollback. The application running inside the DB defines the external interface with its functions (the same way a web service or a library would), and keeps the raw tables as an implementation detail.
If we moved more functions out of the app into a higher layer (ie. Java/Python/etc), we'd actually have more functions overall, as you'd either have to have a bridge method that calls the equivalent function inside the DB, or rely on an ORM and invent some instrumentation to deal with concurrency requirements [1]
At the end of the day, a modern RDBMS allows you to implement applications that present a well-defined interface to the outside world, with the internal implementation remaining a private 'black box'.
[1] https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/htm...
Most of the time, the code I work with operates on a handful of records per transaction; bulk operations require entirely different tradeoffs and I don't have much to offer here.
All of the problems I've seen stem from the understandable urge to treat the database as a giant global variable. Oracle provides a myriad of conveniences to facilitate this from within pl/sql so developers do exactly that:
- In the middle of some logic and realised you needed another piece of data to work with? Just splat another inline SELECT in the code and move on.
- Want to update a table but you can't find an existing SP which writes what you need? Just splat an inline UPDATE in the code and move on.
Over time, these SQL statements littered throughout the stored procs become a complete mess. Another issue is that client code is often given unfettered access to the base tables. If those tables don't force data integrity either by constraints or triggers, then god help you if you want to update any column anywhere in your database: you simply cannot know which clients may break.
Oracle pl/sql offers both FUNCTIONs and PROCEDURES and these form the core of a number of idioms I've come to recommend:
- if your pl/sql performs any side effects, it's a PROCEDURE. End of.
- FUNCTIONs never contain side effects (or use OUT parameters).
- PROCEDURE status is "toxic": if a FUNCTION calls a PROCEDURE, it must also become a PROCEDURE.
- client code never accesses base tables. Wherever possible, try to provide access through a package API: instead of SELECTs, call functions which return REF CURSORs, instead of UPDATEs and INSERTS, call PROCEDUREs. These mechanisms are all supported by both jdbc and .Net though you'll have to do a bit more work.
The key thing is to put your BL in functions, not procedures. They're much easier to test, especially if your variables can be passed in as parameters rather than being lifted from tables via the seductive inline SELECT option.
PROCEDURES are always tricky to test. Try to keep them as simple as possible and try to minimise conditional logic leaking into them.
Data integrity is difficult to get right. Oracle provides table constraints and triggers but beware of putting BL into either of them (the dividing line is often blurry and the risk is that your future applications may be overly constrained whilst your existing ones may come to rely on leaky assumptions if you relax the integrity guarantees). I've not seen constraints or triggers used to great effect anywhere, to be honest. Most shops avoid them, it seems.
The easiest way to stay on top of data integrity is to force all updates through higher level SPs. Don't let clients touch base tables and, instead, force them to call facade packages. Write one facade package per client - Oracle will mark the package as invalid if you modify anything in the execution path of said package and this provides a valuable tool for impact analysis.
CI is nearly impossible in a live database unless you're running a version of Oracle with Edition Based Redefinition. The only alternative I can think of is pulling your logic out into middleware libraries.