Results 1 to 2 of 2

Thread: DB2 PL/SQL Help

  1. #1
    Join Date
    Aug 2016
    Posts
    1

    Unanswered: DB2 PL/SQL Help

    Hi Experts,

    I am new to db2 PL/SQL. I would like to ask for assistance regarding one of the requirement.

    There are 2 tables under same schema and database, say Tab1 & Tab2.

    Tab1 (have 3 columns C1,C2 and C3) have 1000 records and I have to populate Tab2 (C4 and C5) with a filter condition. Also a COMMIT statement should be issued after every 100 records.

    INSERT INTO TAB2 (C4,C5) (SELECT C1,C2 FROM TAB1 WHERE C3=1);

    I would like to handle this requirement with PL/SQL.

    Please assist.

    Thanks,
    Ron.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    68
    Provided Answers: 7
    Hi Ron,

    try this:
    Code:
    begin
      declare l_cnt int default 0;
      declare l_c1 int;
      declare l_c2 int;
      declare SQLSTATE char(5) default '00000';
      declare c1 cursor with hold for SELECT C1,C2 FROM TAB1 WHERE C3=1;
    
      open c1;
      fetch c1 into l_c1, l_c2;
      while SQLSTATE<>'02000' do
        insert into tab2 (c4, c5) values (l_c1, l_c2);
        set l_cnt=l_cnt+1;
        if l_cnt=100 then
          commit;
          set l_cnt=0;
        end if;
        fetch c1 into l_c1, l_c2;
      end while;
      close c1;
      commit;
    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
  •