oracle存贮过程

建表：

create table student ( username varchar(20), pass varchar(20) );



--插入数据存贮过程
create or  replace procedure test_stu( param1  IN varchar2)  as

begin 


   insert into student(username) values (param1);

end test_stu;


--有返回字段值的存贮过程
create or  replace procedure test_stu_backpass(param_in IN varchar2,param_out OUT varchar2)  as

begin 

   select pass into param_out from student where username= param_in; 

end test_stu_backpass;

 

package com;

import java.sql.*;
import java.sql.ResultSet;
/**
 * 调用存贮过程
 * @author jinchun
 *
 */
public class TestProcedureOne {

	public TestProcedureOne() {

	}
	
	/**
     * 
            插入值的存贮过程
     */
    public static void  test_ProcedureOne()
    {
    	String driver = "oracle.jdbc.driver.OracleDriver";
		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:mldn";//数据库名称mldn
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		CallableStatement cstmt = null;

		try {

			Class.forName(driver);

			conn = DriverManager.getConnection(strUrl, "user1", "user1");//用户名和密码user1

			CallableStatement proc = null;
               //存贮过程名称test_stu
			proc = conn.prepareCall("{ call test_stu(?)}");

			proc.setString(1, "king");
			proc.execute();
		} catch (SQLException ex2) {
			ex2.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					if (stmt != null) {
						stmt.close();
					}
					if (conn != null) {
						conn.close();
					}
				}
			} catch (SQLException ex1) {

			}
		}
	
    }
    /**
     * 
            有返回字段值的存贮过程
     */
    public static void test_ProcedureTwo()
    {
    	 String driver = "oracle.jdbc.driver.OracleDriver";

    	    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:mldn";

    	    Statement stmt = null;

    	    ResultSet rs = null;

    	    Connection conn = null;

    	    try {

    	      Class.forName(driver);

    	  	conn = DriverManager.getConnection(strUrl, "user1", "user1");//用户名和密码user1

    	      CallableStatement proc = null;

    	      proc = conn.prepareCall("{ call test_stu_backpass(?,?) }");

    	      proc.setString(1, "king");

    	      proc.registerOutParameter(2, Types.VARCHAR);

    	      proc.execute();

    	      String testPrint = proc.getString(2);

    	      System.out.println("=testPrint=is="+testPrint);

    	    }

    	    catch (SQLException ex2) {

    	      ex2.printStackTrace();

    	    }

    	    catch (Exception ex2) {

    	      ex2.printStackTrace();

    	    }

    	    finally{

    	      try {

    	        if(rs != null){

    	          rs.close();

    	          if(stmt!=null){

    	            stmt.close();

    	          }

    	          if(conn!=null){

    	            conn.close();

    	          }

    	        }

    	      }

    	      catch (SQLException ex1) {

    	      }

    	    }

    	  }


	public static void main(String[] args) {
		
	
		test_ProcedureTwo();
	}
}