HIbernate Oracle存储过程

之前为了实现基于Hibernate+Oracle的存储过程调用，发现了一个又一个坑，然后一个一个的尝试解决。
需求：使用Hibernate调用Oracle的存储过程，需要支持的有动态表名、存储过程变量定义、数组传递

1. oracle变量定义

首先，需要解决的是如何在oracle存储过程中定义变量，否则连存储过程都没法写

变量赋值

  1. 在AS…BEGIN中间插入要定义的变量foo
  2. 使用select into foo语法
  1. -- http://stackoverflow.com/questions/16260068/assign-a-select-to-a-variable-in-a-stored-procedure
  2. create or replace
  3. FUNCTION PCD_COMBAT (identifier_perso NUMBER, identifier_advers NUMBER)
  4. RETURN NUMBER
  5. AS
  6. ATT_PERSO NUMBER;
  7. OFF_PERSO NUMBER;
  8. DEF_ADVERS NUMBER;
  9. BEGIN
  10. SELECT OFFENSE_PERSO
  11. INTO OFF_PERSO
  12. FROM PERSONNAGE
  13. WHERE ID_PERSO = identifier_perso;
  14. SELECT DEFENSE_ADVERSAIRE
  15. INTO DEF_ADVERS
  16. FROM PERSONNAGE
  17. WHERE ID_ADVERSAIRE = identifier_advers;
  19. ATT_PERSO := OFF_PERSO - DEF_ADVERS;
  20. IF ATT_PERSO <1 THEN
  21. ATT_PERSO :=1;
  22. END IF
  24. RETURN ATT_PERSO;
  26. END PCD_COMBAT;

复杂变量+package(未使用到)

Another reason that standalone procedures and functions, like the ones in “Creating and Using Standalone Procedures and Functions” , are limited to large-scale development is that they can only send and receive scalar parameters ( NUMBER , VARCHAR2, and DATE ), but cannot use a composite structure, RECORD , unless it is defined in a package specification.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHCHIDB

2. Hibernate存储过程调用

一个简单的存储过程写完了，得先测试能否使用hibernate对存储过程进行调用，所以接下来了解Hibernate是如何调用存储过程的。稍微查一下，发现调用还是相当简单的。但是，是有一些限制和条件的。

使用hibernate.query限制

For Oracle the following rules apply:

不使用query

如果不能满足Hibernate对query对象的使用条件，就只能自己使用session进行相对原生一些的调用。虽然官方文档给出了session.connection()，但是，明显这个文档也是一段时间没有更新了。在4.X版本中，这个接口已经没有了。因此，进一步找了一些替代方案
seesion.connection()
http://stackoverflow.com/questions/15217984/alternative-of-deprecated-hibernate-getsession-connection

connection异常

用了connection()跟着又有异常发生，看上去是安全策略和对象包装造成的，解决方案如下：
http://www.coderanch.com/t/415597/JDBC/databases/Apache-Commons-DBCP-connection-object
https://community.oracle.com/thread/2564233?start=0&tstart=0
accessToUnderlyingConnectionAllowed="true"
conn = ((DelegatingConnection) conn).getInnermostDelegate();

3. 变量类型

一个简单的存储过程的调用搞定了，就是后面的重头戏，表名做参数、传递数组参数。。。这是个大坑！大坑！！大坑！！！

表名做参数

表名做参数，有点像是Javascript的eval()，让一个字符串可执行
除了下面的例子显示的，还有使用DBMS.parse()

  1. -- https://community.oracle.com/thread/1122692?tstart=0
  2. CREATE OR REPLACE PROCEDURE "P_1"( TAB1 VARCHAR2)
  3. AS
  4. field1 CHAR(5);
  5. field2 CHAR(5);
  6. c1 sys_refcursor;
  7. begin
  8. l_str :='select a,b,c from '||tab1||' INNER JOIN tab2 ON a = .......';
  9. open c1 from l_str;
  11. /*OPEN C1;
  12. CURSOR C1
  13. IS
  14. SELECT a, b , c
  15. FROM TAB1
  16. INNER JOIN
  17. tab2
  18. ON a = .......
  19. OPEN C1;*/
  21. LOOP
  22. FETCH C1
  23. INTO .....
  25. EXIT WHEN C1%NOTFOUND;
  26. .....
  27. END;
  28. END LOOP;
  30. CLOSE C1;
  32. EXCEPTION
  33. WHEN OTHERS
  34. .....
  35. END;

数组做参数

这绝对是大坑，神坑！
http://stackoverflow.com/questions/23573303/send-retrieve-array-list-to-oracle-stored-procedure

http://www.codeproject.com/Articles/164705/Oracle-PL-SQL-collections

