oracle 中 Function 傳參數 查詢傳參數表總數，返回 總數

create or replace function GET_COUNT_ZS(deptcode  in varchar2, tablename in varchar2,bs in varchar2)return  varchar2is num varchar2(1000);c number;beginif(bs=0) then num:='select count(*) from '||tablename||' where  deptcode like '''||deptcode||'%''';end if;if(bs=1) thennum:='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  varchar2isnum varchar2(1000);c number;beginnum:='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 ");