ORACLE:
CREATE OR REPLACE PROCEDURE PROC_DELETE_CAN_ITEM
(
CAN_ITEM_IDS IN VARCHAR2,
RETURNVALUE OUT INT
)
IS
ERRORCOUNT INT;
VAR_CAN_ITEM_ID INT;
VAR_IDS VARCHAR2(200);
BEGIN
RETURNVALUE:=0;
WHILE instr(CAN_ITEM_IDS,',')>0 LOOP
BEGIN
VAR_CAN_ITEM_ID:=SUBSTR(VAR_IDS,1,INSTR(VAR_IDS,',')-1);
--刉壺CAN_ITEM
UPDATE CAN_ITEM SET VALID=0 WHERE CAN_ITEM_ID=VAR_CAN_ITEM_ID;
--刉壺AUTO_MODEL_CAN_ITEM
DELETE FROM AUTO_MODEL_CAN_ITEM WHERE CAN_ITEM_ID=VAR_CAN_ITEM_ID;
--刉壺CURRENT_CAN_ITEM
DELETE FROM CURRENT_CAN_ITEM WHERE CAN_ITEM_ID=VAR_CAN_ITEM_ID;
VAR_IDS:=SUBSTR(CAN_ITEM_IDS,INSTR(CAN_ITEM_IDS,',')+1,LENGTH(CAN_ITEM_IDS)-INSTR(CAN_ITEM_IDS,','));
END;
END LOOP;
--刉壺CAN_ITEM
UPDATE CAN_ITEM SET VALID=0 WHERE CAN_ITEM_ID=CAN_ITEM_IDS;
--刉壺AUTO_MODEL_CAN_ITEM
DELETE FROM AUTO_MODEL_CAN_ITEM WHERE CAN_ITEM_ID=CAN_ITEM_IDS;
--刉壺CURRENT_CAN_ITEM
DELETE FROM CURRENT_CAN_ITEM WHERE CAN_ITEM_ID=CAN_ITEM_IDS;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURNVALUE:=1;
RETURN;
END;
SQLSERVER:
[code="sql"]CREATE PROCEDURE [dbo].[PROC_DELETE_CAN_ITEM]
@CAN_ITEM_IDS NVARCHAR(200),
@RETURNVALUE INT OUTPUT
AS
BEGIN TRANSACTION
DECLARE @ERRORCOUNT INT ,@CAN_ITEM_ID INT
BEGIN
SET @ERRORCOUNT=0
WHILE CHARINDEX (',',@CAN_ITEM_IDS)>0
BEGIN
SET @CAN_ITEM_ID=LEFT(@CAN_ITEM_IDS,CHARINDEX(',',@CAN_ITEM_IDS)-1)
--删除CAN_ITEM
UPDATE CAN_ITEM SET VALID=0 WHERE CAN_ITEM_ID=@CAN_ITEM_ID
--删除AUTO_MODEL_CAN_ITEM
DELETE FROM AUTO_MODEL_CAN_ITEM WHERE CAN_ITEM_ID=@CAN_ITEM_ID
--删除CURRENT_CAN_ITEM
DELETE FROM CURRENT_CAN_ITEM WHERE CAN_ITEM_ID =@CAN_ITEM_ID
SET @ERRORCOUNT=@ERRORCOUNT+@@ERROR
SET @CAN_ITEM_IDS=SUBSTRING( @CAN_ITEM_IDS,CHARINDEX(',',@CAN_ITEM_IDS)+1,LEN(@CAN_ITEM_IDS)-CHARINDEX(',',@CAN_ITEM_IDS))
END
--删除CAN_ITEM
UPDATE CAN_ITEM SET VALID=0 WHERE CAN_ITEM_ID=@CAN_ITEM_IDS
--删除AUTO_MODEL_CAN_ITEM
DELETE FROM AUTO_MODEL_CAN_ITEM WHERE CAN_ITEM_ID=@CAN_ITEM_IDS
--删除CURRENT_CAN_ITEM
DELETE FROM CURRENT_CAN_ITEM WHERE CAN_ITEM_ID =@CAN_ITEM_IDS
SET @ERRORCOUNT=@ERRORCOUNT+@@ERROR
END
IF(@ERRORCOUNT0)
BEGIN
ROLLBACK TRANSACTION --回滚事务
SET @RETURNVALUE=0 --执行失败返回0
END
ELSE
BEGIN
COMMIT TRANSACTION --提交事务
SET @RETURNVALUE=1 --执行成功返回1
END
GO