Array as parameter incoming Oracle stored procedure operations database

Source: Internet
Author: User
Tags rowcount

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.