当前位置：Gxlcms > 数据库问题 > HIbernate Oracle存储过程

HIbernate Oracle存储过程

时间：2021-07-01 10:21:17 帮助过：2人阅读

http://stackoverflow.com/questions/16260068/assign-a-select-to-a-variable-in-a-stored-procedure
  • create or replace
  • FUNCTION PCD_COMBAT (identifier_perso NUMBER, identifier_advers NUMBER)
  • RETURN NUMBER
  • AS
  • ATT_PERSO NUMBER;
  • OFF_PERSO NUMBER;
  • DEF_ADVERS NUMBER;
  • BEGIN
  • SELECT OFFENSE_PERSO
  • INTO OFF_PERSO
  • FROM PERSONNAGE
  • WHERE ID_PERSO = identifier_perso;
  • SELECT DEFENSE_ADVERSAIRE
  • INTO DEF_ADVERS
  • FROM PERSONNAGE
  • WHERE ID_ADVERSAIRE = identifier_advers;
  • ATT_PERSO := OFF_PERSO - DEF_ADVERS;
  • IF ATT_PERSO <1 THEN
  • ATT_PERSO :=1;
  • END IF
  • RETURN ATT_PERSO;
  • 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:

    • A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define aREF CURSOR type. See Oracle literature for further information.

      https://docs.jboss.org/hibernate/orm/4.2/manual/en-US/html/ch18.html#sp_query

    不使用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. 变量类型

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

    • 一般类型
    • 数组类型
      http://www.codeproject.com/Articles/164705/Oracle-PL-SQL-collections

    表名做参数

    表名做参数，有点像是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一样）？这个没有进一步解决了。将这整个过程记录下来，权当整理。



    来自为知笔记(Wiz)



    HIbernate Oracle存储过程

    标签：

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！