Question

oracle存储过程编译错误:PLS-00103: 出现符号 "end-of-file"在需要下列之一时

CREATE OR REPLACE PROCEDURE UNFOLD_STANDBOM_FINAL_SEQ(
CODE VARCHAR2,
GROSS_TYPE VARCHAR2,--'N'表示使用净耗；'G'表示使用总耗
COMPANY varchar2)
AS
CURSOR EXPORTCURSOR is SELECT ITEM,SUM(QTY),bom_ver FROM CC_FINAL
where code=CODE and company=COMPANY
and bom_ver is not null and bom_ver<>'' GROUP BY ITEM,bom_ver;

-- 展BOM数
ITEM VARCHAR2(64);
ITEM_QTY NUMBER(18,8);
bom_ver VARCHAR2(64);
rec_EXPORTCURSOR EXPORTCURSOR%rowtype;
BEGIN
execute immediate 'CREATE TABLE TEMPPROCESS
(
CODE VARCHAR2(64) , --核销代码,
ITEM_CODE VARCHAR2(64) , --成品料号
APPLY_QTY NUMBER(18,8) , --需展BOM数量
ITEM_CODE_D VARCHAR2(64) , --展后料号
GROSS_QTY NUMBER(18,8) , --展后用量
STEP VARCHAR2(10) , --第几阶
PROCESS VARCHAR2(4000), --展BOM过程
ITEM_CLASS VARCHAR2(1) , --父阶料号属性
ITEM_D_CLASS VARCHAR2(1) --子阶料号属性
)';
--删除旧的展BOM数据
DELETE FROM CC_RESULT_STAND_BOM_FINAL WHERE CODE=CODE and company=COMPANY;
FOR rec_EXPORTCURSOR in EXPORTCURSOR loop -- 需展BOM资料的游标
--展BOM
DECLARE S INT;
begin
if GROSS_TYPE='N'--使用净耗计算
then
S:=1;
--PRINT '成品:'+ITEMCODE
INSERT into TEMPPROCESS SELECT CODE,ITEM,ITEM_QTY,ITEM_D,SUM(NET_QTY*ITEM_QTY),S,
ITEM|| '◇'|| to_char(ITEM_QTY) || '→' ||
ITEM_D || '◇' ||to_char(SUM(NET_QTY*ITEM_QTY)),ITEM_CLASS,ITEM_D_CLASS
FROM CC_STAND_BOM
WHERE ITEM =ITEM
AND CODE=CODE
AND bom_ver=bom_ver
and company=COMPANY
GROUP BY ITEM,ITEM_D,ITEM_CLASS,ITEM_D_CLASS;
-- PRINT 'OUTER '+ITEM
--SELECT * FROM #TEMPPROCESS
elsif GROSS_TYPE='G'--使用总耗计算
then
S:=1;
--PRINT '成品:'+ITEMCODE
INSERT into TEMPPROCESS SELECT CODE,ITEM,ITEM_QTY,ITEM_D,SUM(total_qty*ITEM_QTY),S,
ITEM|| '◇'|| to_char(ITEM_QTY) || '→' ||
ITEM_D || '◇' +to_char(SUM(total_qty*ITEM_QTY)),ITEM_CLASS,ITEM_D_CLASS
FROM CC_STAND_BOM
WHERE ITEM =ITEM
AND CODE=CODE
AND bom_ver=bom_ver
and company=COMPANY
GROUP BY ITEM,ITEM_D,ITEM_CLASS,ITEM_D_CLASS;
INSERT into CC_RESULT_STAND_BOM_FINAL (CODE,PROCESS,ITEM,ITEM_D,ITEM_QTY,ITEM_D_QTY,ITEM_CLASS,ITEM_D_CLASS,company)
SELECT CODE,PROCESS,T.ITEM_CODE,T.ITEM_CODE_D,T.APPLY_QTY,T.GROSS_QTY ,T.ITEM_CLASS,T.ITEM_D_CLASS,COMPANY
FROM TEMPPROCESS T;
INSERT into CC_RESULT_STAND_BOM_FINAL (CODE,PROCESS,ITEM,ITEM_D,ITEM_QTY,ITEM_D_QTY,ITEM_CLASS,ITEM_D_CLASS,company)
SELECT CODE,'',ITEM,ITEM,QTY,QTY,'F','M',COMPANY
FROM CC_FINAL
where code=CODE
and company=COMPANY
and (bom_ver is null or bom_ver='');
END if;
COMMIT;
end;
END loop;
请高手帮忙解决下，我得瑟所有存储过程最后都是报这个错误。。。

Answer 1

for 循环就不需要打开关闭游标了，他自动就打开关闭了
还有你上面不是2个LOOP吗
结束就应该是2个END LOOP
start_date datet;这个数据该是date

这样试试
CREATE OR REPLACE Procedure Up_Cc_Stand_Bom_Io As
Cursor Cor Is
Select Item,
Item_Class,
Item_d,
Item_d_Class,
Net_Qty,
Start_Date,
End_Date,
Remark
From Cc_Stand_Bom_Temp_Io;

Item Varchar2(128);
Item_Class Varchar2(1);
Item_d Varchar2(128);
Item_d_Class Varchar2(1);
Net_Qty Number(18, 8);
Start_Date Datet;
End_Date Date;
Remark Varchar2(256);
v_Count Number;
Rec_Cor Cor%Rowtype;
Begin
For Rec_Cor In Cor Loop

While Fetch_Status = 0 Loop
Select Count(*)
Into v_Count
From Cc_Stand_Bom_Io
Where Item = Item
And Item_d = Item_d
And Start_Date = Start_Date;
If v_Count > 0 Then
Delete From Cc_Stand_Bom_Io
Where Item = Item
And Item_d = Item_d
And Start_Date = Start_Date;
End If;
Insert Into Cc_Stand_Bom_Io
(Item,
Item_Class,
Item_d,
Item_d_Class,
Net_Qty,
Waste_Qty,
Total_Qty,
Start_Date,
End_Date,
Remark)
Values
(Item,
Item_Class,
Item_d,
Item_d_Class,
Net_Qty,
0,
Net_Qty,
Start_Date,
End_Date,
Remark);
Delete From Cc_Stand_Bom_Temp_Io;
End Loop;
End Loop;
End Up_Cc_Stand_Bom_Io;

Answer 2

引用lxr_303的回答：
最后少end

rubbish！rubbish！

Answer 3

最后少end

追问

还是错的