Array as parameter incoming Oracle stored procedure operations database

Note: The source of this article: " array as parameter passed into Oracle stored procedure operations database "

stored in the array is String , int , Lang such as basic data types or reference data types (not including java Bean )One, define an array type in Oracle (type)

The code is as follows:

  1  2  Create or replace type Msg_array as table of number;   3  4

It means to create an array type named Msg_array that holds the type integer.

Ii. defining a stored procedure in Oracle

1 2 3Create or Replace procedure Modifyage (M_array in Msg_array)4 5As6 7Begin8 9For I in1. M_array.count LoopTen  OneUpdate users set Age=age+1where Id=m_array (i); A  -End Loop; -  theCommit -  -exception -  +When others then -  +Roll back; A  atEnd Modifyage; - 

Create a stored procedure, the passed in parameter is the Msg_array type defined above, the action content is the loop passed in the array, the age field of the table is added 1 operations.

Third, the Java code is as follows

1Connection conn =NULL;2 3CallableStatement cstmt =NULL;4 5 Try{6 7Class.forName ("Oracle.jdbc.OracleDriver");8 9conn = Drivermanager.getconnection (Ten  One"Jdbc:oracle:thin: @localhost: 1521:orcl", "Orcl_dev", A  -"Dev"); -  theList List =NewArrayList (); -  -List.add (30); -  +List.add (31); -  +List.add (32); A  atList.add (33); -  -Arraydescriptor Tabdesc = Arraydescriptor.createdescriptor ( -  -"Msg_array", conn); -  inARRAY Varray =NewARRAY (TABDESC, Conn, List.toarray ()); -  tocstmt = Conn.preparecall ("Call modifyage (?)"); +  -Cstmt.setarray (1, Varray); the  *Cstmt.execute (); $ Panax NotoginsengCstmt.close (); -  theConn.close (); +  A}Catch(Exception e) { the  +E.printstacktrace (); -  $} $ 

First define the connection, CallableStatement2 variables, and then get connection through JDBC, and then define the array to pass to the stored procedure.

Mapping Oracle-side arrays as TABLE of types

Through Arraydescriptor. CreateDescriptor ("Msg_array", conn) defines how the array is described.

By using the new array (TABDESC, Conn, List.toarray ()), the array to be passed in is generated as a new Oracle-defined array in the form of a batched array description.

Call the stored procedure, pass in the parameters, and execute.

stored in the array is java BeanI. Defining an object type in Oracle

  1 createorreplacetype userobj asobject (  2  3 ID number,  4   5 username nvarchar2 (  6  7 ), age number,  8  9 Password nvarchar2 ( ten   ) 

To create an object type named Userobj, the type of the string must be written as NVARCHAR2, otherwise the string passed to the database is null

Second, define an array type in Oracle (type)

The code is as follows:

  1 Create or replace type Obj_array astable of userobj;

It means creating an array type named Obj_array that holds type userobj.

Iii. defining a stored procedure in Oracle

The code is as follows:

1Create or replace procedure Saveobjarray2 3(4 5Avc_objarray in Obj_array,6 7Rowcountout number,8 9Msg OUTVARCHAR2Ten  One) as A  -UserInfo userobj; -  theBegin -  -For I in Avc_objarray.first (): Avc_objarray.last () loop -  +UserInfo: = Avc_objarray (i); -  +Insertinto Users (Id,name,password,age) A  atValues (userinfo.id,userinfo.username,userinfo.password,userinfo.age); -  -End Loop; -  -Rowcount:=sql%rowcount; -  inCommit -  toexception +  -When others then the  *rowcount:=0; $ Panax Notoginsengmsg: =SQLERRM; -  theRollback +  AEnd Saveobjarray; the 

Create a stored procedure that is passed in as an array of obj_array types defined above, looping through an array, and inserting the database separately. Returns the number of rows affected and the type of information that is mentioned.

Four, Java code is as follows

1Connection conn =NULL;2 3CallableStatement cstmt =NULL;4 5 Try{6 7Class.forName ("Oracle.jdbc.OracleDriver");8 9conn = Drivermanager.getconnection (Ten  One"Jdbc:oracle:thin: @localhost: 1521:orcl", "Orcl_dev", A  -"Dev"); -  theList<user> userlist =NewArraylist<user> (); -  -User User =NewUser (); -  +User.setid (37); -  +User.setusername ("dddddd"); A  atUser.setpassword ("dddddd"); -  -User.setage (14); -  -Userlist.add (user); -  inStructdescriptor Recdesc = -  toStructdescriptor.createdescriptor ("Userobj", conn); +  -Arraylist<struct> pstruct =NewArraylist<struct> (); the  *  for(User u:userlist) { $ Panax Notoginsengobject[] Objs =NewOBJECT[4]; -  theObjs[0] = U.getid (); +  AOBJS[1] = U.getusername (); the  +OBJS[2] = U.getage (); -  $OBJS[3] = U.getpassword (); $  -struct struct =NewSTRUCT (RECDESC, Conn, OBJS); -  thePstruct.add (struct); - Wuyi} the  -Arraydescriptor Tabdesc = Wu  -Arraydescriptor.createdescriptor ("Obj_array", conn); About  $ARRAY Varray =NewARRAY (TABDESC, Conn, Pstruct.toarray ()); -  -cstmt = Conn.preparecall ("Call Saveobjarray (?,?,?)"); -  ACstmt.setarray (1, Varray); +  theCstmt.registeroutparameter (2, Types.integer); -  $Cstmt.registeroutparameter (3, Types.varchar); the  theCstmt.execute (); the  theSystem.out.println (cstmt.getstring (2)); -  inSystem.out.println (Cstmt.getstring (3)); the  theCstmt.close (); About  theConn.close (); the  the}Catch(Exception e) { +  -E.printstacktrace (); the Bayi} the 

First define the connection, CallableStatement2 variables, and then get connection through JDBC, and then define the array to pass to the stored procedure.

Mapping Oracle-side objects as object types

The object description is first defined by Structdescriptor.createdescriptor ("Userobj", conn).

Then, assign the attributes of the Java bean to an array of type object, and use the new STRUCT (RECDESC, Conn, Objs) method to describe the Java bean as an object that Oracle can recognize by its object description. Finally put in the array.

Mapping Oracle-side arrays as TABLE of types

Through Arraydescriptor. CreateDescriptor ("Obj_array", conn) defines how the array is described.

By using the new array (TABDESC, Conn, Pstruct.toarray ()), the array to be passed in is generated as a new Oracle-defined array in the form of a batched array description.

Call the stored procedure, pass in the parameters, and execute.

Array as parameter incoming Oracle stored procedure operations database