技术标签:
【中文标题】必须声明错误 PLS-00201 标识符【英文标题】:Error PLS-00201 identifier must be declared 【发布时间】:2015-04-30 00:39:17 【问题描述】:所以我一直有这个错误,我对发生了什么一无所知。我用谷歌搜索,阅读了一些关于这个错误的问题,但没有任何效果。我只是想让它运行,这样我就可以完成我的项目。
create or replace procedure LOWINVENTORY is--VariablesID number;itemNamed char(15);description char(20);startQty number;--Define inventory item cursorcursor nextItem isselect inventory.itemID, inventory.itemName, inventory.description, inventory.startQty;from inventorywhere inventory.startQty < 5;beginopen nextItem;fetch netxtItem into ID, itemNamed, description, startQty;if nextItem%notfound then dbms_output.put_line("No items in need of reordering.");else dbms_output.put_line("*********************"); dbms_output.put_line("++Inventory Report++"); dbms_output.put_line("*********************"); dbms_output.put_line("Item Name---ID------Description----------Quantity"); loop dbms_output.put_line(itemNamed||"-"||ID||"-"||description||"-"||startQty); fetch netxtItem into ID, itemNamed, description, startQty; if nextItem%notfound then dbms_output.put_line("************END REPORT*************"); exit when nextItem%notfound; end loop;end lowInventory;
错误:开始低库存;结尾; *第 1 行的错误:ORA-06550:第 1 行,第 9 列:PLS-00201:必须声明标识符“LOWINVENTORY”ORA-06550:第 1 行,第 7 列:PL/SQL:语句被忽略
编辑:
INSERT INTO inventory(itemID, itemLocation, itemName, description, typicalPrice, startQty)VALUES(24548576, "toolbox1", "wrench", "turns bolts", 14.00, 6);INSERT INTO inventory(itemID, itemLocation, itemName, description, typicalPrice, startQty)VALUES(83742345, "toolbox1", "pliers", "grabs stuff", 11.00, 4);INSERT INTO inventory(itemID, itemLocation, itemName, description, typicalPrice, startQty)VALUES(39287426, "chest2", "jigsaw", "cuts stuff", 28.00, 3);INSERT INTO inventory(itemID, itemLocation, itemName, description, typicalPrice, startQty)VALUES(48927349, "chest1", "blowtorch", "torches stuff", 330.00, 2);INSERT INTO inventory(itemID, itemLocation, itemName, description, typicalPrice, startQty)VALUES(85463455, "bench3", "oil filter", "filters stuff", 16.00, 20);
表创建:
create table inventory ( itemID number(8) not null primary key constraint lengthCHK8 check(length(itemID)=8), itemLocation varchar2(10), itemName varchar2(12) not null, description varchar2(20), typicalPrice decimal(7,2) not null constraint notNeg2 check(typicalPrice >=0), startQty number(4) not null constraint notNeg5 check(startQty >=0) );
【问题讨论】:
【参考方案1】:你的代码充满了愚蠢的错误,首先,
select inventory.itemID, inventory.itemName, inventory.description, inventory.startQty; --> semicolan here, the statment doesnt endfrom inventorywhere inventory.startQty < 5;
第二,
cursor nextItem is --> using nextItem as name in cursor but "netxtItem" while opening it, a typo
第三,
如果在 else 部分没有结束,循环
这是已经编译好的程序,我创建了清单表,其中只有 4 列在过程中用于编译过程
SQL> create or replace procedure LOWINVENTORY is 2 3 --Variables 4 ID number; 5 itemNamed char(15); 6 description char(20); 7 startQty number; 8 9 --Define inventory item cursor 10 cursor nextItem is 11 select inventory.itemID, inventory.itemName, inventory.description, inventory.startQty 12 from inventory 13 where inventory.startQty < 5; 14 15 begin 16 open nextItem; 17 fetch nextItem into ID, itemNamed, description, startQty; 18 if nextItem%notfound then 19 dbms_output.put_line("No items in need of reordering."); 20 else 21 dbms_output.put_line("*********************"); 22 dbms_output.put_line("++Inventory Report++"); 23 dbms_output.put_line("*********************"); 24 dbms_output.put_line("Item Name---ID------Description----------Quantity"); 25 loop 26 dbms_output.put_line(itemNamed||"-"||ID||"-"||description||"-"||startQty); 27 fetch nextItem into ID, itemNamed, description, startQty; 28 if nextItem%notfound then 29 dbms_output.put_line("************END REPORT*************"); 30 exit when nextItem%notfound; 31 end if; 32 end loop; 33 end if; 34 35 end lowInventory; 36 /Procedure created.SQL>
编辑
SQL> insert into inventory (itemid,itemname,description,startqty) values (1,"abc","descp1",1) ;1 row created.SQL> insert into inventory (itemid,itemname,description,startqty) values (2,"abcd","descp2",1) ;1 row created.SQL> set serveroutput on;SQL> exec lowInventory;*********************++Inventory Report++*********************Item Name---ID------Description----------Quantityabc -1-descp1 -1abcd -2-descp2 -1************END REPORT*************PL/SQL procedure successfully completed.SQL>
编辑 2
使用您提供的数据
SQL> exec lowinventory;*********************++Inventory Report++*********************Item Name---ID------Description----------Quantityjigsaw -39287426-cuts stuff -3pliers -83742345-grabs stuff -4jigsaw -39287426-cuts stuff -3blowtorch -48927349-torches stuff -2************END REPORT*************PL/SQL procedure successfully completed.
【讨论】:
哇,非常感谢。如果可以的话,我会请你喝啤酒。那么我的 Oracle 帐户一定发生了一些奇怪的事情。我做了更正,我得到了同样的错误。我正在使用命令“exec LOWINVENTORY”并尝试“exec lowInventory”,它们都出现了与上述相同的错误。 能否请您发布表结构和示例数据以便我进行模拟? 我已插入样本数据,并运行了程序,请参阅输出的编辑部分以上是关于必须声明错误 PLS-00201 标识符的主要内容,如果未能解决你的问题,请参考以下文章