IBM Support

RECORD variable in stored procedure

Question & Answer


Question

How do I use a RECORD variable in stored procedures?

Answer

In a stored procedure execution, you can use the RECORD variable to temporarily store the result set.  For more information, refer to the IBM Netezza Stored Procedures Developer's Guide.

The example below provides a supplemental example using the RECORD variable. 

CREATE OR REPLACE PROCEDURE my_proc2() RETURNS INT LANGUAGE NZPLSQL AS
  
BEGIN_PROC
  
DECLARE              
  
row RECORD;
myrecord RECORD;
  
BEGIN   
FOR row IN SELECT * FROM my_proc1_tab LOOP
                                
RAISE NOTICE 'Values are %, % and %', row.col1, row.col2, row.col3;
 
END LOOP;                                                                          
SELECT * INTO myrecord FROM my_proc1_tab;
                                                                    IF NOT FOUND THEN
RAISE EXCEPTION 'Unable to load record from table.';
ELSE
RAISE NOTICE 'Record loaded from table.';
END IF;                 
END;
  
END_PROC;

The result of the procedure is the following output:

host(admin)=> CALL my_proc2();
  
NOTICE:  Values are 0, 0 and 0
  
NOTICE:  Values are 30, 300 and 0
  
NOTICE:  Values are 60, 600 and 0
  
NOTICE:  Values are 90, 900 and 0
  
NOTICE:  Values are 10, 100 and 0
  
NOTICE:  Values are 40, 400 and 0
  
NOTICE:  Values are 70, 700 and 0
  
NOTICE:  Values are 20, 200 and 0
  
NOTICE:  Values are 50, 500 and 0
  
NOTICE:  Values are 80, 800 and 0
  
NOTICE:  Record loaded from table.
  
 my_proc2
  
----------
(1 row)

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

NZ716336

Document Information

Modified date:
17 October 2019

UID

swg21567340

Page Feedback