Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2014
    Posts
    8

    Unanswered: loop in stored procedure

    dear,

    I have written a stored procedure but I would like to loop through a select statement based upon different parameters possibly having one or more values; parameters p_test_domain, p_table_name, p_test_owner, p_test_type could contain a single value, multiple values or NULL. When multiple values are inserted in one of de parameters, a loop must be done for all values present in that parameter on the select statement (see code).

    Does anyone can help me out how to incorporate this into my procedure?

    Thanks a million!

    CREATE OR REPLACE PROCEDURE TEST.MY_PROCEDURE

    (
    p_test_domain IN VARCHAR ,
    p_table_name IN VARCHAR(100) ,
    p_test_owner IN CHAR,
    p_test_type IN VARCHAR


    )

    cursor c_test_cases
    is
    select test_domain,
    test_group,
    test_id,
    schema_name,
    file_name,
    table_name,
    field_name,
    field_type,
    field_description,
    test_type,
    test_description,
    test_owner,
    test_query,
    test_creation,
    expected_result
    from TEST.TEST_CASES
    where
    test_domain IN case when p_test_domain is null then test_domain else p_test_domain end
    and table_name IN case when p_table_name is null then table_name else p_table_name end
    and test_owner IN case when p_test_owner is null then test_owner else p_test_owner end
    and test_type IN case when p_test_type is null then test_type else p_test_type end
    order by test_id

    ;

    v_result INTEGER;
    v_tstart timestamp (0);
    v_tend timestamp (0);
    v_cresult char (6);
    v_qresult char (6);
    v_remark varchar2 (2000);
    v_runid number (3,1);
    cnt_refcur SYS_REFCURSOR;
    v_cnt INTEGER;
    v_pstart timestamp (0);
    v_pend timestamp (0);
    v_action char(15);
    v_date_run timestamp (0);
    v_active char(1);
    v_mig_date timestamp (0);

    begin

    ...

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    LOOP through a cursor:
    Code:
    OPEN cursor;
    FETCH cursor INTO variables_list;
    WHILE (SQLCODE <> 100) DO
      -- some code
      FETCH cursor INTO variables_list;
    END WHILE;
    CLOSE cursor;
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If p_test_domain was 'd1 , d2 , d3'
    and p_table_name was 'n1 , n2 , n3'
    so on...

    Then which of (a) or (b) do you want?
    (a)
    Code:
        test_domain IN (d1 , d2 , d3)
    AND table_name  IN (n1 , n2 , n3)
    AND ...
    or
    (b)
    Code:
        test_domain = d1 AND table_name = n1 AND ...
    OR  test_domain = d2 AND table_name = n2 AND ...
    OR  test_domain = d3 AND table_name = n3 AND ...

  4. #4
    Join Date
    Jan 2014
    Posts
    8
    a what I clearly would want, based upon the values that are inserted the select should run using AND in the where condition

  5. #5
    Join Date
    Jan 2014
    Posts
    8
    a is clearly what I want

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Oh, I think I got it:
    you want to send as argument let's say '1, 2, 3' and your SELECT should run with AND column IN (1, 2, 3).

    In a varchar variable build your SQL using string concatenation. At the end, use PREPARE FROM string and OPEN cursor.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by rickmeister View Post
    a is clearly what I want
    So, I want confirm further that the different number of multiple values for each parameters may be possible, like ...
    p_test_domain was 'd1 , d2 , d3'
    and p_table_name was 'n1 , n2 , n3 , n4'
    and p_test_owner was 'o1'
    and p_test_type was 't1 , t2'
    Is it right?
    (I assumed that a separator of each elements in multiple value was a comma(i.e. ",") ).


    Anyway,
    my guess for solution were
    (1) use dynamic query
    or
    (2) decompose each element of multiple values in a parameter into a temporary table or a table expression.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There are some ways to decompose multiple values.

    If maximum number of elements was not known,
    recursive common-table-expression might be a solution.

    If maximum number of elements was limited, here is an example ...
    Note 1: I assumed a separator of each element in the multiple value was a comma(",").
    Note 2: maximum number of elements was 9.
    Code:
    WITH
     test_parameters
    ( p_test_domain , p_table_name , p_test_owner , p_test_type ) AS (
    VALUES
     ( 'd1 , d2 , d3' , 'n1 , n2 , n3 , n4' , 'o1' , 't1 , t2' )
    )
    , decomp_domain AS (
    SELECT TRIM(SUBSTR(  p_test_domain
                       , p
                       , LEAD(p , 1 , LENGTH(p_test_domain) + 2) OVER(ORDER BY k) - p - 1
                      )
               ) AS element
     FROM  test_parameters
     CROSS JOIN
           (VALUES 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9) AS k(k)
     CROSS JOIN
           LATERAL
           (VALUES INSTR(',' || p_test_domain , ',' , 1 , k) ) AS p(p)
     WHERE p > 0 
    )
    SELECT *
     FROM  decomp_domain
    ;
    ------------------------------------------------------------------------------
    
    ELEMENT     
    ------------
    d1          
    d2          
    d3          
    
      3 record(s) selected.

    An example of cursor c_test_cases in your code using this technique might be like...
    Code:
    SELECT test_domain
         , test_group
         , test_id
         , schema_name
         , file_name
         , table_name
         , field_name
         , field_type
         , field_description
         , test_type
         , test_description
         , test_owner
         , test_query
         , test_creation
         , expected_result
     FROM  test.test_cases
     WHERE
      (    test_domain
           IN (SELECT TRIM(SUBSTR(  p_test_domain
                                  , p
                                  , LEAD(p , 1 , LENGTH(p_test_domain) + 2)
                                       OVER(ORDER BY k) - p - 1
                                 )
                          ) AS element
                FROM  (VALUES 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 ) AS k(k)
                CROSS JOIN
                      LATERAL
                      (VALUES INSTR(',' || p_test_domain , ',' , 1 , k) ) AS p(p)
                WHERE p > 0 
              )
       OR  p_test_domain IS NULL
      )
      AND
      (    table_name
           IN (SELECT TRIM(SUBSTR(  p_table_name
                                  , p
                                  , LEAD(p , 1 , LENGTH(p_table_name) + 2)
                                       OVER(ORDER BY k) - p - 1
                                 )
                          ) AS element
                FROM  (VALUES 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 ) AS k(k)
                CROSS JOIN
                      LATERAL
                      (VALUES INSTR(',' || p_table_name , ',' , 1 , k) ) AS p(p)
                WHERE p > 0 
              )
       OR  p_table_name IS NULL
      )
      AND
           ...
           ...
    
     ORDER BY
           test_id

    These example were based on my guess
    Quote Originally Posted by tonkuma View Post
    ...
    ...

    Anyway,
    my guess for solution were
    ...
    or
    (2) decompose each element of multiple values in a parameter into a temporary table or a table expression.
    Last edited by tonkuma; 03-15-14 at 09:44. Reason: Add last paragraph stating basis of the examples.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another my thought was that sometimes functions were covinient than procedures.

    Here is a very simplified example of a table function to accept multiple values of parameters.

    Accept parameters like
    p_dept = 'A00 , C01 , D11'
    p_sex = 'F , M'
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION test.list_parm
    ( p_dept VARCHAR(53)
    , p_sex  VARCHAR(07)
    )
       RETURNS TABLE
       ( empno CHAR(6) , workdept CHAR(3) , sex CHAR(1) )
       READS SQL DATA
       NO EXTERNAL ACTION
    /* Start of function body */
    RETURN
    SELECT empno , workdept , sex
     FROM  employee
     WHERE
      (    workdept
           IN (SELECT TRIM(SUBSTR(  p_dept
                                  , p
                                  , LEAD(p , 1 , LENGTH(p_dept) + 2)
                                       OVER(ORDER BY k) - p - 1
                                 )
                          ) AS element
                FROM  (VALUES 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 ) AS k(k)
                CROSS JOIN
                      LATERAL
                      (VALUES INSTR(',' || p_dept , ',' , 1 , k) ) AS p(p)
                WHERE p > 0 
              )
       OR  p_dept IS NULL
      )
      AND
      (    sex
           IN (SELECT TRIM(SUBSTR(  p_sex
                                  , p
                                  , LEAD(p , 1 , LENGTH(p_sex) + 2)
                                       OVER(ORDER BY k) - p - 1
                                 )
                          ) AS element
                FROM  (VALUES 1 , 2 ) AS k(k)
                CROSS JOIN
                      LATERAL
                      (VALUES INSTR(',' || p_sex , ',' , 1 , k) ) AS p(p)
                WHERE p > 0 
              )
       OR  p_sex IS NULL
      )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Some examples using this table function.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( test.list_parm('A00 , C01 , D11' , 'F , M') )
     ORDER BY
           workdept
         , sex
         , empno
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  WORKDEPT SEX
    ------ -------- ---
    000010 A00      F  
    200010 A00      F  
    000110 A00      M  
    000120 A00      M  
    200120 A00      M  
    000030 C01      F  
    000130 C01      F  
    000140 C01      F  
    200140 C01      F  
    000160 D11      F  
    000180 D11      F  
    000220 D11      F  
    200220 D11      F  
    000060 D11      M  
    000150 D11      M  
    000170 D11      M  
    000190 D11      M  
    000200 D11      M  
    000210 D11      M  
    200170 D11      M  
    
      20 record(s) selected.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( test.list_parm(NULL , 'F') )
     ORDER BY
           workdept
         , sex
         , empno
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  WORKDEPT SEX
    ------ -------- ---
    000010 A00      F  
    200010 A00      F  
    000030 C01      F  
    000130 C01      F  
    000140 C01      F  
    200140 C01      F  
    000160 D11      F  
    000180 D11      F  
    000220 D11      F  
    200220 D11      F  
    000070 D21      F  
    000260 D21      F  
    000270 D21      F  
    000090 E11      F  
    000280 E11      F  
    000310 E11      F  
    200280 E11      F  
    200310 E11      F  
    200330 E21      F  
    
      19 record(s) selected.
    Last edited by tonkuma; 03-15-14 at 10:04. Reason: Add a phrase " of a table function"

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    If p_test_domain was 'd1 , d2 , d3'
    and p_table_name was 'n1 , n2 , n3'
    so on...

    Then which of (a) or (b) do you want?
    (a)
    Code:
        test_domain IN (d1 , d2 , d3)
    AND table_name  IN (n1 , n2 , n3)
    AND ...
    or
    (b)
    Code:
        test_domain = d1 AND table_name = n1 AND ...
    OR  test_domain = d2 AND table_name = n2 AND ...
    OR  test_domain = d3 AND table_name = n3 AND ...
    Although, OP(rickmeister) answered "a is clearly what I want",
    I want to show an example of (b) for reference.


    CREATE FUNCTION:
    Note 1: I wrote the example as a function, mainly because of ease of invoking the routine and ease of getting the results.
    Note 2: Please see the place of "(VALUES ... ) AS k(k)" which is in main query,
    not like an example for (a) (in which the phrase was in subqueries) showed in
    Quote Originally Posted by tonkuma View Post
    Another my thought was that sometimes functions were covinient than procedures.

    Here is a very simplified example of a table function to accept multiple values of parameters.

    ....

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION test.list_parm_a
    ( p_dept VARCHAR(53)
    , p_sex  VARCHAR(35)
    )
       RETURNS TABLE
       ( empno CHAR(6) , workdept CHAR(3) , sex CHAR(1) , k SMALLINT )
       READS SQL DATA
       NO EXTERNAL ACTION
    /* Start of function body */
    RETURN
    SELECT empno , workdept , sex
         , k
     FROM  employee
     CROSS JOIN
           (VALUES 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 ) AS k(k)
     WHERE
      (    workdept
           =  (SELECT TRIM( SUBSTR(p_dept , p1 + 1 , p2 - p1 - 1) )
                FROM  LATERAL
                      (VALUES
                          (  CASE k
                             WHEN 1 THEN
                                  0
                             ELSE INSTR(p_dept , ',' , 1 , k - 1)
                             END
                           , COALESCE(
                                NULLIF(INSTR(p_dept , ',' , 1 , k) , 0)
                              , LENGTH(p_dept) + 1
                             )
                          )
                      ) AS p(p1 , p2)
                WHERE k = 1 AND p2 > 1 OR p1 > 0
              )
       OR  p_dept IS NULL
      )
      AND
      (    sex
           =  (SELECT TRIM( SUBSTR(p_sex , p1 + 1 , p2 - p1 - 1) )
                FROM  LATERAL
                      (VALUES
                          (  CASE k
                             WHEN 1 THEN
                                  0
                             ELSE INSTR(p_sex , ',' , 1 , k - 1)
                             END
                           , COALESCE(
                                NULLIF(INSTR(p_sex , ',' , 1 , k) , 0)
                              , LENGTH(p_sex) + 1
                             )
                          )
                      ) AS p(p1 , p2)
                WHERE k = 1 AND p2 > 1 OR p1 > 0
              )
       OR  p_sex IS NULL
      )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Usage of the function:
    Note:
    k=1 for (workdept , sex) = ('A00' , 'F')
    k=2 for (workdept , sex) = ('D11' , 'M')
    k=3 for (workdept , sex) = ('E11' , 'F')
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( test.list_parm_a('A00 , D11 , E11' , 'F , M , F') );
    ------------------------------------------------------------------------------
    
    EMPNO  WORKDEPT SEX K     
    ------ -------- --- ------
    000010 A00      F        1
    200010 A00      F        1
    000060 D11      M        2
    000150 D11      M        2
    000170 D11      M        2
    000190 D11      M        2
    000200 D11      M        2
    000210 D11      M        2
    200170 D11      M        2
    000090 E11      F        3
    000280 E11      F        3
    000310 E11      F        3
    200280 E11      F        3
    200310 E11      F        3
    
      14 record(s) selected.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    There are some ways to decompose multiple values.

    If maximum number of elements was not known,
    recursive common-table-expression might be a solution.

    ...
    ...
    Here were some examples using common-table-expression.
    Parsing Strings in SQL


    An example of cursor c_test_cases in your code using the table function "elements" in the article might be like...
    Code:
    SELECT test_domain
         , test_group
         , test_id
         , schema_name
         , file_name
         , table_name
         , field_name
         , field_type
         , field_description
         , test_type
         , test_description
         , test_owner
         , test_query
         , test_creation
         , expected_result
     FROM  test.test_cases
     WHERE
      (    test_domain
           IN (SELECT VARCHAR(elem , 20)
                FROM  TABLE ( elements(p_test_domain) ) AS t(elem)
              )
       OR  p_test_domain IS NULL
      )
      AND
      (    table_name
           IN (SELECT VARCHAR(elem , 20)
                FROM  TABLE ( elements(p_table_name) ) AS t(elem)
              )
       OR  p_table_name IS NULL
      )
      AND
           ...
           ...
    
     ORDER BY
           test_id

Posting Permissions

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