【问题】

I have a table called rbd_dtl. The table structure as follows...
RBD_ID ODFEE_ID RBD_VALUE
25 206 ALL 
31 205 A
26 205 B
41 205 C
42 205 D
30 205 E
38 205 F
39 205 H
27 205 J
35 205 K
32 205 N
36 205 O
28 205 Q
44 205 R
34 205 S
29 205 U
33 205 V
40 205 X
37 205 Y
43 205 Z
My requirement is to write a PLSQL Function which returns a character...
The column RBD_VALUE contains values as ALL or from A to Z.
If the column value as all then i return the same
If not as you can see the data above there in the table, the following alphabets are missing for 205 odfee_id...
G, I, L, M, P, T, W
So it should return as A-F,H,J-K,N-O,Q-S,U-V,X-Z
Based on the above data form A to F we can see all consecutive alphabets but after F there is no G so we have to show as A-F.
The next is H and there is no I which is missing so we pick H
After H, I is missing so we see it from J, K and after this there is no consecutive character so we pick as J-K

【回答】

       窗口函数可以实现字母分段，但实现方式较难理解。如果希望代码更简洁，可以考虑用SPL来实现，脚本如下：

A1：读取数据

A2：将RBD_VALUE按着大写26个字母对位

A3：归并分组，将相邻字母连续的成员分为一组

A4：选出非空的组

A5：将每组的首尾字母选出用“-”连接，每组用逗号分隔拼成串返回，结果如下：

写好的脚本如何在应用程序中调用，可以参考Java 如何调用 SPL 脚本