VIEW-TRANSFER-PARAMETERS-1

SQL

VIEW_PARAM 视图传参案例

思路：定义一个程序包，然后在视图 以及在查询视图时，分别使用 get set 方法来实现视图的传参。

案例视图

创建视图

1
2
3
4
5
6
7
8
CREATE OR REPLACE VIEW TEST AS 
SELECT * FROM
(SELECT '1' ID,'AAA' VALUE FROM DUAL
UNION 
SELECT '2','BBB' FROM DUAL
UNION 
SELECT '3','CCC' FROM DUAL)
WHERE ID = P_VIEW_PARAM.get_paramStr1();

查询方式

1
SELECT * FROM TEST WHERE P_VIEW_PARAM.set_paramStr1('1')='1'

包申明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
create or replace PACKAGE "P_VIEW_PARAM" is
--获取财务月起始日期和截止日期
function set_dateFrom(dateStr varchar2) return varchar2;  
function get_dateFrom  return date;
function set_dateTo(dateStr varchar2) return varchar2;  
function get_dateTo  return date;
function set_ndateFrom(dateStr varchar2) return varchar2;  
function get_ndateFrom  return date;
function set_ndateTo(dateStr varchar2) return varchar2;  
function get_ndateTo  return date;
--2020-12-25
function set_timestampFrom(dateStr varchar2) return varchar2;  
function get_timestampFrom  return Timestamp;
function set_timestampTo(dateStr varchar2) return varchar2;  
function get_timestampTo  return Timestamp;
--2020-12-25
--2020-12-28
function set_ndateFrom1(dateStr varchar2) return varchar2;  
function get_ndateFrom1  return date;
function set_ndateTo1(dateStr varchar2) return varchar2;  
function get_ndateTo1  return date;
function set_ndateFrom2(dateStr varchar2) return varchar2;  
function get_ndateFrom2  return date; 
function set_ndateTo2(dateStr varchar2) return varchar2;  
function get_ndateTo2  return date;
function set_ndateFrom3(dateStr varchar2) return varchar2;  
function get_ndateFrom3  return date; 
function set_ndateTo3(dateStr varchar2) return varchar2;  
function get_ndateTo3  return date;
function set_ndateFrom4(dateStr varchar2) return varchar2;  
function get_ndateFrom4  return date;   
function set_ndateTo4(dateStr varchar2) return varchar2;  
function get_ndateTo4  return date;
function set_ndateFrom5(dateStr varchar2) return varchar2;  
function get_ndateFrom5  return date; 
function set_ndateTo5(dateStr varchar2) return varchar2;  
function get_ndateTo5  return date;
function get_month(dateStr varchar2) return varchar2;--返回日期归属的财务月
function set_paramStr1(paramStr varchar2) return varchar2;  
function get_paramStr1  return varchar2;  --参数1
function set_paramStr2(paramStr varchar2) return varchar2;  
function get_paramStr2  return varchar2;  --参数2
function set_paramStr3(paramStr varchar2) return varchar2;  
function get_paramStr3  return varchar2;  --参数3
function set_paramStr4(paramStr varchar2) return varchar2;  
function get_paramStr4  return varchar2;  --参数4
function set_paramStr5(paramStr varchar2) return varchar2;  
function get_paramStr5  return varchar2;  --参数5
function set_paramStr6(paramStr varchar2) return varchar2;  
function get_paramStr6  return varchar2;  --参数6
function set_paramStr7(paramStr varchar2) return varchar2;  
function get_paramStr7  return varchar2;  --参数7
function set_paramStr8(paramStr varchar2) return varchar2;  
function get_paramStr8  return varchar2;  --参数8
function set_paramStr9(paramStr varchar2) return varchar2;  
function get_paramStr9  return varchar2;  --参数9
function set_paramStr10(paramStr varchar2) return varchar2;  
function get_paramStr10  return varchar2;  --参数10
function set_paramStr11(paramStr varchar2) return varchar2;  
function get_paramStr11  return varchar2;  --参数11
function set_paramStr12(paramStr varchar2) return varchar2;  
function get_paramStr12  return varchar2;  --参数12
end p_view_param;

