精华内容
下载资源
问答
  • 接到这个需求,本以为简单。...方法一 分割例:通过SQL Server存储过程传送数组参数删除多条记录eg. ID 值为'1,2,3' 以下存储过程就是删除表中id号为1,2,3的记录:CREATE PROCEDURE DeleteNews@ID ...

    接到这个需求,本以为简单。谁知道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)

    结果就是

    e8c02935185eba868e5609f3aa84d9e3.png

    注:上面的数据类型为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!

    展开全文
  •  所以就决定用 数组作为存储过程参数进行传值,在存储过程中批量删除。说实话这个选择有点得不偿失,本来以为上午就能搞定,谁知道从摸索到实现,居然发了大半天时间。rem 删除选中短信息CREATE OR REPLACE ...

    今天做了一个关于短消息批量删除的功能, 刚开始准备在后台用循环来逐条删除,后来因为其它功能模块也会用到大批量删除数据。 所以就决定用 数组作为存储过程的参数进行传值,在存储过程中批量删除。说实话这个选择有点得不偿失,本来以为上午就能搞定,谁知道从摸索到实现,居然发了大半天时间。

    rem 删除选中短信息

    CREATE OR REPLACE PACKAGE MSGPAG

    AS

    TYPE MSG_ARRAY IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;--先定义包,这个就相当于一个数组

    PROCEDURE DELMSG(M_ARRAY IN MSG_ARRAY);

    END MSGPAG;

    /

    CREATE OR REPLACE PACKAGE BODY MSGPAG

    AS

    PROCEDURE DELMSG(M_ARRAY IN MSG_ARRAY)

    AS

    I NUMBER:=1; --这个可以不写

    BEGIN

    SAVEPOINT SP1;

    FOR  I IN 1..M_ARRAY.COUNT LOOP

    DELETE FROM T_MESSAGE WHERE FID = TO_NUMBER(M_ARRAY(I));

    END LOOP;

    COMMIT;

    EXCEPTION

    WHEN OTHERS THEN

    ROLLBACK TO SAVEPOINT SP1;

    END DELMSG;

    END MSGPAG;

    短短的这些,着实让我走了不少弯路。 最后有一个小时是花在TO_NUMBER(M_ARRAY(I))上的,我想把TO_NUMBER(M_ARRAY(I))赋给一个NUMBER 型变量,但就是执行不到 DELETE 句处,呵呵, 最后才明白是怎么回事。

    后台的调用执行语句就这么多:

    connection.Open();Oracle.DataAccess.Client.OracleCommand cmd = connection.CreateCommand();cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = procedureName;Oracle.DataAccess.Client.OracleParameter parameter = new Oracle.DataAccess.Client.OracleParameter(@"M_ARRAY", Oracle.DataAccess.Client.OracleDbType.Varchar2);// @"M_ARRAY" 是存储过程的IN 参数parameter.Direction = ParameterDirection.Input;parameter.Value = charArray;parameter.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray; cmd.Parameters.Add(parameter);cmd.ExecuteNonQuery();

    展开全文
  • MySql存储过程传数组参数

    千次阅读 2018-09-17 10:38:40
    第二版想法为,把所有参数传入到存储过程,循环参数为一个数组,mysql存储过程里没有split函数, 取数组中的值通过substring截出,通过截的id值作为条件查询数据,查询到的数据有可能是 多条记录,所有想通过游标...

    项目中遇到批量数据的处理,使用hibernate的出来太慢,修改为存储过程。

    第一版修改为应用循环调存储过程,发现效果不理想。可能是频繁的打开session和关闭。

    第二版想法为,把所有参数传入到存储过程,循环参数为一个数组,mysql存储过程里没有split函数,

    取数组中的值通过substring截出,通过截的id值作为条件查询数据,查询到的数据有可能是

    多条记录,所有想通过游标(CURSOR)进行结果集的循环进行处理,但是,

    游标(cursor)的特性

    1,只读的,不能更新的。
    2,不滚动的
    3,不敏感的,不敏感意为服务器可以活不可以复制它的结果表

    游标(cursor)必须在声明处理程序之前被声明,并且变量和条件必须在声明游标或处理程序之前被声明。

    第三版想法为,建立俩个存储过程,第一个为截窜并调起第二个实际处理的存储过程。

    已下截窜存储过程来自网络:

    drop procedure if exists Pro_DeleteLog; -- 存在即删除
    create procedure Pro_DeleteLog( Qualification varchar(100)) -- 定义参数
    begin
    declare i int ; 
    declare Start1 int; 
    declare Length int ;
    declare TotalLenght int ;
    declare filed varchar(100) ;
    declare sqlStr varchar(2000) ;
    declare stmtNovelSearch varchar(2000) ;
    set i=1;
    set Start1=1;
    set Length=0;
    set TotalLenght=length(Qualification); -- 计算输入参数长度
    select TotalLenght; 
    while i <=TotalLenght do -- i=1 开始
    -- select SUBSTRING(Qualification,i,1);
    if(SUBSTRING(Qualification,i,1)=',') -- 注意 mysql 的substring函数截取字符串是从1开始的,而不是0 与java JavaScript不同。
    -- 从第i位开始截取一个字符,看是否等于,
    then 
    set filed=SUBSTRING(Qualification,Start1,Length);-- 从start1开始截取length长度的字符 
    select filed; 
    call pro_do(filed); -- 具体要循环执行的存储过程
    set Start1=i+1; 
    set Length=0;
    else
    set Length=Length+1; -- 如果不是, 则截取的长度加1 
    end if;
    set i=i+1; 
    end while;
    end
    -- 调用存储过程 字段名以,分隔 注意最后 , 结尾
    call Pro_DeleteLog('payload,backresult,dataset,effectrow,clientuser,loginuser,clientprg,')

    转载:http://www.cnblogs.com/bigcelestial/archive/2013/09/05/3303329.html

     

    --经测试可用。

    展开全文
  • 问题场景:做一个物品系统, 需要...思考:储存过程的的参数不支持数组,是否可以用json字符串代替 测试用的JSON字符串: let obj = { itemlist:[ { regid:0, iteminfo:{ item_uid:"10441", },...

    问题场景:做一个物品系统, 需要批量存入。

    思考:储存过程的的参数不支持数组,是否可以用json字符串代替

    测试用的JSON字符串:

    let obj = {
        itemlist:[
            {
                regid:0,
                iteminfo:{
                    item_uid:"10441",
                },
                amoount:1
            },
            {
                regid:10,
                amoount:1
            }
        ]
    }
    
    
    
    
    
    {"itemlist":[{"regid":0,"iteminfo":{"item_uid":"10441"},"amount":1},{"regid":10,"amount":1}],"itemcount":2}
    

    存储过程的代码:

    DELIMITER  $$
    CREATE PROCEDURE testJson(IN json_str VARCHAR(4096), OUT itemcount INT, 
                            OUT info VARCHAR(128), OUT info1 VARCHAR(64),  OUT info2 VARCHAR(64), OUT regid BIGINT)
    BEGIN
        DECLARE item_uid VARCHAR(128);
        DECLARE amount , i INT;
        SET itemcount = json_str->>"$.itemcount";
        SET info = json_str->>"$.itemlist";
        SET info1 = json_str->>"$.itemlist[0]";
        SET info2 = json_str->>"$.itemlist[1]";
        set regid = info1->>"$.regid";
    END$$

    调用测试:

    CALL testJson('{"itemlist":[{"regid":10,"amount":1}, {"regid":0,"iteminfo":{"item_uid":"10441"},"amount":1}],"itemcount":2}', @itemcount, @info, @info1, @info2, @regid);

    测试结果:

     

    说明:

    1. 这段存储过程的的json字符串 里加入了itemcount字段, 用来表示itemlist的数量。感觉应该有办法可以通过itemlist直接获得,但是因为赶进度, 先由调用的程序计算出itemcount传进来。
    2. 还不大理解$.和->>的意义, 查了一下, 没找到, 有空了再找找。应该是mysql 的json类型相关的内容
    3. 上面的一些用法是mysql的json的字段类型, 我是用的5.7的mysql测试的, 不确定更早的版本是否支持

    参考:

    https://blog.csdn.net/qq_25175063/article/details/79969204

    https://www.cnblogs.com/kuangke/p/5607471.html

     

    展开全文
  • 如上所示,参数是一个int数组,Mybatis提供了对调用存储过程的支持,那么PostgreSQL独有的数组类型作为存储过程参数又将如何处理呢?其实很简单,mybatis提供了typeHandlers可以创建一个数组类型的类型处理器,...
  • 在使用oracle进行批量插入的时,如果数据量较小可以使用install all的语法进行批量插入。... 使用存储过程批量插入很显然要接收一个数组参数,而且这个数组里的元素类型应该和java里自定义的数据模型对应。
  • -SELECT * FROM #temp_delete DROP TABLE #temp_insert DROP TABLE #temp_delete END 这是我写的一个MSSQL的存储过程 参数@XML_ARRAY的数据格式是: '' 用openXML解析 想问下如果用mysql解决应该怎么做,如何传递一个...
  • mysql存储过程功能弱问题一直是大家关注的问题,今天讲一下Mysql存储过程无法传递数组类型参数的解决方案。在很多的情况下,在编写存储过程中往往会用到数组,但是mysql中存储过程传入参数并没有可以直接传入数组的...
  • 怎么利用MyBatisList类型参数到数据库存储过程中实现批量插入数据?接下来通过本文给大家介绍Mybatislist参数调用oracle存储过程,需要的朋友可以参考下
  • 问题来自于通过存储过程保存数据时出现以下错误 java.sql.SQLException: 对列来说插入的值太大:"............" 省略号内容超过500个字符。 出错的JAVA程序语句如下: ParameterArray pArray = ...
  • -功能:采用存储过程、type组合来实现批量操作,以节省系统开销,提高效率。--创建 Type bodiesCREATE OR REPLACE TYPE TYPE_ARRAY AS OBJECT(ID NUMBER(10),REMARK VARCHAR2(10))--创建 TypesCREATE OR REPLACE ...
  • --创建存储过程 CREATE OR replace PROCEDURE show_list( p_varlist IN varray_list )IS i number; temp varchar2(20); BEGIN FOR x IN 1..p_varlist.count loop dbms_output.put_line(p_varlist(x)); ...
  • end -- 调用存储过程 字段名以,分隔 注意最后 , 结尾 call Pro_DeleteLog(‘payload,backresult,dataset,effectrow,clientuser,loginuser,clientprg,‘) 个人推荐第一种; 原文:...
  • Oracle存储过程传递数组参数

    千次阅读 2012-02-24 21:22:55
    今天晚上,和同事聊起Oracle存储过程传递数组参数能否使用数据,之后就在自己的电脑上试试,试了试是可以使用。 代码: 先要定义一个类型myvarray_list 数组的长度是10,数组中存放的数据类型是varchar2。 SQL> ...
  • 展开全部mysql中要获得存储过程的返回值,可以增加一32313133353236313431303231363533e58685e5aeb931333365646362个out参数,用来返回。mysql中存储过程的例子:CREATE PROCEDURE addvoucher (IN userid INT,IN ...
  • 直接上过程 1.因为Oracle本身是有数组概念的.我们直接使用Oracle中的数组,但需要先创建类型,varray也就是Oracle的数组,100指定长度,of后面指定类型, CREATE OR REPLACE TYPE tables_array AS VARRAY(100) OF ...
  • SQL Server存储过程数组参数

    万次阅读 2015-06-03 11:14:51
    SQL Server存储过程数组参数    SQL Server存储过程不支持数组参数类型,要传递数量不定的参数,需要采用其他方式进行传递   方法一:利用字符串截取拆分数组字符串 -- ===================================...
  • 在SQL存储过程中进行参数数组Array Parameter的处理函数。
  • 存储过程中自定义varray数组createorreplaceprocedureproc_selectAnyone()astypetype_arrayisvarray(11)ofvarchar2(2);--定义长度为11的数组init_arraytype_array:=type_array('01','02','03','04','05','06','07','...
  • 记录以备后用: sql:--step 1:create object type CREATE or REPLACE TYPE db_arch_type AS OBJECT ( ID_LICENCEID nVARCHAR2 (50), ID_ITEM_ARCHIVES_CODE nVARCHAR2 (50) ); --step 2: create object type array ...
  • 今天做了一个关于短消息批量删除的功能, ... 所以就决定用 数组作为存储过程参数进行传值,在存储过程中批量删除。说实话这个选择有点得不偿失,本来以为上午就能搞定,谁知道从摸索到实现,居然发了大半天时间。
  • sql调用存储过程传入数组方法

    千次阅读 2017-11-22 00:01:52
    sqlserver 调用存储过程在传入参数的时候不能传入数组,所以一般将数据作为字符串 比如表格: --方法一(用xml来拆分字符串) declare @str varchar(1000) declare @index int set @index = 100 set @...
  • 存储过程定义数组

    千次阅读 2019-07-03 14:46:15
    declare type a_type is table of ...-- 下面一种定义方式则指定了该数组的最大元素个数 a a_type := a_type(); -- 定义并初始化一个数组变量 begin a.extend(3); -- 数组扩展到3个元素 a(1) := 1; a(2) := 10; a(...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 389,438
精华内容 155,775
关键字:

存储过程传参数数组