MSCCP0003

This statement has usages of cursor for loop

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is used to indicate that the statement has usages of cursor for loop. A cursor for loop is a control structure that allows to iterate over the result set one row at a time.

This scenario could be considered a complex pattern when the code inside the loop is overly complex. E.g. if the SELECT statement inside the cursor for loop returns a large result set, or if the code inside the loop involves complex operations or nested loops, the cursor for loop could become slow and inefficient. This warning is meant to be a helper to spot some problems that a static-code analyzer such as SnowConvert cannot.

Code Example

Teradata

Input

REPLACE PROCEDURE teradata_cursor_for_loop()    
BEGIN
    FOR fUsgClass AS cUsgClass CURSOR FOR
        (SELECT col1
        FROM sample_table)
    DO
        SET var1 = fUsgClass.col1;
    END FOR;
END;

Output

CREATE OR REPLACE PROCEDURE teradata_cursor_for_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET cUsgClass CURSOR
        FOR
            SELECT
                col1
                   FROM
                sample_table;
        /*** MSC-WARNING - MSCCP0003 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP ***/
        FOR fUsgClass IN cUsgClass DO
            var1 := :fUsgClass.col1;
        END FOR;
    END;
$$;

Oracle

Input

CREATE OR REPLACE PROCEDURE oracle_cursor_for_loop AS
BEGIN
    FOR r1 IN (SELECT col1 FROM sample_table) LOOP
        NULL;
    END LOOP;
END;

Output

CREATE OR REPLACE PROCEDURE oracle_cursor_for_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET temporary_for_cursor_0 CURSOR
        FOR
            (SELECT col1 FROM
                    sample_table
            );
        /*** MSC-WARNING - MSCCP0003 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP ***/
        FOR r1 IN temporary_for_cursor_0 DO
            NULL;
        END FOR;
    END;
$$;

Recommendations

• If you need more support, you can email us at [email protected]