java调用oracle存储过程

1.创建存储过程

/*
create [or replace] procedure 过程名(参数名  in|out 类型)
as
begin
end;
*/

--声明add_sal存储过程,给指定员工涨1000工资
create  procedure add_sal(pno in number)
as
begin
  update emp set sal = sal + 1000 where empno = pno;
  commit;
end;

2.创建maven工程(略)

3.导入maven坐标

	<dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.1.0.7.0</version>
        </dependency>
    </dependencies>

4.编写测试代码

public class OracleDemo {
    /**
     * java调用存储过程
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
     *    {call <procedure-name>[(<arg1>,<arg2>, ...)]}
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
        //得到预编译的Statement对象
        CallableStatement pstm= connection.prepareCall("{call add_sal(?)}");
        //给参数赋值
        pstm.setObject(1,7369);
        //执行数据库查询操作
        pstm.execute();
        pstm.close();
        connection.close();
    }
}

5.创建存储函数

/*
create [or replace] function 方法名(参数 in|out 类型) return 参数类型
as
 定义变量名  类型要和return返回类型一致
begin
  return 变量名;
end;
*/
create  function dname_emp(dno number) return emp.ename%type
as
  dname emp.ename%type;
begin
  select dname into dname from dept where deptno = dno;
  return dname;
end;

6.调用存储函数

public class OracleDemo {
    /**
     * java调用存储过程
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
     *    {call <procedure-name>[(<arg1>,<arg2>, ...)]}
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
        //得到预编译的Sta tement对象
        CallableStatement call= connection.prepareCall("{?=call dname_emp(?)}");
        //给参数赋值
        call.setInt(2,20);
        call.registerOutParameter(1, OracleTypes.VARCHAR);
        //执行数据库查询操作
        call.execute();
        //输出结果
        String dname = call.getString(1);
        System.out.println("部门名称:"+dname);
        call.close();
        connection.close();
    }
}

7.执行结果

部门名称:RESEARCH