Oracle 集合转字符,PL/SQL Challenge 每日一题：2014-5-30 将逗号隔开的字符串转换为集合...

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：Steven Feuerstein

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

哪些选项在执行之后会显示如下的单词：

negative

zero

positive

(A)

CREATE OR REPLACE TYPE plch_str_tab IS TABLE OF VARCHAR2(100);

/

CREATE OR REPLACE FUNCTION plch_get_values(p_str IN VARCHAR2)

RETURN plch_str_tab

AS

l_tab plch_str_tab := plch_str_tab();

l_cnt PLS_INTEGER := 1;

l_stmt VARCHAR2(32767);

BEGIN

LOOP

l_stmt := REGEXP_SUBSTR(p_str, '[^,]+', 1, l_cnt);

EXIT WHEN l_stmt IS NULL;

l_tab.EXTEND;

l_tab(l_tab.LAST) := l_stmt;

l_cnt := l_cnt + 1;

END LOOP;

RETURN l_tab;

END plch_get_values;

/

SELECT *

FROM TABLE(plch_get_values('negative,zero,positive'))

/

(B)

WITH my_tab

AS (    SELECT TRIM (SUBSTR (txt,

INSTR (txt,

',',

1,

LEVEL)

+ 1,

INSTR (txt,

',',

1,

LEVEL + 1)

- INSTR (txt,

',',

1,

LEVEL)

- 1))

AS token

FROM (SELECT ',negative,zero,positive,' txt FROM DUAL)

CONNECT BY LEVEL <=

LENGTH ('negative,zero,positive')

- LENGTH (

REPLACE ('negative,zero,positive',

',',

''))

+ 1)

SELECT *

FROM my_tab

/

(C)

DECLARE

l_list      VARCHAR2 (1000) := 'negative,zero,positive';

l_cnt       BINARY_INTEGER;

l_tab_str   DBMS_UTILITY.uncl_array;

BEGIN

DBMS_UTILITY.comma_to_table (l_list, l_cnt, l_tab_str);

FOR indx IN 1 .. l_tab_str.COUNT-1

LOOP

DBMS_OUTPUT.put_line (TRIM (l_tab_str (indx)));

END LOOP;

END;

/

(D)

WITH original_table

AS (SELECT 'negative,zero,positive' val FROM dual)

SELECT new_val

FROM original_table,

XMLTABLE (

'r/c'

PASSING xmltype (

''

|| REPLACE(val, ',', '')

|| '')

COLUMNS new_val VARCHAR2(50) PATH '.')

/

(E)

WITH original_table

AS (SELECT 'negative,zero,positive' val FROM DUAL)

SELECT t.COLUMN_VALUE

FROM original_table,

XMLTABLE (regexp_replace(val, '([^,]+)', '"\1"')) t

/