create or replace procedure p_t_tj_a
is
t_carworkid number;
t_oil1 number;
t_oil2 number;
t_oil3 number;
t_oil4 number;
t_oil5 number;
t_oil6 number;
t_price1 number;
t_price2 number;
t_price3 number;
t_price4 number;
t_price5 number;
t_price6 number;
t_out_date string(100);
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
BEGIN
strSql1:='select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add1=0 and a.oil_price_all1<>0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add2=0 and a.oil_price_all2<>0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add3=0 and a.oil_price_all3<>0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add4=0 and a.oil_price_all4<>0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add5=0 and a.oil_price_all5<>0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add6=0 and a.oil_price_all6<>0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add6<>0 and a.oil_price_all6=0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add5<>0 and a.oil_price_all5=0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add4<>0 and a.oil_price_all4=0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add3<>0 and a.oil_price_all3=0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add2<>0 and a.oil_price_all2=0 '||
'union select carworkid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carwork a where a.oil_add1<>0 and a.oil_price_all1=0';
OPEN CUR_1 FOR strSql1;--打开bill_tj_out的动态游标
LOOP
FETCH CUR_1 INTO t_carworkid,t_oil1,t_oil2,t_oil3,t_oil4,t_oil5,t_oil6,t_out_date;
EXIT WHEN CUR_1%NOTFOUND;
select nvl(price_oil1,0) into t_price1
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil2,0) into t_price2
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil3,0) into t_price3
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil4,0) into t_price4
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil5,0) into t_price5
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil6,0) into t_price6
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
update t_carwork s set oil_price_all1=oil_add1*t_price1,oil_price_all2=oil_add2*t_price2,oil_price_all3=oil_add3*t_price3,
oil_price_all4=oil_add4*t_price4,oil_price_all5=oil_add5*t_price5,oil_price_all6=oil_add6*t_price6
where carworkid=t_carworkid;
commit;
end loop;
end p_t_tj_a;
create or replace procedure p_t_tj_b
is
t_carworkid number;
t_oil1 number;
t_oil2 number;
t_oil3 number;
t_oil4 number;
t_oil5 number;
t_oil6 number;
t_price1 number;
t_price2 number;
t_price3 number;
t_price4 number;
t_price5 number;
t_price6 number;
t_out_date string(100);
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
BEGIN
strSql1:='select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add1=0 and a.oil_price_all1<>0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add2=0 and a.oil_price_all2<>0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add3=0 and a.oil_price_all3<>0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add4=0 and a.oil_price_all4<>0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add5=0 and a.oil_price_all5<>0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add6=0 and a.oil_price_all6<>0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add6<>0 and a.oil_price_all6=0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add5<>0 and a.oil_price_all5=0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add4<>0 and a.oil_price_all4=0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add3<>0 and a.oil_price_all3=0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add2<>0 and a.oil_price_all2=0 '||
'union select workoutid,oil_add1,oil_add2,oil_add3,oil_add4,oil_add5,oil_add6,to_char(out_date,''yyyy-mm-dd hh24:mi:ss'') from t_carworkout a where a.oil_add1<>0 and a.oil_price_all1=0';
OPEN CUR_1 FOR strSql1;--打开bill_tj_out的动态游标
LOOP
FETCH CUR_1 INTO t_carworkid,t_oil1,t_oil2,t_oil3,t_oil4,t_oil5,t_oil6,t_out_date;
EXIT WHEN CUR_1%NOTFOUND;
select nvl(price_oil1,0) into t_price1
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil2,0) into t_price2
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil3,0) into t_price3
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil4,0) into t_price4
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil5,0) into t_price5
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
select nvl(price_oil6,0) into t_price6
from t_oil_add_price
where to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')>=start_time
and to_date(t_out_date,'yyyy-mm-dd hh24:mi:ss')<=End_time;
update t_carworkout s set oil_price_all1=oil_add1*t_price1,oil_price_all2=oil_add2*t_price2,oil_price_all3=oil_add3*t_price3,
oil_price_all4=oil_add4*t_price4,oil_price_all5=oil_add5*t_price5,oil_price_all6=oil_add6*t_price6
where workoutid=t_carworkid;
commit;
end loop;
end p_t_tj_b;