问题描述
所以我一直遇到这个错误,对发生的事情我一无所知.我在Google周围搜索,阅读了有关此错误的一些问题,但没有任何效果.我只希望它能够运行,这样我就可以完成我的项目.
So I keep having this error, and I'm clueless as to what's going on. I've Googled around, read some questions about this error, and nothing is working. I just want this to run so I can finish my project.
create or replace procedure LOWINVENTORY is
--Variables
ID number;
itemNamed char(15);
description char(20);
startQty number;
--Define inventory item cursor
cursor nextItem is
select inventory.itemID, inventory.itemName, inventory.description, inventory.startQty;
from inventory
where inventory.startQty < 5;
begin
open 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:语句被忽略
ERROR:BEGIN LOWINVENTORY; END; *ERROR at line1:ORA-06550: line 1, column 9:PLS-00201: identifier 'LOWINVENTORY' must be declaredORA-06550: line 1, column7:PL/SQL: Statement ignored
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)
);
推荐答案
您的代码充满了愚蠢的错误,首先,
Your code is filled with silly mistakes,firstly ,
select inventory.itemID, inventory.itemName,
inventory.description, inventory.startQty; --> semicolan here, the statment doesnt end
from inventory
where inventory.startQty < 5;
第二,
cursor nextItem is --> using nextItem as name in cursor but "netxtItem" while opening it, a typo
第三,
如果在else部分中,则循环不结束
No end if inside the else part,loop
这是已编译的过程,我创建了库存表,其中仅在过程中使用了4列来编译过程
Here is the procedure that has been compiled,I created the inventory table with just 4 columns used in procedure to compile the procedure
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----------Quantity
abc -1-descp1 -1
abcd -2-descp2 -1
************END REPORT*************
PL/SQL procedure successfully completed.
SQL>
编辑2
使用您提供的数据
SQL> exec lowinventory;
*********************
++Inventory Report++
*********************
Item Name---ID------Description----------Quantity
jigsaw -39287426-cuts stuff -3
pliers -83742345-grabs stuff -4
jigsaw -39287426-cuts stuff -3
blowtorch -48927349-torches stuff -2
************END REPORT*************
PL/SQL procedure successfully completed.
这篇关于错误PLS-00201标识符必须声明的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!