存储过程定义游标变量更新表数据

create or replace procedure xj_batchavg111

AS

maxcolorvalue number(6,3); ---最大颜色级比率

maxcolorgrade varchar2(20); ---最大颜色级名称

maxmikevalue number(6,3); ---最大马克隆比率

maxmikegrade varchar2(20); ---最大马克隆名称

maxlengvalue number(6,3); ---最大长度级比率

maxlenggrade varchar2(20); ---最大长度级名称

maxvalue number(6,3); ---最大品级比率

maxgrade varchar2(20); ---最大品级名称

CURSOR sel_emp IS

SELECT *

FROM zxj.b_batch_avg@d_zxj

where

---c_factorybatchno='65619161001';

c_checkdate >= '20160901' and c_checkdate <= to_char(sysdate - 1, 'yyyymmdd'); --定义游标,该游标指向查询结果

rowresult zxj.b_batch_avg@d_zxj%ROWTYPE;

BEGIN

---参数赋值

maxcolorvalue :=0.000;

maxcolorgrade :='';

maxmikevalue :=0.000;

maxmikegrade :='';

maxlengvalue :=0.000;

maxlenggrade :='';

maxvalue :=0.000;

maxgrade :='无';

OPEN sel_emp; --打开游标

LOOP

FETCH sel_emp

INTO rowresult; --将游标中的值赋给rowresult

EXIT WHEN sel_emp%NOTFOUND; --判断：游标不存在时跳出循环

 

---判断最大颜色级比率名称并赋值

if

rowresult.N_COLORGD0_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD0_PROP;

maxcolorgrade:='/';

end if;

if

rowresult.N_COLORGD10_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD10_PROP;

maxcolorgrade:='无法确定';

end if;

if

rowresult.N_COLORGD11_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD11_PROP;

maxcolorgrade:='白棉1级';

end if;

if

rowresult.N_COLORGD12_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD12_PROP;

maxcolorgrade:='淡点污棉1级';

end if;

if

rowresult.N_COLORGD13_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD13_PROP;

maxcolorgrade:='淡黄染棉1级';

end if;

if

rowresult.N_COLORGD14_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD14_PROP;

maxcolorgrade:='黄染棉1级';

end if;

if

rowresult.N_COLORGD21_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD21_PROP;

maxcolorgrade:='白棉2级';

end if;

if

rowresult.N_COLORGD22_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD22_PROP;

maxcolorgrade:='淡点污棉2级';

end if;

if

rowresult.N_COLORGD23_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD23_PROP;

maxcolorgrade:='淡黄染棉2级';

end if;

if

rowresult.N_COLORGD24_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD24_PROP;

maxcolorgrade:='黄染棉2级';

end if;

if

rowresult.N_COLORGD31_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD31_PROP;

maxcolorgrade:='白棉3级';

end if;

if

rowresult.N_COLORGD32_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD32_PROP;

maxcolorgrade:='淡点污棉3级';

end if;

if

rowresult.N_COLORGD33_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD33_PROP;

maxcolorgrade:='淡黄染棉3级';

end if;

if

rowresult.N_COLORGD41_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD41_PROP;

maxcolorgrade:='白棉4级';

end if;

if

rowresult.N_COLORGD42_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD42_PROP;

maxcolorgrade:='淡点污棉4级';

end if;

if

rowresult.N_COLORGD51_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD51_PROP;

maxcolorgrade:='白棉5级';

end if;

if

rowresult.N_COLORGD61_PROP>maxcolorvalue

then

maxcolorvalue:=rowresult.N_COLORGD61_PROP;

maxcolorgrade:='白棉6级';

end if;

---判断最大马克隆比率名称并赋值

if

rowresult.N_MIKEGRADEA_PROP>maxmikevalue

then

maxmikevalue:=rowresult.N_MIKEGRADEA_PROP;

maxmikegrade:='A档';

end if;

if

rowresult.N_MIKEGRADEB1_PROP>maxmikevalue

then

maxmikevalue:=rowresult.N_MIKEGRADEB1_PROP;

