调用oralce的procedure时如何传入table类型数据[网上资料]

       前端时间有个项目,需要调用oracle存储过程,他的传入参数中有自定义的table类型的数据。

解决方法如下:

 1、需要安装oracle对应的ODP 

 

PL/SQL   Package   :   My   Pack 

CREATE   PACKAGE   MYPACK   AS 
TYPE   AssocArrayVarchar2_t   is   table   of   VARCHAR(20)   index   by   BINARY_INTEGER; 
PROCEDURE   TestVarchar2( 
        Param1   IN           AssocArrayVarchar2_t, 
        Param2   IN   OUT   AssocArrayVarchar2_t, 
        Param3   OUT         AssocArrayVarchar2_t); 
END   MYPACK;         

PL/SQL   Package   Body   :   My   Pack 

CREATE   PACKAGE   BODY   MYPACK   AS 
PROCEDURE   TestVarchar2( 
        Param1   IN           AssocArrayVarchar2_t, 
        Param2   IN   OUT   AssocArrayVarchar2_t, 
        Param3   OUT         AssocArrayVarchar2_t) 
        IS 
        i   integer; 
        BEGIN 
            --   copy   a   few   elements   from   y   to   z 
            Param3(1)   :=   Param2(1); 
            Param3(2)   :=   NULL; 
            Param3(3)   :=   Param2(3); 

--   copy   all   elements   from   x   to   y 
            Param2(1)   :=   Param1(1); 
            Param2(2)   :=   Param1(2); 
            Param2(3)   :=   Param1(3); 
            
            FOR   i   IN   1..3   LOOP 
                insert   into   T1   values(i,   Param2(i)); 
            END   LOOP; 
                      
            FOR   i   IN   1..3   LOOP 
                select   COL2   into   Param2(i)   from   T2   where   COL1   =   i; 
            END   LOOP; 
        END   TestVarchar2; 
END   MYPACK; 

ODP.NET   Example 

public   void   BindAssocArray(){ 
...   
  
            OracleCommand   cmd   =   new   OracleCommand( 
                "begin   MyPack.TestVarchar2(:1,   :2,   :3);   end; ",   con); 
  
            OracleParameter   Param1   =   cmd.Parameters.Add(...); 
            OracleParameter   Param2   =   cmd.Parameters.Add(...); 
            OracleParameter   Param3   =   cmd.Parameters.Add(...); 
  
            Param1.Direction   =   ParameterDirection.Input; 
            Param2.Direction   =   ParameterDirection.InputOutput; 
            Param3.Direction   =   ParameterDirection.Output; 
  
            //   Specify   that   we   are   binding   PL/SQL   Associative   Array 
            Param1.CollectionType   =   OracleCollectionType.PLSQLAssociativeArray; 
            Param2.CollectionType   =   OracleCollectionType.PLSQLAssociativeArray; 
            Param3.CollectionType   =   OracleCollectionType.PLSQLAssociativeArray; 
  
            //   Setup   the   values   for   PL/SQL   Associative   Array 
            Param1.Value   =   new   string[3]{ "First   Element ", 
                                                                      "Second   Element   ", 
                                                                      "Third   Element   ", 
            Param2.Value   =   new   string[3]{ "First   Element ", 
                                                                      "Second   Element   ", 
                                                                      "Third   Element   ", 
            Param3.Value   =   null; 
  
            //   Specify   the   maximum   number   of   elements   in   the   PL/SQL   Associative   Array 
            Param1.Size   =   3; 
            Param2.Size   =   3; 
            Param3.Size   =   3; 
  
            //   Setup   the   ArrayBindSize   for   Param1 
            Param1.ArrayBindSize   =   new   int[3]{13,   14,   13};     
  
            //   Setup   the   ArrayBindStatus   for   Param1   
            Param1.ArrayBindStatus   =   new   OracleParameterStatus[3]{ 
                OracleParameterStatus.Success, 
                OracleParameterStatus.Success, 
                OracleParameterStatus.Success}; 
  
            //   Setup   the   ArrayBindSize   for   Param2 
            Param2.ArrayBindSize   =   new   int[3]{20,   20,   20}; 
  
            //   Setup   the   ArrayBindSize   for   Param3 
            Param3.ArrayBindSize   =   new   int[3]{20,   20,   20}; 
  
            //   execute   the   cmd 
            cmd.ExecuteNonQuery();   
  
            //print   out   the   parameter 's   values 
...   
        } 

Array   Binding 
The   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   Properties 
The   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. 

Error   Handling   for   Array   Binding 
If   an   error   occurs   during   an   Array   Bind   execution,   it   can   be   difficult   to   determine   which   element   in   the   Value   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   OracleException   is   thrown   during   an   Array   Bind   execution,   the   OracleErrorCollection   contains   one   or   more   OracleError   objects.   Each   of   these   OracleError   objects   represents   an   individual   error   that   occurred   during   the   execution   and   contains   a   provider-specific   property,   ArrayBindIndex,   which   indicates   the   row   number   at   which   the   error   occurred. 

Code   Snippet 

try   {   
    /   An   Array   Bind   execution   errors   out} 
  
catch   (OracleException   e){ 
    Console.WriteLine   ( "OracleException   {0}   occured ",   e.Message); 
    for   (int   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); 

 

 

posted @ 2011-03-09 09:30  mythzz  阅读(510)  评论(0编辑  收藏  举报