例子: CREATE OR REPLACE TYPE propertyvalue AS OBJECT(varchar2(32)) CREATE OR REPLACE TYPE propertyvalueList AS VARRAY(50) OF propertyvalue; create table a( name varchar2(32), provalues propertyvalueList ) insert into a(name,provalues) values('name',propertyvalueList(propertyvalue('csm'),propertyvalue('hg')))create or replace function aaa(provalues propertyvalueList ) return varchar2 is Result varchar2(32); begin for i IN 1..provalues.COUNT loop dbms_output.put_line(provalues(i).v); end loop; return(Result); end aaa; declare i number; vvv propertyvalueList; a varchar2(32); begin select a.provalues into vvv from a; select aaa(vvv) into a from dual; end;
.Net没有用过,但是查到这么一篇文章: Array BindingThe array bind feature enables applications to bind arrays of a type using the OracleParameter class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.The following code example inserts three rows into the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property defines the number of elements of the array to use when executing the statement.// C# ... // Create an array of values that need to be inserted int[] myArrayDeptNo = new int[3]{10, 20, 30}; // Set the command text on an OracleCommand object cmd.CommandText = "insert into dept(deptno) values (:deptno)"; // Set the ArrayBindCount to indicate the number of values cmd.ArrayBindCount = 3; // Create a parameter for the array operations OracleParameter prm = new 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(); See Also:"Value" for more information OracleParameter Array Bind PropertiesThe OracleParameter object provides two properties for granular control when using the array bind feature: * ArrayBindSize Property The ArrayBindSize property is an array of integers specifying the maximum size for each corresponding value in an array. The ArrayBindSize property is similar to the Size property of an OracleParameter except ArrayBindSize specifies the size for each value in an array. Before the execution, the application must populate ArrayBindSize; after the execution, ODP.NET populates the ArrayBindSize. ArrayBindSize is used only for parameter types that have variable length such as Clob, Blob and Varchar2. 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 ArrayBindSize property is ignored for fixed length datatypes. * ArrayBindStatus Property The ArrayBindStatus property is an array of OracleParameterStatus values specifying status of each corresponding value in an array for a parameter. This property is similar to the Status property of OracleParameter, except that ArrayBindStatus specifies the status for each value in an array. Before the execution, the application must populate the ArrayBindStatus property and after the execution, ODP.NET populates it. Before the execution, an application using ArrayBindStatus can specify a NULL value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the ArrayBindStatus array, indicating whether the corresponding element in the array has a NULL value or if data truncation occurred when the value was fetched.
CREATE OR REPLACE TYPE propertyvalue AS OBJECT(varchar2(32))
CREATE OR REPLACE TYPE propertyvalueList AS VARRAY(50) OF propertyvalue;
create table a(
name varchar2(32),
provalues propertyvalueList
)
insert into a(name,provalues) values('name',propertyvalueList(propertyvalue('csm'),propertyvalue('hg')))create or replace function aaa(provalues propertyvalueList ) return varchar2 is
Result varchar2(32);
begin
for i IN 1..provalues.COUNT
loop
dbms_output.put_line(provalues(i).v);
end loop;
return(Result);
end aaa;
declare
i number;
vvv propertyvalueList;
a varchar2(32);
begin
select a.provalues into vvv from a;
select aaa(vvv) into a from dual;
end;
Array BindingThe array bind feature enables applications to bind arrays of a type using the OracleParameter class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.The following code example inserts three rows into the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property defines the number of elements of the array to use when executing the statement.// C#
...
// Create an array of values that need to be inserted
int[] myArrayDeptNo = new int[3]{10, 20, 30}; // Set the command text on an OracleCommand object
cmd.CommandText = "insert into dept(deptno) values (:deptno)";
// Set the ArrayBindCount to indicate the number of values
cmd.ArrayBindCount = 3; // Create a parameter for the array operations
OracleParameter prm = new 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(); See Also:"Value" for more information
OracleParameter Array Bind PropertiesThe OracleParameter object provides two properties for granular control when using the array bind feature: * ArrayBindSize Property The ArrayBindSize property is an array of integers specifying the maximum size for each corresponding value in an array. The ArrayBindSize property is similar to the Size property of an OracleParameter except ArrayBindSize specifies the size for each value in an array. Before the execution, the application must populate ArrayBindSize; after the execution, ODP.NET populates the ArrayBindSize. ArrayBindSize is used only for parameter types that have variable length such as Clob, Blob and Varchar2. 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 ArrayBindSize property is ignored for fixed length datatypes.
* ArrayBindStatus Property The ArrayBindStatus property is an array of OracleParameterStatus values specifying status of each corresponding value in an array for a parameter. This property is similar to the Status property of OracleParameter, except that ArrayBindStatus specifies the status for each value in an array. Before the execution, the application must populate the ArrayBindStatus property and after the execution, ODP.NET populates it. Before the execution, an application using ArrayBindStatus can specify a NULL value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the ArrayBindStatus array, indicating whether the corresponding element in the array has a NULL value or if data truncation occurred when the value was fetched.