拼接查询sql中指定列的结果集

拼接查询sql中指定列的结果集

函数适用于：需要将指定查询sql中的某列拼接成以指定字符分隔连接的字符串。

不足：因函数的返回值为varchar2,且通常拼接得到的字符串会用于查询sql（因用在SQL中，不能大于varchar2的4000个字符限制）中，因此限制了返回长度小于等于4000

1. CREATE OR REPLACE FUNCTION f_con_colunn_set(p_sql IN VARCHAR2,  
2.                                             --传入的sql语句   
3.                                             p_column_index INTEGER DEFAULT 1,  
4.                                             --需要连接的字段索引位置（从1开始，默认为1）    
5.                                             p_con_str IN VARCHAR2 DEFAULT ','  
6.                                             --连接时的分隔符，默认为逗号   
7.                                             ) RETURN VARCHAR2 IS  
8.   /*适用于需要将sql语句的某列查询结果（大于一条记录）拼接成一个字段返回，  
9.   限制了返回长度小于等于4000（因用在SQL中，不能大于varchar2的4000个字符限制）  
10.   */  
11.   v_value       VARCHAR2(4000);  
12.   v_return      VARCHAR2(8000) := '';  
13.   sqlstr_cursor INTEGER;  
14.   v_ignore      INTEGER;  
15.   v_name        INTEGER;  
16.   v_col_count   INTEGER;  
17.   v_column_desc dbms_sql.desc_tab;  
18.   
19. BEGIN  
20.   v_name := dbms_sql.open_cursor;  
21.   dbms_sql.parse(v_name, p_sql, dbms_sql.native);  
22.   dbms_sql.describe_columns(v_name, v_col_count, v_column_desc);  
23.   DBMS_SQL.close_cursor(v_name);  
24.   IF p_column_index > 0 AND p_column_index <= v_col_count THEN  
25.     sqlstr_cursor := DBMS_SQL.open_cursor;  
26.     DBMS_SQL.parse(sqlstr_cursor, p_sql, DBMS_SQL.native);  
27.     DBMS_SQL.define_column(sqlstr_cursor,  
28.                            p_column_index,  
29.                            v_column_desc(p_column_index).col_name,  
30.                            4000);  
31.     v_ignore := DBMS_SQL.EXECUTE(sqlstr_cursor);  
32.     LOOP  
33.       IF DBMS_SQL.fetch_rows(sqlstr_cursor) > 0 THEN  
34.         DBMS_SQL.COLUMN_VALUE(sqlstr_cursor, p_column_index, v_value);  
35.         IF length(v_return || v_value) > 4000 THEN  
36.           EXIT;  
37.         ELSE  
38.           v_return := v_return || p_con_str || v_value;  
39.         END IF;  
40.       ELSE  
41.         EXIT;  
42.       END IF;  
43.     END LOOP;  
44.     v_return := substr(v_return, length(p_con_str) + 1, 4000);  
45.     DBMS_SQL.close_cursor(sqlstr_cursor);  
46.   END IF;  
47.   RETURN v_return;  
48. EXCEPTION  
49.   WHEN OTHERS THEN  
50.     IF DBMS_SQL.is_open(v_name) THEN  
51.       DBMS_SQL.close_cursor(v_name);  
52.     END IF;  
53.     IF DBMS_SQL.is_open(sqlstr_cursor) THEN  
54.       DBMS_SQL.close_cursor(sqlstr_cursor);  
55.     END IF;  
56.     RETURN '';  
57. END f_con_colunn_set;  

--测试方法
--1)只传SQL,默认返回col1的以逗号分隔连接值：test1,test2
SELECT f_con_colunn_set('select ''test1'' col1,1 col2 from dual union all select ''test2'' col1,2 col2 from dual')
  FROM dual;

--2)传SQL,传col2的索引值（从1开始）,返回col2以逗号分隔的连接值： 1,2
SELECT f_con_colunn_set('select ''test1'' col1,1 col2 from dual union all select ''test2'' col1,2 col2 from dual',
                        2)
  FROM dual;
--3)传SQL,传col2的索引值（从1开始）,分隔值';'，返回col2以分号号分隔的连接值1;2
SELECT f_con_colunn_set('select ''test1'' col1,1 col2 from dual union all select ''test2'' col1,2 col2 from dual',
                        2,
                        ';')
  FROM dual;
--4)传SQL,传索引值（从1开始）3，传入的索引值超过SQL的列索引集合，不存在此列，返回空值
SELECT f_con_colunn_set('select ''test1'' col1,1 col2 from dual union all select ''test2'' col1,2 col2 from dual',
                        3)
  FROM dual;