Java oracle stored procedures/functions (1): input of string arrays and int arrays to stored procedures

Recently, many people in CSDN have asked questions about the interaction between java and oracle stored procedure/function. I learned it step by step. I have encountered similar problems before and often get confused. But now some things seem to have a taste of getting started. I think we should write something to sum up to make it easier for the public and to find it for your own convenience. Prepare to write a series.

Well, because it is original, there may be some errors or deficiencies in the code, and we hope you can understand and refer to them. However, I am responsible for saying that the following code is successfully tested and can be used directly ^ C ^ V. Well, let's talk a little bit about it. Let's go to the first article.

 

Oracle connection class

This will be used in all subsequent java code. I didn't want to write it, but I was too lazy to use getConnection in every class, so I got this. In addition, the package where the class is located will not be changed. Please modify the package accordingly with your friends. This will be the case for subsequent articles, so it will not be too long here.

 

 

Package test. oracle. conn; <br/> import java. SQL. connection; <br/> import java. SQL. driverManager; <br/> public class OConnection {<br/> public static Connection getConn () {<br/> String URL = "jdbc: oracle: thin: @ 127.0.0.1: 1521: ORCL "; <br/> String user =" cartoon "; // replace it with your own database username <br/> String password =" oracle "; // replace it with your own Database User Password <br/> Connection connection = null; <br/> try {<br/> Class. forName ("oracle. Jdbc. driver. OracleDriver "); <br/> System. out. println (" class instantiation successful! "); <Br/> connection = DriverManager. getConnection (URL, user, password); <br/> System. out. println (" connection image created successfully! "); </P> <p >}catch (Exception err) {<br/> err. printStackTrace (); <br/> return null; <br/>}< br/> return connection; <br/>}< br/>

 

1varchar2, number array. SQL

-- To ensure that all written data can run, drop the table and type each time you create a table. A friend who runs for the first time may find that the drop statement reports that xxx does not exist and can be ignored. Quotation marks

 

Drop table T_VarcharArray;
Create table T_VarcharArray (
Id number (10 ),
Name varchar2 (100)
);

Drop type T_VARCHAR;

-- Create a definition of an indefinite array with the same name as T_VarcharArray
Create or replace type T_VARCHAR as table of varchar2 (100 );
/

Drop type T_NUMBER;

-- Create an indefinite array with the same id as T_VarcharArray
Create or replace type T_NUMBER as table of number (10 );
/

Create or replace procedure P_VARCHAR2_LST (
I _t_varchar IN t_varchar, -- the input parameter is a variable-length varchar2 array.
O_n_ret OUT number -- output parameter. If it ends normally, 0 is output; otherwise, an exception is thrown.
)
Is
Begin
FOR I in 1 .. I _t_varchar.COUNT loop -- note that the subscript starts from 1.
Insert into T_VarcharArray values (I, I _t_varchar (I ));
End loop;
O_n_ret: = 0;
Exception when others then
Raise;
End;
/

Create or replace function F_NUMBER_LST (
I _t_number in t_number -- the input parameter is an array of defined indefinite numbers.
)
Return number -- function output result
Is
Begin
For I in 1 .. I _t_number.count loop -- note that the subscript starts from 1.
Insert into t_varchararray values (I _t_number (I), to_char (I ));
End loop;
Return 0;
Exception when others then
Raise;
End;
/

Varchar2numberarray. Java

 

Package test. oracle. oj; <br/> import java. SQL. *; <br/> import oracle. jdbc. driver. oracleTypes; <br/> import oracle. SQL. ARRAY; <br/> import oracle. SQL. arrayDescriptor; <br/> import test. oracle. conn. OConnection; <br/> public class Varchar2NumberArray {<br/> // Note: T_VARCHAR2 must be capitalized. <br/> private static final String T_VARCHAR = "T_VARCHAR "; <br/> private static final String T_NUMBER = "T_NUMBER"; <br/> // Note: call The process name must be in lowercase. <br/> private static final String P_VARCHAR2_LST = "{call P_VARCHAR2_LST (?,?)} "; <Br/> // Note: The call must be in lowercase and the process name must be in uppercase. The returned value is the first in java. <br/> private static final String F_NUMBER_LST = "{? = Call F_NUMBER_LST (?)} "; <Br/> public static int varchar2LstTest (String [] lst) {<br/> int retVal =-1; <br/> Connection con = null; <br/> CallableStatement cstmt = null; <br/> try {<br/> con = OConnection. getConn (); <br/> // create an array descriptor <br/> ArrayDescriptor varchar2Desc = ArrayDescriptor. createDescriptor (<br/> T_VARCHAR, con); <br/> // converts a string ARRAY to an ARRAY that oralce can recognize <br/> ARRAY vArray = new ARRAY (varchar2Desc, con, lst); <br/> cstmt = con. PrepareCall (P_VARCHAR2_LST); <br/> cstmt. setArray (1, vArray); <br/> cstmt. registerOutParameter (2, OracleTypes. INTEGER); <br/> cstmt.exe cute (); <br/> retVal = cstmt. getInt (2); <br/>}catch (Exception ex) {<br/> ex. printStackTrace (); <br/>}finally {<br/> // it is best to disable the cs, ps, rs, and con used in finally, <br/> // to ensure that all released items are released when an exception occurs <br/> try {<br/> if (cstmt! = Null) {<br/> cstmt. close (); <br/>}< br/> if (con! = Null) {<br/> con. close (); <br/>}< br/>} catch (SQLException sqle) {<br/> sqle. printStackTrace (); <br/>}< br/> return retVal; <br/>}< br/> public static int numberLstTest (int [] lst) {<br/> int retVal =-1; <br/> Connection con = null; <br/> CallableStatement cstmt = null; <br/> try {<br/> con = OConnection. getConn (); <br/> // create an array descriptor <br/> ArrayDescriptor varchar2Desc = ArrayDescriptor. createDes Criptor (<br/> T_NUMBER, con); <br/> // converts a string ARRAY to an ARRAY that oralce can recognize <br/> ARRAY vArray = new ARRAY (varchar2Desc, con, lst); <br/> cstmt = con. prepareCall (F_NUMBER_LST); <br/> // the return value is the first in java, so the output parameter is registered first. <br/> cstmt. registerOutParameter (1, OracleTypes. INTEGER); <br/> cstmt. setArray (2, vArray); <br/> cstmt.exe cute (); <br/> retVal = cstmt. getInt (1); <br/>}catch (Exception ex) {<br/> ex. printStackTrace (); <br/>} fin Ally {<br/> // it is best to disable cs, ps, rs, and con used in finally to ensure exceptions. <br/>, all released instances are released. <br/> try {<br/> if (cstmt! = Null) {<br/> cstmt. close (); <br/>}< br/> if (con! = Null) {<br/> con. close (); <br/>}< br/>} catch (SQLException sqle) {<br/> sqle. printStackTrace (); <br/>}< br/> return retVal; <br/>}< br/> public static void main (String [] args) throws Exception {<br/> String [] lst = {"test1", "test2", "test2" };< br/> int ret = Varchar2NumberArray. varchar2LstTest (lst); <br/> System. out. println ("test string array passed into the stored procedure as a parameter, Result:" + ret); <br/> int [] lst1 = {1, 2, 3 }; <br/> ret = Varchar2NumberArray. numberLstTest (lst1); <br/> System. out. println ("test the storage process of an integer array as a parameter. Result:" + ret); <br/>}< br/>

 

 

This is the end of the first article. Here we will talk about how to pass the string array and INT array into Oracle and use it. The next section describes how to transfer the string array and INT array.

 

If you like it, pay attention to it.