Stored procedure

Question

I have 4 tables to be used in the procedure

business(abnnumber,name)

business_industry(abnnumber,industryid)

industry(industryid,unionid)

trade_union(unionid)

I was assigned to get trade union title in one line and all the businesses ABNNUMBER and business name in different lines using stored procedure.

What I tried is:

CREATE [OR REPLACE] PROCEDURE INDUSTRY_INFORMATION
[enter image description here][1](P_INDUSTRYID in integer,
P_UNIONTITLE OUT VARCHAR2,
P_BUSINESSNAME OUT VARCHAR2) AS
BEGIN
SELECT TRADE_UNION.UNIONTITLE, BUSINESS.BUSINESSNAME INTO             
P_UNIONTITLE,P_BUSINESSNAME
FROM BUSINESS inner join BUSINESS_INDUSTRY ON 
BUSINESS.ABNNUMBER=BUSINESS_INDUSTRY.ABNNUMBER 
INNER JOIN INDUSTRY ON BUSINESS_INDUSTRY.INDUSTRYID=INDUSTRY.INDUSTRYID
INNER JOIN TRADE_UNION ON INDUSTRY.UNIONID=TRADE_UNION.UNIONID;
END;

Sample data is in the link http://www.mediafire.com/file/8c4dwn4n88n8a42/strd_procedure.txt

Required output should be

UNIONTITLE (one line)

ABNNUMBER BUSINESS NAME (next line)

`` [1]: https://i.sstatic.net/sGuwe.jpg

Answer 1

I suspect that You need something like this:

create or replace procedure industry_info is
begin
  for r in (
    select tu.uniontitle ut, 
           listagg('['||b.abnnumber||'] '||b.businessname, ', ') 
             within group (order by b.businessname) blist
      from business b
        join business_industry bi on b.abnnumber = bi.abnnumber 
        join industry i on bi.industryid = i.industryid
        join trade_union tu on i.unionid = tu.unionid
      group by tu.uniontitle )
  loop
    dbms_output.put_line(r.ut);
    dbms_output.put_line(r.blist);
    dbms_output.put_line('-----');
  end loop;
end;

Function listagg is available in Oracle 11g or later.

Output:

Cleaners' Union
[12345678912] Consolidated Proerty Services, [12345678929] Gold Cleaning Services, [12345678926] Home Cleaning Services, [12345678924] Shine Cleaning
-----
Construction Workers' Union
[12345678920] Build a House, [12345678919] Construction Solutions, [12345678922] Joe's Rubbish Removal, [12345678918] Leak and Roof Repair, [12345678928] Muscle Rubbish Removals
-----
Electricians' Union
[12345678916] Change the Fuse Electricals, [12345678921] Hire a Wire, [12345678917] Vicky Electricals
-----
Movers' Union
[12345678913] Kohlan Movers, [12345678925] Moveit
-----
Mowers' Union
[12345678923] Do it Right Mowers, [12345678911] James Mowers and Landscape
-----
Plumbers' Union
[12345678927] 24X7 Plumbing Service, [12345678915] Anytime Plumbers, [12345678914] Pumbers Delivered
-----