时间戳

首页 > 解决方案 > db2 存储过程中的全局临时表

sql - db2 存储过程中的全局临时表

问题描述

刚刚进入 DB2，并决定在我的存储过程中使用全局临时表来完成我的任务。

任务将是下一个：只需为每天（例如 5 天）填充一些数据，从另一个表中选择随机行我得到我的日期，如：

    select id from (
    select id, rand() rnd from source_table) 
    where rnd>0
    order by rnd
    fetch first 1000 rows only

我想将 int 列表存储在某个地方以重用它们。这个想法是下一个 -

            create table test (id int, dt date);


    create or replace procedure proc1 ()

    begin 

    declare v_start date default '2018-05-25'; 
    declare v_end date default '2018-05-30'; 
    declare v_dml varchar(8000);

    /*                                  this part so far doesn't work
    declare global temporary table
    session.temp_tab(id int)
    not logged on commit preserve;

    insert into session.temp_tab(id)
    select id from my_table;*/

    while v_start <= v_end DO 

        set v_dml = 'insert into test (id, dt)
                with t as (
                select 1 id, '''||v_start||''' dt from sysibm.dual
                union
                select 2 id, '''||v_start||''' dt from sysibm.dual
                union
                select 3 id, '''||v_start||''' dt from sysibm.dual)
                select *
                from t 
                where id in (1,3)';                         
    /*instead of 1,3 I would like to have list of values in some temp 
    table/array..., which I'll get 
    from the other table and can just use duriing this proc     
    I don't want to use sub select, because I'll get every time some random 
    data. But also I need that list for filter in several insert/update 
    statements*/ 
        set v_start = v_start +1 day;

        execute immediate v_dml;
        commit;
    end while;
    end

PS 我使用 DB2 LUW v10.5.0.7

UPD_1：我想在一个循环中进行 DDL 和 DML 操作。例如，我想添加分区，然后将数据插入到同一个表中。像这样：

    create or replace procedure proc1 (
                                    in in_rep int)
    begin 
    declare v_dt date; 
    declare v_end_dt date;
    declare v_add_part varchar(1024);
    declare v_id int;
    declare v_next_id int;       

            select max(id), max(dt) 
            into v_id,  v_dt
            from test;                                                                  

            set v_end_dt  = v_dt + in_rep day; 

                        while v_dt < v_end_dt DO

                                   set v_dt = v_dt +1 day;
                                   set v_next_id = v_id+1;          
                                   set v_add_part = 'alter table TEST               
                                                      add PARTITION part_'||v_next_id||'
                                                      starting from '||v_next_id||' ending at '||v_next_id;     

                                   execute immediate v_add_part;                  

                                   insert into test (id, dt)
                                   select v_next_id, v_dt 
                                   from sysibm.dual;                   
                       end while;
    end

在这种情况下，我会收到一个错误，SQLCODE=-327, SQLSTATE=22525 无法插入行，因为它超出了最后一个分区的分区范围。

因为我试图同时更改表和插入，而不是一步一步。但除了用动态 sql 替换插入之外，无法真正了解如何逐步完成，例如：

    set v_add_part = 'alter table TEST               
    add PARTITION part_'||v_next_id||'
    starting from '||v_next_id||' ending at '||v_next_id;     

    set v_ins = 'insert into test (id, dt)
    select '||v_next_id||','''||v_dt||'''
    from sysibm.dual'; 

    execute immediate v_add_part;
    execute immediate v_ins;

标签： sqldb2procedure

解决方案

这是使用 RAND() 函数填充会话表的示例。

请注意，插入语句只编译一次，但执行次数与日期范围一样多。

对于生产用途，您应该添加相关的错误处理。

create or replace procedure proc1 ()
language sql
specific proc1
begin 
    declare v_start date default '2018-05-25'; 
    declare v_end date default '2018-05-30'; 
    declare v_dml varchar(8000);
    declare global temporary table 
        session.temp_tab(id int not null) 
        with replace not logged on commit preserve rows;
    insert into session.temp_tab(id) 
       select int(rand()*1000) as random 
       from my_table order by random fetch first 1000 rows only;
    set v_dml = 'insert into test (id, dt) 
                 select t.id ,cast(? as date)  from session.temp_tab as t ' ;
    prepare s1 from v_dml;
    while v_start <= v_end do 
        execute s1 using v_start;
        set v_start = v_start + 1 day;
    end while;
    commit;
    end

推荐阅读