oracle 存储过程与SQLserver 对比

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