Is there a way to pass a set of values as a parame

问题:

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

In my case:

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')

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')

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

Any ideas?

回答1:

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

See: here

    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;

回答2:

From VB.net you can pass an "Associative array" to a SQL call.

In PL/SQL create types and procedures like this:

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

CREATE OR REPLACE PACKAGE My_Package AS
    TYPE NUMBER_ARRAY_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    PROCEDURE My_Procedure(arr IN NUMBER_ARRAY_TYPE);
END My_Package;

CREATE OR REPLACE PACKAGE BODY My_Package AS

PROCEDURE My_Procedure(arr IN NUMBER_ARRAY_TYPE) IS
    nested_table NUMBER_TABLE_TYPE := NUMBER_TABLE_TYPE();
BEGIN
    -- First transform "Associative array" to a "Nested Table"
    FOR i IN arr.FIRST..att.LAST LOOP
        nested_table.EXTEND;
        nested_table(nested_table.LAST) := arr(i);
    END LOOP;   

    SELECT * 
    INTO ... 
    FROM MyTable 
    WHERE ID MEMBER OF nested_table;

END My_Procedure;

END My_Package;

In VB.NET it looks like this:

Sub My_Sub(ByVal idArr As Long())
Dim cmd As OracleCommand
Dim par As OracleParameter
    cmd = New OracleCommand("BEGIN My_Package.My_Procedure(:arr); END;"), con)
    cmd.CommandType = CommandType.Text
    par = cmd.Parameters.Add("arr", OracleDbType.Int64, ParameterDirection.Input)
    par.CollectionType = OracleCollectionType.PLSQLAssociativeArray
    par.Value = idArr
    par.Size = idArr.Length
    cmd.ExecuteNonQuery()
End Sub

Check Oracle doc for further information: PL/SQL Associative Array Binding

回答3:

The solution to the question is to ultimately build an SQL statement which would look like this (sorry for the images but I could not paste the XML correctly):

In vb.net (or others I suppose) therefore, you would then replace the XML itself with a parameter as follows:

PASSING XMLTYPE(:1)

where :1 in this case would be the XML text:

(Do remember to build the XML text using a StringBuilder or any other efficient XML string builder).

回答4:

Why can't you just pass it as one atomic value and then work with the INSTR-function Oracle offers. For Example:

WITH MyTable AS (
SELECT 'abc' ID FROM dual UNION ALL
SELECT 'abcc' ID FROM dual UNION ALL
SELECT 'bbc' ID FROM dual UNION ALL
SELECT 'def' ID FROM dual UNION ALL
SELECT 'abcdef' ID FROM dual)
select * from MyTable where instr('(''abc'', ''def'')', '''' || id || '''') > 0;