Tips and best practices for stored procedures

This section describes some common tips and best practices for stored procedures.

Control-C interrupt processing in loops

If the procedure encounters an infinite loop, you can press Control-C to interrupt the loop. This key sequence should cause an exception (which is not catchable) and thus cause the loop and the procedure call to be interrupted and return.

Query processing in loops

Within stored procedures, there can be loop designs that appear to use concurrent queries to process the results. For example, the following procedure uses a SELECT to gather information, which is then processed for a possible DELETE action:

FOR rec in SELECT * from mytable LOOP
  IF rec.type = ‘d’ THEN
     EXECUTE IMMEDIATE ‘DELETE FROM mytable2 WHERE recid = ‘ || rec.id;
  END IF;
END LOOP;

While it appears that the outer FOR loop SELECT and the inner EXECUTE IMMEDIATE queries are running at the same time, the SELECT query finishes and caches its results before the inner query begins. Thus, deleting records from the table in the inner loop does not affect the SELECT query in the outer loop or change its results.

Note: Although it can be common to run row-at-a-time operations, as used in the preceding example, you can significantly improve the performance of these procedures by designing them to operate on sets of records rather than single row operations. For example, if you recast the previous example to the following:

DELETE from mytable2 where recid in (select recid from my table
where type = 'd') ;

This procedure design can take advantage of the Netezza Performance Server massively parallel processing (MPP) environment to run much quicker.