Hi, Everybody
I want to write a SP which use a parameter to accept some values once. For example, I want to pass 5 values to this parameter and read them one by one in SP.
I don't know whether DB2 can support this just like Oracle. Oracle has VARRAY and Nested Table to do this. The VARRAY and Nested TAble both can be used as the parameter type in a SP.
Thank you very much!!!

Use Declared global temp tables

HTH

Sathyaram

Can you give me an example? Can I use a table type parameter?

Here's an example ...

CREATE PROCEDURE PROCEDURE1 ( )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE CHAR2 CHAR(100) ;
--- The following GTT declaration and INSERT typically will happen outside of the stored proc.
DECLARE GLOBAL TEMPORARY TABLE SESSION.AMOUNT_GTT(CURRENCY_CD CHAR(3),AMOUNT DECIMAL(10,3)) WITH REPLACE NOT LOGGED ;
INSERT INTO SESSION.AMOUNT_GTT values('USD',100),('GBP',200),('EUR',500) ;
--- The procedure is now called and the rows in the global temp table are used for processing
--- If GTT was defined outside the scope of this stored proc, a dummy definition has to be done in
--- procedure for compilation purposes, as follows
--- if (1=1) then
--- DECLARE GLOBAL TEMPORARY TABLE SESSION.AMOUNT_GTT(CURRENCY_CD CHAR(3),AMOUNT DECIMAL(10,3)) WITH REPLACE NOT LOGGED ;
--- end if ;

BEGIN
declare usd_amt decimal(10,3) ;
FOR PROCESS1 AS SELECT currency_cd,amount FROM SESSION.AMOUNT_GTT -- LOOPS THROUGH THE GTT RECORDS, 1 ROW AT A TIME.
DO
-- DO YOUR PROCESSING HERE
set usd_amt=case CURRENCY_CD
when 'USD' then AMOUNT
when 'GBP' THEN AMOUNT*1.8
WHEN 'EUR' THEN AMOUNT*1.2
EnD ;
END FOR ;
END ;
END P1