oracle 存储过程与SQLserver 对照

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

?

我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html

查看更多 下一篇