项目中Orcale存储过程优化记录
今天对之前写的Orcale存储过程做了一些优化，使其变得更加灵活，之前写的存储过程是使用游标存储SQL执行结果，但是使用游标带来的弊端是，在声明时，它所执行的SQL就已经被写死，无法修改。若想更加灵活，必须将使用游标这种策略舍弃掉，转而使用在“过程”中定义SQL语句的写法：主要思路是利用execute immediate SQL语句 into 自定义成员变量;执行并存储SQL的结果（单个的），接下来就是拼接这条SQL语句，和JAVA里的SQL拼接套路完全一致。
之前使用游标的存储过程，只能执行代码中写好的SQL语句：
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;
改进后的存储过程，多加入两个传入参数USERRIGHT，TAXTYPE ，根据USERRIGHT，TAXTYPE 返回对应的数据条目数：
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;
--国或地税有税源无
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;
--国或地税无税源有
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;
--识别号不同名称同
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;
--识别号同名称不同
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;
--识别号名称均相同
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;
--国或地税识别号重复
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;
--国或地名称重复
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;
