.NET調用Oracle存儲過程，使用數組類型的參數(如ArrayList)

今天一個項目組的朋友問及：如何在.NET中調用Oracle的存儲過程，并以數組作為參數輸入。

Oracle的PL/SQL非常強大，支援定長數組和變長數組，支援任何自定義資料類型。通過閱讀ODP的文檔，發現Oracle是完全支援将數組作為存儲過程參數的。下面給出文檔資訊。

<a></a>

The array bind feature enables applications to bind arrays of a type using the <code>OracleParameter</code> class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.

See Also:

The <code>OracleParameter</code> class provides two properties for granular control when using the array bind feature:

The <code>ArrayBindSize</code> property is an array of integers specifying the maximum size for each corresponding value in an array. The <code>ArrayBindSize</code> property is similar to the <code>Size</code> property of an <code>OracleParameter</code> object, except the <code>ArrayBindSize</code> property specifies the size for each value in an array.

Before the execution, the application must populate the <code>ArrayBindSize</code> property; after the execution, ODP.NET populates it.

The <code>ArrayBindSize</code> property is used only for parameter types that have variable length such as <code>Clob</code>, <code>Blob</code>, and <code>Varchar2</code>. The size is represented in bytes for binary datatypes, and characters for the Unicode string types. The count for string types does not include the terminating character. The size is inferred from the actual size of the value, if it is not explicitly set. For an output parameter, the size of each value is set by ODP.NET. The <code>ArrayBindSize</code> property is ignored for fixed-length datatypes.

The <code>ArrayBindStatus</code> property is an array of <code>OracleParameterStatus</code> values that specify the status of each corresponding value in an array for a parameter. This property is similar to the <code>Status</code> property of the <code>OracleParameter</code> object, except that the <code>ArrayBindStatus</code> property specifies the status for each array value.

Before the execution, the application must populate the <code>ArrayBindStatus</code> property. After the execution, ODP.NET populates the property. Before the execution, an application using the <code>ArrayBindStatus</code> property can specify a <code>NULL</code> value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the <code>ArrayBindStatus</code> property, indicating whether the corresponding element in the array has a <code>null</code> value, or if data truncation occurred when the value was fetched.

If an error occurs during an array bind execution, it can be difficult to determine which element in the <code>Value</code> property caused the error. ODP.NET provides a way to determine the row where the error occurred, making it easier to find the element in the row that caused the error.

When an <code>OracleException</code> object is thrown during an array bind execution, the <code>OracleErrorCollection</code> object contains one or more <code>OracleError</code> objects. Each of these <code>OracleError</code> objects represents an individual error that occurred during the execution, and contains a provider-specific property, <code>ArrayBindIndex</code>, which indicates the row number at which the error occurred.

The following example demonstrates error handling for array binding:

OracleParameterStatus Members

Member Names 

Description

<code>Success</code>

For input parameters, indicates that the input value has been assigned to the column.

For output parameters, indicates that the provider assigned an intact value to the parameter.

<code>NullFetched</code>

Indicates that a <code>NULL</code> value has been fetched from a column or an <code>OUT</code> parameter.

<code>NullInsert</code>

Indicates that a <code>NULL</code> value is to be inserted into a column.

<code>Truncation</code>

Indicates that truncation has occurred when fetching the data from the column.

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 if the parameter values change in subsequent executions. However, if the literal values in the statements are different, the parsed information cannot be reused unless the subsequent statements also have the same literal values.

The following connection string attributes control the behavior of the ODP.NET statement caching feature:

<code>Statement</code> <code>Cache</code> <code>Size</code>

This attribute enables or disables ODP.NET statement caching. By default, this attribute is set to <code>0</code> (disabled). If it is set to a value greater than <code>0</code>, ODP.NET statement caching is enabled and the value specifies the maximum number of statements that can be cached for a connection. Once a connection has cached up to the specified maximum cache size, the cursor least recently used is freed to make room to cache the newly created cursor.

<code>Statement</code> <code>Cache</code> <code>Purge</code>

This attribute provides a way for connections to purge all statements that are cached when a connection is closed or placed back into the connection pool. By default, this attribute is set to <code>false</code>, which means that cursors are not freed when connections are placed back into the pool.

To enable statement caching by default for all ODP.NET applications running in a system, without changing the application, set the registry key of <code>HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME</code><code>ID</code><code>\ODP.NET\StatementCacheSize</code> to a value greater than <code>0</code>. (<code>ID</code> is the appropriate Oracle Home ID.) This value specifies the number of cursors that are to be cached on the server. By default, it is set to <code>0</code>.

The following property and method are relevant only when statement caching is enabled:

<code>OracleCommand.AddToStatementCache</code> property

If statement caching is enabled, having this property set to <code>true</code> (default) adds statements to the cache when they are executed. If statement caching is disabled or if this property is set to <code>false</code>, the executed statement is not cached.

<code>OracleConnection.PurgeStatementCache</code> 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 remains enabled after this call.

Statement caching is managed separately for each connection. Therefore, executing the same statement on different connections requires parsing once for each connection and caching a separate cursor for each connection.

Pooling and statement caching can be used in conjunction. If connection pooling is enabled and the <code>Statement</code> <code>Cache</code> <code>Purge</code> attribute is set to <code>false</code>, statements executed on each separate connection are cached throughout the lifetime of the pooled connection.If the <code>Statement</code> <code>Cache</code> <code>Purge</code> attribute is set to <code>true</code>, all the cached cursors are freed when the connection is placed back into the pool. When connection pooling is disabled, cursors are cached during the lifetime of the connection, but the cursors are closed when the <code>OracleConnection</code> object is closed or disposed of. 

本文轉自斯克迪亞部落格園部落格，原文連結：http://www.cnblogs.com/sgsoft/archive/2007/04/13/712261.html，如需轉載請自行聯系原作者