oracle 组合列,Oracle - 所有组合行的列

首先，创建表和数据：

create table mytable (id,name) as

(

select 1, 'string_1 string_2 string_3 string_N' from dual

union all

select 2, null from dual

union all

select 3, 'Ma Lo' from dual

);

其次，创建我们需要的对象和表类型：

CREATE OR REPLACE TYPE name_tbl AS TABLE OF VARCHAR2(100);

CREATE OR REPLACE TYPE id_name_rec AS OBJECT (id NUMBER, name VARCHAR2(100));

CREATE OR REPLACE TYPE id_name_tbl AS TABLE OF id_name_rec;

三，创建一个功能，我们会打电话给递归地解析名称值：

CREATE OR REPLACE

FUNCTION parse_name (v_name IN VARCHAR2)

RETURN name_tbl

IS

tbl name_tbl;

subtbl name_tbl;

subname VARCHAR2(100);

thisname VARCHAR2(100);

num INT := 1;

idx INT := 1;

idxspace INT := 0;

BEGIN

IF v_name IS NOT NULL THEN

tbl := name_tbl();

-- find the number of values

FOR ws IN 1 .. LENGTH(v_name) loop

IF (substr(v_name,ws,1) = ' ') THEN

num := num + 1;

END IF;

END loop;

IF (num > 1) THEN

-- increase tbl size

-- get the index of the first whitespace

idxspace := instr(v_name, ' ');

-- get thisname

thisname := substr(v_name, 1, idxspace-1);

-- substring name and make recursive call;

subname := substr(v_name, idxspace+1);

subtbl := parse_name (subname);

FOR i IN 1 .. subtbl.count()

loop

tbl.EXTEND;

-- add each subtbl value to tbl

tbl(tbl.count) := subtbl(i);

tbl.EXTEND;

-- now prepend this name to each value of subtbl and add to tbl

tbl(tbl.count) := thisname||' '||subtbl(i);

END loop;

ELSE

thisname := v_name;

END IF;

tbl.EXTEND;

tbl (tbl.count) := thisname;

END IF;

RETURN tbl;

exception

WHEN others THEN dbms_output.put_line('whoops: '||sqlerrm);

END;

四，创建返回refcursor的主函数：

create or replace

FUNCTION parse_mytable_name

RETURN sys_refcursor

IS

retcur sys_refcursor;

idnametbl id_name_tbl := id_name_tbl();

valuetbl name_tbl;

BEGIN

-- get all the records from mytable

FOR z IN (SELECT ID, NAME FROM mytable)

loop

valuetbl := parse_name(z.NAME);

IF (valuetbl IS NOT NULL) THEN

FOR i IN 1 .. valuetbl.count

loop

idnametbl.EXTEND;

idnametbl(idnametbl.count) := id_name_rec(z.ID, valuetbl(i));

END loop;

END IF;

END loop;

OPEN retcur FOR SELECT ID, NAME FROM TABLE (idnametbl) order by id, name;

RETURN retcur;

exception WHEN others THEN dbms_output.put_line('whoops in parse_mytable_name: '||sqlerrm);

END parse_mytable_name;

五，测试用例PL/SQL：

DECLARE

retcur sys_refcursor;

testid INT;

testname varchar2(100);

BEGIN

retcur := parse_mytable_name();

loop

fetch retcur INTO testid,testname;

exit WHEN retcur%notfound;

dbms_output.put_line('testid: '||testid||', testname: '||testname);

END loop;

EXCEPTION

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('whoops: '||SQLERRM);

END;

最后，测试的结果：

testid: 1, testname: string_1

testid: 1, testname: string_1 string_2

testid: 1, testname: string_1 string_2 string_3

testid: 1, testname: string_1 string_2 string_3 string_N

testid: 1, testname: string_1 string_2 string_N

testid: 1, testname: string_1 string_3

testid: 1, testname: string_1 string_3 string_N

testid: 1, testname: string_1 string_N

testid: 1, testname: string_2

testid: 1, testname: string_2 string_3

testid: 1, testname: string_2 string_3 string_N

testid: 1, testname: string_2 string_N

testid: 1, testname: string_3

testid: 1, testname: string_3 string_N

testid: 1, testname: string_N

testid: 3, testname: Lo

testid: 3, testname: Ma

testid: 3, testname: Ma Lo