Oracle如何写存储过程？ 在什么地方写以及如何调用？最好给个Sample 急急急！
Oracle如何写存储过程？ 在什么地方写以及如何调用？最好给个Sample 急急急！
[解决办法]
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
[解决办法]
装个pl\sql deveploer吧
里面可以增加存储过程
程序什么的
很好用
[解决办法]
找本oracle的书都有讲写存储过程的，java中的调用方法如下：
CallableStatement cs = connection.prepareCall( "call HisData(?,?,?) ");
// 添加参数
cs.setString(1, str1);
cs.setLong(2, long1);
cs.setLong(3, long2);
// 执行存储过程
cs.execute();
[解决办法]
给你一个例子
create or replace procedure called(vbegintime varchar2,vendtime varchar)
as
vservicename varchar2(64);
vcalledcount varchar2(20);
vcallednum_peo varchar2(20);
vcallednum_peo_15 varchar2(20);
vcalledtimes_sec varchar2(20);
vcalledtimes_min varchar2(20);
vcalledtimes_avg varchar2(20);
vcalledcount_avg varchar2(20);
vcalledcost varchar2(20);
vcalledtimes_sec_tem varchar2(20);
vservicecost varchar2(20);
rows int;
vb boolean;
cursor curservericetype is select * from servicetype;
ee servicetype%rowtype;
begin
open curservericetype;--打开
fetch curservericetype into ee;
while curservericetype%found
loop
-----业务代码------
vservicename:=ee.servicename;
-----拨打次数------
select count(*) into vcalledcount from origcdr where substr(begintime,1,10) > = vbegintime and substr(begintime,1,10) <= vendtime and (callingnumber like '13% ' or callingnumber like '15% ') and callednumber like '10176% ' and servicename=vservicename;
if vcalledcount= '0 ' then ----如果拨打次数为0，其他所有统计均为0---
vcallednum_peo := '0 ';
vcallednum_peo_15 := '0 ';
vcalledtimes_sec := '0 ';
vcalledtimes_min := '0 ';
vcalledtimes_avg := '0 ';
vcalledcount_avg := '0 ';
vcalledcost := '0 ';
else
-----拨打人数------
select count(*) into vcallednum_peo from (select distinct callingnumber from origcdr where substr(begintime,1,10) > = vbegintime and substr(begintime,1,10) <= vendtime and (callingnumber like '13% ' or callingnumber like '15% ') and callednumber like '10176% ' and servicename=vservicename);
-----小于等于15秒拨打人数-------
select count(*) into vcallednum_peo_15 from (select distinct 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) where times <=15;
-----拨打总时长(秒)-------
select sum(times) into vcalledtimes_sec 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)where times> 15;
-----拨打结算累计时长（分）------
--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)) 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 msc=0 and substr(begintime,1,10) > = vbegintime and substr(begintime,1,10) <= vendtime) where times_s> 15 and servicename=vservicename;
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;
