Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    13

    Question Unanswered: How DB2 support Multiple value parameters in Stored Procedure?

    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!!!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Use Declared global temp tables

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2006
    Posts
    13
    Can you give me an example? Can I use a table type parameter?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2014
    Posts
    1

    Inserting value in global temp table

    Hi,

    In your example you are inserting the records in the global temporary table and then reading these values in the stored procedure. But here you have hardcoded values to insert. What if the values are coming dynamic from a parameter?

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •