Java oracle stored procedures/functions (2): how to fetch a dynamic SQL statement to a cursor

In (1), we will explain how to input a String array and an int array. Here, we will explain how to output a String array. After reading the Code, some friends may wonder: why do we need to transfer the String array? Is it difficult to transmit a cursor directly?

Here we will briefly describe the original intention: The cursor can only be transmitted from the table, or, from the custom array, select as a cursor. The example here is to query the value of A column in A table, but in actual application, it may need to be converted. For example, A-is available and N-is unavailable, u-unknown... And so on. The number value is the same. Therefore, in my opinion, it is also necessary to use String as an array for output. Hopefully, the speculation here is just a few questions.

In addition, this document describes how to use a cursor to execute dynamic SQL statements that are organized in the stored procedure.

Now, I am used to reading a few remarks at the beginning. If you cannot find the java class, find it in the article before this series.

 

Run the following code:

 

2varchar2, number array outgoing. SQL

 

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

Insert into T_VarcharArray values (1, '');
Insert into T_VarcharArray values (2, 'Hello, hao ');
Insert into T_VarcharArray values (2, 'Hello ');
Insert into T_VarcharArray values (2, 'Hello, I wish you success! ');
Insert into T_VarcharArray values (3, '');
Insert into T_VarcharArray values (4, 'Hello friend ');
Insert into T_VarcharArray values (5, 'Hello friends ');
Insert into T_VarcharArray values (6, '');
Insert into T_VarcharArray values (7, '');
Insert into T_VarcharArray values (8, '');
Commit;

Drop type T_VARCHAR;

-- Create 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_STATIC (
I _name varchar2, -- input parameter. The name to be queried is equal
O_t_varchar OUT t_varchar, -- output parameter, which is a defined variable-length varchar2 array.
O_n_ret OUT number -- output parameter. If it ends normally, 0 is output; otherwise, an exception is thrown.
)
Is
V_cur sys_refcursor; -- defines a cursor variable.
V_name T_VarcharArray.name % TYPE; -- defines a variable of the same TYPE as the name field of the T_VarcharArray table
Begin
-- Initialize an empty string array object. o_t_varchar.COUNT = 0;
O_t_varchar: = t_varchar (); -- Note: = is a value in PL/SEL, and = indicates equal.
-- Open the cursor
Open v_cur for select name from T_VarcharArray where name = I _name;
Loop
-- Fetch the cursor with only one field name to the variable.
-- For all columns of the entire table, you can use % ROWTYPE to define v_rec, which may be used in the next series. Note that.
Fetch v_cur into v_name;
Exit when v_cur % NOTFOUND; -- this sentence is followed by fetch, indicating that if there is no value in the cursor, the loop is exited.
O_t_varchar.EXTEND; -- Array Extension length. In the first loop, COUNT = 1, followed by + 1;
O_t_varchar (o_t_varchar.COUNT): = v_name; -- assign v_name to the last element of the output array.
End loop;

-- Remember to close the opened cursor. Unless you return a cursor as the output parameter or function value of the stored procedure
Close v_cur;

O_n_ret: = 0;
Exception when others then
Raise;
End;
/

Create or replace procedure P_VARCHAR2_LST_DYMANTIC (
I _name varchar2, -- input parameter, the string contained in the name to be queried
O_t_varchar out t_varchar, -- output parameter, which is a defined variable-length varchar2 array.
O_n_ret out number -- output parameter. If it ends normally, 0 is output; otherwise, an exception is thrown.
)
Is
V_cur sys_refcursor; -- defines a cursor variable.
V_name t_varchararray.name % type; -- defines a variable of the same type as the name field of the t_varchararray table
V_ SQL varchar2 (1000); -- defines the SQL statement to be dynamically executed
Begin
-- Organize dynamic SQL statements
-- Note: In the Oracle String constant, two ''represents one'', that is, add a 'before' to indicate escape, which is the same as the escape required by/in Java.
-- This is required for a single '. Other special characters are not studied. In addition, | connects two constant strings.
V_ SQL: = 'select name from t_varchararray where name like '%' | I _name | '% ''';

-- Initialize an empty string array object. o_t_varchar.count = 0;
O_t_varchar: = t_varchar ();
-- Open the cursor
Open v_cur for v_ SQL;
Loop
-- Fetch the cursor with only one field name to the variable.
-- For all columns of the entire table, you can use % rowtype to define v_rec, which may be used in the next series. Note that.
Fetch v_cur into v_name;
Exit when v_cur % notfound; -- this sentence is followed by Fetch, indicating that if there is no value in the cursor, the loop is exited.
O_t_varchar.extend; -- Array Extension length. In the first loop, Count = 1, followed by + 1;
O_t_varchar (o_t_varchar.count): = v_name; -- assign v_name to the last element of the output array.
End loop;

-- Remember to close the opened cursor. Unless you return a cursor as the output parameter or function value of the stored procedure
Close v_cur;

O_n_ret: = 0;
Exception when others then
Raise;
End;
/

Create or replace procedure p_number_lst_static (
I _id number, -- input parameter. The name to be queried is equal
O_t_number out t_number, -- output parameter, which is an array of defined numbers with an indefinite length
O_n_ret out number -- output parameter. If it ends normally, 0 is output; otherwise, an exception is thrown.
)
Is
V_cur sys_refcursor; -- defines a cursor variable.
V_id t_varchararray.id % type; -- defines a variable of the same type as the ID field of the t_varchararray table.
Begin
-- Initialize an empty string array object. o_t_varchar.count = 0;
O_t_number: = t_number (); -- Note: = is a value in PL/Sel, and = indicates equal.
-- Open the cursor
Open v_cur for select ID from t_varchararray where id = I _id;
Loop
-- Fetch the cursor with only one field id to the variable.
-- For all columns of the entire table, you can use % ROWTYPE to define v_rec, which may be used in the next series. Note that.
Fetch v_cur into v_id;
Exit when v_cur % NOTFOUND; -- this sentence is followed by fetch, indicating that if there is no value in the cursor, the loop is exited.
O_t_number.EXTEND; -- Array Extension length. In the first loop, COUNT = 1, followed by + 1;
O_t_number (o_t_number.COUNT): = v_id; -- assign v_name to the last element of the output array.
End loop;

-- Remember to close the opened cursor. Unless you return a cursor as the output parameter or function value of the stored procedure
Close v_cur;

O_n_ret: = 0;
Exception when others then
Raise;
End;
/

Varchar2numberarrayout. Java

Package test. oracle. oj; <br/> import java. math. bigDecimal; <br/> import java. SQL. array; <br/> import java. SQL. callableStatement; <br/> import java. SQL. connection; <br/> import java. SQL. SQLException; <br/> import java. util. arrayList; <br/> import oracle. jdbc. driver. oracleTypes; <br/> import test. oracle. conn. OConnection; <br/> public class Varchar2NumberArrayOut {<br/> // Note: T_VARCHAR2 must be in uppercase. <br/> private stat Ic final String T_VARCHAR = "T_VARCHAR"; <br/> private static final String T_NUMBER = "T_NUMBER"; <br/> // Note: The call must be in lower case, the process name must be in uppercase. <br/> private static final String P_VARCHAR2_LST_STATIC = "{call P_VARCHAR2_LST_STATIC (?,?,?)} "; <Br/> private static final String P_VARCHAR2_LST_DYMANTIC =" {call P_VARCHAR2_LST_DYMANTIC (?,?,?)} "; <Br/> private static final String P_NUMBER_LST_STATIC =" {call P_NUMBER_LST_STATIC (?,?,?)} "; <Br/> public static String [] varchar2lstaticouttest (String name) {<br/> String [] retVal = null; <br/> Connection con = null; <br/> CallableStatement cstmt = null; <br/> try {<br/> con = OConnection. getConn (); <br/> cstmt = con. prepareCall (P_VARCHAR2_LST_STATIC); <br/> cstmt. setString (1, name); <br/> // It must be explicitly specified. The array of T_VARCHAR to be returned <br/> cstmt. registerOutParameter (2, OracleTypes. ARRAY, T_VARCHAR); <br/> cst Mt. registerOutParameter (3, OracleTypes. INTEGER); <br/> cstmt.exe cute (); <br/> // This specifies to obtain the output parameter in Array mode <br/> Array outparam = cstmt. getArray (2); <br/> int ret = cstmt. getInt (3); <br/> // to output String [], an ArrayList is tentatively defined here. <Br/> ArrayList <String> al = new ArrayList <String> (); <br/> if (ret = 0) {<br/> Object [] obj = (Object []) outparam. getArray (); <br/> for (int I = 0; I <obj. length; I ++) {<br/> String item = (String) obj [I]; <br/> al. add (item); <br/>}< br/> retVal = new String [obj. length]; <br/> retVal = al. toArray (retVal); <br/>}< br/>} catch (Exception ex) {<br/> ex. printStackTrace (); <br/>}finally {<br/> // it is best to use The cs, ps, rs, and con used in ally are disabled. <br/> // to ensure exceptions occur, 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 String [] varchar2LstDymanticOutTest (String name) {<br/> String [] retVal = null; <br/> Connection con = null; <br/> CallableStatement cstmt = null; <br/> try {<br/> con = OConnection. getConn (); <br/> cstmt = con. prepareCall (P_VARCHAR2_LST_DYMANTIC ); <Br/> cstmt. setString (1, name); <br/> // It must be explicitly specified. The array of T_VARCHAR to be returned <br/> cstmt. registerOutParameter (2, OracleTypes. ARRAY, T_VARCHAR); <br/> cstmt. registerOutParameter (3, OracleTypes. INTEGER); <br/> cstmt.exe cute (); <br/> // This specifies to obtain the output parameter in Array mode <br/> Array outparam = cstmt. getArray (2); <br/> int ret = cstmt. getInt (3); <br/> // to output String [], an ArrayList is tentatively defined here. <Br/> ArrayList <String> al = new ArrayList <String> (); <br/> if (ret = 0) {<br/> Object [] obj = (Object []) outparam. getArray (); <br/> for (int I = 0; I <obj. length; I ++) {<br/> String item = (String) obj [I]; <br/> al. add (item); <br/>}< br/> retVal = new String [obj. length]; <br/> retVal = al. toArray (retVal); <br/>}< br/>} catch (Exception ex) {<br/> ex. printStackTrace (); <br/>}finally {<br/> // it is best to use The cs, ps, rs, and con used in ally are disabled. <br/> // to ensure exceptions occur, 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 int [] NumberLstStaticOutTest (int id) {<br/> int [] retVal = null; <br/> Connection con = null; <br/> CallableStatement cstmt = null; <br/> try {<br/> con = OConnection. getConn (); <br/> cstmt = con. prepareCall (P_NUMBER_LST_STATIC); <br/> cstmt. setIn T (1, id); <br/> // It must be explicitly specified. The array of T_VARCHAR to be returned <br/> cstmt. registerOutParameter (2, OracleTypes. ARRAY, T_NUMBER); <br/> cstmt. registerOutParameter (3, OracleTypes. INTEGER); <br/> cstmt.exe cute (); <br/> // This specifies to obtain the output parameter in Array mode <br/> Array outparam = cstmt. getArray (2); <br/> int ret = cstmt. getInt (3); <br/> if (ret = 0) {<br/> Object [] obj = (Object []) outparam. getArray (); <br/> retVal = new int [obj. length]; <br/> (Int I = 0; I <obj. length; I ++) {<br/> // Note: to convert the int type with BigDecimal, an error occurs when using Integer. <br/> retVal [I] = (BigDecimal) obj [I]). intValue (); <br/>}< 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 void main (String [] args) throws Exception {<br/> String name = "hello"; <br/> String [] ret = Varchar2NumberArrayOut. varchar2LstStaticOutTest (name); <br/> System. out. println ("test string array as the parameter output stored procedure call static SQL, Result:"); <br/> for (int I = 0; I <ret. length; I ++) {<br/> System. out. println (ret [I]); <br/>}< br/> ret = Varchar2NumberArrayOut. varchar2LstDymanticOutTest (name); <br/> System. out. println ("test string array as the parameter output stored procedure call dynamic SQL, Result:"); <br/> for (int I = 0; I <ret. length; I ++) {<br/> System. out. println (ret [I]); <br/>}< br/> int [] ret1 = Varchar2NumberArrayOut. numberLstStaticOutTest (2); <br/> System. out. println ("test the static SQL statement called when an integer array is passed out as a parameter. Result:"); <br/> for (int I = 0; I <ret1.length; I ++) {<br/> System. out. println (ret1 [I]); <br/>}< br/>