Thursday, September 29, 2005

Never do in SQL what you can do in PL/SQL

Back when I first started out as a trainee Forms developer, one of my colleagues had the bright idea of implementing some "CONNECT BY PRIOR" logic step by step, in Forms. The trigger would add a new blank record to the block, then retrieve the next row into it with SELECT INTO, then loop around until there were no more rows to process or Hell froze over. Perhaps unsurprisingly, it was a close thing.

I mentioned to Scott Swank that we were always on the lookout for material, and he sent me the following code that he'd come across (after editing it for brevity and anonymity), and somehow it reminded me of that old Forms trigger. This pattern is repeated ten times throughout the code, checking for various child attributes by (how else?) looping through all the records one by one until the desired attribute is found not to be true (and if something is not untrue then it must be true, right?)

PROCEDURE has_an_attribute
    ( x IN VARCHAR2, resultout OUT VARCHAR2 )
IS
    l_parent_id   VARCHAR2 (30);

    CURSOR c_child
    IS
        SELECT an_attribute
        FROM   child_table
        WHERE  parent_id = l_parent_id;
BEGIN
    l_parent_id := some_function(x);
    resultout := 'COMPLETE:Y';

    FOR v_child IN c_child
    LOOP
        IF v_child.an_attribute = 'N'
        THEN
            resultout := 'COMPLETE:N';
            EXIT;
        END IF;
    END LOOP;
END;

3 comments:

yas said...

This is the behaviour i have seen maybe thousands of time. Some developers like plsql and hate sql, or they learn plsql without learning sql.

Thai Rices said...

"But this is must be more efficient than SQL, because Oracle does a full table scan!"

Scott Swank said...

The code now looks more like:

[code]
SELECT COUNT (*)
INTO l_n_cnt
FROM child_table
WHERE parent_id = l_parent_id
AND an_attribute = 'N'
AND ROWNUM <= 1;

IF l_n_cnt > 0
THEN
resultout := 'COMPLETE:N';
ELSE
resultout := 'COMPLETE:Y';
END IF;
[/code]