select sum( ...... ) into Result from tbl_1,...,tbl_2 where bh = p_bh and ..... 在没有记录的时候会出错 应当不会有其他的错误,能说出错误号吗?
select sum( ...... ) into Result from tbl_1,...,tbl_2 where bh = p_bh and ..... 在没有记录的时候会出错 应当不会有其他的错误,能说出错误号吗?
首先我证明 select sum( ...... ) from tbl_1,...,tbl_2 where bh = 'K0001' and .....这条sql语句是正常的,根据不同的bh能返回不同的正常结果。 在function中调用该语句时,用v_bh作为函数参数条件,则select写成 select sum(......) into v_result from ... where bh = v_bh...; 那么用不同的参数调用该function时,应该可以返回不同的结果。 但是我在运行时,其结果不是这样的,我用一个不存在的编号去调用,在外面运行select语句返回的结果是0,而function 运行后返回一个非0的值,该值是另一个bh的sum值.
我用的是select nvl( sum( ...... ),0) into Result from tbl_1,...,tbl_2 where bh = p_bh and ..... 即使没有符合该p_bh条件的记录,它也会返回一个0。function fun_calc( p_bh in char ) return number is Result number :=0; begin select nvl(sum( ...... ),0) into Result from tbl_1,...,tbl_2 where bh = p_bh and .....首先,Result 首先赋值为0,没有记录应该返回0,但实际运行时没有符合条件时返回了一个非0的值,该值是另一个bh的值,我的数据库表中只有一个bh,所以当没有符合条件的记录时,它却返回了数据库中已有的那个bh的汇总值 !!
我用的是select nvl( sum( ...... ),0) into Result from tbl_1,...,tbl_2 where bh = p_bh and ..... 即使没有符合该p_bh条件的记录,它也会返回一个0。function fun_calc( p_bh in char ) return number is Result number :=0; begin select nvl(sum( ...... ),0) into Result from tbl_1,...,tbl_2 where bh = p_bh and .....首先,Result 首先赋值为0,没有记录应该返回0,但实际运行时没有符合条件时返回了一个非0的值,该值是另一个bh的值,我的数据库表中只有一个bh,所以当没有符合条件的记录时,它却返回了数据库中已有的那个bh的汇总值 !!
你写一个异常捕获吧,如果条件不匹配,做系统异常,就不能执行return 语句了
function fun_calc( p_bh in char ) return number is Result number :=0; begin select nvl(sum( ...... ),0) into Result from tbl_1,...,tbl_n where bh = p_bh and ..... end fun_calc 中根据多表的关联求汇总值,原来希望没有符合条件的记录是直接返回 0,其实该select语句在function /procedure之外运行都是可以达到这个目的的。不知为何在function中不能达到该目的。 应该如何做异常捕获处理呢,我的目的是没有符合要求的记录时直接返回0,并且对查询的速度要求非常之高。
-- 票据业务,人民币,主办 function fun_pjyw_rmb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,人民币,协办 function fun_pjyw_rmb_xb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,人民币,接管 function fun_pjyw_rmb_jg ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,外币,主办 function fun_pjyw_wb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,外币,协办 function fun_pjyw_wb_xb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number; -- 票据业务,外币,接管 function fun_pjyw_wb_jg ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 计算客户经理的票据业务收益 function fun_pjyw_calc_by_khjlbh(p_khjlbh_in in m_khjlxx.khjlbh%TYPE,p_y_st_in in char,p_m_st_in in char ,p_d_st_in in char ) return number; end pack_cm_pjyw; create or replace package body pack_cm_pjyw is
v_nlv number; -- 行内转贴现考核年利 v_jgxs number; -- 贷款接管起始系数 v_wbbl number; -- 各外币折算成人民币比率 -- 票据业务,获取参数 procedure proc_pjyw_calc_param is begin select nvl(csz,0) into v_nlv from M_XTCS where csbm = '23'; select nvl(csz,0) into v_jgxs from M_XTCS where csbm = '21'; select nvl(csz,0) into v_wbbl from m_XTCS where csbm = '33';
end proc_pjyw_calc_param;
-- 票据业务,人民币,主办 function fun_pjyw_rmb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number is Result number := 0; begin select round( nvl( sum ( d.fkll * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.zbxs ),0 ) / 360 ,2 ) into Result from d_dkmx d where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and d.khjlbh = khjlbh and nvl(rtrim(d.jgkhjilh),' ') = ' ' ;
/* select nvl( round( sum( ( d.fkll - v_nlv ) * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.zbxs ) / 360 ,2 ),0.00) into Result from d_dkmx d ,d_dkzh z ,d_dkmxbc c where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and d.khjlbh = khjlbh and nvl(rtrim(d.jgkhjilh),' ') = ' ' and d.zh = z.zh and z.hbf = 'RMB' and d.zh = c.zh and d.jkbh = c.jkbh and c.jkmh like '126%' ; */ return Result;
end fun_pjyw_rmb_zb;
-- 票据业务,人民币,协办 function fun_pjyw_rmb_xb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number is Result number :=0 ; begin select nvl( round( sum( ( d.fkll - v_nlv ) * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.xbxs ) / 360 ,2 ),0.00) into Result from d_dkmx d ,d_dkzh z ,d_dkmxbc c where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and d.xbkhjilh = khjlbh and nvl(rtrim(d.jgkhjilh),' ') = ' ' and d.zh = z.zh and z.hbf = 'RMB' and d.zh = c.zh and d.jkbh = c.jkbh and c.jkmh like '126%' ;
return Result;
end fun_pjyw_rmb_xb; -- 1.根据客户经理编号、考核日期,求出各单项考核指标,插入到考核结果中间表中 -- 2.根据各单项考核指标结果,求出总的收益,插入/更新到考核结果表中function fun_pjyw_calc_by_khjlbh(p_khjlbh_in in m_khjlxx.khjlbh%TYPE,p_y_st_in in char,p_m_st_in in char ,p_d_st_in in char ) return number is v_rmb_zb number; v_rmb_xb number; v_rmb_jg number; v_wb_zb number; v_wb_xb number; v_wb_jg number; v_pjyw number;
-- 票据业务,人民币,主办 function fun_pjyw_rmb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number is Result number := 0; begin select round( nvl( sum ( d.fkll * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.zbxs ),0 ) / 360 ,2 ) into Result from d_dkmx d where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and d.khjlbh = khjlbh and nvl(rtrim(d.jgkhjilh),' ') = ' ' ;因为只有该函数有符合条件的值,其他的函数都没有符合条件的值,返回的结果是0,在预期之中,正常,所以重点讨论这个函数。 fun_pjyw_rmb_zb函数中的select语句中只有d.khjlbh = 'M0001'时才有数据,其他的khjlbh返回值都应该是0,因为没有其他的khjlbh. 当然,该select语句在其他地方运行都可以得到预期的结果. 但是该select语句在function中调用时就出错了,当参数khjlbh='M00001'时正常,但当参数khjlbh为'M00002'或其他根本不存在的编号时,该function返回的却是khjlbh='M00001'时的值,而不是我想要的0 . 即是说该函数中的select语句在khjlbh参数没有记录的时候返回了别的符合条件的khjlbh的统计数据(该表中只有khjlbh='M0001'才有记录的)
from tbl_1,...,tbl_2 where bh = p_bh and .....
在没有记录的时候会出错
应当不会有其他的错误,能说出错误号吗?
from tbl_1,...,tbl_2 where bh = p_bh and .....
在没有记录的时候会出错
应当不会有其他的错误,能说出错误号吗?
在function中调用该语句时,用v_bh作为函数参数条件,则select写成
select sum(......) into v_result from ... where bh = v_bh...;
那么用不同的参数调用该function时,应该可以返回不同的结果。 但是我在运行时,其结果不是这样的,我用一个不存在的编号去调用,在外面运行select语句返回的结果是0,而function 运行后返回一个非0的值,该值是另一个bh的sum值.
from tbl_1,...,tbl_2 where bh = p_bh and .....
即使没有符合该p_bh条件的记录,它也会返回一个0。function fun_calc( p_bh in char ) return number is
Result number :=0;
begin
select nvl(sum( ...... ),0) into Result
from tbl_1,...,tbl_2 where bh = p_bh and .....首先,Result 首先赋值为0,没有记录应该返回0,但实际运行时没有符合条件时返回了一个非0的值,该值是另一个bh的值,我的数据库表中只有一个bh,所以当没有符合条件的记录时,它却返回了数据库中已有的那个bh的汇总值 !!
from tbl_1,...,tbl_2 where bh = p_bh and .....
即使没有符合该p_bh条件的记录,它也会返回一个0。function fun_calc( p_bh in char ) return number is
Result number :=0;
begin
select nvl(sum( ...... ),0) into Result
from tbl_1,...,tbl_2 where bh = p_bh and .....首先,Result 首先赋值为0,没有记录应该返回0,但实际运行时没有符合条件时返回了一个非0的值,该值是另一个bh的值,我的数据库表中只有一个bh,所以当没有符合条件的记录时,它却返回了数据库中已有的那个bh的汇总值 !!
Result number :=0;
begin
select nvl(sum( ...... ),0) into Result
from tbl_1,...,tbl_n where bh = p_bh and .....
end fun_calc
中根据多表的关联求汇总值,原来希望没有符合条件的记录是直接返回 0,其实该select语句在function /procedure之外运行都是可以达到这个目的的。不知为何在function中不能达到该目的。 应该如何做异常捕获处理呢,我的目的是没有符合要求的记录时直接返回0,并且对查询的速度要求非常之高。
但是不明白你的问题出在哪里
-- 票据业务,获取参数
procedure proc_pjyw_calc_param;
-- 票据业务,人民币,主办
function fun_pjyw_rmb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,人民币,协办
function fun_pjyw_rmb_xb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,人民币,接管
function fun_pjyw_rmb_jg ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,外币,主办
function fun_pjyw_wb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 票据业务,外币,协办
function fun_pjyw_wb_xb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number; -- 票据业务,外币,接管
function fun_pjyw_wb_jg ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number;
-- 计算客户经理的票据业务收益
function fun_pjyw_calc_by_khjlbh(p_khjlbh_in in m_khjlxx.khjlbh%TYPE,p_y_st_in in char,p_m_st_in in char ,p_d_st_in in char ) return number; end pack_cm_pjyw;
create or replace package body pack_cm_pjyw is
/*
khjlbh m_khjlxx.khjlbh%TYPE; -- 客户经理编号
y_st char(4); -- 年
m_st char(2); -- 月
d_st char(2); -- 日
*/
v_nlv number; -- 行内转贴现考核年利
v_jgxs number; -- 贷款接管起始系数
v_wbbl number; -- 各外币折算成人民币比率
-- 票据业务,获取参数
procedure proc_pjyw_calc_param is begin
select nvl(csz,0) into v_nlv from M_XTCS where csbm = '23';
select nvl(csz,0) into v_jgxs from M_XTCS where csbm = '21';
select nvl(csz,0) into v_wbbl from m_XTCS where csbm = '33';
end proc_pjyw_calc_param;
-- 票据业务,人民币,主办
function fun_pjyw_rmb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number is
Result number := 0;
begin
select round( nvl( sum ( d.fkll * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.zbxs ),0 ) / 360 ,2 )
into Result
from d_dkmx d
where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and
d.khjlbh = khjlbh and
nvl(rtrim(d.jgkhjilh),' ') = ' '
;
/*
select nvl( round( sum( ( d.fkll - v_nlv ) * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.zbxs ) / 360 ,2 ),0.00)
into Result
from d_dkmx d ,d_dkzh z ,d_dkmxbc c
where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and d.khjlbh = khjlbh and nvl(rtrim(d.jgkhjilh),' ') = ' ' and
d.zh = z.zh and
z.hbf = 'RMB' and
d.zh = c.zh and
d.jkbh = c.jkbh and
c.jkmh like '126%'
;
*/
return Result;
end fun_pjyw_rmb_zb;
-- 票据业务,人民币,协办
function fun_pjyw_rmb_xb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number is
Result number :=0 ;
begin
select nvl( round( sum( ( d.fkll - v_nlv ) * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.xbxs ) / 360 ,2 ),0.00)
into Result
from d_dkmx d ,d_dkzh z ,d_dkmxbc c
where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and
d.xbkhjilh = khjlbh and
nvl(rtrim(d.jgkhjilh),' ') = ' ' and d.zh = z.zh and
z.hbf = 'RMB' and
d.zh = c.zh and
d.jkbh = c.jkbh and
c.jkmh like '126%'
;
return Result;
end fun_pjyw_rmb_xb;
-- 1.根据客户经理编号、考核日期,求出各单项考核指标,插入到考核结果中间表中
-- 2.根据各单项考核指标结果,求出总的收益,插入/更新到考核结果表中function fun_pjyw_calc_by_khjlbh(p_khjlbh_in in m_khjlxx.khjlbh%TYPE,p_y_st_in in char,p_m_st_in in char ,p_d_st_in in char ) return number is
v_rmb_zb number;
v_rmb_xb number;
v_rmb_jg number;
v_wb_zb number;
v_wb_xb number;
v_wb_jg number;
v_pjyw number;
begin
/*
khjlbh := p_khjlbh_in;
y_st := p_y_st_in;
m_st := p_m_st_in;
d_st := p_d_st_in;
*/
proc_pjyw_calc_param;
v_rmb_zb := fun_pjyw_rmb_zb(p_khjlbh_in,p_y_st_in,p_m_st_in,p_d_st_in );
v_rmb_xb := fun_pjyw_rmb_xb(p_khjlbh_in,p_y_st_in,p_m_st_in,p_d_st_in );
v_rmb_jg := fun_pjyw_rmb_jg(p_khjlbh_in,p_y_st_in,p_m_st_in,p_d_st_in );
v_wb_zb := fun_pjyw_wb_zb(p_khjlbh_in,p_y_st_in,p_m_st_in,p_d_st_in );
v_wb_xb := fun_pjyw_wb_xb(p_khjlbh_in,p_y_st_in,p_m_st_in,p_d_st_in );
v_wb_jg := fun_pjyw_wb_jg(p_khjlbh_in,p_y_st_in,p_m_st_in,p_d_st_in );
v_pjyw := round( ( v_rmb_zb + v_rmb_xb + v_rmb_jg ) + ( v_wb_zb + v_wb_xb + v_wb_jg ) * v_wbbl , 2 );
return v_pjyw;
end fun_pjyw_calc_by_khjlbh;
end pack_cm_pjyw;
function fun_pjyw_rmb_zb ( khjlbh in m_khjlxx.khjlbh%TYPE, y_st in char,m_st in char ,d_st in char ) return number is
Result number := 0;
begin
select round( nvl( sum ( d.fkll * (d.fkdqr - d.fkrq + 1 ) * d.fkye * d.zbxs ),0 ) / 360 ,2 )
into Result
from d_dkmx d
where d.zjrq = to_date(y_st||'-'||m_st||'-'||d_st ,'yyyy-mm-dd') and d.khjlbh = khjlbh and nvl(rtrim(d.jgkhjilh),' ') = ' ' ;因为只有该函数有符合条件的值,其他的函数都没有符合条件的值,返回的结果是0,在预期之中,正常,所以重点讨论这个函数。
fun_pjyw_rmb_zb函数中的select语句中只有d.khjlbh = 'M0001'时才有数据,其他的khjlbh返回值都应该是0,因为没有其他的khjlbh.
当然,该select语句在其他地方运行都可以得到预期的结果. 但是该select语句在function中调用时就出错了,当参数khjlbh='M00001'时正常,但当参数khjlbh为'M00002'或其他根本不存在的编号时,该function返回的却是khjlbh='M00001'时的值,而不是我想要的0 .
即是说该函数中的select语句在khjlbh参数没有记录的时候返回了别的符合条件的khjlbh的统计数据(该表中只有khjlbh='M0001'才有记录的)