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

本文主要是介绍利用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;