create or replace function ListNation(regionName char) return varchar2 is myOutput varchar2(300); nation_Name varchar2(200); begin myOutput := ''; for rowResult in (select r_regionkey, r_name, listagg(rtrim(n_name), ', ') within group(order by n_name) as nation_Name from region inner join nation on r_regionkey = n_regionkey where r_name = upper(regionName) group by r_name, r_regionkey) loop myOutput := rowResult.r_regionkey|| ' ' ||rtrim(rowResult.r_name)|| ': ' || rowResult.nation_Name; end loop; return myOutput; end ListNation; / select listnation('Americ') from dual;
SQL> select listnation('Americ') from dual; LISTNATION('AMERIC') ---------------------------------------------------------------------------------------------------- 1 row selected.
myOutput
myOutput := rowResult.r_regionkey|| ' ' ||rtrim(rowResult.r_name)|| ': ' || rowResult.nation_Name;
" : "
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)