oralceカスタム関数、複数のレコード値の組合せ文字列を返す
732 ワード
create or replace function fun_getorderproducterpcodes(v_ordersysno number) --
return varchar2 is
v_all varchar2(1000);
begin
DECLARE
v_medCode VARCHAR2 (100);
CURSOR D_cursor IS
select p.erpcode from SoOrderItem t
inner join PdProduct p
on p.sysno=t.productsysno
where t.ordersysno=v_ordersysno
order by t.ProductSysNo asc;
BEGIN
OPEN D_cursor;
loop
fetch D_cursor into v_medCode;
EXIT WHEN D_cursor%NOTFOUND;
if length(v_all) > 0 THEN
v_all := v_all ||','|| v_medCode;
ELSE
v_all := v_all || v_medCode;
END IF;
END LOOP;
close D_cursor;
end;
return v_all;
end;