集合参数绑定变量的实现

问题的引出

select ename 
from emp 
where 
empno in (7369,7499,7521)

这个语句显然用的是直接变量，这是我们应该尽量避免的。


新建系统数据类型
create type t_number is table of number(4);

测试这个集合型数据类类型
select column_value from  table(t_number(7369, 7499, 7521,7698))


declare
  --type t_number is table of emp.empno%type;
  --create or replace type t_number is table of number(4);--新建系统数据类型
  c_deptno t_number;
  
  v_deptno1 emp.empno%type :=7369;
  v_deptno2 emp.empno%type :=7499;
  v_deptno3 emp.empno%type :=7521; 
begin
  --直接变量
  dbms_output.put_line('直接变量');
  for x in (select ename from emp where empno in (7369, 7499, 7521)) loop
    dbms_output.put_line(x.ename);
  end loop;
  --半绑定变量
  dbms_output.put_line('半绑定变量');
  for x in (select ename from emp where empno in (v_deptno1, v_deptno2, v_deptno3)) loop
    dbms_output.put_line(x.ename);
  end loop;
  
  --全绑定变量
  dbms_output.put_line('全绑定变量');
  c_deptno := t_number(7369, 7499, 7521);
  for x in (select ename from emp where empno member of c_deptno) loop
    dbms_output.put_line(x.ename);
  end loop;  

  --全绑定变量1
  dbms_output.put_line('全绑定变量1');
  c_deptno := t_number(7369, 7499, 7521,7698);
  for x in (select ename from emp where empno member of c_deptno) loop
    dbms_output.put_line(x.ename);
  end loop;   

  --全绑定变量2
  dbms_output.put_line('全绑定变量2');
  c_deptno := t_number(7369, 7499, 7521,7698);
  for x in (select ename from emp 
  --select column_value from  table(t_number(7369, 7499, 7521,7698))
  where emp.empno in (select column_value from  table(c_deptno))
  ) loop
    dbms_output.put_line(x.ename);
  end loop; 
  
 
  --全绑定变量3
  dbms_output.put_line('全绑定变量3');
  c_deptno := t_number(7844,7876,7900,7902,7934 );
  for x in (select ename from emp 
  --select column_value from  table(t_number(7369, 7499, 7521,7698))
  where emp.empno in (select column_value from  table(c_deptno))
  ) loop
    dbms_output.put_line(x.ename);
  end loop;     
end;

我们看到这个c_deptno := t_number(XXXXXXXXX)很好地解决了集合类型变量的绑定。

但是我们在应用中并没有这样的数据类型，如在jdbc中有varchar,date等类型，但是没有一个类型映射到t_number类型。

Oracle的JDBC扩展解决了这个问题
package methods;

import java.sql.*;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class collectParams {

    public collectParams() {
    }

    public static void main(String[] args) throws SQLException,
            ClassNotFoundException {
        String query = "select  ename from emp where emp.empno member of ?";
        System.out.println(" query = " + query);
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection conn =
                DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:gq", "scott", "tiger");
        int[] empno = {7876, 7900, 7902, 7934};
       // ARRAY ary1 = new ARRAY(ArrayDescriptor.createDescriptor("T_NUMBER", conn), conn, empno);

        PreparedStatement pstmt = conn.prepareStatement(query);
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("T_NUMBER", conn);
        Array ary1 =new ARRAY(descriptor,conn,empno);
       // System.out.println(ary1);
        pstmt.setArray(1,ary1);
      //pstmt.setObject(1, ary1, OracleTypes.ARRAY);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            System.out.println(rs.getString(1));
        }
        rs.close();
        conn.close();
    }
}