Does it make sense to use a cursor for complex business logic?

Question

I am dealing with a pretty extensive data set. After a few joins, the foundation table has something like 40 columns. The next 40 columns will all be calculated. I am trying to keep my Java clean of the business logic because it is fairly verbose, at times difficult to follow, and not necessarily belonging to a single model. In short, I think that because of the sheer magnitude of business logic, Java may be difficult for future users to follow.

On the flip side, I have tried doing the business logic in SQL. However, suppose I have columns A, B, C, D, where A and B are from the foundation table.

C = A + B
D = C + B

Now because D is dependent on C, this requires either a nested select or a with clause, and as a result also becomes very messy very quickly.

Recently my coworker introduced me to cursors, not something I have much experience with, but they seem ideal for this use case. I do a select...join... to retrieve a fairly large foundation view, then I use the cursor for that view to loop through each row and calculate all the additional columns. In this way, I can do D = C + B much more easily. There will still be a fair amount of noise here, however it feels as if my business logic will be centralized here. At the end of all calculations, I will simply insert the values as a row into some table, then my Java can query that table.

Some preliminary reading tells me that cursors are not adviseable as they introduce additional overhead. However, if all I am doing is purely arithmetic operations to populate a number of columns, is the user of a cursor valid? I have written some of the code and it is easy to follow and centralized.

I get that I can do the same in Java, but I would rather have the fully calculated table available in my DB, and it feels like the roundtrip through Java is unnecessary because the cleanliness of the code there will not be much better.

EDIT: I'd like to include also that although the column span is fairly large, the expected amount of rows is actually not that great, it is somewhere between 1-2,000. To that effect, an INSERT in a loop seems like it wouldn't be too bad.

Answer 1

I would use a series of with clauses:

WITH some_stuff AS (
  SELECT ...
  FROM ...
  INNER JOIN ... ON 
  ...
),
some_stuff_with_c AS (
  SELECT *,
    a + b AS c
  FROM some_stuff
),
some_stuff_with_d AS (
  SELECT *,
    b + c AS d
) ...

and wrap it all up in a view. I think this is pretty easy to understand, It's just a list of column definitions. Using SELECT * eliminates the repetition. If you have unneeded intermediate values, you can drop them in the final SELECT. This seems far simpler than using a cursor.

Answer 2

Cursors can be the preferred solution, but more often than not, they're [ab]used for logic that would be better implemented using joins, case statements, whiles etc.

You state this logic:

C = A + B
D = C + B

But is D really dependent on C? Could you not write all the derived columns as a series of long hand statements e.g.

C = A + B
D = A + B + B

If you can, it is questionable as to whether a cursor is really the solution here.