在Java语言中调用存储过程、存储函数、包头、包体
需要拷贝连接Oracle的jar包,路径如下图所示:
连接Oracle数据库的代码:
package demo.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCUtils { private static String driver = "oracle.jdbc.OracleDriver"; private static String url = "jdbc:oracle:thin:@192.168.56.101:1521:orcl"; private static String user = "scott"; private static String password = "tiger"; static{ try { Class.forName(driver); } catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(e); } } public static Connection getConnection(){ try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); } return null; } /* * 运行Java程序 * java -Xms100M -Xmx200M HellWorld * * 技术方向: * 1. 性能调优 * 2. 故障诊断: 死锁 */ public static void release(Connection conn,Statement st,ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ rs = null;//why? --> Java GC,让Java的垃圾回收器回收,我们是不能控制垃圾回收器的,即使我们调用了System.gc(),也是不能立即回收的,Java的垃圾回收器是不可控的。 } } if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ st = null; } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ conn = null; } } } }Java GC,让Java的垃圾回收器回收,我们是不能控制垃圾回收器的,即使我们调用了System.gc(),也是不能立即回收的,Java的垃圾回收器是不可控的。System.gc()只是请求jvm回收。
测试的代码:
分别是在Java语言中调用存储过程、在Java语言中调用存储函数、在Java语言中调用包头和包体。
--查询某个员工的姓名 职位和月薪 /* 思考: 1. out参数太多??? 2. 查询某个部门中所有员工的所有信息 */ create or replace procedure queryEmpInfoma(eno in number, pename out varchar2, pjob out varchar2, psal out number) as begin select ename,empjob,sal into pename,pjob,psal from emp where empno=eno; end; /
--查询某个员工的年收入 create or replace function queryEmpIncome(eno in number) return number as --定义变量保存月薪和奖金 psal emp.sal%type; pcomm emp.comm%type; begin select sal,comm into psal,pcomm from emp where empno=eno; --返回年收入 return psal*12+nvl(pcomm,0); end; / declare income number; begin income:=queryEmpIncome(7369); dbms_output.put_line(income); end; /
查询某个部门中所有员工的所有信息 包头 CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor is ref cursor; procedure queryEmpList(dno in number,empList out empcursor); END MYPACKAGE; 包体 CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS procedure queryEmpList(dno in number,empList out empcursor) AS BEGIN open empList for select * from emp where deptno=dno; END queryEmpList; END MYPACKAGE;
package demo.oracle; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes; import org.junit.Test; import demo.utils.JDBCUtils; /* * 思考 * 1. 光标close了吗?关闭了,光标是通过call得到的,而call是通过conn得到的,所以conn关闭了,那么光标就关闭了 * 2. 下面的代码能在mysql上运行吗?不能,实际和理论总是有差别的。 */ public class TestOracle { /* * create or replace procedure queryEmpInfo(eno in number, pename out varchar2, pjob out varchar2, psal out number) */ @Test public void tesProcedure(){ // {call <procedure-name>[(<arg1>,<arg2>, ...)]} String sql = "{call queryEmpInfoma(?,?,?,?)}"; Connection conn = null; CallableStatement call = null; try { conn = JDBCUtils.getConnection(); call = conn.prepareCall(sql); //对于in参数,赋值 call.setInt(1, 7839); //对于out参数,申明 call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.VARCHAR); call.registerOutParameter(4, OracleTypes.NUMBER); //执行 call.execute(); //取出结果 String name = call.getString(2); String job = call.getString(3); double sal = call.getDouble(4); System.out.println(name); System.out.println(job); System.out.println(sal); } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } } /* * create or replace function queryEmpIncome(eno in number) return number */ @Test public void tesFunction(){ //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} String sql = "{?=call queryEmpIncome(?)}"; Connection conn = null; CallableStatement call = null; try { conn = JDBCUtils.getConnection(); call = conn.prepareCall(sql); //对于out参数,申明 call.registerOutParameter(1, OracleTypes.NUMBER); //对于in参数,赋值 call.setInt(2, 7839); //执行 call.execute(); //取出结果 double income = call.getDouble(1); System.out.println(income); } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } } @Test public void testCursor(){ String sql = "{call MYPACKAGE.queryEmpList(?,?)}"; Connection conn = null; CallableStatement call = null; ResultSet rs = null; try { conn = JDBCUtils.getConnection(); call = conn.prepareCall(sql); //对于in参数,赋值 call.setInt(1,20); //对于out参数,申明 call.registerOutParameter(2, OracleTypes.CURSOR); //执行 call.execute(); //取出结果 rs = ((OracleCallableStatement)call).getCursor(2); while(rs.next()){ String name = rs.getString("ename"); String job = rs.getString("job"); double sal = rs.getDouble("sal"); System.out.println(name+"\t"+job+"\t"+sal); } } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, rs); } } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。