11. Dezember 2008

IN-Klauseln, dynamisches SQL und Bindevariablen

English title: IN conditions, dynamic SQL and Bind Variables

Auf der DOAG-Konferenz diskutierte ich mit jemandem, wie man IN-Klauseln mit Bindevariablen (für die Java-Programmierer: PreparedStatements) implementieren kann. Das Problem dabei ist, dass die Anzahl der Werte in der IN-Klausel nicht immer gleich ist. Für den Java-Programmierer kann ein Abfrage mit vier Argumenten so aussehen:
At the "DOAG conference" two weeks ago I discussed with somebody how an IN condition within a SQL WHERE clause could be used with bind variables (for with Java words: with PreparedStatements). And the challenge is that the number of arguments is not each time the same. A query with four arguments might look like the following ...
select [col1], [col2], [col3] from [table] where [col4] in (?, ?, ?, ?)
Wenn für die nächste Abfrage nun fünf Werte in die IN-Klausel gesetzt werden sollen, muss ein neues Statement erzeugt und geparst werden - in der Datenbank kann der vorhandene Cursor also nicht wiederverwendet werden; auf Java-Seite muss man neue Objekte erzeugen.
The next query now might have five arguments - so the Java programmer cannot reuse the first query statement. A new object has to be constructed which means that the current cursor in the database can also not be reused.
Ein Lösungsansatz ist, mit temporären Tabellen zu arbeiten; die Werte dort mit SQL INSERT-Kommandos hineinzuschreiben und dann anstelle der konkreten Werte eine Subquery in der eigentlichen Abfrage zu verwenden. Es gibt jedoch auch noch einen anderen Ansatz: Anstelle der temporären Tabelle kann man auch einen Objekttypen nehmen: Abhängig von dem Datentypen, der in der IN-Klausel verwendet werden soll, erzeugt man sich also zunächst einen "Array-Typen":
One possible solution is the usage of temporary tables. So the developer can create a temporary table, insert the arguments into that table and use a subquery in the actual query. But (if you don't like temporary tables) there is also another solution: You also can use object types for this. So first we create an array type suitable for our query.
CREATE TYPE QUERY_ARRAY AS TABLE OF [NUMBER | VARCHAR2(x) | DATE] 
Die SQL-Abfrage muss nun so umgestellt werden:
Next we rearrange the SQL query:
SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))
Wir verwenden wieder eine Subquery; allerdings nicht mit einer temporären Tabelle, sondern mit einer Variablen innerhalb der Funktion TABLE(). Diese Variable muss vom soeben neu erstellten Typ QUERY_ARRAY sein. Dann stellt sich natürlich sofort die Frage, wie man einen solchen Typen (von Java aus) erzeugt ... Hier ist ein Beispiel:
We use (as we would have done with a temporary table) a subquery to embed the array of arguments into the SQL query. The TABLE() function contains a variable of the just created data type QUERY_ARRAY and makes the contents accessible to the SQL engine. This leads immediately to the next question: How are those user-defined data types representated in JDBC code ...? Here is some example code.
import java.sql.*;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

/*
 * This program queries multiple rows from the EMP
 * table using the IN clause.
 *
 * It always uses THE SAME STATIC SQL Query - the number
 * of arguments for the IN clause does not matter.
 *
 * SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))
 *
 * Prerequisite: 
 * - An object type NUMBER_ARRAY must exist in the DB Schema
 *   CREATE TYPE QUERY_ARRAY AS TABLE OF NUMBER   
 *   
 */ 

public class JdbcDynamicInQuery {
  static String sConn = "jdbc:oracle:thin:@192.168.2.140:1521:orcl";
  static String sUser = "scott";
  static String sPass = "tiger";
  static String sSql =  "SELECT * FROM emp WHERE empno in (SELECT * FROM TABLE(?))";

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection(sConn, sUser, sPass);
    con.setAutoCommit(false);

    // Those following lines generate the JDBC representation of the
    // object type QUERY_ARRAY 
    ArrayDescriptor dbArrayDesc = ArrayDescriptor.createDescriptor("QUERY_ARRAY", con);
    ARRAY dbArray = new ARRAY(dbArrayDesc, con, args);

    // prepare the Statement
    PreparedStatement pstmt = con.prepareStatement(sSql);

    // Use the setArray method to pass the Array from JDBC to the database
    pstmt.setArray(1, dbArray);
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      System.out.println(rs.getString("ENAME"));
    }
    rs.close();
    pstmt.close();
    con.close();
  }
}
Die Klasse oracle.sql.ARRAY repräsentiert einen vom Benutzer in der Datenbank definierten Array-Typen. Da der Name dieses Typen frei wählbar ist, wird der oracle.sql.ArrayDescriptor benötigt, um ein solches Objekt zu erzeugen. Die Inhalte kommen aus einem gewöhnlichen Java-Array (hier: args). Ihr könnt das Beispielprogramm testen, indem Ihr eine beliebige Anzahl EMPNOs als Argumente übergebt ...
The java class oracle.sql.ARRAY represents user-defined array data type. Since the name of this data type is also user-defined JDBC needs the oracle.sql.ArrayDescriptor to create an instance of the oracle.sql.ARRAY class. The actual contents come from an ordinary java array (here: args). You can test the sample program after compiling as follows: Just provide a random amount of EMPNOs as command line arguments ...
$ java JdbcDynamicInQuery 7844 7839 7822
... und es werden stets alle in der IN-Klausel berücksichtigt. Es wird dabei stets ein- und dieselbe SQL-Abfrage verwendet. Übrigens: Das ist mit PL/SQL und EXECUTE IMMEDIATE genauso nutzbar - hier ist das Erzeugen der Variable sogar noch leichter:
... and you'll see that all of them are considered in the IN condition. But the SQL statement is always the same. BTW: This is also (of course) usable with PL/SQL and EXECUTE IMMEDIATE - the construction of the array variable is even much simpler.
set serveroutput on

declare
  type cursor_t is ref cursor;

  v_array  query_array;
  v_cursor cursor_t;  

  v_ename  SCOTT.EMP.ENAME%TYPE;
begin
  v_array := query_array(7844, 7839);
  open v_cursor for 'select ename from scott.emp where empno in (select column_value from table(:1))' using v_array;
  loop
    fetch v_cursor into v_ename;
    exit when v_cursor%notfound;
    dbms_output.put_line(v_ename);
  end loop;
  close v_cursor;
end;
/

Keine Kommentare:

Beliebte Postings