存储过程错误(列定义不明确)

Question

获取此行的以下错误创建一个以industryid 为参数的存储过程

CREATE OR REPLACE PROCEDURE businesses (industryid INDUSTRY.INDUSTRYID%TYPE) AS

CURSOR businessloop IS
select tx.UNIONTITLE,b.ABNNUMBER,b.BUSINESSNAME
from business b INNER JOIN business_industry bi
on
b.ABNNUMBER = bi.ABNNUMBER
INNER JOIN industry ind
on
bi.INDUSTRYID = ind.INDUSTRYID
INNER JOIN trade_union tx
on 
ind.UNIONID = tx.UNIONID
where 
INDUSTRY.INDUSTRYID = industryid;
BEGIN
FOR ptr IN businessloop LOOP
DBMS_OUTPUT.PUT_LINE(ptr.UNIONTITLE);
DBMS_OUTPUT.PUT_LINE(ptr.ABNNUMBER);
DBMS_OUTPUT.PUT_LINE(ptr.BUSINESSNAME);
DBMS_OUTPUT.PUT_LINE('---------------------------');
END LOOP;
end businesses;
/
Execute businesses(6);

我的输出看起来像这个

Errors: PROCEDURE BUSINESSES
Line/Col: 5/1 PL/SQL: SQL Statement ignored
Line/Col: 16/23 PL/SQL: ORA-00918: column ambiguously defined
Line/Col: 26/1 PL/SQL: Statement ignored
Line/Col: 26/22 PLS-00364: loop index variable 'PTR' use is invalid
Line/Col: 27/1 PL/SQL: Statement ignored
Line/Col: 27/22 PLS-00364: loop index variable 'PTR' use is invalid
Line/Col: 28/1 PL/SQL: Statement ignored
Line/Col: 28/22 PLS-00364: loop index variable 'PTR' use is invalid
ORA-06550: line 1, column 7:
PLS-00905: object SQL_PQETEGYQDKCHNVXHUMIWRPLSE.BUSINESSES is invalid 

我认为这条线路会出错

INDUSTRY.INDUSTRYID = industryid;

Answer 1

在评论中回答：

我认为这行有错误你可能是对的。重命名过程的参数并消除名称干扰Akina 2小时前

谢谢，伙计，这就是原因，@Akina–sandun wijerathne Jerry 2小时前

Answer 2

回答

CREATE OR REPLACE PROCEDURE businesses (industryidx INDUSTRY.INDUSTRYID%TYPE) AS

CURSOR businessloop IS
select tx.UNIONTITLE,b.ABNNUMBER,b.BUSINESSNAME
from business b INNER JOIN business_industry bi
on
b.ABNNUMBER = bi.ABNNUMBER
INNER JOIN industry ind
on
bi.INDUSTRYID = ind.INDUSTRYID
INNER JOIN trade_union tx
on 
ind.UNIONID = tx.UNIONID
where 
ind.INDUSTRYID = industryidx;
BEGIN
FOR ptr IN businessloop LOOP
DBMS_OUTPUT.PUT_LINE(ptr.UNIONTITLE);
DBMS_OUTPUT.PUT_LINE(ptr.ABNNUMBER);
DBMS_OUTPUT.PUT_LINE(ptr.BUSINESSNAME);
DBMS_OUTPUT.PUT_LINE('---------------------------');
END LOOP;
end businesses;
/
Execute businesses(6);