首页 > 代码库 > jdbc调用 oracle 存储过程操作
jdbc调用 oracle 存储过程操作
创建有参存储函数findEmpNameAndSal(编号)，查询7902号员工的的姓名和月薪，【返回多个值，演示out的用法】
当返回2个或多个值，必须使用out符号
当返回1个值，就无需out符号
create or replace function findEmpNameAndSal(pempno in number,pename out varchar2) return number
as
psal emp.sal%type;
begin
select ename,sal into pename,psal from emp where empno=pempno;
--返回月薪
return psal;
end;
/
---------------------------------------相互转值
以下为测试调用
declare
psal emp.sal%type;
pename emp.ename%type;
begin
psal := findEmpNameAndSal(7902,pename);
dbms_output.put_line(‘7902号员工的姓名‘||pename||‘，薪水是‘||psal);
end;
/
存储过程
创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7902号员工的的姓名，职位，月薪【演示out的用法】
create or replace procedure
findEmpNameAndSalAndJob(pempno in number,pename out varchar2,pjob out varchar2,psal out number)
as
begin
select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
end;
/
以下测试调用
declare
pename emp.ename%type;
pjob emp.job%type;
psal emp.sal%type;
begin
findEmpNameAndSalAndJob(7902,pename,pjob,psal);
dbms_output.put_line(‘7902号员工的姓名是‘||pename||‘:‘||pjob||‘:‘||psal);
end;
/
JDBC连接oracle
JdbcUtil.java 1 package cn.itcast.web.oracle.util; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.Statement; 7 8 public class JdbcUtil { 9 private static String driver = "oracle.jdbc.driver.OracleDriver";10 //1521是主端口，也可能是其它端口去连接oracle数据库11 private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";12 private static String username = "scott";13 private static String password = "friends";14 private static Connection conn;15 private static Statement stmt;16 private static ResultSet rs;17 //注册数据库驱动18 static{19 try {20 Class.forName(driver);21 } catch (Exception e) {22 e.printStackTrace();23 throw new RuntimeException("oracle驱动注册失败");24 }25 }26 //获取数据库连接27 public static Connection getConnection(){28 Connection conn = null;29 try {30 conn = DriverManager.getConnection(url,username,password);31 } catch (Exception e) {32 e.printStackTrace();33 throw new RuntimeException("oracle连接获取失败");34 }35 return conn;36 }37 //关闭连接对象38 public static void close(Connection conn){39 if(conn!=null){40 try {41 conn.close();42 } catch (Exception e) {43 e.printStackTrace();44 throw new RuntimeException("oracle连接关闭失败");45 }46 }47 }48 public static void close(Statement stmt){49 if(stmt!=null){50 try {51 stmt.close();52 } catch (Exception e) {53 e.printStackTrace();54 throw new RuntimeException("oracle连接关闭失败");55 }56 }57 }58 public static void close(ResultSet rs){59 if(rs!=null){60 try {61 rs.close();62 } catch (Exception e) {63 e.printStackTrace();64 throw new RuntimeException("oracle连接关闭失败");65 }66 }67 }68 }
测试
1 @Test2 public void testConnection()3 {4 Connection conn=JdbcUtil.getConnection();5 System.out.println(conn==null?"no":"yes");6 }7
测试存储过程
1 //Java调用存储过程和函数 2 public class OracleDao { 3 //调用存储过程 4 @Test 5 public void callProcedure() throws Exception{ 6 Connection conn = JdbcUtil.getConnection(); 7 //第一参数:in 编号 7902 8 //第二参数:out 姓名 9 //第三参数:out 工作10 //第四参数:out 薪水11 String sql = "call findEmpNameAndSalAndJob(?,?,?,?)";12 //创建专用于调用过程或函数的对象13 CallableStatement cstmt = conn.prepareCall(sql);14 //为?占位符设置in、out值15 //hibernate从0开始，jdbc从1开始16 cstmt.setInt(1,7902);//in值17 cstmt.registerOutParameter(2,Types.VARCHAR);//out值18 cstmt.registerOutParameter(3,Types.VARCHAR);//out值19 cstmt.registerOutParameter(4,Types.INTEGER);//out值20 cstmt.execute();//抛行调用存储过程21 //依次接收3个返回值22 String ename = cstmt.getString(2);23 String job = cstmt.getString(3);24 Integer sal = cstmt.getInt(4);25 //显示26 System.out.println(ename+"的工作是：" + job + "，它是薪水是" + sal);27 //关闭连接对象28 JdbcUtil.close(cstmt);29 JdbcUtil.close(conn);30 }31 //调用存储函数32 @Test33 public void callFunction() throws Exception{34 Connection conn = JdbcUtil.getConnection();35 //参数一：in 编号 数值型36 //参数二：out 姓名 字符串型37 //返回值：out 薪水 数值型38 String sql = "{? = call findEmpNameAndSal(?,?)}";39 CallableStatement cstmt = conn.prepareCall(sql);40 cstmt.setInt(2,7788);//in41 cstmt.registerOutParameter(3,Types.VARCHAR);//out42 cstmt.registerOutParameter(1,Types.INTEGER);//返回值43 cstmt.execute();//执行调用存储函数44 String ename = cstmt.getString(3);45 Integer sal = cstmt.getInt(1);46 System.out.println(ename+"的薪水是"+sal);47 //关闭连接对象48 JdbcUtil.close(cstmt);49 JdbcUtil.close(conn);50 }51 }