Oracle-Parameter IN der Anweisung?

Habe noch zu finden, eine db unterstützt die Auswertung einer einzigen string-Variablen mit Kommata zu trennen, wie die Sohle IN - Klausel.

Ihre Optionen sind Teilzeichenfolge der Variablen, so werden die durch Komma getrennten Variablen-Inhalte eingeschaltet sind, die in Zeilen, so kann man dann mitmachen auf. Oder verwenden Sie dynamische SQL ist eine SQL-Anweisung aufgebaut, wie ein string in einem sproc, bevor die Anweisung ausgeführt wird.

könnte man eine Pipeline-Funktion zur Umwandlung eines string in eine Tabelle, die verwendet werden könnten, mit der IN Betreiber. Zum Beispiel (getestet mit 10gR2):

SQL> select * from table(demo_pkg.string_to_tab('i,j,k'));

COLUMN_VALUE
-----------------
i
j
k

mit dem folgenden Paket:

SQL> CREATE OR REPLACE PACKAGE demo_pkg IS
  2     TYPE varchar_tab IS TABLE OF VARCHAR2(4000);
  3     FUNCTION string_to_tab(p_string VARCHAR2,
  4                            p_delimiter VARCHAR2 DEFAULT ',')
  5        RETURN varchar_tab PIPELINED;
  6  END demo_pkg;
  7  /

Package created
SQL> CREATE OR REPLACE PACKAGE BODY demo_pkg IS
  2     FUNCTION string_to_tab(p_string VARCHAR2,
  3                            p_delimiter VARCHAR2 DEFAULT ',')
  4        RETURN varchar_tab PIPELINED IS
  5        l_string          VARCHAR2(4000) := p_string;
  6        l_first_delimiter NUMBER := instr(p_string, p_delimiter);
  7     BEGIN
  8        LOOP
  9           IF nvl(l_first_delimiter,0) = 0 THEN
 10              PIPE ROW(l_string);
 11              RETURN;
 12           END IF;
 13           PIPE ROW(substr(l_string, 1, l_first_delimiter - 1));
 14           l_string          := substr(l_string, l_first_delimiter + 1);
 15           l_first_delimiter := instr(l_string, p_delimiter);
 16        END LOOP;
 17     END;
 18  END demo_pkg;
 19  /

Package body created

Deine Abfrage würde wie folgt Aussehen:

select * 
  from contract 
 where contractnum in (select column_value
                         from table(demo_pkg.string_to_tab(:ContractNum)))

Ich weiß, das ist eine alte Frage, aber es ist eines von mehreren, in denen die gewählte Antwort hat mein problem nicht lösen und ich will auch nicht zum starten noch ein anderer thread zu diesem Thema, so werde ich nur nach unten setzen, was ich gefunden habe in meinen Reisen, in der Hoffnung, dass es vielleicht jemand helfen.

Ich nicht mit Oracle arbeiten viel, aber, wie in SQL Server, es scheint, dass übergeben eine Tabellenwert-parameter, den Sie brauchen, um einen entsprechenden UDT (user defined table), denen Sie die EXECUTE-Berechtigungen (ich könnte falsch sein). Dies bedeutet, dass andere Antworten darauf, die Verwendung eines integrierten SYS UDT kommen mit einigen Fracht-und ich konnte nicht herausfinden, ob es wirklich möglich ist, zu übergeben einen Tisch, um etwas, das nicht ist ein PL/SQL-Prozedur in der aktuellen version von ODP.net.

Zweite, die string-parsing-Lösung ist eine Behelfslösung für alle offensichtlichen Gründen (kann nicht zwischengespeichert werden, der Ausführungsplan Oracle oder was auch immer es nennt, nicht gut skalieren, etc).

So verbrachte ich ziemlich viel Zeit mit dem Versuch zu tun, die IN-Klausel mit einem Tabellenwert-parameter auf einem datamart auf die ich nur Leserechte, bevor ich getroffen wurde von einem blendenden Blitz der offensichtlichen (Bei einer ASP.net forum nicht weniger). Stellt sich heraus Oracle unterstützt Xml-Abfragen 'nativ', also statt der übergabe ein array von Werten übergeben Sie eine xml-Liste (wenn es das ist alles, was Sie brauchen). Wieder, ich könnte falsch sein, aber es wird behandelt, als eine legitime parameter binden und diese ist ein Beispiel dafür, wie einfach es zu verwenden ist (vb.net, ADO.net, ODP.net NuGet-Paket):

    Dim xe As New XElement("l", New XElement("i", "ITEM-A"), New XElement("i", "ITEM-B"))
    Using conn As New OracleConnection(myConnectionString)
        conn.Open()
        Using cmd As OracleCommand = conn.CreateCommand()
            cmd.CommandType = CommandType.Text
            Dim query As String
            query = "  SELECT s.FOO, q.BAR " & vbCrLf
            query &= " FROM TABLE1 s LEFT OUTER JOIN " & vbCrLf
            query &= "      TABLE2 q ON q.ID = s.ID " & vbCrLf
            query &= " WHERE (COALESCE(q.ID, 'NULL') NOT LIKE '%OPTIONAL%') AND "
            query &= "       (s.ID IN ("
            query &= "                      SELECT stid "
            query &= "                      FROM XMLTable('/l/i' PASSING XMLTYPE(:stid) COLUMNS stid VARCHAR(32) PATH '.')"
            query &= "                 )"
            query &= "        )"
            cmd.CommandText = query
            Dim parameter As OracleParameter = cmd.Parameters.Add("stid", OracleDbType.NVarchar2, 4000)
            parameter.Value = xe.ToString
            Using r As OracleDataReader = cmd.ExecuteReader
                While r.Read()
                    //Do something
                End While
            End Using
        End Using
        conn.Close()

Dies ist eher eine Beobachtung als eine sorgfältig recherchierte Lösung, also bitte kommentieren, wenn es etwas unangemessen, es zu tun auf diese Weise.

BEARBEITEN. Es ist offenbar ein 4000 Zeichen begrenzt, mit dieser Methode (2000, wenn NVARCHAR), so musste ich aufpassen, meine paging. Die informative Fehlermeldung, die Sie erhalten, wenn Sie gehen über wird 'ORA-01460: implementierte oder unzumutbar Umwandlung beantragt'