http://viralpatel.net/blogs/java-passing-array-to-oracle-stored-procedure/

  1. -- http://xiaogui9317170.iteye.com/blog/286401
  2. create table study_array_nick_tab
  3. (
  4. name varchar2(200)
  5. );
  6. create or replace type study_array_nick_list is VARRAY(1000) of varchar2(200);
  7. create or replace procedure study_array_nick(in_array in study_array_nick_list)is
  8. v_i number;
  9. begin
  10. for v_i in1.. in_array.count loop
  11. insert into study_array_nick_tab(name) values(in_array(v_i));
  12. end loop;
  14. commit;
  15. exception when others then
  16. rollback;
  17. raise_application_error('20999','测试错误');
  18. end study_array_nick;
  1. -- http://forum.spring.io/forum/spring-projects/data/24224-how-can-i-pass-arraylist-to-stored-procedure/page2
  3. CREATE OR REPLACE TYPE TEST_EMP_OBJ AS OBJECT (empno number, empname varchar2(30));
  4. create or replace type TEST_EMP_OBJ_ARRAY as table of TEST_EMP_OBJ;
  5. create table test_emp (empno number, empname varchar2(30));
  6. CREATE OR REPLACE PROCEDURE TEST_EMP_OBJ_ARRAY_PROC ( p_obj_array in TEST_EMP_OBJ_ARRAY ) AS
  7. begin
  8. for i in1..p_obj_array.count loop
  9. insert into test_emp (empno, empname)
  10. values(p_obj_array(i).empno, p_obj_array(i).empname);
  11. end loop;
  12. end;

ORACLE+JAVA

照着上面的那些例子，写了存储过程，然后通过Hibernate调用，实际上一个都没有成功。不传递数组就没问题，传递数组就会报异常，大意是参数无法转化为Oracle的表示。然后找到下面这个完整的例子，照着一模一样敲了一遍，除了那个IDS的TYPE我定义，而是直接用了varchar(32)，结果就是不行。。。

http://stackoverflow.com/questions/3626061/how-to-call-oracle-stored-procedure-which-include-user-defined-type-in-java

https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets#11g-updates

  1. SQL> CREATE OR REPLACE TYPE IDS AS OBJECT ( id1 NUMBER, id2 NUMBER, id3 NUMBER );
  2. 2/
  3. Type created
  5. SQL> CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS;--这里我用的是OF varvchar(32)
  6. 2/
  7. Type created
  9. SQL> CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS
  10. 2BEGIN
  11. 3 FOR i IN 1.. p_ids.COUNT LOOP
  12. 4 dbms_output.put_line(p_ids(i).id1
  13. 5||','|| p_ids(i).id2
  14. 6||','|| p_ids(i).id3);
  15. 7END LOOP;
  16. 8END getInfo;
  17. 9/
  18. Procedure created
  1. SQL> CREATE OR REPLACE
  2. 2 AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
  3. 3as
  4. 4import java.io.*;
  5. 5import java.sql.*;
  6. 6import oracle.sql.*;
  7. 7import oracle.jdbc.driver.*;
  8. 8
  9. 9publicclassArrayDemo{
  10. 10
  11. 11publicstaticvoid passArray()throwsSQLException{
  12. 12
  13. 13Connection conn =
  14. 14newOracleDriver().defaultConnection();
  15. 15
  16. 16
  17. 17//这里没有定义IDS
  18. 18StructDescriptor itemDescriptor =StructDescriptor.createDescriptor("IDS",conn);
  19. 19
  20. 20Object[] itemAtributes =newObject[]{newInteger(1),
  21. 21newInteger(2),
  22. 22newInteger(3)};
  23. 23 STRUCT itemObject1 =new STRUCT(itemDescriptor,conn,itemAtributes);
  24. 24
  25. 25 itemAtributes =newObject[]{newInteger(4),
  26. 26newInteger(5),
  27. 27newInteger(6)};
  28. 28 STRUCT itemObject2 =new STRUCT(itemDescriptor,conn,itemAtributes);
  29. 29
  30. 30 STRUCT[] idsArray ={itemObject1,itemObject2};
  31. 31
  32. 32ArrayDescriptor descriptor =
  33. 33ArrayDescriptor.createDescriptor("IDS_TABLE", conn );
  34. 34//在这里直接穿了String的List。明明构造函数是有这种构造的
  35. 35 ARRAY array_to_pass =
  36. 36new ARRAY( descriptor, conn, idsArray );
  37. 37//然后调用就是出现异常
  38. 38OraclePreparedStatement ps =
  39. 39(OraclePreparedStatement)conn.prepareStatement
  40. 40("begin getInfo(:x); end;");
  41. 41
  42. 42 ps.setARRAY(1, array_to_pass );
  43. 43 ps.execute();
  44. 44
  45. 45}
  46. 46}
  47. 47/
  48. Java created
  1. SQL> CREATE OR REPLACE
  2. 2 PROCEDURE show_java_calling_plsql
  3. 3 AS LANGUAGE JAVA
  4. 4 NAME 'ArrayDemo.passArray()';
  5. 5/
  6. Procedure created
  8. SQL>exec show_java_calling_plsql ;
  9. 1,2,3
  10. 4,5,6
  12. PL/SQL procedure successfully completed

4. 结论

关于使用Hibernate+Oracle存储过程，除了最后的数组参数没有搞定，其他的都尝试成功了。关于数组参数这个问题，我还是不知道怎么解决。难道一定要定义一个只包含一个字符串的OBJECT（就像上面的那个IDS一样）？这个没有进一步解决了。将这整个过程记录下来，权当整理。





posted @ 2016-05-07 15:14  cosmoseeker  阅读(411)  评论(0)  编辑  收藏  举报