JDBC调用存储过程和函数

阳光穿透心脏的1/2处 2022-05-18 02:10 263阅读 0赞

在数据库中我们可以写一些存储过程和函数，来封装一些常用的SQL语句，存储过程和函数目的是为了可重复地执行操作数据库的sql语句的集合

返回值：

*  存储过程的返回值，可以有多个值，但不是必须的
 *  函数的返回值，只有一个值，而且是必须的

函数是可以嵌入在SQL中使用的，可以在SELECT等SQL语句中调用，而存储过程则不行。我们可以在数据库中创建一些常用的存储过程和函数，这样我们在数据访问层直接调用即可。这里记录一下使用JDBC调用存储过程和函数的方法

# 调用没有返回值的存储过程 #

首先我们在数据库中创建一个没有返回值的存储过程：

create or replace procedure proc_Ins_Dept(vid in varchar2 ,vname  in varchar2,vloc in varchar2) is
    begin insert into Dept values(vid,vname,vloc);
    end proc_Ins_Dept;

我是在Oracle中创建的，其他的数据库大同小异

在Dao中调用：

// 无返回值的存储过程
        public void proc1() {
            try {
    
                Connection conn = super.getConn();    //调用了BaseDao创建连接
    
                CallableStatement cs = conn.prepareCall("{call proc_Ins_Dept(?,?,?)}");  //调用格式 {call 存储过程名(参数)}
                cs.setObject(1, 76);
                cs.setObject(2, "技术部");
                cs.setObject(3, "zhengzhou");
                cs.execute();            //执行
                cs.close();
                conn.close();
    
            } catch (Exception ex) {
                ex.printStackTrace();
            }
    
        }

# 调用有返回值的存储过程 #

创建有返回值的存储过程：

create or replace procedure pro_Ins_Dept (vid in varchar2,vname in varchar2,vloc in varchar2,vresult out varchar2) is 
    begin insert into Dept values(vid,vname,vloc);
      vresult:='success';
    Exception
      when others then
       vresult:='fail';
    end pro_Ins_Dept;

输入值使用in，返回值使用out表示

在Dao中调用：

// 带返回值的存储过程
        public void proc2() {
            try {
                Connection conn = super.getConn();
                CallableStatement cs = conn.prepareCall("{call proc_Ins_Dept2(?,?,?,?)}");
                cs.setObject(1, 76);
                cs.setObject(2, "市场部");
                cs.setObject(3, "luoyang");
                cs.registerOutParameter(4, java.sql.Types.VARCHAR);           //注册返回类型(sql类型)
                cs.execute();
    
                Object objRtn = cs.getObject(4);      //得到返回值
    
                System.out.println(objRtn);
    
                cs.close();
                conn.close();
    
            } catch (Exception ex) {
                ex.printStackTrace();
            }
    
        }

唯一不同的是我们需要给返回值注册类型，可以在java.sql.Types类中找到对应的Sql类型，Oracle中的varchar2对应的也是varchar。最后使用CallableStatement的get方法得到返回结果

# 调用自定义函数 #

SQL语句：

create or replace function fun_avg_dept(vdeptno in number)
        return number is 
           r number;
    begin select avg(sal) into r from emp where deptno=vdeptno;
           return(r);
    end fun_avg_dept;

Dao中调用：

// 带返回值的自定义函数
        public void fun1() {
            try {
                Connection conn = super.getConn();
                               
                //函数可以嵌入到Sql中
                String sql = "select fun_avg_dept(?) from dual";
    
                //调用方式还是和使用ps调用普通SQL一样
                PreparedStatement ps = conn.prepareStatement(sql);
    
                ps.setObject(1, 10);
    
                ResultSet rs = ps.executeQuery();
    
                if (rs.next()) {
                    System.out.println(rs.getObject(1));
                }
    
                rs.close();
                ps.close();
                conn.close();
    
            } catch (Exception ex) {
                ex.printStackTrace();
            }
    
        }