-- 我的思路是把字符串拆成最多6个字段(每个字段最多3900个字符),需要的话再用||拼接起来,可以参考一下: SQL> select sum(length(id)+1)-1 as lengths from test_wto_xf_44;
LENGTHS ---------- 11908SQL> select max(decode(length_level,1,my_str,null)) as my_str1, 2 max(decode(length_level,2,my_str,null)) as my_str2, 3 max(decode(length_level,3,my_str,null)) as my_str3, 4 max(decode(length_level,4,my_str,null)) as my_str4, 5 max(decode(length_level,5,my_str,null)) as my_str5, 6 max(decode(length_level,6,my_str,null)) as my_str6 7 from (select length_level, 8 WMSYS.WM_CONCAT(id) as my_str 9 from (select id, 10 CEIL((sum(length(id)+1) over(order by id) -1)/3900) as length_level 11 from test_wto_xf_44) t 12 group by length_level) z 13 ;
create or replace function postgresql(p_tname varchar2, p_colname varchar2) return clob is v_tmp varchar2(200); v_result clob; v_cum sys_refcursor; begin open v_cum for 'select ' || p_colname || ' from ' || p_tname; loop fetch v_cum into v_tmp; exit when v_cum%notfound; v_result := v_result || v_tmp; end loop; dbms_output.put_line(v_result); return v_result; end; 试试这个:select postgresql('emp','ename') from dual;
可以换个思路 如://先截取,在拼接 SELECT COL1, SUBSTR(WMSYS.WM_CONCAT(COL2),0,3990)|| SUBSTR(WMSYS.WM_CONCAT(COL2),3991,6990).... FROM TEMP GROUP BY COL1
SQL> SQL> create or replace function wm_concat return clob is 2 Result clob; 3 CURSOR cv_test IS 4 SELECT id FROM test ORDER BY id ASC; 5 v_id NUMBER(20); 6 v_clob CLOB; 7 BEGIN 8 v_clob:=empty_clob(); 9 OPEN cv_test; 10 LOOP 11 FETCH cv_test INTO v_id; 12 EXIT WHEN cv_test%NOTFOUND ; 13 v_clob:=v_clob||v_id; 14 END LOOP ; 15 return(v_clob); 16 end wm_concat; 17 /
SQL> select sum(length(id)+1)-1 as lengths from test_wto_xf_44;
LENGTHS
----------
11908SQL> select max(decode(length_level,1,my_str,null)) as my_str1,
2 max(decode(length_level,2,my_str,null)) as my_str2,
3 max(decode(length_level,3,my_str,null)) as my_str3,
4 max(decode(length_level,4,my_str,null)) as my_str4,
5 max(decode(length_level,5,my_str,null)) as my_str5,
6 max(decode(length_level,6,my_str,null)) as my_str6
7 from (select length_level,
8 WMSYS.WM_CONCAT(id) as my_str
9 from (select id,
10 CEIL((sum(length(id)+1) over(order by id) -1)/3900) as length_level
11 from test_wto_xf_44) t
12 group by length_level) z
13 ;
MY_STR1 MY_STR2 MY_STR3 MY_STR4 MY_STR5 MY_STR6
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1-1,1-10,1-100,1-100000,1-1000004,1-1000015,1-1000017,1-100002,1-1000028,1-10000 1-1001532,1-100243,1-1002427,1-1002426,1-1002424,1-1002420,1-100242,1-1002419,1- 1-1002437,1-1003336,1-1003335,1-1003334,1-1003333,1-1003332,1-1003331,1-1003330, 1-1003533,1-1003557,1-1003556,1-1003555,1-1003554,1-1003553,1-1003552,1-100355,1
自己写一个类似的函数替换下,不用系统的wm_concat()函数
create or replace function postgresql(p_tname varchar2, p_colname varchar2)
return clob is v_tmp varchar2(200);
v_result clob;
v_cum sys_refcursor;
begin open v_cum for 'select ' || p_colname || ' from ' || p_tname;
loop
fetch v_cum
into v_tmp;
exit when v_cum%notfound;
v_result := v_result || v_tmp;
end loop;
dbms_output.put_line(v_result);
return v_result;
end;
试试这个:select postgresql('emp','ename') from dual;
如://先截取,在拼接
SELECT COL1,
SUBSTR(WMSYS.WM_CONCAT(COL2),0,3990)||
SUBSTR(WMSYS.WM_CONCAT(COL2),3991,6990)....
FROM TEMP GROUP BY COL1
用这个类型来处理大于4000个字符,
这种方法在动态sql中经常用到(拼SQL)
SQL> select * from test;
ID AMMETER_ID TIME
--------------------- --------------------- -----------
1 1 2010-1-1
2 1 2010-7-20
3 1 2010-7-21
4 1 2010-7-22
5 2 2010-7-1
6 2 2010-7-2
7 2 2010-7-22
7 rows selected
SQL>
SQL> create or replace function wm_concat return clob is
2 Result clob;
3 CURSOR cv_test IS
4 SELECT id FROM test ORDER BY id ASC;
5 v_id NUMBER(20);
6 v_clob CLOB;
7 BEGIN
8 v_clob:=empty_clob();
9 OPEN cv_test;
10 LOOP
11 FETCH cv_test INTO v_id;
12 EXIT WHEN cv_test%NOTFOUND ;
13 v_clob:=v_clob||v_id;
14 END LOOP ;
15 return(v_clob);
16 end wm_concat;
17 /
Function created
SQL> SELECT wm_concat FROM dual;
WM_CONCAT
--------------------------------------------------------------------------------
1234567
SQL>