今天對(duì)之前寫(xiě)的Orcale存儲(chǔ)過(guò)程做了一些優(yōu)化，使其變得更加靈活，之前寫(xiě)的存儲(chǔ)過(guò)程是使用游標(biāo)存儲(chǔ)SQL執(zhí)行結(jié)果，但是使用游標(biāo)帶來(lái)的弊端是，在聲明時(shí)，它所執(zhí)行的SQL就已經(jīng)被寫(xiě)死，無(wú)法修改。若想更加靈活，必須將使用游標(biāo)這種策略舍棄掉，轉(zhuǎn)而使用在“過(guò)程”中定義SQL語(yǔ)句的寫(xiě)法：主要思路是利用execute immediate SQL語(yǔ)句 into 自定義成員變量;執(zhí)行并存儲(chǔ)SQL執(zhí)行后的結(jié)果（單個(gè)的），接下來(lái)就是拼接這條SQL語(yǔ)句，和JAVA里的SQL拼接套路完全一致。

之前使用游標(biāo)的存儲(chǔ)過(guò)程，只能執(zhí)行代碼中寫(xiě)好的SQL語(yǔ)句：

create or replace procedure proc_g_normalcount(GSYSYW OUT varchar2,GSWSYY OUT varchar2,SBHBTMCT OUT varchar2,SBHTMCBT OUT varchar2,SBHMCXT OUT varchar2,GDSBHCF OUT varchar2,GDMCCF OUT varchar2) as
cursor c_postype is
    SELECT COUNT(*) FROM TAX_INFO_GD  WHERE (SELECT COUNT(1) as num  from TAX_INFO  WHERE A.NSR = B.NAME  AND A.SHXYDM = B.SH_ID) = 0 AND B.STATE = '正常'  AND (B.SYLX = '1' OR B.SYLX = '3');
  
cursor c_postype1 is 
    SELECT COUNT(*) FROM TAX_INFO B WHERE (SELECT COUNT(1) as num    from TAX_INFO_GD A   WHERE A.NAME = B.NSR  AND A.SH_ID = B.SHXYDM) = 0  AND B.STATE = '正常';
  cursor c_postype2 is select  COUNT(NAME)  from (select TAX_INFO_GD.NAME, TAX_INFO.NSR  TAX_INFO_GD.SH_ID,  TAX_INFO.SHXYDM,  TAX_INFO_GD.SYLX,  TAX_INFO_GD.JDXZ,  TAX_INFO_GD.STATE as GDSTATE,  TAX_INFO.STATE as SYSTATE   FROM TAX_INFO_GD  INNER JOIN TAX_INFO  ON TAX_INFO_GD.NAME = TAX_INFO.NSR)  where SH_ID <> SHXYDM   and (SYLX = 1 OR SYLX = 3) and GDSTATE='正常'  and SYSTATE='正常';  cursor c_postype3 is   select COUNT(SH_ID)    from (select TAX_INFO_GD.NAME,   TAX_INFO.NSR,  TAX_INFO_GD.SH_ID,  TAX_INFO.SHXYDM,  TAX_INFO_GD.SYLX,   TAX_INFO_GD.JDXZ, TAX_INFO_GD.STATE as GDSTATE, TAX_INFO.STATE as SYSTATE   FROM TAX_INFO_GD   INNER JOIN TAX_INFO  ON TAX_INFO_GD.SH_ID = TAX_INFO.SHXYDM)   where NAME <> NSR    and (SYLX = 1 OR SYLX = 3)  and GDSTATE='正常'  and SYSTATE='正常';

cursor c_postype4 is
    select COUNT(NAME)  from (select TAX_INFO_GD.NAME,  TAX_INFO.NSR,  TAX_INFO_GD.SH_ID,  TAX_INFO.SHXYDM,  TAX_INFO_GD.SYLX,  TAX_INFO_GD.JDXZ,  TAX_INFO_GD.STATE as GDSTATE,  TAX_INFO.STATE as SYSTATE  FROM TAX_INFO_GD  INNER JOIN TAX_INFO  ON TAX_INFO_GD.NAME = TAX_INFO.NSR  AND TAX_INFO_GD.SH_ID = TAX_INFO.SHXYDM  AND (SYLX = 1 OR SYLX = 3)) where GDSTATE='正常'  and SYSTATE='正常';  

