一、描述
小需求又来了,现在需要将某表上的记录查询出来并且要求连接使用。通过查找后发现Oracle有个内部函数 wm_concat,这个函数在wmsys用户下。使用时那是当然的容易。一般人我都不告诉他(开个小玩笑)。
二、实验
1.创建实验表
-
SAM@orcl > create table t1 (id int, name varchar2(10));
-
Table created.
-
-
SAM@orcl > insert into t1 values (1,'sam1');
-
1 row created.
-
-
SAM@orcl > insert into t1 values (2,'sam2');
-
1 row created.
-
-
SAM@orcl > insert into t1 values (3,'sam3');
-
1 row created.
-
-
SAM@orcl > insert into t1 values (4,'sam4');
-
1 row created.
-
-
SAM@orcl > insert into t1 values (5,'sam5');
-
1 row created.
-
-
SAM@orcl > commit;
-
Commit complete.
-
-
SAM@orcl > select * from t1;
-
-
ID NAME
-
---------- ----------
-
1 sam1
-
2 sam2
-
3 sam3
-
4 sam4
-
5 sam5
2.使用wm_concat函数查询,并得到想要的结果
-
SAM@orcl > select wm_concat(id),wm_concat(name) from t1;
-
-
WM_CONCAT(ID)
-
--------------------------------------------------------------------------------
-
WM_CONCAT(NAME)
-
--------------------------------------------------------------------------------
-
1,2,3,4,5
-
sam1,sam2,sam3,sam4,sam5
3.到此处问题已被解决,但更加深入了解一下,我打算建立一个t2表,将查询出来的数据插入到t2表中,得到的结果却报错,数据类型不匹配。
-
SAM@orcl > create table t2 (sum_id int,sum_name varchar2(100));
-
Table created.
-
-
SAM@orcl > insert into t2 select wm_concat(id),wm_concat(name) from t1;
-
insert into t2 select wm_concat(id),wm_concat(name) from t1
-
*
-
ERROR at line 1:
-
ORA-00932: inconsistent datatypes: expected NUMBER got CLOB
4.再次验证,看数据类型到底变为什么?将查出结果直接放在新建表t3中,成功。
-
SAM@orcl > create table t3 as select wm_concat(id) as sum_id,wm_concat(name) as sum_name from t1;
-
Table created.
5.查看新表t3结构,数据类型发生变化,由源来的int, varchar2变为了clob
-
SAM@orcl > desc t3;
-
Name Null? Type
-
----------------------------------------- -------- ----------------------------
-
SUM_ID CLOB
-
SUM_NAME CLOB
6.查询t3表内容
-
SAM@orcl > select * from t3;
-
-
SUM_ID
-
--------------------------------------------------------------------------------
-
SUM_NAME
-
--------------------------------------------------------------------------------
-
1,2,3,4,5
-
sam1,sam2,sam3,sam4,sam5
四、扩展知识
1.此时开发人员认为这个函数果然厉害,就说能不能看一下这个函数如何写的,与此同时也想到我的心里去了,看看Oracle开发人员的思路。说不定能大开眼界。采用dbms_matadata.get_ddl来取function脚本。结果发现该Function已被加密,无法读取。加密方式为wrap加密。
-
SAM@orcl > set long 99999 pages 1000
-
SAM@orcl > SELECT SYS.DBMS_METADATA.get_ddl ('FUNCTION','WM_CONCAT','WMSYS') FROM DUAL;
-
-
SYS.DBMS_METADATA.GET_DDL('FUNCTION','WM_CONCAT','WMSYS')
-
--------------------------------------------------------------------------------
-
-
CREATE OR REPLACE FUNCTION "WMSYS"."WM_CONCAT" wrapped
-
a000000
-
1
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
abcd
-
8
-
53 96
-
antgYqrbNGLSC7Re+71hueZFyT4wg0SvLZ6pyi+mUCJD1KOso
-
xPiallQXtwu7BTsCmx9/hIg
-
+ln6MEC75cHHT8YFQPvfjqPM1MuiY1Z0kXN0TQ0W8KE1SkAqjh/+tB/q
-
+oI45dREmV5OHaYy
-
H/E=
五、总结
有时候通过小小的知识点就可以帮助我们DBA和开发人员解决很大的问题,所以此时我想到的就是知识是无限的海洋,等待着我们不停的探索与学习。再有就是我认为古人云:三人行,必有我师。那是相当的corrent and nice. 最后还是希望我能够不断的成长与进步。fighting! Where there is a will, there is a way.