Oracle 函数 wm_concat 将列转行用法实例

来源：互联网发布：算法工程师考试题目编辑：程序博客网时间：2024/04/29 05:47

这篇文章主要记录下我工作中遇到sql语句查询的问题，工作中有个需求是做问卷调查，然后统计导出数据，其中有个问题是多选题，存的是答案表对应的答案的id（以逗号分隔），然后查询的时候怎么样才能把这个问题的答案也查出来拼接成逗号分隔，就用到了 WM_CONCAT 函数，下面就说一下具体用法。

1. 首先看一下我的表结构，总共有三张表，qv_question 记录了问题，qv_answer 记录了问题对应的答案（单选，多选的选项，而不是调查回答的答案），qv_investreplay 这个记录了每个人问卷调查回答的问题对应的答案，因此一个人回答了多个问题，一个问题有多个答案，qv_investreplay 对qv_question 是一对多，同样 qv_question 对 qv_answer 也是一对多

-- Create table
create table QV_QUESTION
(
id            VARCHAR2(32) not null,
questionorder NUMBER,
questionname VARCHAR2(300),
questiontype VARCHAR2(1),
investid      VARCHAR2(32),
addusername   VARCHAR2(50),
addtime       DATE,
adddept       VARCHAR2(200),
addname       VARCHAR2(50),
isdeleted     VARCHAR2(50),
investname    VARCHAR2(200),
allowwrite    VARCHAR2(50)
)
tablespace RMSFUSION
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64
    minextents 1
    maxextents unlimited
);
-- Add comments to the table
comment on table QV_QUESTION
is '题目表';
-- Add comments to the columns
comment on column QV_QUESTION.questionorder
is '题目排序';
comment on column QV_QUESTION.questionname
is '题目名称';
comment on column QV_QUESTION.questiontype
is '类型 1 :单选2：复选 3：文本框';
comment on column QV_QUESTION.investid
is '主题id';
comment on column QV_QUESTION.isdeleted
is '0:未删除 1：删除';
comment on column QV_QUESTION.investname
is '主题名称';
comment on column QV_QUESTION.allowwrite
is '如果是单选复选的话是否允许客户手动输入 0：不允许1允许';
-- Create/Recreate primary, unique and foreign key constraints
alter table QV_QUESTION
add constraint PK_QV_QUESTION primary key (ID)
using index
tablespace RMSFUSION
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

-- Create table
create table QV_ANSWER
(
id           VARCHAR2(32) not null,
answername   VARCHAR2(200),
questionid   VARCHAR2(3200),
addusername VARCHAR2(50),
addtime      DATE,
adddept      VARCHAR2(200),
addname      VARCHAR2(50),
isdeleted    VARCHAR2(50),
questionname VARCHAR2(200),
ordernumber NUMBER
)
tablespace RMSFUSION
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64
    minextents 1
    maxextents unlimited
);
-- Add comments to the table
comment on table QV_ANSWER
is '问题答案表';
-- Add comments to the columns
comment on column QV_ANSWER.answername
is '答案名称';
comment on column QV_ANSWER.questionid
is '题目id';
comment on column QV_ANSWER.isdeleted
is '0:未删除 1：已删除';
comment on column QV_ANSWER.questionname
is '题目名称';
comment on column QV_ANSWER.ordernumber
is '答案排序';
-- Create/Recreate primary, unique and foreign key constraints
alter table QV_ANSWER
add constraint PK_QV_ANSWER primary key (ID)
using index
tablespace RMSFUSION
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

-- Create table
create table QV_INVESTREPLAY
(
id          VARCHAR2(32) not null,
investid    VARCHAR2(32),
questionid VARCHAR2(32),
answerid    VARCHAR2(255),
useranswer VARCHAR2(500),
addusername VARCHAR2(50),
addtime     DATE,
adddept     VARCHAR2(200),
addname     VARCHAR2(50),
isreplay    VARCHAR2(30),
allowanswer VARCHAR2(255)
)
tablespace RMSFUSION
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64
    minextents 1
    maxextents unlimited
);
-- Add comments to the table
comment on table QV_INVESTREPLAY
is '问卷反馈表';
-- Add comments to the columns
comment on column QV_INVESTREPLAY.investid
is '问卷id';
comment on column QV_INVESTREPLAY.questionid
is '题目id';
comment on column QV_INVESTREPLAY.answerid
is '答案id';
comment on column QV_INVESTREPLAY.useranswer
is '用户答案（文本框）';
comment on column QV_INVESTREPLAY.addusername
is '参与者工号';
comment on column QV_INVESTREPLAY.addtime
is '参与时间';
comment on column QV_INVESTREPLAY.adddept
is '参与者部门';
comment on column QV_INVESTREPLAY.addname
is '参与者姓名';
comment on column QV_INVESTREPLAY.isreplay
is '是否已经回复 0：未回复 1：已回复';
comment on column QV_INVESTREPLAY.allowanswer
is '单选多选题客户补充答案';
-- Create/Recreate primary, unique and foreign key constraints
alter table QV_INVESTREPLAY
add constraint PK_QV_INVESTREPLAY primary key (ID)
using index
tablespace RMSFUSION
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

2. 这里只看有个多选题， “你希望影吧播放的电影类型是？”，以及这个问题的答案。

这是问卷调查回答的答案，可以看到，存的都是 qv_answer 表对应的 id，并且以逗号分隔

3. 如何用 wm_concat 函数查出下列结果，sql 语句如下：

        select a.id,a.addusername,a.addname,a.adddept,a.addtime,to_char(WM_CONCAT(b.answername)) 类型
          from qv_investreplay a,qv_answer b
              where b.questionid = '422517' and instr(','||a.answerid||',',','||b.id||',')>0
               group by a.id,a.addusername,a.addname,a.adddept,a.addtime

关联条件使用 instr(','||a.answerid||',',','||b.id||',')>0，这样就可以关联出所有 id 对应的答案，这时候使用to_char(WM_CONCAT(b.answername)) 类型将答案以逗号拼接，其他查询字段，都要在 group by 里。

也可以用with as 做临时表 an （qv_investreplay 也可以写一个with as，只把相关字段写出来，便于测试），简单写下如下：

              with an
                   as (
                          select id,answername from qv_answer where questionid = '422517'
                    )
             select a.id,a.addusername,a.addname,a.adddept,a.addtime,to_char(WM_CONCAT(b.answername)) 类型
                      from qv_investreplay a,an b
                                where instr(','||a.answerid||',',','||b.id||',')>0
                                      group by a.id,a.addusername,a.addname,a.adddept,a.addtime

3. 有时安装的 oracle 可能版本并没有 wm_concat 函数，这时可以自定义，如下sql语句：

Oracle 11gR2和12C上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修改程序的工作量，只有通过手工创建个wm_concat函数，来临时解决该问题，但是注意，及时创建了该函数，在使用的过程中，也需要用to_char(wm_concat())方式，才能完全替代之前的应用。

一.解锁wmsys用户
alter user wmsys account unlock;

二.创建包、包体和函数
以wmsys用户登录数据库，执行下面的命令

CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/

--定义类型body:
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
--自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
/

三.创建同义词并授权

create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/

grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/

0 0

Oracle 函数 wm_concat 将列转行 用法 实例

Oracle 函数 wm_concat 将列转行用法实例