maxmikegrade:='B1档';

end if;

if

rowresult.N_MIKEGRADEB2_PROP>maxmikevalue

then

maxmikevalue:=rowresult.N_MIKEGRADEB2_PROP;

maxmikegrade:='B2档';

end if;

if

rowresult.N_MIKEGRADEC1_PROP>maxmikevalue

then

maxmikevalue:=rowresult.N_MIKEGRADEC1_PROP;

maxmikegrade:='C1档';

end if;

if

rowresult.N_MIKEGRADEC2_PROP>maxmikevalue

then

maxmikevalue:=rowresult.N_MIKEGRADEC2_PROP;

maxmikegrade:='C2档';

end if;

---判断最大长度级比率名称并赋值

if

rowresult.N_LENGRADE1_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE1_PROP;

maxlenggrade:='25毫米';

end if;

if

rowresult.N_LENGRADE2_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE2_PROP;

maxlenggrade:='26毫米';

end if;

if

rowresult.N_LENGRADE3_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE3_PROP;

maxlenggrade:='27毫米';

end if;

if

rowresult.N_LENGRADE4_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE4_PROP;

maxlenggrade:='28毫米';

end if;

if

rowresult.N_LENGRADE5_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE5_PROP;

maxlenggrade:='29毫米';

end if;

if

rowresult.N_LENGRADE6_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE6_PROP;

maxlenggrade:='30毫米';

end if;

if

rowresult.N_LENGRADE7_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE7_PROP;

maxlenggrade:='31毫米';

end if;

if

rowresult.N_LENGRADE8_PROP>maxlengvalue

then

maxlengvalue:=rowresult.N_LENGRADE8_PROP;

maxlenggrade:='32毫米';

end if;

---判断最大品级比率名称并赋值

if

rowresult.N_GRADEMARK1_PROP>maxvalue

then

maxvalue:=rowresult.N_GRADEMARK1_PROP;

maxgrade:='品级1级';

end if;

if

rowresult.N_GRADEMARK2_PROP>maxvalue

then

maxvalue:=rowresult.N_GRADEMARK2_PROP;

maxgrade:='品级2级';

end if;

if

rowresult.N_GRADEMARK3_PROP>maxvalue

then

maxvalue:=rowresult.N_GRADEMARK3_PROP;

maxgrade:='品级3级';

end if;

if

rowresult.N_GRADEMARK4_PROP>maxvalue

then

maxvalue:=rowresult.N_GRADEMARK4_PROP;

maxgrade:='品级4级';

end if;

if

rowresult.N_GRADEMARK5_PROP>maxvalue

then

maxvalue:=rowresult.N_GRADEMARK5_PROP;

maxgrade:='品级5级';

end if;

if

rowresult.N_GRADEMARK6_PROP>maxvalue

then

maxvalue:=rowresult.N_GRADEMARK6_PROP;

maxgrade:='品级6级';

end if;

if

rowresult.N_GRADEMARK7_PROP>maxvalue

then

maxvalue:=rowresult.N_GRADEMARK7_PROP;

maxgrade:='品级7级';

end if;

---根据游标查一删一

delete from cotton_lint_fctbatch_quaidx

where processbatchcode = rowresult.c_factorybatchno; --删除

insert into cotton_lint_fctbatch_quaidx

values