cursor c_postype5 is
    SELECT COUNT(*) FROM (SELECT SH_ID   FROM TAX_INFO_GD A  WHERE SH_ID IN (SELECT SH_ID   FROM TAX_INFO_GD B   WHERE A.NAME <> B.NAME  AND (A.SYLX = 1 OR A.SYLX = 3) AND (B.SYLX = 1 OR B.SYLX = 3))  AND STATE = '正常'  GROUP BY SH_ID); 

cursor c_postype6 is  
    SELECT COUNT(*)   FROM (SELECT NAME FROM TAX_INFO_GD A  WHERE NAME IN (SELECT NAME  FROM TAX_INFO_GD B   WHERE A.SH_ID <> B.SH_ID  AND (A.SYLX = 1 OR A.SYLX = 3)  AND (B.SYLX = 1 OR B.SYLX = 3))  AND STATE = '正常'  GROUP BY NAME);
begin
  open c_postype;
  fetch c_postype into GSYSYW;
  close c_postype;

  open c_postype1;
  fetch c_postype1 into GSWSYY;
  close c_postype1;
  
  open c_postype2;
  fetch c_postype2 into SBHBTMCT;
  close c_postype2;
  
  open c_postype3;
  fetch c_postype3 into SBHTMCBT;
  close c_postype3;
  
  open c_postype4;
  fetch c_postype4 into SBHMCXT;
  close c_postype4;
  
  open c_postype5;
  fetch c_postype5 into GDSBHCF;
  close c_postype5;
  
  open c_postype6;
  fetch c_postype6 into GDMCCF;
  close c_postype6;
end;

改進(jìn)后的存儲(chǔ)過(guò)程，多加入兩個(gè)傳入?yún)?shù)USERRIGHT，TAXTYPE ，根據(jù)USERRIGHT，TAXTYPE 返回對(duì)應(yīng)的數(shù)據(jù)條目數(shù)：

create or replace procedure proc_d_normalcount(
                                             USERRIGHT IN varchar2,
                                             TAXTYPE IN varchar2,
                                             GDSYSYW OUT varchar2,
                                             GDSWSYY OUT varchar2,
                                             SBHBTMCT OUT varchar2,
                                             SBHTMCBT OUT varchar2,
                                             SBHMCXT OUT varchar2,
                                             GDSBHCF OUT varchar2,
                                             GDMCCF OUT varchar2) as

  s_sql1 varchar2(1000) := '';
  s_sql2 varchar2(1000) := '';
  s_sql3 varchar2(1000) := '';
  s_sql4 varchar2(1000) := '';
  s_sql5 varchar2(1000) := '';
  s_sql6 varchar2(1000) := '';
  s_sql7 varchar2(1000) := '';
  
  v_urstr varchar2(100) := '';
  v_urstr2 varchar2(100) := '';
 
