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

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

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;