mysql 存储过程参数数组_如何给存储过程，传一个数组参数？

接到这个需求，本以为简单。谁知道SQL不支持数组。于是想用','分割传进去，哪知道SQL居然没有split()函数，还得用substring & charindex，坑爹啊。

方法一 分割

例:通过SQL Server存储过程传送数组参数删除多条记录

eg. ID 值为'1,2,3' 以下存储过程就是删除表中id号为1,2,3的记录：

CREATE PROCEDURE DeleteNews

@ID nvarchar(500)

as

DECLARE @PointerPrev int

DECLARE @PointerCurr int

DECLARE @TId int

Set @PointerPrev=1

while (@PointerPrev < LEN(@ID))

Begin

Set @PointerCurr=CharIndex(',',@ID,@PointerPrev)

if(@PointerCurr>0)

Begin

set @TId=cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev) as int)

Delete from News whereID=@TIDSET @PointerPrev = @PointerCurr+1

End

else

Break

End

--删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除

set @TId=cast(SUBSTRING(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as int)

Delete from News whereID=@TIDGO

这个方法麻烦不？于是又有另外一种方法——临时表

方法二 Table对象

传3个参数，都是数组形式还有时间类型用存储过程更新

@Oid = 1,2,3,4

@Did = 111,222,333,444

@DateArr = '2007-1-1,2007-1-2,2007-1-3,2007-1-4'

CREATE proc Test999

@Oid nvarchar(1000)    --ID1

,@Did nvarchar(1000)  --ID2

,@DateArr nvarchar(1000) --日期

AS

DECLARE @id1s varchar(8000), @id2s varchar(8000), @dates varchar(8000)

set @id1s=@Oid

set @id2s=@Did

set @dates = @DateArr

-- 调用函数实现处理

SELECT @id1s=@id1s, @id2s=@id2s,@dates = @dates

UPDATE A SET terminate_time = B.dt

FROM [Table] A,(

SELECT

id1 = CONVERT(int, Desk_id.value),

id2 = CONVERT(int, room_id.value),

dt = CONVERT(datetime, terminate_time.value)

FROM dbo.f_splitstr(@id1s) Desk_id, dbo.f_splitstr(@id2s) room_id, dbo.f_splitstr(@dates) terminate_time

WHERE Desk_id.id = room_id.id

AND Desk_id.id = terminate_time.id

) B

WHERE A.Desk_id = B.ID1 AND A.room_id = B.ID2

GO这个还用到一个函数f_splitstr

CREATE FUNCTION dbo.f_splitstr(

@str varchar(8000)

)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))

AS

BEGIN

DECLARE @pos int

SET @pos = CHARINDEX(',', @str)

WHILE @pos > 0

BEGIN

INSERT @r(value) VALUES(LEFT(@str, @pos - 1))

SELECT

@str = STUFF(@str, 1, @pos, ''),

@pos = CHARINDEX(',', @str)

END

IF @str > ''

INSERT @r(value) VALUES(@str)

RETURN

END

这个方法更加可怕~~~辗转百度，找到了一个还不错的方法，用OPENXML，这个SQL2000就支持了。

方法三 xml

应该用SQL2000 OpenXML更简单，效率更高，代码更可读：

CREATE Procedure [dbo].[ProductListUpdateSpecialList]

(

@ProductId_Array NVARCHAR(2000),

@ModuleId INT

)

AS

delete from ProductListSpecial whereModuleId=@ModuleId

-- If empty, return

IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0)

RETURN

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array

Insert into ProductListSpecial (ModuleId,ProductId)

Select

@ModuleId,C.[ProductId]

FROM

OPENXML(@idoc, '/Products/Product', 3)

with (ProductId int ) as C

where

C.[ProductId] is not null

EXEC sp_xml_removedocument @idoc

哇，看起来还是很复杂的说。有木有更好的办法呢？

既然是OPENXML，为啥不用XML呢？于是查到，SQL2005以上都支持XML。Good，找到一片天了。

利用SQL2005的XML/XQuery功能，可以很方便的解决传数组参数的问题。

declare @xml xml

set @xml = '<?xml version="1.0"?>

1

2

3

'

select N.value( '(text())[1]','int' ) RoomId from @xml.nodes('/ArrayOfInt/int') V(N)

结果就是

注：上面的数据类型为XML

这样就可以给存储过程传一个集合了，一般是数组，比如主键的集合。然后可用通过主键集合来查询记录。

客户端可用使用序列化，把list转化成xml。不过在序列化过程中，遇到了一些小麻烦。

1. .net默认是utf-16，SQL只认识utf-8

2. 出现很讨厌的xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

可以用我这个类

public static classSerializeHelper

{private static readonly XmlSerializerNamespaces Namespaces = newXmlSerializerNamespaces();staticSerializeHelper()

{//去掉 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

Namespaces.Add(string.Empty, string.Empty);

}public static string SerializeXml(T obj)

{
XmlSerializer serializer= new XmlSerializer(typeof(T));using (MemoryStream stream = newMemoryStream())

{
serializer.Serialize(stream, obj, Namespaces);returnEncoding.UTF8.GetString(stream.ToArray());

}

}public static T DeserializeXml(stringobj)

{
XmlSerializer serializer= new XmlSerializer(typeof(T));using (StringReader reader = newStringReader(obj))

{return(T)serializer.Deserialize(reader);

}

}

}

SQL与XML，XQuery结合起来，功能会很强大的。

从这个事件也看出，我应该多关注SQL各个版本之间新增功能，比如SQL2008新增功能。否则遇到问题，找不到比较好的解决方案。

OK，问题以完美的方式解决了，用最妙的方法解决问题，好开森哦~~~O(∩_∩)O！
————————————————
版权声明：本文为CSDN博主「wonder-yyc」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_33603963/article/details/113322836