CREATE OR REPLACE PROCEDURE up_ApplyTest
(
V_DelKey1 IN NUMBER,
V_DelKey2 IN NUMBER,
V_OUT OUT NUMBER
)
AS
V_Status NUMBER; --单据状态
BEGIN
--获得单据状态
SELECT ApplyOrder.Status INTO V_Status
FROM ApplyOrder
WHERE ApplyOrderID = V_DelKey1 AND
AgentId=V_DelKey2;
IF V_Status IS NULL THEN
V_OUT:=-100;
RETURN; --数据不存在
IF V_Status=1 THEN
V_OUT:=-101;
RETURN; --数据不是草稿状态
--1删除途径港
DELETE FROM VoyagePorts WHERE ApplyOrderID = V_DelKey1;
--2删除船用物品
DELETE FROM ShipGoods WHERE ApplyOrderID = V_DelKey1;
--3删除检验项目
DELETE FROM QuarantineItem WHERE ApplyOrderID = V_DelKey1 ;
--4删除货物列表
DELETE FROM GoodsList A WHERE A.GoodsBillID=
(Select GoodsBillID From GoodsBill where ApplyOrderID = V_DelKey1) ;
--5删除货单
DELETE FROM GoodsBill WHERE ApplyOrderID = V_DelKey1;
--6删除人员物品
DELETE FROM CrewEffects A WHERE A.CrewID =(SELECT CrewID FROM Crew WHERE ApplyOrderID = V_DelKey1);
--7删除人员
DELETE FROM Crew WHERE ApplyOrderID = V_DelKey1;
--8删除船舶适任证
DELETE FROM ShipEligibility WHERE ApplyOrderID = V_DelKey1;
--9删除船舶证书
DELETE FROM ShipCertificate WHERE ApplyOrderID = V_DelKey1 ;
--10删除船舶
DELETE FROM Ship WHERE ApplyOrderID = V_DelKey1;
--11删除日志
DELETE FROM ApplyOrderLog WHERE ApplyOrderID = V_DelKey1;
--12删除航次信息
DELETE FROM Voyage WHERE ApplyOrderID = V_DelKey1;
--13删除申报单据
DELETE FROM ApplyOrder WHERE ApplyOrderID = V_DelKey1;
COMMIT;
V_OUT:=0;
RETURN;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
V_OUT:=-999; --DELETE FAILURE
RETURN;
END;------------------------------------------------------------
报错信息
13:45:44.671 DBMS 10.10.40.236 -- Error: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
13:45:44.718 DBMS 10.10.40.236 -- begin case declare else elsif end exit for goto if loop mod
13:45:44.781 DBMS 10.10.40.236 -- null pragma raise return select update while with
13:45:44.843 DBMS 10.10.40.236 -- <an identifier> <a double-quoted delimited-identifier>
13:45:44.906 DBMS 10.10.40.236 -- <a bind variable> << close current delete fetch lock insert
13:45:44.968 DBMS 10.10.40.236 -- open rollback savepoint set sql execute commit forall merge
13:45:45.031 DBMS 10.10.40.236 -- <a single-quoted SQL string> pipe
13:45:45.093 DBMS 10.10.40.236 -- <an alternatively-quoted SQL string>, Batch 1 Line 68 Col 5
13:45:45.156 DBMS 10.10.40.236 -- Error: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
13:45:45.218 DBMS 10.10.40.236 -- end not pragma final instantiable order overriding static
13:45:45.296 DBMS 10.10.40.236 -- member constructor map, Batch 1 Line 73 Col 0
应该怎么写啊?
(
V_DelKey1 IN NUMBER,
V_DelKey2 IN NUMBER,
V_OUT OUT NUMBER
)
AS
V_Status NUMBER; --单据状态
BEGIN
--获得单据状态
SELECT ApplyOrder.Status INTO V_Status
FROM ApplyOrder
WHERE ApplyOrderID = V_DelKey1 AND
AgentId=V_DelKey2;
IF V_Status IS NULL THEN
V_OUT:=-100;
RETURN; --数据不存在
IF V_Status=1 THEN
V_OUT:=-101;
RETURN; --数据不是草稿状态
--1删除途径港
DELETE FROM VoyagePorts WHERE ApplyOrderID = V_DelKey1;
--2删除船用物品
DELETE FROM ShipGoods WHERE ApplyOrderID = V_DelKey1;
--3删除检验项目
DELETE FROM QuarantineItem WHERE ApplyOrderID = V_DelKey1 ;
--4删除货物列表
DELETE FROM GoodsList A WHERE A.GoodsBillID=
(Select GoodsBillID From GoodsBill where ApplyOrderID = V_DelKey1) ;
--5删除货单
DELETE FROM GoodsBill WHERE ApplyOrderID = V_DelKey1;
--6删除人员物品
DELETE FROM CrewEffects A WHERE A.CrewID =(SELECT CrewID FROM Crew WHERE ApplyOrderID = V_DelKey1);
--7删除人员
DELETE FROM Crew WHERE ApplyOrderID = V_DelKey1;
--8删除船舶适任证
DELETE FROM ShipEligibility WHERE ApplyOrderID = V_DelKey1;
--9删除船舶证书
DELETE FROM ShipCertificate WHERE ApplyOrderID = V_DelKey1 ;
--10删除船舶
DELETE FROM Ship WHERE ApplyOrderID = V_DelKey1;
--11删除日志
DELETE FROM ApplyOrderLog WHERE ApplyOrderID = V_DelKey1;
--12删除航次信息
DELETE FROM Voyage WHERE ApplyOrderID = V_DelKey1;
--13删除申报单据
DELETE FROM ApplyOrder WHERE ApplyOrderID = V_DelKey1;
COMMIT;
V_OUT:=0;
RETURN;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
V_OUT:=-999; --DELETE FAILURE
RETURN;
END;------------------------------------------------------------
报错信息
13:45:44.671 DBMS 10.10.40.236 -- Error: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
13:45:44.718 DBMS 10.10.40.236 -- begin case declare else elsif end exit for goto if loop mod
13:45:44.781 DBMS 10.10.40.236 -- null pragma raise return select update while with
13:45:44.843 DBMS 10.10.40.236 -- <an identifier> <a double-quoted delimited-identifier>
13:45:44.906 DBMS 10.10.40.236 -- <a bind variable> << close current delete fetch lock insert
13:45:44.968 DBMS 10.10.40.236 -- open rollback savepoint set sql execute commit forall merge
13:45:45.031 DBMS 10.10.40.236 -- <a single-quoted SQL string> pipe
13:45:45.093 DBMS 10.10.40.236 -- <an alternatively-quoted SQL string>, Batch 1 Line 68 Col 5
13:45:45.156 DBMS 10.10.40.236 -- Error: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
13:45:45.218 DBMS 10.10.40.236 -- end not pragma final instantiable order overriding static
13:45:45.296 DBMS 10.10.40.236 -- member constructor map, Batch 1 Line 73 Col 0
应该怎么写啊?
V_OUT:=-100;
RETURN; --数据不存在
END IF; IF V_Status=1 THEN
V_OUT:=-101;
RETURN; --数据不是草稿状态
END IF;需要加 END IF;