最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
以往旧题索引：
/forum.php?m ... eid&typeid=1808
原始出处：
/
作者：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
ma_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
/
