如下：
1 create or replace procedure JDGL_PRO_GET_DFLZJSBB3(p_date1 in varchar2, res OUT sys_refcursor) is 2 3 strSql clob; 4 5 FUNCTION CONACT_SQL_JS(XFAJ0902 IN VARCHAR2,XFAJ0903 IN VARCHAR2) RETURN clob AS 6 BEGIN 7 RETURN ' 8 SELECT 9 --合计 省 10 COUNT(CASE WHEN A.XFAJ0909 = ''1'' THEN 1 ELSE NULL END) AS COL1, 11 --合计 地 12 COUNT(CASE WHEN A.XFAJ0909 = ''2'' THEN 1 ELSE NULL END) AS COL2, 13 --合计 县 14 COUNT(CASE WHEN A.XFAJ0909 = ''3'' THEN 1 ELSE NULL END) AS COL3, 15 --检察长 省 16 COUNT(CASE WHEN A.XFAJ0909 = ''1'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''1'') THEN 1 ELSE NULL END) AS COL4, 17 --检察长 地 18 COUNT(CASE WHEN A.XFAJ0909 = ''2'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''1'') THEN 1 ELSE NULL END) AS COL5, 19 --检察长 县 20 COUNT(CASE WHEN A.XFAJ0909 = ''3'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''1'') THEN 1 ELSE NULL END) AS COL6, 21 --其他领导班子成员 省 22 COUNT(CASE WHEN A.XFAJ0909 = ''1'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''2'') THEN 1 ELSE NULL END) AS COL7, 23 --其他领导班子成员 地 24 COUNT(CASE WHEN A.XFAJ0909 = ''2'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''2'') THEN 1 ELSE NULL END) AS COL8, 25 --其他领导班子成员 县 26 COUNT(CASE WHEN A.XFAJ0909 = ''3'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''2'') THEN 1 ELSE NULL END) AS COL9, 27 --检委会专职委员 省 28 COUNT(CASE WHEN A.XFAJ0909 = ''1'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''3'') THEN 1 ELSE NULL END) AS COL10, 29 --检委会专职委员 地 30 COUNT(CASE WHEN A.XFAJ0909 = ''2'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''3'') THEN 1 ELSE NULL END) AS COL11, 31 --检委会专职委员 县 32 COUNT(CASE WHEN A.XFAJ0909 = ''3'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''3'') THEN 1 ELSE NULL END) AS COL12, 33 --内设机构负责人 省 34 COUNT(CASE WHEN A.XFAJ0909 = ''1'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''4'') THEN 1 ELSE NULL END) AS COL13, 35 --内设机构负责人 地 36 COUNT(CASE WHEN A.XFAJ0909 = ''2'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''4'') THEN 1 ELSE NULL END) AS COL14, 37 --内设机构负责人 县 38 COUNT(CASE WHEN A.XFAJ0909 = ''3'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''4'') THEN 1 ELSE NULL END) AS COL15, 39 --其他领导干部 省 40 COUNT(CASE WHEN A.XFAJ0909 = ''1'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''5'') THEN 1 ELSE NULL END) AS COL16, 41 --其他领导干部 地 42 COUNT(CASE WHEN A.XFAJ0909 = ''2'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''5'') THEN 1 ELSE NULL END) AS COL17, 43 --其他领导干部 县 44 COUNT(CASE WHEN A.XFAJ0909 = ''3'' AND EXISTS(SELECT 1 FROM XFAJ10 BB WHERE BB.XFAJ0900=A.XFAJ0900 AND BB.DELETEFLAG = ''0'' AND BB.XFAJ1002 = ''5'') THEN 1 ELSE NULL END) AS COL18 45 FROM XFAJ09 A 46 WHERE A.DELETEFLAG = ''0'' 47 --案件分类 48 AND A.XFAJ0901 = ''3'' 49 --二级分类 50 AND A.XFAJ0902 = ' || XFAJ0902 || ' 51 --三级分类 52 AND A.XFAJ0903 = ' || XFAJ0903 || ' 53 AND A.XFAJ0907 BETWEEN TO_DATE(''2017-11-01'', ''yyyy-mm-dd'') 54 AND TO_DATE(''2017-11-30'', ''yyyy-mm-dd'') 55 AND EXISTS ( 56 SELECT DMCOD 57 FROM G099_HIBER GH 58 WHERE (GH.DMPARENTCOD = ''b8652adc-e096-47e1-88fe-d14dbf7b3f9e'' 59 OR GH.DMCOD = ''b8652adc-e096-47e1-88fe-d14dbf7b3f9e'') 60 AND GH.DMCOD = A.CREATEUSERDEPT 61 ) 62 63 '; 64 END; 65 66 FUNCTION CONACT_SQL_JS2(STR1 IN clob) RETURN clob AS 67 BEGIN 68 RETURN STR1; 69 END; 70 71 begin 72 73 strSql := 74 --第1行 75 CONACT_SQL_JS('1','1') 76 --第2行 77 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 78 --第3行 79 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 80 --第4行 81 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 82 --第5行 83 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 84 --第6行 85 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 86 --第7行 87 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 88 --第8行 89 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 90 --第9行 91 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 92 --第10行 93 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 94 --第11行 95 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 96 --第12行 97 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 98 --第13行 99 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 100 --第14行 101 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 102 --第15行 103 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 104 --第16行 105 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 106 --第17行 107 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 108 --第18行 109 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 110 --第19行 111 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 112 --第20行 113 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 114 --第21行 115 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 116 --第22行 117 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 118 --第23行 119 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 120 --第24行 121 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 122 --第25行 123 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 124 --第26行 125 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 126 --第27行 127 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 128 --第28行 129 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 130 --第29行 131 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 132 --第30行 133 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 134 --第31行 135 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 136 --第32行 137 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 138 --第33行 139 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 140 --第34行 141 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 142 --第35行 143 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 144 --第36行 145 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 146 --第37行 147 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 148 --第38行 149 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 150 --第39行 151 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 152 --第40行 153 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 154 --第41行 155 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 156 --第42行 157 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 158 --第43行 159 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 160 --第44行 161 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 162 --第45行 163 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 164 --第46行 165 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 166 --第47行 167 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 168 --第48行 169 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 170 --第49行 171 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 172 --第50行 173 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 174 --第51行 175 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 176 --第52行 177 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 178 --第53行 179 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 180 --第54行 181 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 182 --第55行 183 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 184 --第56行 185 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 186 --第57行 187 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 188 --第58行 189 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 190 --第59行 191 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 192 --第60行 193 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 194 --第61行 195 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 196 --第62行 197 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 198 --第63行 199 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 200 --第64行 201 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 202 --第65行 203 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 204 --第66行 205 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 206 --第67行 207 || ' UNION ALL ' || CONACT_SQL_JS('1','1') 208 ; 209 open res for strSql; 210 211 end JDGL_PRO_GET_DFLZJSBB3;