有没有一种方法可以将一组值作为Oracle SQL语句中的参数传递 [英] Is there a way to pass a set of values as a parameter in an Oracle SQL Statement

问题描述

我想将一组值作为参数传递给Sql语句(在vb.net中).

I would like to pass a set of values as a parameter to an Sql Statement (in vb.net).

在我的情况下:

允许用户上传一组ID，以检查商品的可用性.我想执行一条语句，通过执行以下操作来返回与任何ID匹配的项目:

Users are allowed to upload a set of IDs, to check availability of an item. I would like to execute a statement that will return the items that match any of the IDs by doing something like the following:

SELECT * FROM MyTable WHERE id IN ('123','456','789')

但是我不能将值('123'，'456'，'789')作为参数传递，因为它将被视为原子值-整个字符串，即，这将不起作用:

But I cannot pass on the value ('123','456','789') as a parameter as it will be taken as an atomic value - a whole string, i.e., this will not work:

SELECT * FROM MyTable WHERE id IN :param

where :param is ('123','456','789')

我也无法连接字符串(如上所示)，以避免客户端sql注入.

I cannot concatenate the strings (as shown above) either to avoid client-side sql injection.

有什么想法吗?

推荐答案

您可以将值作为XML传递，并使用XMLDOM对其进行解析.

you could pass the values in as XML and parse them using the XMLDOM.

请参见:此处

    DECLARE
       vXML   VARCHAR2 (10000 CHAR) := '<ids><id>1</id><id>2</id><id>3</id></ids>';
    BEGIN
       OPEN :refc FOR
          SELECT c."id"
            FROM XMLTABLE ('/ids/id'
                           PASSING XMLTYPE (vXML)
                           COLUMNS "id" VARCHAR2 (32)) c;
    END;

这篇关于有没有一种方法可以将一组值作为Oracle SQL语句中的参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！