存储过程动态生成列（一个记录号对应多条值，拼装成一条记录）

存储过程动态生成列（一个记录号对应多条值，拼装成一条记录）

1.创建包
create or replace
PACKAGE test_pkg AS
  type t_cursor is ref cursor;
  type t_record is record(AORD CHAR(1),SRNU CHAR(5));
  function test_function(p_SRNU AIIS01.BSV.SRNU%TYPE) return varchar2;
  FUNCTION test_fun return varchar2;
  PROCEDURE test_proc(cursor_t out t_cursor);
END test_pkg;
2.实现函数和存储过程
create or replace
PACKAGE BODY test_pkg AS
function test_function(p_SRNU AIIS01.BSV.SRNU%TYPE) return varchar2
IS
cc varchar2(10):='';
begin

   -- if (p_SRNU ='00101') THEN
    --   cc:='BLUE-TRI';
   -- else
   --   cc:='cccgg';
   -- end if;
   select bsv.srnu into cc from bsv where bsv.srnu=p_SRNU;
    return  cc;

end test_function;


FUNCTION test_fun return varchar2 IS
  cursortest t_cursor;
  aa t_record;
  rec_service varchar2(1000);
  v_header varchar2(3000):=''; --定义列的数组，不断累加列
  sqlCURSERVICE_TYPE VARCHAR2(100);--定义变量
  BEGIN
   sqlCURSERVICE_TYPE:='SELECT bsv.AORD,bsv.srnu FROM BSV,fsr,flt where flt.flnu=fsr.flnu and fsr.srnu=bsv.srnu';
    open cursortest for sqlCURSERVICE_TYPE;
    loop
      fetch cursortest into aa;
       EXIT WHEN cursortest%NOTFOUND;
       if not(trim(rec_service) is null) then
        rec_service:=rec_service||',test_pkg.test_function('||aa.srnu||') as '||aa.aord||aa.srnu;
       else
        rec_service:=',test_pkg.test_function('||aa.srnu||') as '||aa.aord||aa.srnu;
       end if;
      
    end loop;
    return rec_service;
  end test_fun;

  PROCEDURE test_proc(cursor_t out t_cursor) IS
     sqlSCHEDULE   VARCHAR2 (9000);
    v_COlHEADER_SERVICE varchar2(3000):='';
  BEGIN
    v_COlHEADER_SERVICE:=test_fun;
   sqlSCHEDULE:='select flt.aord '||v_COlHEADER_SERVICE||' from flt,fsr,bsv';
    --sqlschedule:='select * from flt';
    open cursor_t for sqlschedule;   
   
  END;

END test_pkg;