linux数据库查询结果集,拼接查询sql中指定列的结果集

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

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

CREATEORREPLACEFUNCTIONf_con_colunn_set(p_sqlINVARCHAR2,

--传入的sql语句

p_column_indexINTEGERDEFAULT1,

--需要连接的字段索引位置(从1开始，默认为1)

p_con_strINVARCHAR2DEFAULT','

--连接时的分隔符，默认为逗号

)RETURNVARCHAR2IS

/*适用于需要将sql语句的某列查询结果(大于一条记录)拼接成一个字段返回，

限制了返回长度小于等于4000(因用在SQL中，不能大于varchar2的4000个字符限制)

*/

v_value       VARCHAR2(4000);

v_return      VARCHAR2(8000) :='';

sqlstr_cursorINTEGER;

v_ignoreINTEGER;

v_nameINTEGER;

v_col_countINTEGER;

v_column_desc dbms_sql.desc_tab;

BEGIN

v_name := dbms_sql.open_cursor;

dbms_sql.parse(v_name, p_sql, dbms_sql.native);

dbms_sql.describe_columns(v_name, v_col_count, v_column_desc);

DBMS_SQL.close_cursor(v_name);

IF p_column_index > 0ANDp_column_index <= v_col_countTHEN

sqlstr_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(sqlstr_cursor, p_sql, DBMS_SQL.native);

DBMS_SQL.define_column(sqlstr_cursor,

p_column_index,

v_column_desc(p_column_index).col_name,

4000);

v_ignore := DBMS_SQL.EXECUTE(sqlstr_cursor);

LOOP

IF DBMS_SQL.fetch_rows(sqlstr_cursor) > 0THEN

DBMS_SQL.COLUMN_VALUE(sqlstr_cursor, p_column_index, v_value);

IF length(v_return || v_value) > 4000THEN

EXIT;

ELSE

v_return := v_return || p_con_str || v_value;

ENDIF;

ELSE

EXIT;

ENDIF;

ENDLOOP;

v_return := substr(v_return, length(p_con_str) + 1, 4000);

DBMS_SQL.close_cursor(sqlstr_cursor);

ENDIF;

RETURNv_return;

EXCEPTION

WHENOTHERSTHEN

IF DBMS_SQL.is_open(v_name)THEN

DBMS_SQL.close_cursor(v_name);

ENDIF;

IF DBMS_SQL.is_open(sqlstr_cursor)THEN

DBMS_SQL.close_cursor(sqlstr_cursor);

ENDIF;

RETURN'';

ENDf_con_colunn_set;CREATE OR REPLACE FUNCTION f_con_colunn_set(p_sql IN VARCHAR2,

--传入的sql语句

p_column_index INTEGER DEFAULT 1,

--需要连接的字段索引位置(从1开始，默认为1)

p_con_str IN VARCHAR2 DEFAULT ','

--连接时的分隔符，默认为逗号

) RETURN VARCHAR2 IS

/*适用于需要将sql语句的某列查询结果(大于一条记录)拼接成一个字段返回，

限制了返回长度小于等于4000(因用在SQL中，不能大于varchar2的4000个字符限制)

*/

v_value VARCHAR2(4000);

v_return VARCHAR2(8000) := '';

sqlstr_cursor INTEGER;

v_ignore INTEGER;

v_name INTEGER;

v_col_count INTEGER;

v_column_desc dbms_sql.desc_tab;

BEGIN

v_name := dbms_sql.open_cursor;

dbms_sql.parse(v_name, p_sql, dbms_sql.native);

dbms_sql.describe_columns(v_name, v_col_count, v_column_desc);

DBMS_SQL.close_cursor(v_name);

IF p_column_index > 0 AND p_column_index <= v_col_count THEN

sqlstr_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(sqlstr_cursor, p_sql, DBMS_SQL.native);

DBMS_SQL.define_column(sqlstr_cursor,

p_column_index,

v_column_desc(p_column_index).col_name,

4000);

v_ignore := DBMS_SQL.EXECUTE(sqlstr_cursor);

LOOP

IF DBMS_SQL.fetch_rows(sqlstr_cursor) > 0 THEN

DBMS_SQL.COLUMN_VALUE(sqlstr_cursor, p_column_index, v_value);

IF length(v_return || v_value) > 4000 THEN

EXIT;

ELSE

v_return := v_return || p_con_str || v_value;

END IF;

ELSE

EXIT;

END IF;

END LOOP;

v_return := substr(v_return, length(p_con_str) + 1, 4000);

DBMS_SQL.close_cursor(sqlstr_cursor);

END IF;

RETURN v_return;

EXCEPTION

WHEN OTHERS THEN

IF DBMS_SQL.is_open(v_name) THEN

DBMS_SQL.close_cursor(v_name);

END IF;

IF DBMS_SQL.is_open(sqlstr_cursor) THEN

DBMS_SQL.close_cursor(sqlstr_cursor);

END IF;

RETURN '';

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;