mysql 存储过程支持传入数组参数_问个小问题,关于存储过程传递数组参数

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

ALTER PROCEDURE [dbo].[Proce_Building_Relation_RP]

-- Add the parameters for the stored procedure here

@RID BIGINT, -- 角色id

@XML_Array varchar(500), -- 代表权限的id序列化字符串

@Rowcount INT=0 OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

--SET NOCOUNT ON;

DECLARE @IdHandel INT

CREATE TABLE #temp_insert(RID BIGINT,Nums BIGINT)

CREATE TABLE #temp_delete(RID BIGINT,Nums BIGINT)

EXEC sp_xml_preparedocument @IdHandel OUTPUT, @XML_Array

-- 填充与源数据不重复的数值的表

INSERT INTO #temp_insert(RID,Nums)

SELECT @RID,N.Nums

FROM OPENXML(@IdHandel,'/Array/PA') WITH(Nums BIGINT) AS N

WHERE N.[Nums] IS NOT NULL

AND (Nums not in (select pid from Sys_User_Relation_RP where rid=@RID) )

-- 填充与源数据重复的数值的表

INSERT INTO #temp_delete(RID,Nums)

SELECT @RID,N.Nums

FROM OPENXML(@IdHandel,'/Array/PA') WITH(Nums BIGINT) AS N

WHERE N.[Nums] IS NOT NULL

AND (Nums in (select pid from Sys_User_Relation_RP where rid=@RID) )

-- 向表插入与数组参数不重复的数值

INSERT INTO Sys_User_Relation_RP(rid,pid) SELECT RID,Nums FROM #temp_insert

SET @Rowcount=@@ROWCOUNT

-- 删除表中与数组参数重复的数值

DELETE FROM Sys_User_Relation_RP WHERE rid=@RID and pid in (SELECT Nums FROM #temp_delete WHERE RID=@RID)

SET @Rowcount=@Rowcount+@@ROWCOUNT

EXEC sp_xml_removedocument @IdHandel

--SELECT * FROM #temp_insert

--SELECT * FROM #temp_delete

DROP TABLE #temp_insert

DROP TABLE #temp_delete

END

这是我写的一个MSSQL的存储过程

参数@XML_ARRAY的数据格式是:

''

用openXML解析

想问下如果用mysql解决应该怎么做,如何传递一个序列化字符串然后解析