Have a routine handle special cases or split the code in n routines, one for each case?

Question

I wonder what the best practice is for handling optional calls, special cases etc. within procedural code (PL/SQL without OOP). We have a program with lots of configuration and things that will or will not happen because of database state etc. In terms of splitting my code, most of the times I begin in splitting it by domain concepts, so in the following example someGoodNameAction within main whould be one of these.

Sometimes those routines grow and there will be many special cases latter on. I wonder if those should be handled within such a procedure or if that procedure again should be splitted into n procedures per case. This would keep those procedures pretty easy in clean but would lead in many, many, many procedures with names like someGoodNameActionSpecialCaseN. So one domain concept could easily explode into 8, 16 or 32 procedures without those conditions and there would be hundreds of procedures and of course a lot of duplication, although just duplication in terms of function/procedure calls not in terms of knowledge.

Those procedures aren't that hard to maintain but they get pretty ugly and have a lot of s.. inside them that won't be called for many cases etc. On the other hand a lot of ultraspecific procedures for special cases don't seem quite right either.

Simplified pseudo code:

function someGoodNameAction1(row) {
    if (someGuard1(row)) {
        log('sh.. hit the fan!');
        return;
    }
    
    if (someGuard2(row)) {
        log('sh.. hit the fan.. again!');
        return;
    }
    
    someSubRoutine(row);
    
    maybeModifiedRow = row;
    if (specialCase2(row)) {
        maybeModifiedRow = enrichRow(row);
    }
    
    if (specialCase3(maybeModifiedRow)) {
        someOtherSubRoutine(maybeModifiedRow);
    }
    
    if (specialCase3(maybeModifiedRow)) {
        someData = getSomeData(maybeModifiedRow);
        if (someData.field > maybeModifiedRow.field) {
            log('sh....');
            return;
        } else if (someData.field2 == maybeModifiedRow.field2) {
            log('oh nooo..');
            return;
        }
    }
    
    someOtherOtherSubroutine(maybeModifiedRow);
    
    someOtherData = getSomeOtherData(maybeModifiedRow);
    if (someCondition(someOtherData)) {
        someFinalSubRoutine1(maybeModifiedRow);
    } else {
        someFinalSubRoutine2(maybeModifiedRow);
    }
}

function main() {
    
    rows = getSomeRecords();
    
    for (row in rows) {
        if (isGoodName1()) {
            someGoodNameAction1(row);
        } else if (isGoodName2()) {
            someGoodNameAction2(row);
        }
    }
    
}

Edit: Some clarification. Yes I know about packages in PL/SQL and those functions and procedures would be inside of one. And there would be a lot of calls to other packages etc.

To have a more realistic example, the thing I was thinking of was the code of a material flow controller, or some procedure inside that. The main function would loop over some records inside a database which are provided by an automated warehouse and contain conveyors informations. Which pallet is on the conveyor, the operating mode, maybe something like weight, height, length of the pallet etc.

So the main loop would seperate those rows into different types of conveyors. I was thinkin about a scale/profile control, which would be someGoodNameAction1. Lets call it shapeControl or checkPoint.

Inside that there might be conditional code. If the pallet is going into the highbay rack the height must be checked and maybe the pallet won't get in. Same for maximum weight.

But the weight still have to be checked to make sure the pallet data is ok and the right item is on there or the excepted weight is at least close to the one from the scale.

And if the pallet already have a transport that could be kept if not it will get one into the highbay rack or maybe it is a pallet for some other destination etc. Or it is a specific pallet type or some special case for a special item.

I could keep those conditions and optional calls inside the "checkPoint" routine like in the previous example which makes it kinda messy. Or that checkPoint routine would have a lot of specific subroutines (exaggerated names just to be clear), e.g.:

