HWQY油料价格计算存储过程(方便自己用发网上)
来源:互联网 发布:双11淘宝红包口令 编辑:程序博客网 时间:2024/06/11 10:34
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;
- HWQY油料价格计算存储过程(方便自己用发网上)
- DM3730上。android2.3源码编译过程,问题解析。(方便以后自己用)
- 华夏银行发网上测评通知了
- 存储过程的方便与不方便
- 自己用的一个mysql存储过程
- 存储过程计算报表
- 存储过程统计计算
- 存储过程 计算 啤酒
- 计算价格
- 宣布降低Windows Azure 存储和计算的价格
- 微软加入云计算厮杀 存储价格猛降三分之二
- EF 存储过程(上)
- 用AspNetPager和数据存储过程分页真的很方便
- Oracle存储过程详解(自己)
- 自己写的存储过程
- 网络查找文档比自己电脑上还方便?[转帖]
- 用pl/sql自己编写简单的存储过程及jdbc调用存储过程
- 利用SqlServer内部存储过程实现快速方便的分页
- Js事件触发列表与解说
- qt配置vs2005的方法
- 介绍linux上两种rootkits检测工具: Rootkit Hunter和Chkrootkit
- Log4J 在系统运行时更改log4j的配置
- 带有事务集合整理--更新中
- HWQY油料价格计算存储过程(方便自己用发网上)
- 根据日志消息的内容进行日志输出
- c语言标准输入结束符
- C#基础学习日志===>数组<===
- 实现ajax时一些跨浏览器的js方法
- jsp标签jsp:setProperty用法
- 一些序列号
- 用8086汇编写的多线程任务的程序!
- ExecuteScalar为空的烦恼