将用户定义的列表从 hibernate 传递到 oracle 存储过程 [英] Passing user defined list from hibernate to oracle stored procedure

问题描述

我想从我的休眠类中传递一个用户定义的对象，并将它传递给一个存储过程，该过程读​​取这个对象列表并进行处理.我该怎么做?

I want to pass a user defined object from my hibernate class and pass it onto a stored procedure which reads this list of objects and does the processing. How can i do the same?

类如下.

public class ExcelListenerBean {
    private int id;
    private String shortName;
    private String fmrCusip;
    private Double incorrectTrdShares;
    private Double incorrectTrdPrice;
    private String incorrectTrdBuySell;
    private Double incorrectTrdCommRate;
    private Double incorrectTrdCommission;
    private Double incorrectTrdFees;
    private Double incorrectTrdNet;
    private Double correctionTrdShares;
    private Double correctionTrdPrice;
    private String correctionTrdBuySell;
    private Double correctionTrdCommRate;
    private Double correctionTrdCommission;
    private Double correctionTrdFees;
    private Double correctionTrdNet;
    private String currency;
    private String fx;
    private Double netUSD;
    private String notes;
}

谁能告诉我如何起草程序以及如何遍历 ExcelListenerBean 对象列表并将它们保存到表格中.

Can any one please let me know how to draft the procedure and how to loop through the list of ExcelListenerBean objects and save them to a table.

推荐答案

1. 在 Oracle 中创建 OBJECT 类型，例如 MyType 是 OBJECT ....，其中包含您需要的所有字段
2. 创建集合类型，TableOfMyObject IS TABLE OF MyObjectType
3. 创建以 TableOfMyObject 作为参数的过程.

1. Create OBJECT type , say MyType is OBJECT .... in Oracle that has all the fields you need
2. Create collection type , TableOfMyObject IS TABLE OF MyObjectType
3. Create procedure that takes TableOfMyObject as parameter.

您可以在存储过程中的 SQL 语句中使用集合变量，例如SELECT * FROM TABLE(collection_variable)

You can use collection variable in SQL statements in your stored procedure like SELECT * FROM TABLE(collection_variable)

我做了同样的事情，但最大的挑战是使用 hibernate 从应用程序调用它 - 我终于找到了方法.

I did the same, but the biggest challenge wast to call it from the app using hibernate - I finally found the way to do that.

更新可以从 Toad 运行的 SQL.

Update SQL that can be run from Toad.

set serveroutput on; -- for debugging, 
-- it makes sense if your procedure outputs anything
declare my_list TableOfMyObject  := TableOfMyObject ();
begin 
  my_list.extend;
  my_list(1) := MyType([MyType constructor parameters]);

  my_list.extend;
  my_list(2) := MyType([MyType constructor parameters]);
  your_procedure(my_list);
end;

这篇关于将用户定义的列表从 hibernate 传递到 oracle 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！