当前位置:我的异常网» Java Web开发 » Oracle怎么写存储过程? 在什么地方写以及怎么调用
Oracle怎么写存储过程? 在什么地方写以及怎么调用?最好给个Sample 急(2)
www.myexceptions.net 网友分享于:2013-03-27 浏览:244次
select sum(times_m) into vcalledtimes_min from (select servicename,ceil(to_char((to_date(endtime, 'yyyy-mm-dd hh24:mi:ss ')-to_date(begintime, 'yyyy-mm-dd hh24:mi:ss '))*24*60-0.25)) as times_m,round(to_char((to_date(endtime, 'yyyy-mm-dd hh24:mi:ss ')-to_date(begintime, 'yyyy-mm-dd hh24:mi:ss '))*24*60*60),0) as times_s from origcdr where (callingnumber like '13% ' or callingnumber like '15% ') and callednumber like '10176% ' and substr(begintime,1,10) > = vbegintime and substr(begintime,1,10) <= vendtime) where times_s> 15 and servicename=vservicename;
-----人均使用时长-----
select sum(times) into vcalledtimes_sec_tem from (select round(to_char((to_date(endtime, 'yyyy-mm-dd hh24:mi:ss ')-to_date(begintime, 'yyyy-mm-dd hh24:mi:ss '))*24*60*60),0) as times from origcdr where (callingnumber like '13% ' or callingnumber like '15% ') and callednumber like '10176% ' and substr(begintime,1,10) > = vbegintime and substr(begintime,1,10) <= vendtime and servicename=vservicename);
vcalledtimes_avg := round(vcalledtimes_sec_tem/vcallednum_peo,2);
-----人均拨打次数-----
vcalledcount_avg := round(vcalledcount/vcallednum_peo,2);
-----预计信息费------
select servicecost into vservicecost from servicetype where servicename=vservicename;
vcalledcost := round(vservicecost*vcalledtimes_min,1);
end if;
insert into called_temp (servicename,calledcount,callednum_peo,callednum_peo_15,calledtimes_sec,calledtimes_min,calledtimes_avg,calledcount_avg,calledcost)
values(vservicename,vcalledcount,vcallednum_peo,vcallednum_peo_15,vcalledtimes_sec,vcalledtimes_min,vcalledtimes_avg,vcalledcount_avg,vcalledcost);
dbms_output.put_line(vservicename);
dbms_output.put_line(vcalledcount);
dbms_output.put_line(vcallednum_peo);
dbms_output.put_line(vcallednum_peo_15);
dbms_output.put_line(vcalledtimes_sec);
dbms_output.put_line(vcalledtimes_min);
dbms_output.put_line(vcalledtimes_avg);
dbms_output.put_line(vcalledcount_avg);
dbms_output.put_line(vcalledcost);
fetch curservericetype into ee;
end loop;
close curservericetype;
end;
文章评论