Records in a FOR...IN EXECUTE statement

Here is an example of an NZPLSQL procedure written for use with Netezza Platform Software (NPS):

CREATE or replace PROCEDURE  myproc(varchar(256))
    RETURNS INT4
    LANGUAGE NZPLSQL
AS 
BEGIN_PROC 
 declare 
  sqlstr alias for $1;
  r1 record;
 begin 
  FOR r1 IN EXECUTE sqlstr 
  loop 
   insert into t1 values r1.c1;
  end loop;
 end;
END_PROC@

Because the input SQL statement is unknown, the procedure uses a record with an unstructured (that is, generic) row type to retrieve the result. However, Db2® requires that such records use a structured data type. To work around this limitation:

• If you know the structure of the record, define a new row type, then use the AS keyword to specify that type for the record that retrieves the result. For example, define a new row type called myrecord and use it to define the type of record r1:

  CREATE TYPE myrecord AS ROW (c1 INTEGER)@
  
  CREATE or replace PROCEDURE  myproc(varchar(256))
      RETURNS INT4
      LANGUAGE NZPLSQL
  AS 
  BEGIN_PROC 
   declare 
    sqlstr alias for $1;
    r1 record AS myrecord;
   begin 
    FOR r1 IN EXECUTE sqlstr 
    loop 
     insert into t1 values r1.c1;
    end loop;
   end;
  END_PROC@
  

• If you know that the procedure will select from a particular table (or a table with an identical structure), use an anchored row type for the record that retrieves the result. For example, if the table being read from is table t2, use that table to define the row type of record r1:

  CREATE or replace PROCEDURE  myproc(varchar(256))
      RETURNS INT4
      LANGUAGE NZPLSQL
  AS 
  BEGIN_PROC 
   declare 
    sqlstr alias for $1;
    r1 t2%ROWTYPE;
   begin 
    FOR r1 IN EXECUTE sqlstr 
    loop 
     insert into t1 values r1.c1;
    end loop;
   end;
  END_PROC@