Use of Oracle stored procedures

This article is mainly to summarize how to implement JDBC call Oracle stored procedures, from the following:
[1], only input in parameter, no output out parameter
[2], both input in parameters and output out parameters, output is a simple value (not list)
[3], both input in parameter and output out parameter, output is List
[4], input and output parameters are the same (in out)
[5], using truncate in stored procedures to truncate data in a table
"Ready to Work"
Create a test table Tmp_michael and insert the data in SQL as follows:

CREATE TABLE Tmp_michael  
(  
  user_id    VARCHAR2),  
  user_name  VARCHAR2 (     a), SALARY Number (8,2),  
  other_info VARCHAR2 ()  
)  
  
insert INTO Tmp_michael (user_id, user_name, SALARY, Other_info)  
values (' Michael ', ' Michael ', 5000, ' http://sjsky.iteye.com ');  
Insert into Tmp_michael (user_id, user_name, SALARY, other_info)  
values (' Zhangsan ', ' John ', 10000, null);  
Insert into Tmp_michael (user_id, user_name, SALARY, other_info)  
values (' Aoi_sola ', ' Cang jing empty ', 99999.99, ' Twitter accou NT ');  
Insert into Tmp_michael (user_id, user_name, SALARY, other_info)  
values (' Dick ', ' Dick ', 2500, NULL); 
 

Oracle JDBC Constants:

Private final static String Db_driver = "Oracle.jdbc.driver.OracleDriver";  
Private final static String db_connection = "jdbc:oracle:thin:@127.0.0.1:1521:ora11g";  
Private final static String db_name = "MyTest";  
Private final static String db_pwd = "111111"; 

[one], only input in parameter, no output out parameter

CREATE OR REPLACE PROCEDURE test_michael_noout (P_userid    in VARCHAR2,  
                                               p_username  in VARCHAR2,  
                                               p_ SALARY    in number,  
                                               P_otherinfo in VARCHAR2) are  
BEGIN  
  
  INSERT into Tmp_michael  
    (user_id, User_ NAME, SALARY, other_info)  
  VALUES  
    (P_userid, P_username, P_salary, p_otherinfo);  
  
End Test_michael_noout;  

The calling code is as follows:

