记录下oralcle多表多行多字段的一个小实现函数

最近遇到个需求，需要将多表多行多列的值转换成一个字符串输出，然后大家纷纷自己写，有的在数据库里用游标，有的在java中写循环，都比较耗时间。大概想了一下，在数据库中处理效率应该比较高，然后最好是能提供一个公用的实现函数。其中用到了拆分字符串方法非原创，网上直接抄袭的

1.先定义拆分字符串的变量

 

CREATE OR REPLACE TYPE strsplit_type IS TABLE OF VARCHAR2 (400); 

 

2.函数大概写法，使用package。如下： 包含定义和body

create or replace package PKG_XXX_PUB is

  -- Author  : lijq
  -- Created : XXX
  -- Purpose : 北京XXX部分公用方法

  --根据行转列公用方法
  --table_str : 取数的表，目前只支持一张,如果多张表建议写成临时表的方式先行关联好临时表中需要包括is_last,state字段，例如（select pc.pc_id,cd.dep_name,pc.is_last,pc.state from pm_contract pc ,core_department cd where cd.dep_id = pc.dep_id and cd.is_last = 1 and state = 1）
  --id_str_column : 表中查询的ID字段名称或其他字段名称
  --id_value ：表中查询ID字段的值或其他字段的值
  --columns_str ：需要查询出来并且拼接的列名,支持多列，以逗号间隔
  --columns_split_str :  列与列之间的分隔符
  --rows_split_str : 行与行之间的分隔符
  function getStr(table_str in varchar2,id_str_column in varchar2,
    id_value in varchar2,columns_str in varchar2,columns_split_str in varchar2,rows_split_str in varchar2) return varchar2;
  --字符串分割方法
  function strsplit(p_value varchar2,
                                    p_split varchar2)return strsplit_type pipelined;
end PKG_XXX_PUB;
/

create or replace package body PKG_XXX_PUB is




  --行转列通用方法
  function getStr(table_str in varchar2,id_str_column in varchar2,
    id_value in varchar2,columns_str in varchar2,columns_split_str in varchar2,rows_split_str in varchar2) return varchar2
    is
    --定义返回字符串
    v_str    varchar2(5000):='';
    --定义动态sql
    V_sql  varchar2(5000);
    --定义动态游标
    TYPE ref_cursor_type IS REF CURSOR;

    usrs ref_cursor_type;
    --每个字段的名称
    v_column varchar2(1000);
    --字段拼接sql
    v_column_sql  varchar2(1000);

  begin
    v_column_sql := '';
    --拼接字段信息，并且将列信息进行拼接
    V_sql := 'select * from   table(PKG_XXX_PUB.strsplit('''||columns_str||''','',''))' ;
    open usrs for V_sql;
    loop
      fetch usrs into v_column;
      exit when usrs%notfound;
      v_column_sql := v_column_sql ||'||'''||columns_split_str||'''||'||v_column;
    end loop;

    close usrs;
    --如果v_column_sql不为空，则去除前面两个||
    if v_column_sql is not null  then
      select substr(v_column_sql,3+length(columns_split_str)+4) into v_column_sql from dual;

    end if;


    --查询出所有行的数据
    V_sql := 'select '||v_column_sql||' from '||table_str||' where  state = 1 and '
    ||id_str_column||' = '''||id_value||' '' ';
    --按行进行拼接
    open usrs for V_sql;
    loop
      fetch usrs into v_column;
      exit when usrs%notfound;
      v_str := rows_split_str||v_column||v_str;
    end loop;

    close usrs;
    select substr(v_str,1+length(rows_split_str)) into v_str from dual;

    return(v_str);
  end getStr;

  --拆分字符串
 function strsplit(p_value varchar2,
                                    p_split varchar2)
 return strsplit_type
  pipelined is
  v_idx       integer;
  v_str       varchar2(500);
  v_strs_last varchar2(4000) := p_value;

begin
  loop
    v_idx := instr(v_strs_last, p_split);
    exit when v_idx = 0;
    v_str       := substr(v_strs_last, 1, v_idx - 1);
    v_strs_last := substr(v_strs_last, v_idx + 1);
    pipe row(v_str);
  end loop;

  pipe row(v_strs_last);
  return;

end strsplit;


end PKG_XXX_PUB;

3.最后实验效果：

调用

select  PKG_XXX_PUB.getStr('PM_CONTRACT','PC_CID','2','PC_CODE,PC_NAME',',','|') from dual;

输出：行之间以|间隔，列之间以,间隔

BJ.SJA100917020000.SW2,研发中心项目0209内部|BJ.SJA100916110002.SW1,HT20161101|BJ.16147.SW22,测试内部流程|BJ.16147.SW24,测速的|BJ.SJHDSJY15080076.SW5,荣耀71103|BJ.SJHDSJY15100061.SW0,夏洛特烦恼1013|BJ.12.10-2.SW0,12.10-2|BJ.SJHDSJY15080139.SW0,桌椅子|2015.6.2-test,2015.6.2-test|12.11,12.11|BJ.SJHDSJY15080164.SW0,罗技鼠标啊|BJ.SJHDSJY15080184.SW1,长城保护合同2|BJ.SJHDSJY15100004.SW1,喜糖1008|BJ.SJHDSJY15090097.SW0,iphone6s|BJ.SJHDSJY15110041.SW4,斗鱼合同|BJ.SJA100915080017.SW7,SMHT002|BJ.SJHDSJY15080063.SW0,测试子表数据|ljq-ceshi,ljq-ceshi|BJ.16133.SW8,23123大森蝶三|BJ.SJHDSJY15080200.SW1,寰宇牌抹布合同2|BJ.lijq-test-temp.SW6,111111|BJ.SJHDSJY15080004.SW0,2015.8.3|BJ.SJHDSJY15080093.SW1,好吃烧鸡|BJ.lijq-test-temp.SW7,tjq-test