包体申明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
create or replace PACKAGE BODY "P_VIEW_PARAM" is  
dateFrom date;--财务月起始日期
dateTo date;--财务月截止日期
ndateFrom date;--起始日期
ndateTo date;--截止日期
--2020-12-25 
timestampFrom timestamp;--起始日期
timestampTo timestamp;--截止日期
--2020-12-25 
--2020-12-28
ndateFrom1 date;--起始日期
ndateTo1 date;--截止日期
ndateFrom2 date;--起始日期
ndateTo2 date;--截止日期
ndateFrom3 date;--起始日期
ndateTo3 date;--截止日期
ndateFrom4 date;--起始日期
ndateTo4 date;--截止日期
ndateFrom5 date;--起始日期
ndateTo5 date;--截止日期
--2020-12-28
monthStr varchar2(10);--返回日期归属的财务月
paramStr1 varchar2(500);--参数1
paramStr2 varchar2(500);--参数2
paramStr3 varchar2(500);--参数3
paramStr4 varchar2(500);--参数4
paramStr5 varchar2(500);--参数5
paramStr6 varchar2(500);--参数6
paramStr7 varchar2(500);--参数7
paramStr8 varchar2(500);--参数8
paramStr9 varchar2(500);--参数9
paramStr10 varchar2(500);--参数10
paramStr11 varchar2(500);--参数11
paramStr12 varchar2(500);--参数12
function get_month(dateStr varchar2) return varchar2 is  
begin
    SELECT to_char(fy)||decode(length(to_char(period)),1,'0'||to_char(period),to_char(period)) into monthStr 
    FROM DA_CLNDR where to_char(clndr_date,'yyyy/mm/dd')=dateStr;
    return monthStr;  
end;   
function set_dateFrom(dateStr varchar2) return varchar2 is  
begin
    --select (SELECT PD_BEGIN FROM DA_CLNDR DA WHERE da.clndr_date=to_date(dateStr||'/10','yyyy/mm/dd')) INTO dateFrom FROM DUAL;
    select  pd_begin   INTO dateFrom  from da_clndr where fy=substr(datestr,1,4) and  period=substr(datestr,6, 2) and  rownum=1   ;
    return dateStr;  
end;
function get_dateFrom return date is  
begin  
    return dateFrom;  
end;
function set_dateTo(dateStr varchar2) return varchar2 is  
begin
--select (SELECT PD_END  FROM DA_CLNDR DA WHERE da.clndr_date=to_date(dateStr||'/10','yyyy/mm/dd')) INTO dateTo FROM DUAL;
select  pd_end   INTO dateTo from da_clndr where fy=substr(datestr,1,4) and  period=substr(datestr,6, 2) and  rownum=1;
    return dateStr;  
end;   
function get_dateTo return date is  
begin  
    return dateTo;  
end; 
function set_ndateFrom(dateStr varchar2) return varchar2 is  
begin
    select to_date(dateStr,'yyyy/mm/dd') INTO ndateFrom FROM DUAL;
    return dateStr;  
end;   
function get_ndateFrom return date is  
begin  
    return ndateFrom;  
end;
function set_ndateTo(dateStr varchar2) return varchar2 is
begin
select to_date(dateStr,'yyyy/mm/dd') INTO ndateTo FROM DUAL;
    return dateStr;  
end;   
function get_ndateTo return date is  
begin  
    return ndateTo;  
end; 
--2020-12-28
function set_ndateFrom1(dateStr varchar2) return varchar2 is  
begin
    select to_date(dateStr,'yyyy/mm/dd')  INTO ndateFrom1 FROM DUAL;
    return dateStr;  
end;   
function get_ndateFrom1 return date is  
begin  
    return  ndateFrom1;   
end;
function set_ndateTo1(dateStr varchar2) return varchar2 is
begin
    select   to_date(dateStr,'yyyy/mm/dd')  INTO ndateTo1 FROM DUAL;
    return dateStr;  
end;  
function get_ndateTo1 return date is  
begin  
    return ndateTo1;   
end; 
function set_ndateFrom2(dateStr varchar2) return varchar2 is  
begin
    select  to_date(dateStr,'yyyy/mm/dd')   INTO ndateFrom2 FROM DUAL;
    return dateStr;  
end;   
function get_ndateFrom2 return date is  
begin  
    return ndateFrom2;  
end;
function set_ndateTo2(dateStr varchar2) return varchar2 is
begin
    select    to_date(dateStr,'yyyy/mm/dd')  INTO ndateTo2 FROM DUAL;
    return dateStr;
end;  
function get_ndateTo2 return date is  
begin  
    return ndateTo2;  
end; 
function set_ndateFrom3(dateStr varchar2) return varchar2 is  
begin
    select   to_date(dateStr,'yyyy/mm/dd')   INTO ndateFrom3  FROM DUAL;
    return dateStr;  
