聚合拼接字符串的方法

Oracle-sql中创建聚合字符串函数：

（测试过，可以使用。子表某一列所有内容拼接成一个字符串，以逗号分隔）

首先要为子表赋权限： 

         Oracle-command中输入（要在system用户下）：grant select , insert ,update on 表名 to 用户;

 

A:一个关联键

create or replace function PUBLIC_TOGETHER( key_name in varchar2,

                    key  in varchar2,

                    coname in varchar2,

                    tname     in varchar2 )

 return varchar2

as

    type rc is ref cursor;

    str    varchar2(4000);

    sep    varchar2(2);

    val    varchar2(4000);

    cur    rc;

begin

    open cur for 'select '||coname||'

                    from '|| tname || '

                    where ' || key_name || ' = :x '

                using key;

    loop

        fetch cur into val;

        exit when cur%notfound;

        str := str || sep || val;

        sep := ', ';

    end loop;

    close cur;

    return str;

end;

 

B: 三个关联键

create or replace function TOGETHER_THREE_PARAM( key1_name in varchar2,

                    key1  in varchar2,

                    key2_name in varchar2,

                    key2  in varchar2,

                    key3_name in varchar2,

                    key3  in varchar2,

                    coname in varchar2,

                    tname     in varchar2 )

 return varchar2

as

    type rc is ref cursor;

    str    varchar2(4000);

    sep    varchar2(2);

    val    varchar2(4000);

    cur    rc;

begin

    open cur for 'select '||coname||'

                    from '|| tname || '

                    where ' || key1_name || ' = :x ' ||

                    'and ' || key2_name || ' = :y ' ||

                    'and ' || key3_name || ' = :z '

                using key1,key2,key3;

    loop

        fetch cur into val;

        exit when cur%notfound;

        str := str || sep || val;

        sep := ', ';

    end loop;

    close cur;

    return str;

end;