(rowresult.C_FACTORYBATCHNO,

rowresult.N_GINGOOD_PROP,

rowresult.N_GINMID_PROP,

rowresult.N_GINBAD_PROP,

rowresult.C_COLORGD,

rowresult.N_COLORGD11_PROP,

rowresult.N_COLORGD21_PROP,

rowresult.N_COLORGD31_PROP,

rowresult.N_COLORGD41_PROP,

rowresult.N_COLORGD51_PROP,

rowresult.N_COLORGD61_PROP,

rowresult.N_COLORGD12_PROP,

rowresult.N_COLORGD22_PROP,

rowresult.N_COLORGD32_PROP,

rowresult.N_COLORGD42_PROP,

rowresult.N_COLORGD13_PROP,

rowresult.N_COLORGD23_PROP,

rowresult.N_COLORGD33_PROP,

rowresult.N_COLORGD14_PROP,

rowresult.N_COLORGD24_PROP,

rowresult.N_AVGLENGTH,

rowresult.N_MAXLENGTH,

rowresult.N_MINLENGTH,

rowresult.N_LENGRADE1_PROP,

rowresult.N_LENGRADE2_PROP,

rowresult.N_LENGRADE3_PROP,

rowresult.N_LENGRADE4_PROP,

rowresult.N_LENGRADE5_PROP,

rowresult.N_LENGRADE6_PROP,

rowresult.N_LENGRADE7_PROP,

rowresult.N_LENGRADE8_PROP,

substr(rowresult.c_mikegrade, 1, 1),

case when

substr(rowresult.N_AVGMIKE, instr(rowresult.N_AVGMIKE, '.') + 2, 1) = 5 and

mod(substr(rowresult.N_AVGMIKE,

instr(rowresult.N_AVGMIKE, '.') + 1,

1),

2) = 0 then trunc(rowresult.N_AVGMIKE, 1) else

round(rowresult.N_AVGMIKE, 1) end,

 

case when

substr(rowresult.N_MAXMIKE, instr(rowresult.N_MAXMIKE, '.') + 2, 1) = 5 and

mod(substr(rowresult.N_MAXMIKE,

instr(rowresult.N_MAXMIKE, '.') + 1,

1),

2) = 0 then trunc(rowresult.N_MAXMIKE, 1) else

round(rowresult.N_MAXMIKE, 1) end,

 

case when

substr(rowresult.N_MINMIKE, instr(rowresult.N_MINMIKE, '.') + 2, 1) = 5 and

mod(substr(rowresult.N_MINMIKE,

instr(rowresult.N_MINMIKE, '.') + 1,

1),

2) = 0 then trunc(rowresult.N_MINMIKE, 1) else

round(rowresult.N_MINMIKE, 1) end,

case when

substr(rowresult.N_MIKEGRADEA_PROP,

instr(rowresult.N_MIKEGRADEA_PROP, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEA_PROP,

instr(rowresult.N_MIKEGRADEA_PROP, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEA_PROP, 1) else

round(rowresult.N_MIKEGRADEA_PROP, 1) end,

 

case when

substr(rowresult.N_MIKEGRADEB1_PROP,

instr(rowresult.N_MIKEGRADEB1_PROP, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEB1_PROP,

instr(rowresult.N_MIKEGRADEB1_PROP, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEB1_PROP, 1) else

round(rowresult.N_MIKEGRADEB1_PROP, 1) end,

 

case when

substr(rowresult.N_MIKEGRADEB2_PROP,

instr(rowresult.N_MIKEGRADEB2_PROP, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEB2_PROP,

instr(rowresult.N_MIKEGRADEB2_PROP, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEB2_PROP, 1) else

round(rowresult.N_MIKEGRADEB2_PROP, 1) end,

 

case when

substr(rowresult.N_MIKEGRADEC1_PROP,

instr(rowresult.N_MIKEGRADEC1_PROP, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEC1_PROP,

instr(rowresult.N_MIKEGRADEC1_PROP, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEC1_PROP, 1) else

round(rowresult.N_MIKEGRADEC1_PROP, 1) end,

 

case when

substr(rowresult.N_MIKEGRADEC2_PROP,

instr(rowresult.N_MIKEGRADEC2_PROP, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEC2_PROP,

instr(rowresult.N_MIKEGRADEC2_PROP, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEC2_PROP, 1) else

round(rowresult.N_MIKEGRADEC2_PROP, 1) end,

case when

substr(rowresult.N_MIKEGRADEA_AVG,

instr(rowresult.N_MIKEGRADEA_AVG, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEA_AVG,

instr(rowresult.N_MIKEGRADEA_AVG, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEA_AVG, 1) else

round(rowresult.N_MIKEGRADEA_AVG, 1) end,

case when

substr(rowresult.N_MIKEGRADEB1_AVG,

instr(rowresult.N_MIKEGRADEB1_AVG, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEB1_AVG,

instr(rowresult.N_MIKEGRADEB1_AVG, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEB1_AVG, 1) else

round(rowresult.N_MIKEGRADEB1_AVG, 1) end,

case when

substr(rowresult.N_MIKEGRADEB2_AVG,

instr(rowresult.N_MIKEGRADEB2_AVG, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEB2_AVG,

instr(rowresult.N_MIKEGRADEB2_AVG, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEB2_AVG, 1) else

round(rowresult.N_MIKEGRADEB2_AVG, 1) end,

case when

substr(rowresult.N_MIKEGRADEC1_AVG,

instr(rowresult.N_MIKEGRADEC1_AVG, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEC1_AVG,

instr(rowresult.N_MIKEGRADEC1_AVG, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEC1_AVG, 1) else

round(rowresult.N_MIKEGRADEC1_AVG, 1) end,

case when

substr(rowresult.N_MIKEGRADEC2_AVG,

instr(rowresult.N_MIKEGRADEC2_AVG, '.') + 2,

1) = 5 and mod(substr(rowresult.N_MIKEGRADEC2_AVG,

instr(rowresult.N_MIKEGRADEC2_AVG, '.') + 1,

1),

2) = 0 then

trunc(rowresult.N_MIKEGRADEC2_AVG, 1) else

round(rowresult.N_MIKEGRADEC2_AVG, 1) end,

rowresult.N_AVGUNIFORMITY,

rowresult.N_MAXUNIFORMITY,

rowresult.N_MINUNIFORMITY,

rowresult.N_UNIFORMITY1_PROP,

rowresult.N_UNIFORMITY2_PROP,

rowresult.N_UNIFORMITY3_PROP,

rowresult.N_UNIFORMITY4_PROP,

rowresult.N_UNIFORMITY5_PROP,

rowresult.N_UNIFORMITY1_AVG,

rowresult.N_UNIFORMITY2_AVG,

rowresult.N_UNIFORMITY3_AVG,

rowresult.N_UNIFORMITY4_AVG,

rowresult.N_UNIFORMITY5_AVG,

rowresult.N_AVGINTENSION,

rowresult.N_MAXINTENSION,

rowresult.N_MININTENSION,

rowresult.N_INTENSION1_PROP,

rowresult.N_INTENSION2_PROP,

rowresult.N_INTENSION3_PROP,

rowresult.N_INTENSION4_PROP,

rowresult.N_INTENSION5_PROP,

rowresult.N_INTENSION1_AVG,

rowresult.N_INTENSION2_AVG,

rowresult.N_INTENSION3_AVG,

rowresult.N_INTENSION4_AVG,

rowresult.N_INTENSION5_AVG,

rowresult.N_AVGRD,

rowresult.N_MAXRD,

rowresult.N_MINRD,

rowresult.N_AVGPB,

rowresult.N_MAXPB,

rowresult.N_MINPB,

rowresult.C_LENGTHGRADE,

'',

'',

'',

rowresult.n_lab || substr(rowresult.c_checkdate, 3, 7) ||

lpad(rowresult.n_serialno, 3, '0'),

'',

'2016',

substr(rowresult.C_FACTORYBATCHNO, 1, 5),

sysdate,

rowresult.C_GRADEMARK,

rowresult.N_GRADEMARK1_PROP,

rowresult.N_GRADEMARK2_PROP,

rowresult.N_GRADEMARK3_PROP,

rowresult.N_GRADEMARK4_PROP,

rowresult.N_GRADEMARK5_PROP,

rowresult.N_GRADEMARK6_PROP,

rowresult.N_GRADEMARK7_PROP,

maxcolorgrade,

maxcolorvalue,

maxmikegrade,

maxmikevalue,

rowresult.n_lab,

maxlenggrade,

maxlengvalue,

'1',

maxgrade,

maxvalue);

commit;

END LOOP;

CLOSE sel_emp; --关闭游标

END;