着急啊,,求大神看看我的存储过程为什么插入数据出现问题。。
对于oracle数据库的存储过程:
create or replace procedure P_VFIND1
(
--时间 字符格式
P_STRATETIME VARCHAR2--将时间的判断放入到代码中
)
AS
w_RESULT NUMBER;
/*定义一个游标存放读取的记录*/
CURSOR cur_read_result IS
SELECT SUBSTR(CREATE_DATE,0,10) as querydate,
sum(case when STEP_RESULT>='010600' AND STEP_RESULT<='010699' THEN 1 else 0 END) as applnumber,
sum(case when STEP_RESULT>='011001'THEN 1 else 0 END) signnumber,
(sum(case when STEP_RESULT>='010600' AND STEP_RESULT<='010699' THEN 1 else 0 END)+sum(case when STEP_RESULT>='011001'THEN 1 else 0 END)) as callallnumber,
sum(case when STEP_RESULT='010699' THEN 1 else 0 END) succapplnumber,
sum(case when STEP_RESULT='011099'THEN 1 else 0 END) succsignnumber,
(sum(case when STEP_RESULT='010699' THEN 1 else 0 END)+sum(case when STEP_RESULT='011099'THEN 1 else 0 END))as succallnumber,
((sum(case when STEP_RESULT='010699' THEN 1 else 0 END)+sum(case when STEP_RESULT='011099'THEN 1 else 0 END))/(sum(case when STEP_RESULT>='010600' AND STEP_RESULT<='010699' THEN 1 else 0 END)+sum(case when STEP_RESULT>='011001'THEN 1 else 0 END))) as connectrate,
(select sum(round(to_number(callend-callbegin)*24*60))/count(*)from TBILLLOG1 @VCM_LINK1 where to_char(callend,'yyyy-MM-dd')=P_STRATETIME ) as averagecalltime ,
(select sum(round(to_number(waitend-waitbegin)*24*60*60))/count(*)from TBILLLOG1 @VCM_LINK1 where to_char(waitend,'yyyy-MM-dd')=P_STRATETIME and waitend-waitbegin>0 ) as averagewaittime
FROM IC_LOAN_REQUEST
WHERE SUBSTR(CREATE_DATE,0,10) =P_STRATETIME
GROUP BY SUBSTR(CREATE_DATE,0,10)
ORDER BY SUBSTR(CREATE_DATE,0,10) ASC;
/* 插入记录函数 */
FUNCTION fnc_record -- 返回0正常,异常为-1
(
p_querydate IN VARCHAR2
, p_applnumber IN VARCHAR2
, p_signnumber IN VARCHAR2
, p_callallnumber IN VARCHAR2
, p_succapplnumber IN VARCHAR2
, p_succsignnumber IN VARCHAR2
, p_succallnumber IN VARCHAR2
, p_connectrate IN VARCHAR2
, p_averagecalltime IN VARCHAR2
, p_averagewaittime IN VARCHAR2
) RETURN NUMBER
IS
BEGIN
INSERT INTO
IC_VIDEO_STAT_REP
( REPNO
, QUERY_DATE --查询的日期
, APPL_NUMBER --申请的数量
, SIGN_NUMBER --签约数量
, CALL_ALL_NUMBER --呼入的总量
, SUCC_APPL_NUMBER --成功申请数量
, SUCC_SIGN_NUMBER --成功签约流程
, SUCC_ALL_NUMBER --成功受理数量
, CONNECT_RATE --接通率
, AVERAGE_CALL_TIME --平均通话时长
, AVERAGE_WAIT_TIME --平均等待时长
)
values
(
SEQUENCE_ID.nextval
, p_querydate
, p_applnumber
, p_signnumber
, p_callallnumber
, p_succapplnumber
, p_succsignnumber
, p_succallnumber
, p_connectrate
, p_averagecalltime
, p_averagewaittime
);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END fnc_record;
/*执行函数*/
FUNCTION fnc_excute
RETURN NUMBER
IS
w_RES NUMBER := 0;
BEGIN
IF w_RES = 0 THEN
FOR rec IN cur_read_result
LOOP
w_RES := fnc_record(
rec.querydate
, rec.applnumber
, rec.signnumber
, rec.callallnumber
, rec.succapplnumber
, rec.succsignnumber
, rec.succallnumber
,rec.connectrate
,rec.averagecalltime
,rec.averagewaittime
);
EXIT WHEN w_RES != 0;
END LOOP;
END IF;
RETURN w_RES;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END fnc_excute;
BEGIN
w_RESULT := 0;
IF w_RESULT = 0 THEN
w_RESULT := fnc_excute;
END IF;
IF w_RESULT != 0 THEN
ROLLBACK;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END P_VFIND1;
函数为:
CREATE OR REPLACE FUNCTION F_VCM_VALIDATEDATE
(
i_StartDate VARCHAR2
) RETURN INTEGER
AS
v_Ret NUMBER;
BEGIN
IF (i_StartDate IS NULL) THEN
RETURN 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_Ret := 1;
RETURN v_Ret;
END F_VCM_VALIDATEDATE;
我出现的问题是存储过程编译通过了,但是运行后没有数据库中没有插入数据,但是,SEQUENCE_ID.nextval已经自增“1”了,
我的运行过程为:
proc = conn.prepareCall("{ Call P_VFIND1(?) }");
proc.setString(1, "2016-01-05");
proc.execute();
麻烦大神解决一下。。。谢谢