Oracle数据库中存储过程的学习实例

Oracle数据库中存储过程的学习实例 测试用表student: 1. 实例1:Oracle存储过程返回结果集 --声明一个package create or replace package package_pro_test as type cursor_rs is ref cursor; Procedure proc_test(p_rs out cursor_rs); end package_pro_test; --实现package体，创建存储过程 -- IN 和OUT 参数 --java将IN 参数传给 CallableStatement 对象是通过 setXXX 方法完成 --的。该方法继承自 --PreparedStatement。所传入参数的类型决定了所用的setXXX 方法 --(例如，用 setFloat 来传入float 值等)。 CREATE OR REPLACE Package Body PACKAGE_PRO_TEST Is Procedure proc_test(p_rs out cursor_rs) is begin open p_rs for 'select * from student'; end proc_test; end PACKAGE_PRO_TEST; //java中调用存储过程 import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import oracle.jdbc.OracleResult; Set /*** * 调用存储过程获取查询结果集 * @author gwy **/ public class Test1 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); System.out.println("连接成功～"); //核心代码: /** 通过call调用PACKAGE_PRO_TEST.proc_ * test存储过程。通过数据库连接对象 * conn调用prepareCall()方法创建CallableStatement 对象。 * 存储过程调用形式:?conn. prepareCall(“call 存储过程名(?,?,„)”) * ? conn. prepareCall(“call 包名.存储过程名(?,?,„)” ) * * **/ CallableStatement cs = conn.prepareCall("{call PACKAGE_PRO_TEST.proc_test(?)}"); //注册输出参数类型 cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); cs.execute(); //获取存储过程返回的结果集 ResultSet ors = (ResultSet)cs.getObject(1); //迭代结果集 while(ors.next()){ System.out.println("学生编号:"+ors.getInt("stu_id")+"学生姓名:"+ors.getString("stu_name")+"学生年龄:"+ors.getInt("age")); } } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs = null; } if (stmt != null) { stmt = null; } if (conn != null) { conn = null; } } } } 2. 实例2:通过变量列表获取单条记录值 //创建存储过程: --in关键词修饰的v_stuname为输入参数，out关键词修饰的out_stuid，out_stuname,out_age, out_result_statement为输出参数 create or replace procedure pro_test(v_stuname in varchar,out_stuid out number,out_stuname out varchar,out_age out number,out_result_statment out varchar) is --声明标志标量 flag number; begin flag:=0; begin --stu_id,stu_name,age为student表中的字段，--out_stuid,out_st uname,out_age为返回参数 select stu_id,stu_name,age into out_stuid,out_stuname,out_age from student where stu_name=v_stuname; exception when no_data_found then flag:=1; end; if flag>0 then out_result_statment:='查询数据异常!'; else out_result_statment:='成功获取结果!'; end if; end; //java中调用该存储过程 import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import oracle.jdbc.OracleResult; Set/*** * 调用存储过程获取单条记录 * @author gwy **/ public class Test2 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { //加载Oracle驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //创建数据库连接对象 conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); System.out.println("连接成功～"); //创建CallableStatement对象 CallableStatement cs = conn.prepareCall("{call pro_test(?,?,?,?,?)}"); //给存储过程的对应输入参数传值 cs.setString(1, "tom1"); //注册存储过程的输出参数类型(例:索引为2的参数类型为 NUMBER类型) cs.registerOutParameter(2,oracle.jdbc.OracleTypes.NUMBE); cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHA R); cs.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER ); cs.registerOutParameter(5, oracle.jdbc.OracleTypes.VARCHAR); cs.execute(); System.out.println("学生编号:"+cs.getInt(2)+"学生姓 名:"+cs.getString(3)+"学生年龄:"+cs.getInt(4)); System.out.println("异常:"+cs.getString(5)); } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs = null; } if (stmt != null) { stmt = null; } if (conn != null) { conn = null; } } } } 3. 实例3:存储过程的参数既作为输入参数也作为输出参数 //创建存储过程 --参数v_stuname被in和out关键字修饰，即v_stuname既作为输入参数，也作 为输出参数 create or replace procedure pro_test2(v_stuname in out varchar) is begin select stu_name into v_stuname from student where stu_name=v_stuname; exception when no_data_found then dbms_output.put_line('没有找到数据～'); when others then dbms_output.put_line('发生其它异常～'); end; //java调用存储过程 import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Test3 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("oracle.jdbc.driver .OracleDriver "); conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); System.out.println("连接成功～"); CallableStatement cs = conn.prepareCall("{call pro_test2(?)}"); cs.setString(1, "tom1"); cs.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR); cs.execute(); System.out.println("学生姓 名:"+cs.getString(1)); } catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { rs = null; } if (stmt != null) { stmt = null; } if (conn != null) { conn = null; } } } } 4.