学习笔记——Java调用Oracle存储过程

1.Java调用存储过程无返回值

--创建一个存储过程  输入员工号，判断工资是否高于1000，否则就200+;

首先在oracle通过PLSQL创建一个自己的过程：

create or replace procedure pro_emp_sal(v_in_empno number) is
v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno = v_in_empno;
  if v_sal<1000 then
    update emp set sal=sal+200 where empno=v_in_empno;
  end if;
end;

通过Java代码调用该存储过程：

注意几点：

1.声明CallableStatement对象

2.SQL语句为：{call pro_name(?,?....)};

具体代码：

package com.oracle.study;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Java调用存储过程
 * @author new
 *
 */
public class Test1 {
	public static void main(String[] args) {
		Connection conn = null;
		CallableStatement cs =  null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager
				.getConnection
				("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
			//创建包含预编译的SQL语句的新对象
			cs = conn.prepareCall("{call pro_emp_sal(?)}");		
			cs.setInt(1, 7369);		//填充参数
			cs.execute();
		} catch (Exception e) {
		} finally{
			try {
				//关闭资源
				cs.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

执行上面的Java代码，查询数据库中的数据，可以看到编号为7369的雇员薪水增加了200；

2.Java调用存储过程，返回非列表结果集（单列）

--创建一个存储过程  输入员工号，返回该员工的所属部门，以及上级编号

存储过程：
次出应该注意一下存储过程中的参数，需要区分，输入的用in，输出的用out

create or replace procedure pro_emp_empno(v_in_empno in number,v_out_deptno out number, v_out_mgr out number) is
begin
  select deptno,mgr into v_out_deptno,v_out_mgr from emp where empno=v_in_empno;
end;

主要步骤：

1.声明CallableStatement对象

2.生成预编译对象

3.填充参数

4.注册需要输出的参数位置，以及类型

5.执行

6.取出过程返回值

Java代码：

package com.oracle.study;

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;

/**
 * 单列返回值
 * @author new
 *
 */
public class Test2 {
	public static void main(String[] args) {
		Connection conn = null;
		CallableStatement cs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager
					.getConnection
					("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
			cs = conn.prepareCall("{call pro_emp_empno(?,?,?)}");
			cs.setInt(1, 7369);
			//注册返回结果的位置以及类型
			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);	
			cs.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER);
			cs.execute();
			int deptno = cs.getInt(2);
			int mgr = cs.getInt(3);
			System.out.println("部门编号是：" + deptno);
			System.out.println("上级编号是：" + mgr);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			try {
				cs.close();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

控制台输出：

3.Java调用存取过程，返回列表结果集（多列）

  --编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。和返回的结果集

首先创建一个包：

create or replace package mypack as
       type cur_emp_all is ref cursor;
end mypack;

create or replace procedure pro_re_cursor
(
       v_in_name in varchar2,  --表名
       v_in_size in number,    --每页显示数量
       v_in_page in number,    --当前页数
       v_out_cursor out mypack.cur_emp_all
) is
       v_sql varchar2(200);
       v_begin number := (v_in_page - 1)*v_in_size+1;
       v_end number := v_in_page*v_in_size;
begin
       v_sql := 'select t2.* from (select t1.*,rownum rn from (select * from '||v_in_name||') t1'||
       ' where rownum <='||v_end||') t2 where t2.rn>='||v_begin;
       open v_out_cursor for v_sql;
end;

注：此处的游标不能关闭，否则在Java中会报错，应当在Java中关闭游标。

步骤和上面的差不多，只是返回的类型不同。（cursor）

Java代码：

<pre name="code" class="java">package com.oracle.study;

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

/**
 * 多列返回值
 * @author new
 *
 */
public class Test3 {
	public static void main(String[] args) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager
				.getConnection
				("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
			cs = conn.prepareCall("{call pro_re_cursor(?,?,?,?)}");
			cs.setString(1, "emp");
			cs.setInt(2, 3);
			cs.setInt(3, 2);
			//返回Cursor结果集
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);	
			cs.execute();
			rs = (ResultSet) cs.getObject(4);	//将返回的列表结果集赋给rs
			while(rs.next()){
				System.out.print("编号：" + rs.getObject("empno") + "\t");
				System.out.print("姓名：" + rs.getObject(2) + "\t");
				System.out.print("职位：" + rs.getObject(3) + "\t");
				System.out.println();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			try {
				cs.close();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

控制台打印：