如下：
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;
转载请注明：IT运维空间 » 虚拟化 » oracle存储过程例子，包含函数，动态执行sql
你可能喜欢:
继续浏览有关 数据库技术文章/教程 的文章
发表评论