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 _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 @des('/ArrayOfInt/int') V(N)
结果就是
注：上⾯的数据类型为XML
这样就可以给存储过程传⼀个集合了，⼀般是数组，⽐如主键的集合。然后可⽤通过主键集合来查询记录。
客户端可⽤使⽤序列化，把list转化成xml。不过在序列化过程中，遇到了⼀些⼩⿇烦。
1. 默认是utf-16，SQL只认识utf-8
2. 出现很讨厌的xmlns:xsi="www.w
xmlns:xsd="/2001/XMLSchema"
可以⽤我这个类
public static classSerializeHelper
{private static readonly XmlSerializerNamespaces Namespaces = newXmlSerializerNamespaces();staticSerializeHelper() {//去掉 xmlns:xsi="/2001/XMLSchema-instance" xmlns:xsd="/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新增功能。否则遇到问题，不到⽐较好的解决⽅案。typeof array
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论