在SQL Server 2008中，我能够将表值参数从NHibernate传递到我的存储过程中. [英] In SQL Server 2008 I am able to pass table-valued parameter to my stored procedure from NHibernate.How to achieve the same in Oracle
问题描述
我已经在SQL Server 2008中创建了一个表作为类型.
I have created a table as a type in SQL Server 2008.
由于SQL Server 2008支持将表值参数作为IN参数传递给存储过程.一切正常.
As SQL Server 2008 supports passing table value parameter as IN parameter to stored procedure. It is working fine.
现在我必须在Oracle中执行相同的方法.
Now I have to perform the same approach in Oracle.
我是通过PLSQLAssociativeArray做到的，但是关联数组的局限性是它们是同质的(每个元素都必须是同一类型).
I did it through PLSQLAssociativeArray but the limitaion of Associative array is they are homogeneous (every element must be of the same type).
在使用SQL Server 2008表值参数的情况下，这是可能的.
Where as in case of table-valued parameter of SQL Server 2008, it is possible.
如何在Oracle中实现相同目标??
以下是我在SQL Server 2008中的类型和存储过程:
Following are my type and stored procedure in SQL Server 2008:
CREATE TYPE [dbo].[EmployeeType] AS TABLE(
[EmployeeID] [int] NULL,
[EmployeeName] [nvarchar](50) NULL
)
GO
CREATE PROCEDURE [dbo].[TestCustom] @location EmployeeType READONLY
AS
insert into Employee (EMP_ID,EMP_NAME)
SELECT EmployeeID,EmployeeName
FROM @location;
GO
从NHibernate拨打电话
var dt = new DataTable();
dt.Columns.Add("EmployeeID", typeof(int));
dt.Columns.Add("EmployeeName", typeof(string));
dt.Rows.Add(new object[] { 255066, "Nachi11" });
dt.Rows.Add(new object[] { 255067, "Nachi12" });
ISQLQuery final = eventhistorysession.CreateSQLQuery("Call TestCustom @pLocation = :id");
IQuery result = final.SetStructured("id", dt);
IList finalResult = result.List();
推荐答案
据我了解，不可能使用Oracle对象表参数(请参见 @Quassnoi的答案)使用nHibernate或ODP.NET. ODP.NET支持的唯一集合类型是PLSQLAssociativeArray.
As I understand, it is not possible to use Oracle object table parameters (see @Quassnoi's answer for an example) using either nHibernate or ODP.NET. The only collection type supported by ODP.NET is PLSQLAssociativeArray.
但是，可以很容易地获得与使用关联数组的SQL Server TVP相同的结果.诀窍是为每个参数定义一个数组，而不是为整个表定义一个数组.
However, one could easily achieve the same result as with SQL Server TVPs using associative arrays. The trick is to define an array for each parameter instead of a single one for the whole table.
由于我无法找到一个完整的概念验证解决方案，因此我正在发布一个完整的概念验证解决方案.
I'm posting a complete proof-of-concept solution as I haven't been able to find one.
该模式包括一个表和一个打包的插入过程.它将每个参数视为一列，并假定每个数组至少与第一个数组一样长.
The schema includes a table and a packaged insert procedure. It treats each parameter as a column and assumes each array is at least as long as the first one.
create table test_table
(
foo number(9),
bar nvarchar2(64)
);
/
create or replace package test_package as
type number_array is table of number(9) index by pls_integer;
type nvarchar2_array is table of nvarchar2(64) index by pls_integer;
procedure test_proc(p_foo number_array, p_bar nvarchar2_array);
end test_package;
/
create or replace package body test_package as
procedure test_proc(p_foo number_array, p_bar nvarchar2_array) as
begin
forall i in p_foo.first .. p_foo.last
insert into test_table values (p_foo(i), p_bar(i));
end;
end test_package;
/
nHibernate映射
<sql-query name="test_proc">
begin test_package.test_proc(:foo, :bar); end;
</sql-query>
nHibernate自定义IType
我从一个与SQL Server有关的好答案中借用了这个概念，并对该类进行了少许修改以使其与ODP.NET兼容.由于IType很大，因此我仅显示实现的方法；其余的抛出NotImplementedException.
nHibernate Custom IType
I've borrowed the concept from a great SQL Server related answer and modified the class slightly to work with ODP.NET. As IType is huge, I only show the implemented methods; the rest throws NotImplementedException.
如果有人想在生产代码中使用它，请注意，即使它满足了我的即时需求，我也没有对它进行广泛的测试.
If anyone wants to use this in production code, please be aware that I've not tested this class extensively even if it does what I immediately need.
public class OracleArrayType<T> : IType
{
private readonly OracleDbType _dbType;
public OracleArrayType(OracleDbType dbType)
{
_dbType = dbType;
}
public SqlType[] SqlTypes(IMapping mapping)
{
return new []{ new SqlType(DbType.Object) };
}
public bool IsCollectionType
{
get { return true; }
}
public int GetColumnSpan(IMapping mapping)
{
return 1;
}
public void NullSafeSet(IDbCommand st, object value, int index, ISessionImplementor session)
{
var s = st as OracleCommand;
var v = value as T[];
if (s != null && v != null)
{
s.Parameters[index].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
s.Parameters[index].OracleDbType = _dbType;
s.Parameters[index].Value = value;
s.Parameters[index].Size = v.Length;
}
else
{
throw new NotImplementedException();
}
}
// IType boiler-plate implementation follows.
构造函数参数指定基本数组类型的类型(即，如果传递字符串数组，则传递OracleDbType.NVarchar2.可能有一种从值类型推导数据库类型的方法，但是我不确定怎么办呢?
The constructor parameter specifies the type of the base array type (i.e. if you passing an array of strings, pass OracleDbType.NVarchar2. There probably is a way to deduce the DB type from the value type, but I'm not sure yet how to do that.
这将包装类型创建:
public static class OracleExtensions
{
public static IQuery SetArray<T>(this IQuery query, string name, OracleDbType dbType, T[] value)
{
return query.SetParameter(name, value, new OracleArrayType<T>(dbType));
}
}
用法
将所有这些联系在一起，这是使用该类的方式:
Usage
To tie all this together, this is how the class is used:
using (var sessionFactory = new Configuration().Configure().BuildSessionFactory())
using (var session = sessionFactory.OpenSession())
{
session
.GetNamedQuery("test_proc")
.SetArray("foo", OracleDbType.Int32, new[] { 11, 21 })
.SetArray("bar", OracleDbType.NVarchar2, new [] { "bar0", "bar1" })
.ExecuteUpdate();
}
运行代码后select * from test_table的结果:
FOO BAR
----------------
11 bar0
21 bar1
这篇关于在SQL Server 2008中，我能够将表值参数从NHibernate传递到我的存储过程中.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