checkPoint_PalletType1_ForHighbayRack_NonSpecialItem
checkPoint_PalletType1_ForHighbayRack_SpecialItem
checkPoint_PalletType1_ForOtherDestination_SpecialItem
checkPoint_PalletType1_ForOtherDestination_NonSpecialItem
checkPoint_PalletType1_TransportAlreadyExists_SpecialItem
checkPoint_PalletType1_TransportAlreadyExists_NonSpecialItem
checkPoint_PalletType2_ForHighbayRack_NonSpecialItem
checkPoint_PalletType2_ForHighbayRack_SpecialItem
checkPoint_PalletType2_ForOtherDestination_SpecialItem
checkPoint_PalletType2_ForOtherDestination_NonSpecialItem
checkPoint_PalletType2_TransportAlreadyExists_SpecialItem
checkPoint_PalletType2_TransportAlreadyExists_NonSpecialItem
checkPoint_PalletType3_ForHighbayRack_NonSpecialItem
checkPoint_PalletType3_ForHighbayRack_SpecialItem
checkPoint_PalletType3_ForOtherDestination_SpecialItem
checkPoint_PalletType3_ForOtherDestination_NonSpecialItem
checkPoint_PalletType3_TransportAlreadyExists_SpecialItem
checkPoint_PalletType3_TransportAlreadyExists_NonSpecialItem

Those would be highly specific for a single case and wouldn't have any conditions. Some might even never happen. But at the end that are a lot of routines and there are of course some more conveyor types inside the main loop too. So those top level routines would reach the hundreds and of course there would be some integration code to decide which of those functions to call, so there might be a isCheckPoint_PalletType3_TransportAlreadyExists_NonSpecialItem function too, to check if that procedure should be called. And any new case would lead to an explosion of new functions and procedures.

Oh and there is of course some code which actually does something useful..

Answer 1

Can't tell for sure, but - reading what you said - my first thought was: "gobnepla is talking about packages". As you never mentioned that term, maybe you don't even know they exist. Have a look at PL/SQL Packages documentation.

A few citations:

What is a package?

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents.

Reasons to use packages:

Modularity: Packages let you encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions in named PL/SQL modules. You can make each package easy to understand, and make the interfaces between packages simple, clear, and well defined. This practice aids application development.

---

You said:

So one domain concept could easily explode into 8, 16 or 32 procedures ...

So, one domain concept = one package that contains as many procedures as needed.

---

Another approach might be trying to "generalize" code. That might require (a lot of?) dynamic SQL which allows you to compose the final statement to be executed. In my opinion, it reduces number of code lines, but becomes debugging and maintenance nightmare in long term. I'd rather have 10 packages with 30 procedures/functions each - which are simple and easy to understand and maintain - than 2 procedures with a lot of dynamic SQL that does the work, but once sh.. hits the fan, you wish you went different path.

---

I apologize if I completely misunderstood the question.

Answer 2

You may want to consider abstracting each class of similar-but-different actions and table-drive which action procedure gets called given a set of database state values. Something like this:

StateA, StateB, StateC, Acquire, Move, Calibrate, Repair, Decommission
______________________________________________________________________
A       A       A       [proc]   [proc]  [proc]   [proc]  [proc]
A       A       B       [otherproc], [proc], [otherproc], etc..

Then you can have a main driver proc that uses this to call the correct procedure for the class of action needed, using EXECUTE IMMEDIATE and a standardized parameter set.

EXECUTE IMMEDIATE 'BEGIN '||rec_rules.acquire||'(in_key => :key, out_result => :result); END;' USING IN var_key, OUT var_result;

EXECUTE IMMEDIATE 'BEGIN '||rec_rules.move||'(in_key => :key, out_result => :result); END;' USING IN var_key, OUT var_result;

This would allow you to not have to hard-code a thousand IF THEN ELSE statements all over the place. Any decisions based on database state not being used to drive the proc choice would then be handled inside the procs with normal branching logic. And if the differences between minute cases comes down to a single line of code or even a single variable within a line of code, that might argue for table-driving cases even at this small granular level.

And it would probably make sense to place all the procs for a given class/domain of action in their own package, which facilitates at least some degree of sharing, particularly local package state for things common to all the procs of that domain.

In terms of code reuse, in addition to using packages you would certainly want to break out any boilerplate you find yourself copy-pasting into their own common procedures or functions so all the procs can use them. Package-typed PL/SQL collections or even unnested dictionary-typed SQL objects used as SQL-callable collections (type table of type object, without going full OOP) can be passed back and forth by reference using IN OUT parameters. A cursor can be defined in one place and its reference returned to calling procs to fetch from using REF CURSORs. Package instantiation code can auto-execute certain common procedures to set up package state no matter which member proc gets called, etc...

I think whatever you do, it's going to be a bit of both, a trade-off between isolating cases to their own procs, and dealing with minute sub-cases within procs. Exactly where to draw that line I don't think there's a right answer.