定义类型到Oracle存储过程
参考Oracle:存储过程的可变参数数量
我有存储过程将多个用户插入到用户表中。 该表的定义如下所示:
CREATE TABLE "USER"
(
"Name" VARCHAR2(50),
"Surname" VARCHAR2(50),
"Dt_Birth" DATE,
)
存储过程插入多个用户是:
type userType is record (
name varchar2(100),
...
);
type userList is table of userType index by binary_integer;
procedure array_insert (p_userList in userList) is
begin
forall i in p_userList.first..p_userList.last
insert into users (username) values (p_userList(i) );
end array_insert;
我如何从C#中传递存储过程传递userType的userList? 谢谢
以下是我用于在Oracle中使用存储过程的帮助程序:
internal class OracleDataHelper
{
#region Variables
private static readonly string _connectionString;
#endregion
#region Constructors
static OracleDataHelper()
{
//_connectionString = ConfigurationManager.ConnectionStrings["..."]
// .ConnectionString;
}
#endregion
public static object ExecuteScalar(string query)
{
object result;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
result = command.ExecuteScalar();
command.Dispose();
conn.Close();
}
return result;
}
public static object ExecuteScalar(
string query,
params object[] parameters)
{
object result;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
command.Parameters.AddRange(
ConvertParameters(parameters)
);
result = command.ExecuteScalar();
command.Dispose();
conn.Close();
}
return result;
}
public static int ExecuteNonQuery(string query)
{
return ExecuteNonQuery(query, new List<OracleParameter>());
}
public static int ExecuteNonQuery(
string query,
List<OracleParameter> parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
command.BindByName = true;
command.Parameters.AddRange(
ConvertParameters(parameters.ToArray())
);
result = command.ExecuteNonQuery();
command.Dispose();
conn.Close();
}
return result;
}
public static int ExecuteNonQuery(
string query,
params object[] parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.BindByName = true;
command.CommandType = CommandType.Text;
command.Parameters.AddRange(ConvertParameters(parameters));
result = command.ExecuteNonQuery();
command.Dispose();
conn.Close();
}
return result;
}
public static OracleDataReader ExecuteReader(
OracleConnection conn,
string commandText
)
{
OracleCommand command = new OracleCommand(commandText, conn);
return command.ExecuteReader();
}
public static IDataReader ExecuteReader(
OracleConnection conn,
string spName,
out List<OracleParameter> outParameters,
params object[] parameters)
{
throw new NotImplementedException();
}
public static int ExecuteProcedure(
string spName,
out OutputParameters outputParameters,
params object[] parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
result = command.ExecuteNonQuery();
outputParameters = GetOutputParameters(command.Parameters);
command.Dispose();
conn.Close();
}
return result;
}
public static int ExecuteProcedure(
string spName,
params object[] parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
result = command.ExecuteNonQuery();
command.Dispose();
conn.Close();
}
return result;
}
public static OracleDataReader ExecuteProcedure(
OracleConnection conn,
string spName,
out OutputParameters outputParameters,
params object[] parameters
)
{
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
OracleDataReader reader = command.ExecuteReader();
outputParameters = GetOutputParameters(command.Parameters);
command.Dispose();
return reader;
}
public static OracleDataReader ExecuteProcedure(
OracleConnection conn,
string spName,
params object[] parameters
)
{
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
OracleDataReader reader = command.ExecuteReader();
command.Dispose();
return reader;
}
private static OracleParameter[] ConvertParameters(object[] parameters)
{
parameters = parameters ?? new object[] { };
int parametersCount = parameters.Length;
OracleParameter[] parametersArray =
new OracleParameter[parametersCount];
for (int i = 0; i < parametersCount; i++)
{
object parameter = parameters[i];
OracleParameter oracleParameter;
if (parameter is OracleParameter)
{
oracleParameter = (OracleParameter)parameter;
if (null == oracleParameter.Value)
{
oracleParameter.Value = DBNull.Value;
}
}
else
{
oracleParameter = new OracleParameter();
oracleParameter.Value = parameter == null ?
DBNull.Value :
parameter;
}
// adding udt mapping for the parameter
if (oracleParameter.Value != null &&
oracleParameter.Value is IOracleCustomTypeFactory)
{
MemberInfo info = oracleParameter.Value.GetType();
OracleCustomTypeMappingAttribute[] attributes =
info.GetCustomAttributes(
typeof(OracleCustomTypeMappingAttribute),
false
) as OracleCustomTypeMappingAttribute[];
if (null != attributes && attributes.Length > 0)
{
oracleParameter.UdtTypeName = attributes[0].UdtTypeName;
}
}
parametersArray[i] = oracleParameter;
}
return parametersArray;
}
private static OutputParameters GetOutputParameters(
OracleParameterCollection parameters)
{
OutputParameters outputParameters = new OutputParameters();
foreach (OracleParameter parameter in parameters)
{
if (parameter.Direction == ParameterDirection.Output)
outputParameters.Add(parameter);
}
return outputParameters;
}
internal static string ConnectionString
{
get { return _connectionString; }
}
}
这些方法与UDT一起工作,以及他们使用简单的参数。
这是一个UDT实体的例子:
[Serializable]
[OracleCustomTypeMappingAttribute("MDSYS.SDO_GEOMETRY")]
public class SdoGeometry : IOracleCustomTypeFactory,
IOracleCustomType,
ICloneable, INullable
{
#region Variables
private int _sdoGType;
private int _sdoSrid;
private SdoPoint _sdoPoint;
private SdoElemInfo _sdoElemInfo;
private SdoOrdinates _sdoOrdinate;
private bool _sdoGTypeIsNull;
private bool _sdoSridIsNull;
#endregion
#region Properties
[OracleObjectMappingAttribute("SDO_GTYPE")]
public int SdoGType
{
get { return _sdoGType; }
set
{
_sdoGType = value;
_sdoGTypeIsNull = false;
}
}
public SdoGeometryType SdoGeometryType
{
get { return (Entities.Geometry.SdoGeometryType)(SdoGType % 100); }
}
public int Dimensions
{
get { return (int)(SdoGType / 1000); }
}
public int LrsDimensions
{
get { return (int)((SdoGType / 100) % 10); }
}
[OracleObjectMappingAttribute("SDO_SRID")]
public int SdoSrid
{
get { return _sdoSrid; }
set
{
_sdoSrid = value;
_sdoSridIsNull = false;
}
}
[OracleObjectMappingAttribute("SDO_POINT")]
public SdoPoint SdoPoint
{
get { return _sdoPoint; }
set { _sdoPoint = value; }
}
[OracleObjectMappingAttribute("SDO_ELEM_INFO")]
public SdoElemInfo SdoElemInfo
{
get { return _sdoElemInfo; }
set { _sdoElemInfo = value; }
}
[OracleObjectMappingAttribute("SDO_ORDINATES")]
public SdoOrdinates SdoOrdinates
{
get { return _sdoOrdinate; }
set { _sdoOrdinate = value; }
}
public static SdoGeometry Null
{
get
{
SdoGeometry obj = new SdoGeometry();
return obj;
}
}
#endregion
#region Constructors
public SdoGeometry()
{
_sdoGTypeIsNull = true;
_sdoSridIsNull = true;
_sdoElemInfo = SdoElemInfo.Null;
_sdoOrdinate = SdoOrdinates.Null;
_sdoPoint = SdoPoint.Null;
}
public SdoGeometry(SdoGeometry obj)
{
if (obj != null && this != obj)
{
SdoGType = obj.SdoGType;
SdoSrid = obj.SdoSrid;
SdoPoint = (SdoPoint)obj.SdoPoint.Clone();
SdoElemInfo = (SdoElemInfo)obj.SdoElemInfo.Clone();
SdoOrdinates = (SdoOrdinates)obj.SdoOrdinates.Clone();
}
}
public SdoGeometry(
int gType,
int srid,
SdoPoint point,
SdoElemInfo elemInfo,
SdoOrdinates ordinate)
{
SdoGType = gType;
SdoSrid = srid;
SdoPoint = (SdoPoint)point.Clone();
SdoElemInfo = (SdoElemInfo)elemInfo.Clone();
SdoOrdinates = (SdoOrdinates)ordinate.Clone();
}
#endregion
#region ICloneable Members
public object Clone()
{
return new SdoGeometry(this);
}
#endregion
#region IOracleCustomType Members
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
if (!_sdoGTypeIsNull)
OracleUdt.SetValue(con, pUdt, "SDO_GTYPE", SdoGType);
if (!SdoOrdinates.IsNull)
OracleUdt.SetValue(con, pUdt, "SDO_ORDINATES", SdoOrdinates);
if (!SdoElemInfo.IsNull)
OracleUdt.SetValue(con, pUdt, "SDO_ELEM_INFO", SdoElemInfo);
if (!_sdoSridIsNull)
OracleUdt.SetValue(con, pUdt, "SDO_SRID", SdoSrid);
else
OracleUdt.SetValue(con, pUdt, "SDO_SRID", DBNull.Value);
if (!SdoPoint.IsNull)
OracleUdt.SetValue(con, pUdt, "SDO_POINT", SdoPoint);
}
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
object sdoGType = OracleUdt.GetValue(con, pUdt, "SDO_GTYPE");
_sdoGTypeIsNull = sdoGType == null || sdoGType is DBNull;
if (!_sdoGTypeIsNull)
SdoGType = (int)sdoGType;
SdoOrdinates =
(SdoOrdinates)OracleUdt.GetValue(con, pUdt, "SDO_ORDINATES");
SdoElemInfo =
(SdoElemInfo)OracleUdt.GetValue(con, pUdt, "SDO_ELEM_INFO");
object sdoSrid = OracleUdt.GetValue(con, pUdt, "SDO_SRID");
if (!(sdoSrid == null || sdoSrid is DBNull))
SdoSrid = (int)sdoSrid;
SdoPoint = (SdoPoint)OracleUdt.GetValue(con, pUdt, "SDO_POINT");
}
#endregion
#region INullable Members
public bool IsNull
{
get { return _sdoGTypeIsNull; }
}
#endregion
#region IOracleCustomTypeFactory Members
public IOracleCustomType CreateObject()
{
return new SdoGeometry();
}
#endregion
}
PS在我的项目期间,Oracle发布了ODP.NET的3个版本。 有趣的是:2.111.6.10版本的Oracle.DataAcess.dll工作的代码根本无法用于2.111.6.20!
不知道现在哪个版本的ODP.NET是真实的,但我上面发布的样本与2.111.6.10一起使用得很好。
希望这可以帮助。 祝你好运!
在许多错误开始之后,这篇文章保存了我的培根(绑定到TABLE OF VARCHAR2(100)
的UDT)。
要点
varchar2(100)
IOracleCustomType
和INullable
接口。 string[]
),并且该属性必须使用OracleArrayMapping
属性进行标记。 IOracleArrayTypeFactory
和IOracleCustomTypeFactory
接口的UDT Factory类。 它需要以下方法 OracleCustomTypeMapping("SCHEMA.UDT_TYPE")
,其中SCHEMA.UDT_TYPE
与您的UDT类型相匹配,即CREATE TYPE SCHEMA.UDT_TYPE AS TABLE OF VARCHAR2(100)
相比之下,参数的绑定很简单:
var oracleArray = new MyArrayStorageClass
{
Array = new string[] {"Hello", "World"}
};
command.CommandType = CommandType.StoredProcedure;
var param = new OracleParameter("ip_parameterName", OracleDbType.Array)
{
// Case sensitive match to the `OracleCustomTypeMapping` on the factory
UdtTypeName = "SCHEMA.UDT_TYPE",
Value = oracleArray,
Direction = ParameterDirection.Input,
};
command.Parameters.Add(param);
ODP.net支持用户定义的类型。 http://www.oracle.com/technology/tech/windows/odpnet/index.html
谷歌为例或阅读手册。
链接地址: http://www.djcxy.com/p/20285.html