Oracle存储过程随记（一）

1.包含输出某个时间段的所有时间，执行动态的sql。传入动态的参数包括表名

create or replace procedure pro_updateStock_betweenTime(beginTime in varchar2,
                                                        endTime   in varchar2) is
  num number;
  v_tableName varchar2(2000);
  i number;

  cursor time_data is
    SELECT TO_CHAR(TO_DATE(beginTime, 'yyyy-MM-dd') + ROWNUM - 1,
                   'yyyyMMdd') as curday,
           'scm_stockagereport' ||
           TO_CHAR(TO_DATE(beginTime, 'yyyy-MM-dd') + ROWNUM - 1, 'yyyyMM') as tableName
      FROM DUAL
    CONNECT BY ROWNUM <=
               trunc(to_date(endTime, 'yyyy-MM-dd') -
                     to_date(beginTime, 'yyyy-MM-dd')) + 1;

  time_row time_data%rowtype;

begin
  
  i := 1;

  for time_row in time_data loop
    
    
    --判断表是否存在  如果存在判断遍历的表名是否相同  不相同就删除表然后重新创建
    select count(1) into num from user_tables a where a.TABLE_NAME = UPPer('' || time_row.tablename || '');
     
     if num=1 and (i=1 or trim(v_tableName)<>trim(time_row.tablename)) then
      dbms_output.put_line('11111111111111111111'); 
     --删除表
      execute immediate 'drop table '||time_row.tablename;
      --创建表
     execute immediate 'create table '||time_row.tablename||' (
                            id                     NUMBER(19) not null,
                            input_unit_id          NUMBER(19),
                            short_name             VARCHAR2(500),
                            unit_name              VARCHAR2(500),
                            warehouse_org_code     VARCHAR2(100),
                            material_belong        VARCHAR2(50),
                            product_name           VARCHAR2(500),
                            input_unit_name        VARCHAR2(100),
                            warehouse_id           VARCHAR2(100),
                            warehouse_name         VARCHAR2(300),
                            location_name          VARCHAR2(200),
                            entity_name            VARCHAR2(200),
                            material_cate_name     VARCHAR2(300),
                            product_model_name     VARCHAR2(500),
                            material_code          VARCHAR2(100),
                            material_name          VARCHAR2(500),
                            provider_product_code  VARCHAR2(100),
                            material_property_name VARCHAR2(500),
                            is_main_product        VARCHAR2(100),
                            provider_product_name  VARCHAR2(350),
                            provider_short_name    VARCHAR2(150),
                            measure_name           VARCHAR2(300),
                            total_amount           NUMBER(18,2),
                            total_money            NUMBER(18,2),
                            preempt_amount         NUMBER(18,2),
                            age                    NUMBER(18,1),
                            first_input_date       DATE,
                            account_cate_name      VARCHAR2(150),
                            cur_date               DATE,
                            site_code              VARCHAR2(300),
                            site_name              VARCHAR2(300),
                            project_code           VARCHAR2(512),
                            project_name           VARCHAR2(300),
                            location_id            VARCHAR2(100),
                            warehouse_belong       VARCHAR2(100),
                            is_main_product_id     VARCHAR2(100),
                            material_cate_id       VARCHAR2(100),
                            provider_id            VARCHAR2(100),
                            material_property_id   VARCHAR2(100),
                            is_barcode_control     VARCHAR2(100),
                            provider_name          VARCHAR2(350),
                            warehouse_org_name     VARCHAR2(300),
                            dilivery_location      VARCHAR2(50),
                            product_codes_id       NUMBER(19),
                            input_date             DATE,
                            input_detail_id        NUMBER(19)
                         )
                         tablespace WZGL_DATA01
                            pctfree 10
                            initrans 1
                            maxtrans 255
                            storage
                            (
                              initial 64K
                              minextents 1
                              maxextents unlimited
                            )';
              
     end if;
     
     if num<>1 then
       execute immediate 'create table '||time_row.tablename||' (
                                id                     NUMBER(19) not null,
                                input_unit_id          NUMBER(19),
                                short_name             VARCHAR2(500),
                                unit_name              VARCHAR2(500),
                                warehouse_org_code     VARCHAR2(100),
                                material_belong        VARCHAR2(50),
                                product_name           VARCHAR2(500),
                                input_unit_name        VARCHAR2(100),
                                warehouse_id           VARCHAR2(100),
                                warehouse_name         VARCHAR2(300),
                                location_name          VARCHAR2(200),
                                entity_name            VARCHAR2(200),
                                material_cate_name     VARCHAR2(300),
                                product_model_name     VARCHAR2(500),
                                material_code          VARCHAR2(100),
                                material_name          VARCHAR2(500),
                                provider_product_code  VARCHAR2(100),
                                material_property_name VARCHAR2(500),
                                is_main_product        VARCHAR2(100),
                                provider_product_name  VARCHAR2(350),
                                provider_short_name    VARCHAR2(150),
                                measure_name           VARCHAR2(300),
                                total_amount           NUMBER(18,2),
                                total_money            NUMBER(18,2),
                                preempt_amount         NUMBER(18,2),
                                age                    NUMBER(18,1),
                                first_input_date       DATE,
                                account_cate_name      VARCHAR2(150),
                                cur_date               DATE,
                                site_code              VARCHAR2(300),
                                site_name              VARCHAR2(300),
                                project_code           VARCHAR2(512),
                                project_name           VARCHAR2(300),
                                location_id            VARCHAR2(100),
                                warehouse_belong       VARCHAR2(100),
                                is_main_product_id     VARCHAR2(100),
                                material_cate_id       VARCHAR2(100),
                                provider_id            VARCHAR2(100),
                                material_property_id   VARCHAR2(100),
                                is_barcode_control     VARCHAR2(100),
                                provider_name          VARCHAR2(350),
                                warehouse_org_name     VARCHAR2(300),
                                dilivery_location      VARCHAR2(50),
                                product_codes_id       NUMBER(19),
                                input_date             DATE,
                                input_detail_id        NUMBER(19)
                              )
                              tablespace WZGL_DATA01
                            pctfree 10
                            initrans 1
                            maxtrans 255
                            storage
                            (
                              initial 64K
                              minextents 1
                              maxextents unlimited
                            )';
              
        
     end if; 
   
      v_tableName := time_row.tablename;
  
    --动态删除tableName中的curDay的数据
    execute immediate 'delete from ' || time_row.tablename ||
                      ' t where t.cur_date=to_date(' || time_row.curday ||
                      ',''yyyy-mm-dd'')';
    commit;
    
     
    --重新插入删除的数据
    pro_scm_stockagereport_month(time_row.tablename, time_row.curday);
    commit;
    
    i := i+1;
  end loop;

end;