/** * Test Call stored procedure: no return value * @blog http://sjsky.iteye.com * @author Michael * @throws Exception * *  
       public static void Testprocnoout () throws Exception {System.out.println ("-------start Test Call stored procedure: no return value");  
       Connection conn = null;  
       CallableStatement callstmt = null;  
           try {class.forname (db_driver);  
           conn = Drivermanager.getconnection (Db_connection, db_name, db_pwd);  
  
           Stored procedure test_michael_noout is actually inserting a data into the database callstmt = Conn.preparecall ("{Call Test_michael_noout (?,?,?,?)}");  
           Parameter index starts from 1, sequentially 1,2,3 ...  
           Callstmt.setstring (1, "JDBC");  
           Callstmt.setstring (2, "JDBC");  
           Callstmt.setdouble (3, 8000.00);  
           Callstmt.setstring (4, "http://sjsky.iteye.com");  
           Callstmt.execute ();  
       System.out.println ("-------Test end.");  
       catch (Exception e) {e.printstacktrace (System.out); } finally {if (null!= callstmt) {callstmt.close ();  
           } if (NULL!= conn) {conn.close ();  
 }  
       }  
   }

[ii], both input in parameters and output out parameters, output is a simple value (not list)

CREATE OR REPLACE PROCEDURE test_michael (P_userid in VARCHAR2,  
                                         p_salary in number,  
                                         p_count out number  ) Is
  v_salary number: = P_salary;  
BEGIN  
  IF v_salary is NULL THEN  
    v_salary: = 0;  
  End IF;  
  IF P_userid is NULL THEN  
    SELECT COUNT (*) to  
      p_count from  
      tmp_michael T  
     WHERE t.salary >= v_salar Y;  
  ELSE  
    SELECT COUNT (*) into  
      p_count from  
      tmp_michael T  
     WHERE t.salary >= v_salary and  
       T.user _id like '% ' | | P_userid | | '%';  
  End IF;  
  Dbms_output. Put_Line (' v_count=: ' | | P_count);  
End Test_michael;  

The calling procedure is as follows

/** * Test call stored procedure: Return value is a simple value non-list * @blog http://sjsky.iteye.com * @author Michael * @throws Exception * /public static void Testprocoutsimple () throws Exception {System.out.println ("-------start Test Call stored procedure: return value is  
       Simple value not list ");  
       Connection conn = null;  
       CallableStatement stmt = null;  
           try {class.forname (db_driver);  
  
           conn = Drivermanager.getconnection (Db_connection, db_name, db_pwd);  
  
           stmt = Conn.preparecall ("{Call Test_michael (?,?,?)}");  
           Stmt.setstring (1, "");  
  
           Stmt.setdouble (2, 3000);  
           The index and value of out registration should correspond to Stmt.registeroutparameter (3, Types.integer);  
  
           Stmt.execute ();  
           The index in GETXXX (index) needs to correspond to the index of the upper registeroutparameter int i = Stmt.getint (3);  
           SYSTEM.OUT.PRINTLN ("Query result of Symbol condition count: =" + i);  
       System.out.println ("-------Test end.");  
  catch (Exception e) {         E.printstacktrace (System.out);  
           finally {if (null!= stmt) {stmt.close ();  
           } if (NULL!= conn) {conn.close ();  

 }  
       }  
   }

The test program is to inquire about the number of people who pay more than 3000, the results of the operation are as follows:
-------start test calls a stored procedure: The return value is a simple value not a list
Query results for symbol criteria Count: = 4
-------Test end.


[three], both input in parameters and output out parameters, output is a list
First you need to create the SQL package Test_pkg_cursor as follows:

First you need to create PACKAGE Test_pkg_cursor SQL as follows:
SQL code     
create OR REPLACE PACKAGE test_pkg_cursor is  
  
  --Author  : MICHAEL  http://sjsky.iteye.com  
  TYPE test_cursor is REF CURSOR;  
  
End Test_pkg_cursor;  
To create a stored procedure Test_p_outrs SQL is as follows:
SQL code     
create OR REPLACE PROCEDURE test_p_outrs (p_salary in number,  
                                         p_outrs< C12/>out Test_pkg_cursor. Test_cursor) is  
  v_salary number: = P_salary;  
BEGIN  
  IF p_salary is NULL THEN  
    v_salary: = 0;  
  End IF;  
  OPEN p_outrs for  
    SELECT * tmp_michael T WHERE t.salary > v_salary;  
End Test_p_outrs;  

The code that invokes the stored procedure is as follows:

/** * Test Call stored procedure: * @blog http://sjsky.iteye.com * @author Michael * @throws with return value and a list of return values Exception */public static void Testprocoutrs () throws Exception {System.out.println ("-------start Test Call stored procedure: has return value and return  
       Return value is a list of ");  
       Connection conn = null;  
       CallableStatement stmt = null;  
       ResultSet rs = null;  
           try {class.forname (db_driver);  
  
           conn = Drivermanager.getconnection (Db_connection, db_name, db_pwd);  
  
           stmt = Conn.preparecall ("{Call Test_p_outrs (?,?)}");  
           Stmt.setdouble (1, 3000);  
           Stmt.registeroutparameter (2, oracletypes.cursor);  
  
           Stmt.execute ();  
           The index in GETXXX (index) corresponds to the index of the upper Registeroutparameter rs = (ResultSet) stmt.getobject (2);  
           Gets the column name and type int colunmcount = Rs.getmetadata (). getColumnCount ();  
           string[] Colnamearr = new String[colunmcount]; String[] Coltypearr= new String[colunmcount];  
               for (int i = 0; i < Colunmcount i++) {Colnamearr[i] = Rs.getmetadata (). getColumnName (i + 1);  
               Coltypearr[i] = Rs.getmetadata (). Getcolumntypename (i + 1);  
           System.out.print (Colnamearr[i] + "(" + coltypearr[i] + ")" + "|");}  
           System.out.println ();  
               while (Rs.next ()) {StringBuffer sb = new StringBuffer ();  
               for (int i = 0; i < Colunmcount i++) {sb.append (rs.getstring (i + 1) + "|");  
           } System.out.println (SB);  
  
       } System.out.println ("-------Test Proc out of ResultSet end.");  
       catch (Exception e) {e.printstacktrace (System.out);  
           finally {if (null!= RS) {rs.close ();  
     } if (null!= stmt) {stmt.close ();      } if (NULL!= conn) {conn.close (); The results of the run are as follows:-------start test invokes a stored procedure: user_id (VARCHAR2) with return values and a list of return values | USER_NAME (VARCHAR2) | SALARY (number) |  
Other_info (VARCHAR2) | Michael | Michael | 5000 |  
null | Zhangsan | John | 10000 |  
null | Aoi_sola | Cang Jing Empty | 99999.99 |  
null | JDBC | JDBC | 8000 |  
http://sjsky.iteye.com |
 -------Test Proc out of ResultSet end.

[Four], input and output parameters are the same (in out)

CREATE OR REPLACE PROCEDURE test_p_inout (P_userid in VARCHAR2, p_num in Out number    ) is  
  v_count  number;< C5/>v_salary number: = P_num;  
BEGIN  
  IF v_salary is NULL THEN  
    v_salary: = 0;  
  End IF;  
  
  SELECT COUNT (*) into the  
    v_count from  
    tmp_michael  
   WHERE user_id like '% ' | | P_userid | | '% ' and  
     SALARY >= v_salary;  
  P_num: = V_count;  
End Test_p_inout;  

Code that invokes the stored procedure:

/** * Test Call stored procedure: InOut the same parameter: * @blog http://sjsky.iteye.com * @author Michael * @throws Exception /public static void Testprocinout () throws Exception {System.out.println ("-------start test calls a stored procedure  
        : InOut with one parameter ");  
        Connection conn = null;  
        CallableStatement stmt = null;  
            try {class.forname (db_driver);  
  
            conn = Drivermanager.getconnection (Db_connection, db_name, db_pwd);  
  
            stmt = Conn.preparecall ("{Call Test_p_inout (?,?)}");  
            Stmt.setstring (1, "Michael");  
  
            Stmt.setdouble (2, 3000);  
            Note that this registration out of the index and the above in parameter index the same stmt.registeroutparameter (2, Types.integer);  
  
            Stmt.execute ();  
            The index in GETXXX (index) needs to correspond to the index of registeroutparameter above, int count = Stmt.getint (2);  
            SYSTEM.OUT.PRINTLN ("Query results for symbol criteria Count: =" + count); System.out.println ("-------TesT end. ");  
        catch (Exception e) {e.printstacktrace (System.out);  
            finally {if (null!= stmt) {stmt.close ();  
            } if (NULL!= conn) {conn.close ();
 The results of the run are as follows:-------the start test invokes the stored procedure: inout The query result of the same parameter symbol condition count: = 1-------Test end.

[Five] Use truncate to clear data from tables in stored procedures

Create or Replace procedure Proc_insert_bldaren (rownums in number) is
begin
  EXECUTE IMMEDIATE ' TRUNCATE TABLE bi_ Baoliao_daren ';
  Insert into Bi_baoliao_daren (id,userid,username,baoliaonum,credits) Select bi_baoliao_sequence.nextval,bl.* from ( Select B.userid,b.username,count (b.id), sum (b.credits) credits from Bi_baoliao b  GROUP by B.userid,b.username Order by credits desc) BL where rownum <=rownums;
End Proc_insert_bldaren;

Java call

/** * Use truncate first to empty the data in the table * then insert data * * public static Boolean updateData1 (int rownum) {Boolean result
		=true;
		Connection Conn=null;
		CallableStatement Cs=null;
			try {date stime=new date ();
			Conn=dbconnection.getconnection ();
			Cs=conn.preparecall ("{Call Proc_insert_bldaren (?)}");
			Cs.setint (1, rownum);
			Result=cs.execute ();
			Date Etime=new date ();
		System.out.println (Etime.gettime ()-stime.gettime ());
		}catch (Exception e) {e.printstacktrace ();
		}finally{dbconnection.cleanup (NULL, NULL, CS, NULL);
	return result; }