Java获取Oracle存储过程返回的自定义嵌套表

偏执的太偏执、 2022-04-02 11:14 166阅读 0赞

# Java获取Oracle存储过程返回的自定义嵌套表 #

2013年01月12日 12:13:22 [luckystar2008][] 阅读数：1363

1.  `在命令窗口执行以下语句，创建自定义类型NESTEDARRAY。`
2.  `create or replace TYPE NESTEDARRAY IS TABLE OF NVARCHAR2(32);`
3.   
4.  `在存储过程中使用自定义类型NESTEDARRAY。`
5.  `PROCEDURE GET_ARR_RESULT(INPUTARRAY IN NESTEDARRAY, AR OUT NESTEDARRAY) IS`
6.  `BEGIN`
7.  `AR := NESTEDARRAY();`
8.   
9.  `FOR I IN 1 .. INPUTARRAY.COUNT LOOP`
10. `AR.EXTEND;`
11. `AR(I) := I|| INPUTARRAY(I);`
12. `END LOOP;`
13. `END GET_ARR_RESULT;`

1.  `Java代码：`
2.  `import java.sql.Connection;`
3.  `import java.sql.SQLException;`
4.   
5.  `import oracle.jdbc.OracleCallableStatement;`
6.  `import oracle.jdbc.OracleTypes;`
7.  `import oracle.sql.ARRAY;`
8.  `import oracle.sql.ArrayDescriptor;`
9.  `import oracle.sql.Datum;`
10.  
11. `/**`
12. `* Java获取Oracle存储过程返回自定义类型`
13. `* @author lucky star`
14. `*`
15. `*/`
16. `public class OracleTypeResultTest {`
17.  
18. `/**`
19. `* @param args`
20. `*/`
21. `public static void main(String[] args) {`
22. `Connection con = null;`
23. `OracleCallableStatement ocs = null;`
24. `String sql = "{call test.GET_ARR_RESULT(?,?)}";`
25.  
26. `try {`
27. `con = DBUtil.dbUtil.getConnection();`
28. `ocs = (OracleCallableStatement) con.prepareCall(sql);`
29. `String[] params = {“10001”,”10003”};`
30. `ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("NESTEDARRAY", con);`
31. `ARRAY inputArray = new ARRAY(arrayDesc, con, params);`
32. `ocs.setARRAY(1, inputArray);`
33. `ocs.registerOutParameter(2,OracleTypes.ARRAY,"NESTEDARRAY");`
34. `ocs.execute();`
35.  
36. `ARRAY array = ocs.getARRAY(2);`
37. `Datum[] datum = array.getOracleArray();`
38. `for (int i=0;i<datum.length;i++) {`
39. `System.out.println(datum[i].intValue());`
40. `}`
41.  
42. `} catch (SQLException e) {`
43. `// TODO Auto-generated catch block`
44. `e.printStackTrace();`
45. `} finally {`
46. `try {`
47. `if(ocs!=null)ocs.close();`
48. `if(con!=null)con.close();`
49. `} catch (SQLException e) {`
50. `// TODO Auto-generated catch block`
51. `e.printStackTrace();`
52. `}`
53. `}`
54. `}`
55.  
56. `}`
57.  
58. `用VARCHAR2测试，发现存储过程中无法获取到传入的数组的值。`
59. `不过，将自定义类型由varchar2改成NVarchar2就成功了。`
60. `即：create or replace TYPE NESTEDARRAY IS TABLE OF NVARCHAR2(32);不知道什么原因。`

[https://blog.csdn.net/qincidong/article/details/8495893][https_blog.csdn.net_qincidong_article_details_8495893]

[luckystar2008]: https://me.csdn.net/qincidong
[https_blog.csdn.net_qincidong_article_details_8495893]: https://blog.csdn.net/qincidong/article/details/8495893