oracle 存储过程与SQLserver 对比

bjhwdvyusx

　　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