在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)asbegin  select ename,empjob,sal into pename,pjob,psal from emp where empno=eno;end;/

--查询某个员工的年收入create or replace function queryEmpIncome(eno in number)return numberas     --定义变量保存月薪和奖金     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;beginincome:=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) */@Testpublic 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 */@Testpublic 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);}}@Testpublic 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);}}}