end;   
function get_ndateFrom3 return date is  
begin  
    return ndateFrom3;  
end;
function set_ndateTo3(dateStr varchar2) return varchar2 is
begin
    select  to_date(dateStr,'yyyy/mm/dd')   INTO ndateTo3 FROM DUAL;
    return dateStr;    
end;  
function get_ndateTo3 return date is  
begin  
    return ndateTo3;  
end; 
function set_ndateFrom4(dateStr varchar2) return varchar2 is  
begin
    select  to_date(dateStr,'yyyy/mm/dd')  INTO ndateFrom4 FROM DUAL;
    return dateStr; 
end;   
function get_ndateFrom4 return date is  
begin  
    return ndateFrom4;  
end;
function set_ndateTo4(dateStr varchar2) return varchar2 is
begin
    select   to_date(dateStr,'yyyy/mm/dd')  INTO ndateTo4 FROM DUAL;
    return dateStr;  
end;  
function get_ndateTo4 return date is  
begin  
    return ndateTo4;  
end; 
function set_ndateFrom5(dateStr varchar2) return varchar2 is  
begin
    select   to_date(dateStr,'yyyy/mm/dd')  INTO ndateFrom5 FROM DUAL;
    return dateStr; 
end;   
function get_ndateFrom5 return date is  
begin  
    return ndateFrom5;  
end;
function set_ndateTo5(dateStr varchar2) return varchar2 is
begin
    select  to_date(dateStr,'yyyy/mm/dd')  INTO ndateTo5 FROM DUAL;
    return dateStr;   
end;  
function get_ndateTo5 return date is  
begin  
    return ndateTo5;  
end;  
--2020-12-25  
function set_timestampFrom(dateStr varchar2) return varchar2 is  
begin
    select to_timestamp(dateStr,'yyyy-mm-dd hh24:mi:ss:ff3') INTO timestampFrom FROM DUAL;
    return dateStr;  
end;   
function get_timestampFrom return timestamp is  
begin  
    return timestampFrom;  
end;
function set_timestampTo(dateStr varchar2) return varchar2 is
begin
    select to_timestamp(dateStr,'yyyy-mm-dd hh24:mi:ss:ff3') INTO timestampTo FROM DUAL;
    return dateStr;  
end;   
function get_timestampTo return timestamp is  
begin  
    return timestampTo;  
end; 
--2020-12-25
function set_paramStr1(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr1 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr1 return varchar2 is  
begin  
    return paramStr1;  
end; 
function set_paramStr2(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr2 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr2 return varchar2 is  
begin  
    return paramStr2;  
end; 
function set_paramStr3(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr3 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr3 return varchar2 is  
begin  
    return paramStr3;  
end; 
function set_paramStr4(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr4 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr4 return varchar2 is  
begin  
    return paramStr4;  
end; 
function set_paramStr5(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr5 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr5 return varchar2 is  
begin  
    return paramStr5;  
end;  
function set_paramStr6(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr6 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr6 return varchar2 is  
begin  
    return paramStr6;  
end;  
function set_paramStr7(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr7 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr7 return varchar2 is  
begin  
    return paramStr7;  
end;  
function set_paramStr8(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr8 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr8 return varchar2 is  
begin  
    return paramStr8;  
end;   
--2021-01-20
function set_paramStr9(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr9 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr9 return varchar2 is  
begin  
    return paramStr9;  
end;
function set_paramStr10(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr10 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr10 return varchar2 is  
begin  
    return paramStr10;  
end;
function set_paramStr11(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr11 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr11 return varchar2 is  
begin  
    return paramStr11;  
end;
function set_paramStr12(paramStr varchar2) return varchar2 is
begin
    select paramStr  INTO paramStr12 FROM DUAL;
    return paramStr;  
end;   
function get_paramStr12 return varchar2 is  
begin  
    return paramStr12;  
end;   
end p_view_param;

Title:VIEW-TRANSFER-PARAMETERS-1

Author:

Created:2022-02-25, 19:44:26

Updated:2023-04-13, 09:40:40

Full URL:http://yoursite.com/2022/02/25/VIEW-TRANSFER-PARAMETERS-1/

License: "CC BY-NC-SA 4.0" Keep Link & Author if Distribute.

Contents
  1. 1. VIEW_PARAM 视图传参案例
    1. 1.1. 案例视图
    2. 1.2. 包申明
    3. 1.3. 包体申明
|