存储过程
存储过程
提问于 2017-05-21 18:28:41
在这个过程中我有4张桌子要用
业务(编号、名称)
Business_industry(工业编号)
工业(工业，工会)
Trade_union(工会)
我被指派在一行中获得工会头衔，所有业务( ABNNUMBER )和业务名称都使用存储过程在不同的行中获得。
我试过的是：
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所需输出应为
单行(一行)
ABNNUMBER业务名称(下一行)
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-05-22 13:04:59
我怀疑你需要这样的东西：
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;函数listagg可在Oracle11g或更高版本中使用。
输出：
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
-----页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44100388
复制相关文章
相似问题
腾讯云开发者
