以下是完整的函数和在存储过程中的调用:
create or replace function f_get__tzcx_lx(
jdxzin in varchar2,
swsin in varchar2,
glyin in varchar2,
hydlin in varchar2,
hyxlin in varchar2,
tjrq_q in date,
tjrq_z in date,
czdm in varchar2
) return number as
--根据税务所代码,管理员代码,行业大类,行业细类统计利息收入
numb NUMBER(12, 2);
c_sql varchar2(1500);
tmpjdxz varchar2(20); --街道乡镇
tmpsws varchar2(20); --税务所
tmpgly varchar2(20); --管理员
tmphydl varchar2(20); --行业大类
tmphyxl varchar2(20); --行业细类
begin
numb:=0.0;
tmpjdxz:=jdxzin;
tmpsws:=swsin;
tmpgly:=glyin;
tmphydl:=hydlin;
tmphyxl:=hyxlin;
c_sql:='SELECT sb_lxsds_bgb.lxe_hj FROM sb_lxsds_bgb, dj_nsrxx,dj_nsrxx_kz WHERE sb_lxsds_bgb.nsrdzdah = dj_nsrxx.nsrdzdah and dj_nsrxx.nsrdzdah =dj_nsrxx_kz.nsrdzdah
and dj_nsrxx_kz.nsrdzdah=sb_lxsds_bgb.nsrdzdah and cxckjg_dm = :1 and sssq_q >= :2 and sssq_z <= :3 ';
if (jdxzin is not null) then
c_sql := c_sql || ' and dj_nsrxx.jdxz_dm = ' || tmpjdxz;
end if;
if (swsin is not null) then
c_sql := c_sql || ' and dj_nsrxx.NSR_SWJG_DM=' || tmpsws;
end if;
if (glyin is not null) then
c_sql := c_sql || ' and dj_nsrxx.dj_nsrxx.zgswry_dm=' || tmpgly;
end if;
if (hydlin is not null) then
c_sql := c_sql || ' and dj_nsrxx.hy_dm =' || tmphydl;
end if;
if (hyxlin is not null) then
c_sql := c_sql || ' and dj_nsrxx_kz.hymx_dm =' || tmphyxl;
end if;
c_sql:=c_sql||' ORDER BY sb_lxsds_bgb.sssq_q ASC;';
execute immediate c_sql into numb using czdm,tjrq_q,tjrq_z;
return numb;
end;
create or replace procedure P_GRSDS_TZ_ALL(
yearin in varchar2,
jdxzin in varchar2,
swsin in varchar2,
glyin in varchar2,
hydlin in varchar2,
hyxlin in varchar2,
nsrsbh in varchar2
)
AS
year_var varchar2(10);
tmpjdxz varchar2(20); --街道乡镇
tmpsws varchar2(20); --税务所
tmpgly varchar2(20); --管理员
tmphydl varchar2(20); --行业大类
tmphyxl varchar2(20); --行业细类
tmpnsrsbh varchar2(20);
date_q DATE;
date_z DATE;
SumTmp NUMBER(10, 2); --每月税款统计
BEGIN
tmpnsrsbh := nsrsbh;
year_var := yearin;
tmpjdxz :=jdxzin;
tmpsws := swsin;
tmpgly :=glyin;
tmphydl :=hydlin;
tmphyxl :=hyxlin;
date_q :=to_date('2005-12-01','yyyy-mm-dd');
date_z :=to_date('2006-03-31','yyyy-mm-dd');
sumTmp :=0.0;
sumTmp := f_get__tzcx_lx(tmpjdxz ,tmpsws ,tmpgly,tmphydl ,tmphyxl,date_q ,date_z,'100');
END P_GRSDS_TZ_ALL; --end of Procedure
系统报函数 execute immediate c_sql into numb using czdm,tjrq_q,tjrq_z; 一句有无效字符,
请帮忙解答,谢谢.