弹冠相庆

October 10, 2010

用pl/sql分割字符串

Filed under: Uncategorized — Corey @ 22:06
Tags:

pl/sql 自带的package里面有一个dbms_utility.comma_to_table,可以用来分割以’,’为分割符
的字符串,并且对返回的字符数组类型也有限制,并不是一个通用的方法。如果你的需求恰
好符合该方法的定义,可以考虑使用。与此对应的方法是dbms_utility.table_to_comma,是
把字符串数组转换成字符串。

以下实现改自Tom Kyte的例子

功能: 用pl/sql function分割字符串,并返回相应的number数组

CREATE OR REPLACE TYPE typ_number_table as table of NUMBER;

CREATE OR REPLACE FUNCTION split_string(p_string    IN VARCHAR2
                                                                         ,p_delimiter IN VARCHAR2 DEFAULT ‘,’)
  RETURN typ_number_table IS
  l_string    VARCHAR2(32000) DEFAULT p_string || p_delimiter;
  l_num       NUMBER;
  l_num_table typ_number_table := typ_number_table();
BEGIN
  LOOP
    l_num := instr(l_string
                  ,p_delimiter);
    EXIT WHEN(nvl(l_num
                 ,0) = 0);
    l_num_table.EXTEND;
    l_num_table(l_num_table.COUNT) := to_number(trim(substr(l_string
                                                                                               ,1
                                                                                               ,l_num – 1)));
    l_string := substr(l_string
                      ,l_num + length(p_delimiter));
  END LOOP;
  RETURN l_num_table;
END;

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: