oracle 中 Function 传参数 查询传参数表总数，返回 总数

create or replace function GET_COUNT_ZS
(deptcode  in varchar2, tablename in varchar2,bs in varchar2)
return  varchar2
is 
num varchar2(1000);
c number;
begin
if(bs=0) then 
num:='select count(*) from '||tablename||' where  deptcode like '''||deptcode||'%''';
end if;
if(bs=1) then
num:='select count(*) from '||tablename||' where  deptcode like '''||deptcode||'%'''||' and  SFDTGL=1' ;
end if;
execute immediate num into   c;
return c;
end;

  

第二个 ：

create or replace function GET_COU_SYQK
(deptcode  in varchar2, SYZT in varchar2)
return  varchar2
is
num varchar2(1000);
c number;
begin
num:='select count(*) from t_fwcq where  deptcode like '''||deptcode||'%'' and sszthz = '''||SYZT||'''';
execute immediate num into   c;
return c;
end;

调用的sql 语句 ：

 var stb = new StringBuilder();
            stb.Append("select ");
            stb.Append("GET_COUNT_ZS('" + deptcode + "','t_fwzc',0) as countzc ");
            stb.Append(",GET_COUNT_ZS('" + deptcode + "','t_fwcq',0) as countfw ");
            stb.Append(",GET_COUNT_ZS('" + deptcode + "','zc_dt',0) as countdt ");
            stb.Append(",GET_COUNT_ZS('" + deptcode + "','t_fwzc',1) as countygl ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','自用') as countzy ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','闲置') as countxz ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','出租') as countcz ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','占用') as countzhany ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','出售') as countcs ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','盘亏') as countpk ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','封存') as countfc ");
            stb.Append(",GET_COU_SYQK('" + deptcode + "','其他') as countyqt ");
            stb.Append("from dual ");

转载于:https://www.cnblogs.com/smile-wei/archive/2012/10/31/2747728.html