使用下面的Java类TestStoreProcedure.java测试存储过程。

使用下面的Java类TestStoreProcedure.java测试存储过程。

package test;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

    /**

     * This class is used to test Oracle store procedure

     * @author CarlWu

     *

     */

    public class TestStoreProcedure{



              /**

               * 测试主方法

               * @param args

               */

              public static void main(String[] args) {

                       Connection con=null;

                       try {

                                //获得数据库连接

                                java.lang.Class.forName("oracle.jdbc.driver.OracleDriver");

                                con = DriverManager.getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:LEGDB");

                                //测试存储过程

                                TestStoreProcedure sp=new TestStoreProcedure();

                                sp.execute(con,"carl_sp_test","carl");

                       } catch (ClassNotFoundException cnfe) {

                                cnfe.printStackTrace();

                       } catch (SQLException sqlE) {

                                sqlE.printStackTrace();

                       }catch(Exception e){

                                e.printStackTrace();

                       }finally {

                                if (con != null) {

                                          try {

                                                   con.close();

                                                  

                                          } catch (Exception e) {

                                         }

                                }



                       }

              }

             

              /**

               * 该方法用来调用Oracle存储过程

               * @param con 数据库连接

               * @param parameter传入存储过程的参数值

               * @throws SQLException SQL 异常

               */

              public void execute(Connection con,String sp_name, String parameter) throws SQLException{



                       //调用存储过程

                       CallableStatement callStat=con.prepareCall("{call "+sp_name+"(?,?,?)}");                        

                       //最后一次插入的行数

                       int last_inserted_rows=0;

                       //总影响行数

                       int all_effected_rows=0;

                       //设置输入参数值

                       callStat.setString(1, parameter);

                       //注册存储过程的第二、第三个参数类型

                       callStat.registerOutParameter(2, OracleTypes.NUMBER);

                       callStat.registerOutParameter(3, OracleTypes.NUMBER);

                       //执行存储过程

                       callStat.execute();

                       //从存储过程执行结果中获得第二、第三个参数值

                       last_inserted_rows=callStat.getInt(2);

                       all_effected_rows=callStat.getInt(3);

                       //关闭CallableStatement，释放资源

                       callStat.close();

                       //在控制台中打印结果

                       System.out.println("最后一次插入条数:"+last_inserted_rows);

                       System.out.println("总影响行数:"+all_effected_rows);

              }

    }