调用oracle存储过程:.NET调用Oracle存储过程 使用数组类型的参数来源: 发布时间:星期一, 2009年2月23日 浏览:0次 评论:0
="t18">今天
 个项目组 朋友问及:如何在.NET中 Oracle存储过程 并以 作为参数输入 Oracle PL/SQL非常强大支持定长和变长支持任何自定义数据类型通过阅读ODP文档发现Oracle是完全支持将作为存储过程参数下面给出文档信息Array Binding The  . Using the  o a table in a single database round-trip. The following example inserts three rows o the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property  s the number of elements of the // C# using  ;using .Data;using Oracle.DataAccess.Client; ArrayBindSample{  void Main { OracleConnection con =  OracleConnection;con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; con.Open ;Console.WriteLine("Connected successfully");  myArrayDeptNo = [3] { 10, 20, 30 };OracleCommand cmd = OracleCommand;// Set the command text _disibledevent=> cmd.Connection = con; // Set the ArrayBindCount to indicate the number of values cmd.ArrayBindCount = 3; // Create a parameter for the OracleParameter prm = OracleParameter("deptno", OracleDbType.Int32);prm.Direction = ParameterDirection.Input; prm.Value = myArrayDeptNo; // Add the parameter to the parameter collection cmd.Parameters.Add(prm); // Execute the command cmd.ExecuteNonQuery ;Console.WriteLine("Insert Completed Successfully"); // Close and Dispose OracleConnection object con.Close ;con.Dispose ;} } See Also: "Value" for more information OracleParameter Array Bind Properties The OracleParameter provides two properties for granular control when using the ArrayBindSize The ArrayBindSize property is an egers spec ying the maximum size for each corresponding value in an ies the size for each value in an Before the execution, the application must populate the ArrayBindSize property; after the execution, ODP.NET populates it. The ArrayBindSize property is used _disibledevent=>scott/tiger@oracle drop table depttest; create table depttest(deptno number(2)); */ // C# using ;using .Data;using Oracle.DataAccess.Client; ArrayBindExceptionSample{ void Main{ OracleConnection con = OracleConnection;con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; con.Open ;OracleCommand cmd = OracleCommand;// Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); try { myArrayDeptNo = [3] { 10, 200000, 30 };// myArrayDeptNo = [3]{ 10,20,30};// Set the command text _disibledevent=> cmd.Connection = con; // Set the ArrayBindCount to indicate the number of values cmd.ArrayBindCount = 3; // Create a parameter for the OracleParameter prm = OracleParameter("deptno", OracleDbType.Int32);prm.Direction = ParameterDirection.Input; prm.Value = myArrayDeptNo; // Add the parameter to the parameter collection cmd.Parameters.Add(prm); // Execute the command cmd.ExecuteNonQuery ;} catch (OracleException e) { Console.WriteLine("OracleException {0} occured", e.Message); (e.Number  24381)for ( i = 0; i < e.Errors.Count; i )Console.WriteLine("Array Bind Error {0} occured at Row Number {1}", e.Errors[i].Message, e.Errors[i].ArrayBindIndex); txn.Commit ;} cmd.Parameters.Clear ;cmd.CommandText = "select count(*) from depttest"; decimal rows = (decimal)cmd.ExecuteScalar ;Console.WriteLine("{0} row have been inserted", rows); con.Close ;con.Dispose ;} } See Also: "ArrayBindIndex" for more information OracleParameterStatus Enumeration Types Table: OracleParameterStatus Members lists OracleParameterStatus enumeration values. OracleParameterStatus Members Member Names Description Success For input parameters, indicates that the input value has been assigned to the column. For output parameters, indicates that the provider assigned an act value to the parameter. NullFetched Indicates that a NULL value has been fetched from a column or an OUT parameter. NullInsert Indicates that a NULL value is to be inserted o a column. Truncation Indicates that truncation has occurred when fetching the data from the column. Statement Caching Statement caching eliminates the need to parse each SQL or PL/SQL statement before execution by caching server cursors created during the initial statement execution. Subsequent executions of the same statement can reuse the parsed information from the cursor, and then execute the statement without reparsing, for better performance. In order to see performance gains from statement caching, Oracle recommends caching only those statements that will be repeatedly executed. Furthermore, SQL or PL/SQL statements should use parameters rather than literal values. Doing so takes full advantage of statement caching, because parsed information from parameterized statements can be reused even the parameter values change in subsequent executions. However, the literal values in the statements are dferent, the parsed information cannot be reused unless the subsequent statements also have the same literal values. Statement Caching Connection String Attributes The following connection  attributes control the behavior of the ODP.NET statement caching feature: Statement Cache Size This attribute enables or disables ODP.NET statement caching. By default, this attribute is  to 0 (disabled). If it is to a value greater than 0, ODP.NET statement caching is enabled and the value species the maximum number of statements that can be cached for a connection. Once a connection has cached up to the specied maximum cache size, the cursor least recently used is freed to make room to cache the ly created cursor. Statement Cache Purge This attribute provides a way for connections to purge all statements that are cached when a connection is closed or placed back o the connection pool. By default, this attribute is to false, which means that cursors are not freed when connections are placed back o the pool. Enabling Statement Caching through the Registry To enable statement caching by default for all ODP.NET applications running in a system, without changing the application, the registry key of HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\ODP.NET\StatementCacheSize to a value greater than 0. (ID is the appropriate Oracle Home ID.) This value species the number of cursors that are to be cached on the server. By default, it is to 0. Statement Caching Methods and Properties The following property and method are relevant only when statement caching is enabled: OracleCommand.AddToStatementCache property If statement caching is enabled, having this property to true (default) adds statements to the cache when they are executed. If statement caching is disabled or this property is to false, the executed statement is not cached. OracleConnection.PurgeStatementCache method This method purges all the cached statements by closing all open cursors on the database that are associated with the particular connection. Note that statement caching re  s enabled after this call. Connections and Statement Caching Statement caching is managed separately for each connection. Therefore, executing the same statement on d ferent connections requires parsing once for each connection and caching a separate cursor for each connection. Pooling and Statement Caching Pooling and statement caching can be used in conjunction. If connection pooling is enabled and the Statement Cache Purge attribute is to false, statements executed on each separate connection are cached throughout the letime of the pooled connection.If the Statement Cache Purge attribute is to true, all the cached cursors are freed when the connection is placed back o the pool. When connection pooling is disabled, cursors are cached during the letime of the connection, but the cursors are closed when the OracleConnection object is closed or disposed of. 0
相关文章读者评论发表评论 |
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
