-
Notifications
You must be signed in to change notification settings - Fork 0
/
body.sql
334 lines (256 loc) · 12 KB
/
body.sql
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
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
alter session set plsql_ccflags='string_op_debug:false';
create or replace package body txt as
--
-- V0.3
--
function strtok (str in varchar2, delimiter in varchar2) -- {
return varchar2_t
is
tokens varchar2_t :=varchar2_t();
i pls_integer;
t varchar2(4000);
begin
if str is null then
return tokens;
end if;
t := str;
loop
i := instr(t, delimiter);
tokens.extend;
if i is null or i = 0 then /* none or last one found */
tokens(tokens.count) := t;
return tokens;
else
tokens(tokens.count) := substr(t, 0, i -1);
end if;
t := substr(t,i + length(delimiter),length(t));
end loop;
end strtok; -- }
function grep_re(str in clob, re in varchar2) return varchar2_t -- {
is
tokens varchar2_t;
begin
select * bulk collect into tokens from grep_re_pipelined(str, re);
return tokens;
end grep_re; -- }
function grep_re_pipelined(str in clob, re varchar2) return clob_t pipelined -- {
is
occ pls_integer := 1;
cnt pls_integer;
begin
cnt := regexp_count(str, re);
while occ <= cnt loop -- {
pipe row(regexp_substr(str, re, 1, occ));
occ := occ + 1;
end loop; -- }
end grep_re_pipelined; -- }
function replace_entire_words(text clob, what varchar2, replacement varchar2) return clob -- {
is
ret clob;
begin
ret := regexp_replace(
text,
'(\A|\W)' || what || '(\W|\Z)',
'\1' || replacement || '\2',
1, 0, 'ni');
return ret;
end replace_entire_words; -- }
-- sprintf related -- {
function sprintf (fmt in varchar2, parms in varchar2_t) return varchar2 -- {
is -- {
ret varchar2(4000);
cur_pos number := 0;
cur_format varchar2(4000);
len_format number := length(fmt);
left_aligned boolean;
print_sign boolean;
cur_param number := 0;
-- }
begin
loop -- {
-- Iterating over each character in the fmt.
-- cur_pos points to the character 'being examined'.
cur_pos := cur_pos + 1;
exit when cur_pos > len_format;
-- The iteration is over when cur_pos is past the last character.
if substr(fmt, cur_pos, 1) = '%' then -- { A % sign is recognized.
-- I assume the default: left aligned, sign (+) not printed
left_aligned := false;
print_sign := false;
-- Advance cur_pos so that it points to the character
-- right of the %
cur_pos := cur_pos + 1;
if substr(fmt, cur_pos, 1) = '%' then -- {
-- If % is immediately followed by another %, a literal
-- % is wanted:
ret := ret || '%';
-- No need to further process the fmt (it is none)
goto percent;
end if; -- }
if substr(fmt, cur_pos, 1) = '-' then -- {
-- Current fmt will be left aligned
left_aligned := true;
cur_pos := cur_pos + 1;
end if; -- }
if substr(fmt, cur_pos, 1) = '+' then -- {
-- Print plus sign explicitely (only number, %d)
print_sign := true;
cur_pos := cur_pos + 1;
end if; -- }
-- Now, reading the rest until 'd' or 's' and
-- store it in cur_format.
cur_format := '';
-- cur_param points to the corresponding entry
-- in parms
cur_param := cur_param + 1;
loop -- {
-- Make sure, iteration doesn't loop forever
-- (for example if incorrect fmt is given)
exit when cur_pos > len_format;
if substr(fmt, cur_pos, 1) = 'd' then -- {
declare -- { some 'local' variables, only used for %d
chars_left_dot number;
chars_rite_dot number;
chars_total number;
dot_pos number;
to_char_format varchar2(50);
buf varchar2(50);
num_left_dot char(1) := '9';
-- }
begin
if cur_format is null then -- {
-- Format is: %d (maybe %-d, or %+d which SHOULD be
-- handled, but isn't)
ret := ret || to_char(parms(cur_param), 'TM9', 'nls_numeric_characters=''.,''');
-- current fmt specification finished, exit the loop
exit;
end if; -- }
-- does the current fmt contain a dot?
-- dot_pos will be the position of the dot
-- if it contains one, or will be 0 otherwise.
dot_pos := instr(cur_format, '.');
if substr(cur_format, 1, 1) = '0' then -- {
-- Is the current fmt something like %0...d?
num_left_dot := '0';
end if; -- }
-- determine how many digits (chars) are to be printed left
-- and right of the dot.
if dot_pos = 0 then -- {
-- If no dot, there won't be any characters rigth of the dot
-- (and no dot will be printed, either)
chars_rite_dot := 0;
chars_left_dot := to_number(cur_format);
chars_total := chars_left_dot;
-- }
else -- {
chars_rite_dot := to_number(substr(cur_format, dot_pos + 1));
chars_left_dot := to_number(substr(cur_format, 1, dot_pos - 1));
chars_total := chars_left_dot + chars_rite_dot + 1;
end if; -- }
if parms(cur_param) is null then -- {
-- null h
ret := ret || lpad(' ', chars_total);
exit;
end if; -- }
to_char_format := lpad('9', chars_left_dot-1, '9') || num_left_dot;
if dot_pos != 0 then -- {
-- There will be a dot
to_char_format := to_char_format || '.' || lpad('9', chars_rite_dot, '9');
end if; -- }
if print_sign then -- {
to_char_format := 'S' || to_char_format;
-- The explicit printing of the sign widens the output by one character
chars_total := chars_total + 1;
end if; -- }
$if $$string_op_debug $then
dbms_output.put_line('cur_param: ' || cur_param || ', parms(cur_param) = ' || parms(cur_param) || '<');
$end
buf := to_char(to_number(parms(cur_param)), to_char_format, 'nls_numeric_characters=''.,''');
if left_aligned then -- {
buf := rpad(trim(buf), chars_total);
else
buf := lpad(trim(buf), chars_total);
end if; -- }
ret := ret || buf;
exit;
end;
-- }
elsif substr(fmt, cur_pos, 1) = 's' then -- {
$if $$string_op_debug $then
dbms_output.put_line('string fmt, fmt = ' || fmt || ', cur_pos: ' || cur_pos);
$end
if cur_format is null then
ret := ret || parms(cur_param);
exit;
end if;
if left_aligned then
$if $$string_op_debug $then
dbms_output.put_line(' left_aligned, cur_format=' || cur_format);
$end
ret := ret || rpad(nvl(parms(cur_param), ' '), to_number(cur_format));
else
$if $$string_op_debug $then
dbms_output.put_line(' right_aligned, cur_format=' || cur_format);
$end
ret := ret || lpad(nvl(parms(cur_param), ' '), to_number(cur_format));
end if;
exit;
end if; -- }
cur_format := cur_format || substr(fmt, cur_pos, 1);
cur_pos := cur_pos + 1;
end loop; -- }
-- }
else -- { A non-% character
ret := ret || substr(fmt, cur_pos, 1);
end if; -- }
<<PERCENT>> null;
end loop; -- }
return ret;
end sprintf; -- }
function sprintf(fmt varchar2, p_01 varchar2 ) return varchar2 is begin return sprintf(fmt, varchar2_t(p_01 )); end sprintf;
function sprintf(fmt varchar2, p_01 varchar2, p_02 varchar2 ) return varchar2 is begin return sprintf(fmt, varchar2_t(p_01, p_02 )); end sprintf;
function sprintf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2 ) return varchar2 is begin return sprintf(fmt, varchar2_t(p_01, p_02, p_03 )); end sprintf;
function sprintf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2, p_04 varchar2 ) return varchar2 is begin return sprintf(fmt, varchar2_t(p_01, p_02, p_03, p_04 )); end sprintf;
function sprintf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2, p_04 varchar2, p_05 varchar2 ) return varchar2 is begin return sprintf(fmt, varchar2_t(p_01, p_02, p_03, p_04, p_05 )); end sprintf;
function sprintf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2, p_04 varchar2, p_05 varchar2, p_06 varchar2) return varchar2 is begin return sprintf(fmt, varchar2_t(p_01, p_02, p_03, p_04, p_05, p_06)); end sprintf;
-- }
-- printf related -- {
procedure printf(fmt varchar2, parms in varchar2_t) is begin dbms_output.put_line(sprintf(fmt, parms)); end printf;
procedure printf(fmt varchar2, p_01 varchar2 ) is begin dbms_output.put_line(sprintf(fmt, p_01 )); end printf;
procedure printf(fmt varchar2, p_01 varchar2, p_02 varchar2 ) is begin dbms_output.put_line(sprintf(fmt, p_01, p_02 )); end printf;
procedure printf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2 ) is begin dbms_output.put_line(sprintf(fmt, p_01, p_02, p_03 )); end printf;
procedure printf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2, p_04 varchar2 ) is begin dbms_output.put_line(sprintf(fmt, p_01, p_02, p_03, p_04 )); end printf;
procedure printf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2, p_04 varchar2, p_05 varchar2 ) is begin dbms_output.put_line(sprintf(fmt, p_01, p_02, p_03, p_04, p_05 )); end printf;
procedure printf(fmt varchar2, p_01 varchar2, p_02 varchar2, p_03 varchar2, p_04 varchar2, p_05 varchar2, p_06 varchar2) is begin dbms_output.put_line(sprintf(fmt, p_01, p_02, p_03, p_04, p_05, p_06)); end printf;
-- }
function is_number(str varchar2) return boolean is -- {
num number;
begin
--
-- Check if str is numerical.
-- Consider using SQL function validate_conversion(expr as number) instead.
--
-- num := to_number(str, …, nls_param => 'nls_numeric_characters=''.,''');
num := to_number(str);
return true;
exception when value_error then
return false;
end is_number; -- }
function is_number_sql(str varchar2) return number is -- {
num number;
begin
--
-- Check if str is numerical.
-- Consider using SQL function validate_conversion(expr as number) instead.
--
if is_number(str) then return 1; end if;
return 0;
end is_number_sql; -- }
function from_(b boolean) return varchar2 is begin -- {
if b then return 'true' ; end if;
if not b then return 'false'; end if;
return 'null' ;
end from_; -- }
end txt;
/
show errors