oraclenerd said (excerpted):
I'm going to have to disagree with you on the internal procedure (in the declaration section) paradigm. What about testing?
...
Now you have 2000 lines in your declaration section and 400 or so in the body. (I've seen it...really, I've seen it). Then you want to change one of those internal procedures...you can't test it without testing the entire thing.
This is to me one of the conundrums with any programming language; PL/SQL just has its own unique aspects.
Any set of procedures can be a testing challenge, since you can think of calling a procedure saying "do this", whereas you can think of calling a function as asking "tell me what would happen if you did this". Sure, it's easier to test a function, because you just have an in-memory return value that you can compare against some expected value, and don't have to worry about changing data by accident or doing rollbacks.
In the situations where I would use the technique of lifting code directly into internal procedures, the code is typically very short and easily verifiable: select count(*) into... followed by an IF test; a sequence of simple assignments that would be cumbersome to turn into one function per assignment; blocks of code that have already been verified in toto by virtue of tests run against the outermost procedure or function; that sort of thing.
After the code is modularized this way:
- The outermost procedure can often be improved and/or optimized simply by reordering the inner procedure calls. For example, to do all the "should I quit early" tests before doing any substantial work. Or to put "set up complicated string value" right next to "use that string value".
- Debugging steps such as removing blocks of code can be performed by commenting out single lines. (Much appreciated if those blocks of code themselves contain multi-line /* */ comment blocks.)
- If a logic problem does turn up in the procedure, it's easier to figure out the part to look at if it has its own descriptive name. And a tool like ctags makes it handy to jump directly to that inner procedure.
The problem with a 2000-line declaration section, to me, is no worse than if you go the package route and must fix syntax errors or track down logic errors within a big package body. I use my favorite SQL*Plus hack to push that code off into separate files once it gets too big.
Anyway, my point is not to advocate using this kind of structure for every procedure or even every big procedure, rather to suggest that if you do restructure a procedure this way, using exceptions instead of return can make the work a little simpler, which is not a bad starting point if you are trying to get your head around how the flow control works for exceptions.
Brian Tkatch said:
Personally, i have done this (with a PACKAGE though) by RETURNing a value from the PROCEDURE, and then checking it in the main code:
IF Some_check() = 1 THEN RETURN; END IF;
It's not as pretty, but i found it to workout nicely.
Sure, I'm a big fan of function-oriented design; I think it's been sadly overlooked in the rush to make everything object-oriented.
Restructuring the early tests into functions does take a little work. And it requires some design decisions -- use Boolean values, 0/1, or named constants? how to ensure all cases are handled, maybe use the case statement? OK, I made my function return Boolean values and tested the values inside a case statement; but I can't test the function values via SQL queries, and maybe case will throw a runtime exception if some unexpected value like null comes back. The sample code in Brian's comment doesn't suffer from those problems, but this is the kind of thing I could imagine a junior programmer coding too elaborately.
That's not to say that the resulting code is any better or worse, just that it's now subject to potential new bugs and maintenance (have to document return values etc.) by introducing functions. That's the idea behind the use of exceptions in my previous post, to make the restructured code 100% the same as the original, not 99 44/100ths %.
1 comment:
I sometimes write PACKAGEs with a PROCEDURE called RUN, to be similar to main().
It calls everything in order, and includes branching logic, but doesn't do anything itself. Everything is in another PROCEDURE OR FUNCTION and each returns or SETs a tested variable.
Something like:
PROCEDURE RUN
IS
Set_Options;
Reset_Variables;
If Sanity_Check() = 1 THEN RETURN; END IF;
Figure_Out_What_To Do;
IF Error = 1 THEN
Report_Error;
RETURN;
END IF;
END;
Each once needs its own style, but it keeps the logic and flow separate, defines each function separately, and as you pointed out, becomes much easier to test.
Post a Comment