利用oracel的存储过程把当前有的值更新到另外一列上

环比值的生成

说明：在原有的数据基础之上把指标的执行值，根据月份，把本月的放到上月的环比值字里其它字段保持不变，以此类推。

 

 

create or replace procedure HBZ_UPDATE(tableName varchar2,startDate varchar2,endDate varchar2) is
v_tableName varchar2(100);
v_count_ywsj number;
v_sql varchar2(2000);--拼SQL语句

begin
  v_tableName:=tableName;
   SELECT count(1) into v_count_ywsj FROM dmc_model A,DMC_MODEL_DIM B, dmc_dimension C
          WHERE A.TABLENAME = v_tableName and  A.RECORDID = B.MODELID AND B.DIMID = C.RECORDID AND C.CODE='YWSJ';
   if v_count_ywsj = 1 then
     v_sql := 'update '|| v_tableName || ' A set HBZ = (SELECT ZXZ FROM ' || v_tableName || ' B  WHERE 1=1';
     declare cursor c_rows is
        SELECT C.CODE  FROM dmc_model A,DMC_MODEL_DIM B, dmc_dimension C  WHERE A.TABLENAME = v_tableName and  A.RECORDID = B.MODELID AND B.DIMID = C.RECORDID;
        c_row c_rows%rowtype;
        BEGIN
        for c_row in c_rows loop
            if c_row.code = 'YWSJ' then 
               v_sql :=   v_sql || ' AND A.YWSJ=add_months(B.YWSJ,1)';
            else
                v_sql :=   v_sql || ' AND ' || 'A.' || c_row.code || '= B.' || c_row.code ;
            end if; 
        end loop;
        END;
        v_sql := v_sql || ' AND A.ZBID = B.ZBID) where A.YWSJ >=''' || startDate || ''' AND A.YWSJ <=''' ||endDate||'''';
        dbms_output.put_line(v_sql);
        execute immediate v_sql;
        commit;
   end if;
   

end HBZ_UPDATE;

同比值只需要该一下就好了

create or replace procedure TBZ_UPDATE(tableName varchar2,startDate varchar2,endDate varchar2) is
v_tableName varchar2(100);
v_count_ywsj number;
v_sql varchar2(2000);--拼SQL语句

begin
  v_tableName:=tableName;
   SELECT count(1) into v_count_ywsj FROM dmc_model A,DMC_MODEL_DIM B, dmc_dimension C
          WHERE A.TABLENAME = v_tableName and  A.RECORDID = B.MODELID AND B.DIMID = C.RECORDID AND C.CODE='YWSJ';
   if v_count_ywsj = 1 then
     v_sql := 'update '|| v_tableName || ' A set TBZ = (SELECT ZXZ FROM ' || v_tableName || ' B  WHERE 1=1';
     declare cursor c_rows is
        SELECT C.CODE  FROM dmc_model A,DMC_MODEL_DIM B, dmc_dimension C  WHERE A.TABLENAME = v_tableName and  A.RECORDID = B.MODELID AND B.DIMID = C.RECORDID;
        c_row c_rows%rowtype;
        BEGIN
        for c_row in c_rows loop
            if c_row.code = 'YWSJ' then 
               v_sql :=   v_sql || ' AND A.YWSJ=add_months(B.YWSJ,6)';
            else
                v_sql :=   v_sql || ' AND ' || 'A.' || c_row.code || '= B.' || c_row.code ;
            end if; 
        end loop;
        END;
        v_sql := v_sql || ' AND A.ZBID = B.ZBID) where A.YWSJ >=''' || startDate || ''' AND A.YWSJ <=''' ||endDate||'''';
        dbms_output.put_line(v_sql);
        execute immediate v_sql;
        commit;
   end if;
   

end TBZ_UPDATE;