Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Dynamic variables on a declare cursor statement

    Hi,


    I am trying to write a cursor with dynamic variables.
    I am new to db2, I don't know whether I can carry as this or not.kindly correct me If i am wrong.

    This is my dynamic cursor statemet

    DECLARE worklist_cursor_ME CURSOR WITH RETURN for VALUES
    'select cst.OWNER as DB_NAME,cst.MT_NAME as TABLE_NAME,cstc.MCL_NAME as COLUMN_NAME,
    cstc.ENCR_METHOD as ENCRYPTION_METHOD,cstc.ID_USE ID_USE,cstc.ID_USE2 ID_USE2
    FROM MENC as cst, MEN as cstc WHERE cstc.MT_ID = cst.MT_ID and
    cstc.MENTC_ID = cst.MENTC_ID and cst.OWNER =''' || p_db_name || '''
    and cst.MT_name =''' || p_table_name || ''' and cstc.encr_method <> ''gafjagfjg''
    order by sort_order ';


    can i use the Dynamic variables on a declare cursor stmt. If not plz tel me how to use dynamic variables on a declare cursor stmt.

  2. #2
    Join Date
    Jul 2014
    Posts
    294

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:

    Code:
    --#SET TERMINATOR @
    
    set serveroutput on@
    
    -- static sql --
    begin
      declare l_tabschema varchar(128);
      declare l_tabname varchar(128);
    
      set l_tabschema='SYSCAT';
      for c1 as 
        select tabname from syscat.tables where tabschema=l_tabschema
      do 
        set l_tabname=c1.tabname;
        call dbms_output.put_line(l_tabname);
      end for;
    end@
    
    -- dynamic sql --
    begin
      declare SQLSTATE CHAR(5) default '00000';
      declare l_stmt varchar(128) default 'select tabname from syscat.tables where tabschema=?';
      declare l_tabschema varchar(128);
      declare l_tabname varchar(128);
      declare c1 cursor for s1;
    
      prepare s1 from l_stmt;
      set l_tabschema='SYSCAT';
      open c1 using l_tabschema;
      fetch c1 into l_tabname;
      while (SQLSTATE<>'02000') do
        call dbms_output.put_line(l_tabname);
        fetch c1 into l_tabname;
      end while;
    end@
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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