oracle--在java中调用存储过程和存储函数

在java中调用存储过程和存储函数

存储过程：

查询某个员工的姓名 月薪 职位：

create or replace procedure queryempinfo(eno in number,
                                            pename out varchar2,
                                            psal   out number,
                                            pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob 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;

JDBCUtils:

package demo;

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);
        }
        //DriverManager.registerDriver(driver)
    }

    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /*
     * 运行Java程序
     * java -Xms100M -Xmx200M HelloWorld
     * 
     * 技术方向:
     * 1. 性能调优  ---> tomcat
     * 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;// ----> java GC
            }
        }
        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;
            }
        }
    }
}

TestOracle:

package demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import org.junit.Test;

public class TestOracle {
/*
 * create or replace procedure queryempinfo(eno in number,
                                            pename out varchar2,
                                            psal   out number,
                                            pjob   out varchar2)
 */
    @Test
    public void testProcedure(){
        //{call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call queryempinfo(?,?,?,?)}";

        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.NUMBER);
            call.registerOutParameter(4, OracleTypes.VARCHAR);

            //执行
            call.execute();

            //取出结果
            String name = call.getString(2);
            double sal = call.getDouble(3);
            String job = call.getString(4);
            System.out.println(name+"\t"+sal+"\t"+job);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, null);
        }
    }

/*
 * create or replace function queryempincome(eno in number)
return number
 */
    @Test
    public void testFunction(){
        //{?= 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);
        }       
    }

}

在out参数中使用光标

查询某个部门中的所有员工信息 —> 返回集合

包头
CREATE OR REPLACE PACKAGE MYPAKCAGE AS 

  type empcursor is ref cursor;
  procedure queryEmpList(dno in number, empList out empcursor);

END MYPAKCAGE;


包体
CREATE OR REPLACE PACKAGE BODY MYPAKCAGE AS

  procedure queryEmpList(dno in number, empList out empcursor) AS
  BEGIN

    open empList for select * from emp where deptno=dno;

  END queryEmpList;

END MYPAKCAGE;

java代码：

@Test
public void testCursor(){
    String sql = "{call MYPAKCAGE.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");
            double sal = rs.getDouble("sal");
            System.out.println(name+"\t"+sal);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        JDBCUtils.release(conn, call, rs);
    }
}

光标在JDBCUtils.release(conn, call, rs);语句中被关闭。