begin

  if USERRIGHT='0' then 
     v_urstr := ' AND 0=1';
     v_urstr2 := ' AND 0=1';
  else if USERRIGHT='1' then
     v_urstr := ' AND 1=1';
     v_urstr2 := ' AND 1=1';
  else 
    v_urstr := ' AND B.JDXZ='''||USERRIGHT||'''';
    v_urstr2 := ' AND JDXZ='''||USERRIGHT||'''';
  end if;
  end if;   
     
  
  --國(guó)或地稅有稅源無(wú)
  s_sql1 := 'SELECT COUNT(*) FROM TAX_INFO_GD B WHERE (SELECT COUNT(1) as num from TAX_INFO A WHERE A.NSR = B.NAME AND A.SHXYDM = B.SH_ID) = 0 AND B.STATE = ''正常'' AND (B.SYLX = '''||TAXTYPE||''' OR B.SYLX =''3'')'||v_urstr;           
  execute immediate s_sql1 into GDSYSYW; 

  --國(guó)或地稅無(wú)稅源有
  s_sql2 := 'SELECT COUNT(*) FROM TAX_INFO B WHERE (SELECT COUNT(1) as num from TAX_INFO_GD A WHERE A.NAME = B.NSR AND A.SH_ID = B.SHXYDM) = 0 AND B.STATE = ''正常''';
  execute immediate s_sql2 into GDSWSYY; 
  
  --識(shí)別號(hào)不同名稱同
  s_sql3 := 'select COUNT(NAME) from (select TAX_INFO_GD.NAME,TAX_INFO.NSR,TAX_INFO_GD.SH_ID,TAX_INFO.SHXYDM,TAX_INFO_GD.SYLX,TAX_INFO_GD.JDXZ,TAX_INFO_GD.STATE as GDSTATE,TAX_INFO.STATE as SYSTATE FROM TAX_INFO_GD INNER JOIN TAX_INFO ON TAX_INFO_GD.NAME = TAX_INFO.NSR) B where SH_ID <> SHXYDM  and (SYLX = '||TAXTYPE||' OR SYLX = 3) and GDSTATE=''正常''and SYSTATE=''正常'''||v_urstr2;
  execute immediate s_sql3 into SBHBTMCT;
  
  --識(shí)別號(hào)同名稱不同
  s_sql4 := 'select COUNT(SH_ID) from (select TAX_INFO_GD.NAME, TAX_INFO.NSR, TAX_INFO_GD.SH_ID, TAX_INFO.SHXYDM, TAX_INFO_GD.SYLX, TAX_INFO_GD.JDXZ, TAX_INFO_GD.STATE as GDSTATE, TAX_INFO.STATE as SYSTATE FROM TAX_INFO_GD INNER JOIN TAX_INFO ON TAX_INFO_GD.SH_ID = TAX_INFO.SHXYDM) where NAME <> NSR  and (SYLX = '||TAXTYPE||' OR SYLX = 3) and GDSTATE=''正常'' and SYSTATE=''正常'''||v_urstr2;
  execute immediate s_sql4 into SBHTMCBT;
  
  --識(shí)別號(hào)名稱均相同
  s_sql5 := 'select COUNT(NAME) from (select TAX_INFO_GD.NAME, TAX_INFO.NSR, TAX_INFO_GD.SH_ID, TAX_INFO.SHXYDM, TAX_INFO_GD.SYLX, TAX_INFO_GD.JDXZ, TAX_INFO_GD.STATE as GDSTATE, TAX_INFO.STATE as SYSTATE FROM TAX_INFO_GD INNER JOIN TAX_INFO ON TAX_INFO_GD.NAME = TAX_INFO.NSR AND TAX_INFO_GD.SH_ID = TAX_INFO.SHXYDM AND (SYLX = '||TAXTYPE||' OR SYLX = 3)) where GDSTATE=''正常'' and SYSTATE=''正常'''||v_urstr2;
  execute immediate s_sql5 into SBHMCXT;
  
  --國(guó)或地稅識(shí)別號(hào)重復(fù)
  s_sql6 := 'SELECT COUNT(*) FROM (SELECT SH_ID FROM TAX_INFO_GD A WHERE SH_ID IN (SELECT SH_ID FROM TAX_INFO_GD B WHERE A.NAME <> B.NAME AND (A.SYLX = '||TAXTYPE||' OR A.SYLX = 3) AND (B.SYLX = '||TAXTYPE||' OR B.SYLX = 3) AND A.STATE = ''正常'' AND B.STATE = ''正常'') '||v_urstr2||' GROUP BY SH_ID)';
  execute immediate s_sql6 into GDSBHCF;
  
  --國(guó)或地名稱重復(fù)
  s_sql7 := 'SELECT COUNT(*) FROM (SELECT NAME FROM TAX_INFO_GD A WHERE NAME IN (SELECT NAME FROM TAX_INFO_GD B WHERE A.SH_ID <> B.SH_ID AND (A.SYLX = '||TAXTYPE||' OR A.SYLX = 3) AND (B.SYLX = '||TAXTYPE||' OR B.SYLX = 3) AND A.STATE = ''正常'' AND B.STATE = ''正常'') '||v_urstr2||' GROUP BY NAME)';
  execute immediate s_sql7 into GDMCCF;
  
end;