第一部分,存储过程代码
create or replace package pack_type
as
type overspeed_array is table of varchar2(500) index by binary_integer;
end pack_type;
CREATE OR REPLACE PROCEDURE sp_overspeed(v_serial varchar2,v_begintime varchar2,
v_endtime varchar2,n_speed number,n_runtime number,v_speed out pack_type.overspeed_array)
IS/*
creator:lijin
create_time:2009-08-14
function:统计超速超时明细数据
*/
--参数声明 ln_overspeed_count NUMBER(10); --超速超时结果集的记录数
i number(10); --循环变量
ln_time number(10);
lv_temp1 varchar2(20);
lv_temp2 varchar2(20);
ld_temp1 date;
ld_temp2 date;
TYPE REC_OVERSPEED IS RECORD(SERIAL VARCHAR2(50),BEGIN_TIME DATE,END_TIME DATE,
MAX_SPEED VARCHAR2(10),BEGIN_MILEAGE VARCHAR2(10),END_MILEAGE VARCHAR2(10) ); TYPE REC_SPEEDOVER_INS IS TABLE OF REC_OVERSPEED INDEX BY BINARY_INTEGER; overspeed REC_SPEEDOVER_INS; --超速轨迹记录数组
CURSOR c_peed IS SELECT sendtime,velocity,mileage FROM locationmsg
WHERE serial = v_serial
and sendtime >= to_date(v_begintime,'yyyy-mm-dd hh24:mi:ss')
and sendtime <= to_date(v_endtime,'yyyy-mm-dd hh24:mi:ss') order by sendtime;
BEGIN -- v_speed := pack_type.overspeed_array();
--1、入参合法性判断
IF v_serial IS NULL or n_speed is null THEN
RETURN ;
END IF; DBMS_OUTPUT.PUT_LINE(v_serial||v_begintime||v_endtime||to_number(n_speed)||to_char(n_runtime)); ln_overspeed_count := 1;
overspeed(ln_overspeed_count).SERIAL := '';
overspeed(ln_overspeed_count).BEGIN_TIME := null;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := '';
overspeed(ln_overspeed_count).MAX_SPEED := '';
overspeed(ln_overspeed_count).END_TIME := null;
overspeed(ln_overspeed_count).END_MILEAGE := '';
DBMS_OUTPUT.PUT_LINE('开始:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')); FOR c1 IN c_peed LOOP
if to_number(c1.velocity) >= n_speed then --超速开始
-- DBMS_OUTPUT.PUT_LINE('开始超速');
if overspeed(ln_overspeed_count).BEGIN_TIME is null then --是否首次出现超速
overspeed(ln_overspeed_count).SERIAL := v_serial;
overspeed(ln_overspeed_count).BEGIN_TIME := c1.sendtime;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := c1.mileage;
overspeed(ln_overspeed_count).MAX_SPEED := c1.velocity;
else
--判断持续超速的时长是否符合条件
-- DBMS_OUTPUT.PUT_LINE(to_char(c1.sendtime,'yyyy-mm-dd hh24:mi:ss'));
-- DBMS_OUTPUT.PUT_LINE(to_char(overspeed(ln_overspeed_count).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss'));
lv_temp1 := to_char(c1.sendtime,'yyyy-mm-dd hh24:mi:ss');
lv_temp2 := to_char(overspeed(ln_overspeed_count).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss');
ld_temp1 := to_date(lv_temp1,'yyyy-mm-dd hh24:mi:ss');
ld_temp2 := to_date(lv_temp2,'yyyy-mm-dd hh24:mi:ss');
ln_time := floor(to_number(ld_temp1 - ld_temp2)*24*60);
if ln_time >= n_runtime then
overspeed(ln_overspeed_count).END_TIME := c1.sendtime;
overspeed(ln_overspeed_count).END_MILEAGE := c1.mileage;
--更新最大速度
if to_number(c1.velocity) > to_number(overspeed(ln_overspeed_count).MAX_SPEED) then
overspeed(ln_overspeed_count).MAX_SPEED := c1.velocity;
end if;
end if;
end if;
else
--超速结束的处理,不符合超时的清空内容;符合的形成超速记录,次数加1;
if (overspeed(ln_overspeed_count).BEGIN_TIME is null) then
overspeed(ln_overspeed_count).SERIAL := null;
overspeed(ln_overspeed_count).BEGIN_TIME := null;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := null;
overspeed(ln_overspeed_count).MAX_SPEED := null;
overspeed(ln_overspeed_count).END_TIME := null;
overspeed(ln_overspeed_count).END_MILEAGE := null;
end if;
if (overspeed(ln_overspeed_count).BEGIN_TIME is not null) and (overspeed(ln_overspeed_count).END_TIME is not null) then
ln_overspeed_count := ln_overspeed_count + 1;
overspeed(ln_overspeed_count).SERIAL := '';
overspeed(ln_overspeed_count).BEGIN_TIME := null;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := '';
overspeed(ln_overspeed_count).MAX_SPEED := '';
overspeed(ln_overspeed_count).END_TIME := null;
overspeed(ln_overspeed_count).END_MILEAGE := '';
end if; end if;
END LOOP; --实际结果打印
DBMS_OUTPUT.PUT_LINE('结束:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'@@'||to_char(ln_overspeed_count));
FOR i IN 1.. ln_overspeed_count -1 LOOP
if overspeed(i).SERIAL is not null then
/*
DBMS_OUTPUT.PUT_LINE(to_char(i)||'****************');
DBMS_OUTPUT.PUT_LINE(overspeed(i).SERIAL||'@@'||to_char(overspeed(i).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).BEGIN_MILEAGE);
DBMS_OUTPUT.PUT_LINE(overspeed(i).MAX_SPEED||'@@'||to_char(overspeed(i).END_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).END_MILEAGE);
*/
v_speed(i) := overspeed(i).SERIAL||'@@'||to_char(overspeed(i).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).BEGIN_MILEAGE||'@@'||overspeed(i).MAX_SPEED||'@@'||to_char(overspeed(i).END_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).END_MILEAGE; end if; END LOOP;
--测试输出结果
/*
i := 1;
while i <= v_speed.count loop
dbms_output.put_line(v_speed(i));
i := i + 1;
end loop;
*/
return ;
END;
第二部分:JAVA调用片段 Connection conn = this.getSession().connection();
CallableStatement st = conn
.prepareCall("{CALL MSGSERVER.SP_OVERSPEED(?,?,?,?,?,?)}");
st.setString(1, serial);
st.setString(2, begintime);
st.setString(3, endtime);
st.setDouble(4, speed);
st.setDouble(5, runtime);
st.registerOutParameter(6, OracleTypes.ARRAY,"OVERSPEED_ARRAY");
st.execute();报错提示找不到"OVERSPEED_ARRAY" 找不到解决办法啊,各位大侠救救小弟
create or replace package pack_type
as
type overspeed_array is table of varchar2(500) index by binary_integer;
end pack_type;
CREATE OR REPLACE PROCEDURE sp_overspeed(v_serial varchar2,v_begintime varchar2,
v_endtime varchar2,n_speed number,n_runtime number,v_speed out pack_type.overspeed_array)
IS/*
creator:lijin
create_time:2009-08-14
function:统计超速超时明细数据
*/
--参数声明 ln_overspeed_count NUMBER(10); --超速超时结果集的记录数
i number(10); --循环变量
ln_time number(10);
lv_temp1 varchar2(20);
lv_temp2 varchar2(20);
ld_temp1 date;
ld_temp2 date;
TYPE REC_OVERSPEED IS RECORD(SERIAL VARCHAR2(50),BEGIN_TIME DATE,END_TIME DATE,
MAX_SPEED VARCHAR2(10),BEGIN_MILEAGE VARCHAR2(10),END_MILEAGE VARCHAR2(10) ); TYPE REC_SPEEDOVER_INS IS TABLE OF REC_OVERSPEED INDEX BY BINARY_INTEGER; overspeed REC_SPEEDOVER_INS; --超速轨迹记录数组
CURSOR c_peed IS SELECT sendtime,velocity,mileage FROM locationmsg
WHERE serial = v_serial
and sendtime >= to_date(v_begintime,'yyyy-mm-dd hh24:mi:ss')
and sendtime <= to_date(v_endtime,'yyyy-mm-dd hh24:mi:ss') order by sendtime;
BEGIN -- v_speed := pack_type.overspeed_array();
--1、入参合法性判断
IF v_serial IS NULL or n_speed is null THEN
RETURN ;
END IF; DBMS_OUTPUT.PUT_LINE(v_serial||v_begintime||v_endtime||to_number(n_speed)||to_char(n_runtime)); ln_overspeed_count := 1;
overspeed(ln_overspeed_count).SERIAL := '';
overspeed(ln_overspeed_count).BEGIN_TIME := null;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := '';
overspeed(ln_overspeed_count).MAX_SPEED := '';
overspeed(ln_overspeed_count).END_TIME := null;
overspeed(ln_overspeed_count).END_MILEAGE := '';
DBMS_OUTPUT.PUT_LINE('开始:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')); FOR c1 IN c_peed LOOP
if to_number(c1.velocity) >= n_speed then --超速开始
-- DBMS_OUTPUT.PUT_LINE('开始超速');
if overspeed(ln_overspeed_count).BEGIN_TIME is null then --是否首次出现超速
overspeed(ln_overspeed_count).SERIAL := v_serial;
overspeed(ln_overspeed_count).BEGIN_TIME := c1.sendtime;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := c1.mileage;
overspeed(ln_overspeed_count).MAX_SPEED := c1.velocity;
else
--判断持续超速的时长是否符合条件
-- DBMS_OUTPUT.PUT_LINE(to_char(c1.sendtime,'yyyy-mm-dd hh24:mi:ss'));
-- DBMS_OUTPUT.PUT_LINE(to_char(overspeed(ln_overspeed_count).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss'));
lv_temp1 := to_char(c1.sendtime,'yyyy-mm-dd hh24:mi:ss');
lv_temp2 := to_char(overspeed(ln_overspeed_count).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss');
ld_temp1 := to_date(lv_temp1,'yyyy-mm-dd hh24:mi:ss');
ld_temp2 := to_date(lv_temp2,'yyyy-mm-dd hh24:mi:ss');
ln_time := floor(to_number(ld_temp1 - ld_temp2)*24*60);
if ln_time >= n_runtime then
overspeed(ln_overspeed_count).END_TIME := c1.sendtime;
overspeed(ln_overspeed_count).END_MILEAGE := c1.mileage;
--更新最大速度
if to_number(c1.velocity) > to_number(overspeed(ln_overspeed_count).MAX_SPEED) then
overspeed(ln_overspeed_count).MAX_SPEED := c1.velocity;
end if;
end if;
end if;
else
--超速结束的处理,不符合超时的清空内容;符合的形成超速记录,次数加1;
if (overspeed(ln_overspeed_count).BEGIN_TIME is null) then
overspeed(ln_overspeed_count).SERIAL := null;
overspeed(ln_overspeed_count).BEGIN_TIME := null;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := null;
overspeed(ln_overspeed_count).MAX_SPEED := null;
overspeed(ln_overspeed_count).END_TIME := null;
overspeed(ln_overspeed_count).END_MILEAGE := null;
end if;
if (overspeed(ln_overspeed_count).BEGIN_TIME is not null) and (overspeed(ln_overspeed_count).END_TIME is not null) then
ln_overspeed_count := ln_overspeed_count + 1;
overspeed(ln_overspeed_count).SERIAL := '';
overspeed(ln_overspeed_count).BEGIN_TIME := null;
overspeed(ln_overspeed_count).BEGIN_MILEAGE := '';
overspeed(ln_overspeed_count).MAX_SPEED := '';
overspeed(ln_overspeed_count).END_TIME := null;
overspeed(ln_overspeed_count).END_MILEAGE := '';
end if; end if;
END LOOP; --实际结果打印
DBMS_OUTPUT.PUT_LINE('结束:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'@@'||to_char(ln_overspeed_count));
FOR i IN 1.. ln_overspeed_count -1 LOOP
if overspeed(i).SERIAL is not null then
/*
DBMS_OUTPUT.PUT_LINE(to_char(i)||'****************');
DBMS_OUTPUT.PUT_LINE(overspeed(i).SERIAL||'@@'||to_char(overspeed(i).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).BEGIN_MILEAGE);
DBMS_OUTPUT.PUT_LINE(overspeed(i).MAX_SPEED||'@@'||to_char(overspeed(i).END_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).END_MILEAGE);
*/
v_speed(i) := overspeed(i).SERIAL||'@@'||to_char(overspeed(i).BEGIN_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).BEGIN_MILEAGE||'@@'||overspeed(i).MAX_SPEED||'@@'||to_char(overspeed(i).END_TIME,'yyyy-mm-dd hh24:mi:ss')||'@@'||overspeed(i).END_MILEAGE; end if; END LOOP;
--测试输出结果
/*
i := 1;
while i <= v_speed.count loop
dbms_output.put_line(v_speed(i));
i := i + 1;
end loop;
*/
return ;
END;
第二部分:JAVA调用片段 Connection conn = this.getSession().connection();
CallableStatement st = conn
.prepareCall("{CALL MSGSERVER.SP_OVERSPEED(?,?,?,?,?,?)}");
st.setString(1, serial);
st.setString(2, begintime);
st.setString(3, endtime);
st.setDouble(4, speed);
st.setDouble(5, runtime);
st.registerOutParameter(6, OracleTypes.ARRAY,"OVERSPEED_ARRAY");
st.execute();报错提示找不到"OVERSPEED_ARRAY" 找不到解决办法啊,各位大侠救救小弟
解决方案 »
- java.lang.NoClassDefFoundError:java/sql/SQLClientInfoException
- 跪求牛人做个设计方案
- struts2的类型转换,从页面传Map集合到Action
- 关于java应用服务器的困惑
- cvs 如何查看自己刚提交过哪些内容
- 如何使滚动条停在最底下?
- 如何在struts框架中实现JDBC数据源连接Oracle数据库
- 为什么Tomcat服务器的地址输入的大小写要与页面名的大小写一样才能访问?
- 高分跪求:applet访问数据库解决方法,急!!!!
- 怎么将huffman编码的图片转换为byte[]
- 描述spring+hibernate+struts三者合用的流程
- Tomcat5.0连接数据源问题
p_cardsuitcode in varchar,
p_userseqidArr out USERSEQID_ARRAY ,
p_usernameArr out USERNAME_ARRAY
)
IS
v_addedtime date:= sysdate;BEGIN