首页 > 代码库 > java怎样将一个List传入Oracle存储过程
java怎样将一个List传入Oracle存储过程
java怎样将一个List传入Oracle存储过程。样例例如以下:
数据库端建一个PL/SQL的数组。
CREATE OR REPLACE TYPE tables_array AS VARRAY(100) OF VARCHAR2(32) ; drop table test purge; create table test ( name varchar2(32) ); create or replace procedure t_list_to_p(arr_t in tables_array) is begin for i in arr_t.first .. arr_t.last loop insert into test values(arr_t(i)); end loop; commit; end t_list_to_p;
java代码:
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; public class TestListToProcedure { static final String driver_class = "oracle.jdbc.driver.OracleDriver"; static final String connectionURL = "jdbc:oracle:thin:@10.150.15.150:1521:orcl"; static final String userID = "test"; static final String userPassword = "test"; public void runTest() { Connection con = null; CallableStatement stmt = null ; try { Class.forName (driver_class).newInstance(); con = DriverManager.getConnection(connectionURL, userID, userPassword); stmt = con.prepareCall("{call t_list_to_p(?)}"); ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY",con); List list = new ArrayList(); list.add("张三"); list.add("李四"); list.add("王五"); ARRAY array = new ARRAY(descriptor,con,list.toArray()); stmt.setArray(1, array); stmt.execute(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(con != null){ try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } public static void main(String[] args) { TestListToProcedure testListToProcedure = new TestListToProcedure(); testListToProcedure.runTest(); } }
java怎样将一个List传入Oracle存储过程