由于下面的脚本是根据公司的业务产生的 重点是指标对应确定范围的值。业务不一样需要进行调整。
create or replace procedure cy_ckmy(ywlx varchar2) as
v_ywlx varchar2(16);--业务类型、传入参数
v_zxz varchar2(16);
v_ind varchar2(16);
v_ywsj DATE;
v_jgid varchar2(16);
begin
v_ywlx:=ywlx;
--指标ID
declare cursor c_inds is
select indid from dmc_model_ind where modelid = v_ywlx and indid in( select zbid from T_ZB_CKm t where T.lx='2' );
c_ind c_inds%rowtype;
begin
for c_ind in c_inds loop
--业务时间
declare cursor c_ywsjs is
select MODEL from t_date;
c_ywsj c_ywsjs%rowtype;
begin
for c_ywsj in c_ywsjs loop
--机构ID
declare cursor c_jgids is
select DIC_KEY from comm_dic where TYPE='JGMC';
c_jgid c_jgids%rowtype;
begin
for c_jgid in c_jgids loop
v_ind := c_ind.indid;
v_ywsj := c_ywsj.MODEL;
v_jgid := c_jgid.DIC_KEY;
--多种类型分别判断
-- if v_jgid !='R001' AND v_jgid !='Z001' AND v_jgid !='F001' then
-- select CK into v_zxz from T_ZB_CKm t where t.zbid = v_ind AND t.lx='3';
-- elsif v_jgid ='R001' then
-- select CK into v_zxz from T_ZB_CKm t where t.zbid = v_ind AND T.lx='4';
-- elsif v_jgid ='Z001' then
-- select CK into v_zxz from T_ZB_CKm t where t.zbid = v_ind AND T.lx='5' ;
-- elsif v_jgid ='F001' then
-- select CK into v_zxz from T_ZB_CKm t where t.zbid = v_ind AND T.lx='6' ;
-- end if;
select CK into v_zxz from T_ZB_CKm t where t.zbid = v_ind ;
insert into jhsy (area_code,ywsj,zbid,org_jgmc,zxz,xfxh) --表名
values('A001',v_ywsj,v_ind,v_jgid,trunc(dbms_random.value(v_zxz*0.8,v_zxz),4),'xfxh13');
commit;
end loop;
end;
end loop;
end;
end loop;
end;