在 SQL Server 2008 中，我可以将表值参数从 NHibernate 传递给我的存储过程.如何在 Oracle 中实现相同的目标 [英] 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.可能有一种方法可以从值类型推导出DB类型，但我还不确定该怎么做.
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 传递给我的存储过程.如何在 Oracle 中实现相同的目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
