Java调用Oracle存储过程返回结果集---从建表、存储过程到调用的详细过程

参考：https://www.cnblogs.com/qixin622/archive/2011/08/09/2131827.html，其中的try catch及colse()。

存储过程返回单个数据，非结果集

创建表：

创建存储过程：

CREATE OR REPLACE 
PROCEDURE TEST_SELECT(
	IN_SNO in NUMBER,
	OUT_SNAME out varchar2,
	OUT_SAGE out NUMBER
) AS
BEGIN
	SELECT SNAME,SAGE
	into  OUT_SNAME,OUT_SAGE
	FROM TEST_STUDENT WHERE SNO = IN_SNO;
END;

JAVA代码：

package com.aoyang.wxapp.util.mail;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;

public class CallableDemo {
	public static void main(String[] args) throws SQLException {

        try {
    		//加载OracleDriver驱动，即装载、连接、初始化，最新JDK可省略
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
        
		String url ="jdbc:oracle:thin:@172.30.112.197:1521:orclutf8";
		String user ="itsm20";
		String passWord = "Itsm20.123"; 
		
		Connection conn = DriverManager.getConnection(url, user, passWord);
		/**
		 * 存储过程返回单个数据，非结果集
		 */
		String sql1 = "call TEST_SELECT(?,?,?)";
		CallableStatement cs = conn.prepareCall(sql1);
		//第1个参数的值设定为2
		cs.setInt(1, 2);
		//声明 out参数的数据类型
		//SQL用Types.VARCHAR、Types.INTEGER、Types.DOUBLE
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
		//第二种表达方式，但是需要导包import oracle.jdbc.OracleTypes;
		cs.registerOutParameter(3, OracleTypes.NUMBER);
		//CallableStatement执行
		cs.execute();
		//取结果
		System.out.println(cs.getString(2));
		System.out.println(cs.getInt(3)); //getDouble()也可以的
		
		rs.close();
		cs.close();
		conn.close();
	}
}

存储过程返回结果集，利用游标取数据

准备表TEST_EMPLOYEES：

创建程序包，包内创建一个游标

创建存储过程，用到包的游标定义out参数

CREATE OR REPLACE 
PROCEDURE 
TEST_SELECT2(O_CUR OUT TEST_PACKAGE.TEST_CURSOR) AS

BEGIN
	OPEN O_CUR FOR SELECT * FROM TEST_EMPLOYEES;
END;

JAVA代码：

		/**
		 * 存储过程返回结果集，利用游标取数据
		 */
		String sql3 = "call TEST_SELECT2(?)";
		cs = conn.prepareCall(sql3);
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(1);
		System.out.println("NAME"+"\t"+"SALARY");
		while(rs.next()) {
			System.out.println(rs.getString(3)+"\t"+rs.getString(5));
		}

另外一种方法，先写包，再写包体，但是没有试验成功。

包：

CREATE OR REPLACE 
PACKAGE mypack IS
	TYPE mycursor IS REF CURSOR;
	PROCEDURE myproc(outcursor IN OUT mycursor);
END mypack;

包体：

CREATE OR REPLACE PACKAGE BODY mypack IS
	PROCEDURE myproc(
        outcursor IN OUT mycursor
	)
		IS
		BEGIN
			OPEN outcursor FOR
			SELECT*FROM Student WHERE ROWNUM<10;
			RETURN;
	END myproc;
END;

JAVA调用存储过程（in out 参数）

存储过程

CREATE OR REPLACE 
procedure TEST_EXCHANGE(a in out  int,b in out  int)
as
	temp NUMBER(12);
begin
	temp := a;
	a := b;
	b := temp;
end ;

JAVA代码：

		/**
		 * 存储过程in out参数
		 */
		String sql5 = "call TEST_EXCHANGE(?,?)";
		cs = conn.prepareCall(sql5);
		cs.setInt(1, 100);
		cs.setInt(2, 200);
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);
		cs.execute();
		System.out.println("数字1是："+cs.getInt(1)+"，数字2是："+cs.getInt(2));

存储过程case状况

存储过程

CREATE OR REPLACE 
procedure TEST_SELECT3(idnum in varchar,out_gender out varchar2)
as
rowData TEST_STUDENT%rowtype;
begin
select * into rowData from TEST_STUDENT where SNO=idnum;
case rowData.GENDER
when 1 then
dbms_output.put_line('女人');
out_gender :='女人';
when 2 then
dbms_output.put_line('男人');
out_gender :='男人';
else
dbms_output.put_line('人妖');
out_gender :='人妖';
end case;
end;

JAVA代码：

		/**
		 * 存储过程case
		 */
		String sql6 = "call TEST_SELECT3(?,?)";
		cs = conn.prepareCall(sql6);
		cs.setInt(1, 2);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
		cs.execute();
		System.out.println(cs.getString(2));

完整JAVA代码：

package com.aoyang.wxapp.util.mail;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;

public class CallableDemo {
	public static void main(String[] args) throws SQLException {
        try {
    		//加载OracleDriver驱动，即装载、连接、初始化，最新JDK可省略
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
        
		String url ="jdbc:oracle:thin:@172.30.112.197:1521:orclutf8";
		String user ="itsm20";
		String passWord = "Itsm20.123"; 
		
		Connection conn = DriverManager.getConnection(url, user, passWord);
		/**
		 * 存储过程返回单个数据，非结果集
		*	CREATE OR REPLACE PROCEDURE TEST_SELECT(
		*		INSNO in NUMBER,
		*		OSNAME out varchar2,
		*		OSAGE out NUMBER
		*	) AS
		*	BEGIN
		*		SELECT SNAME,SAGE
		*		into  OSNAME,OSAGE
		*		FROM TEST_STUDENT WHERE SNO = INSNO;
		*	END;
		 */
		String sql1 = "call TEST_SELECT(?,?,?)";
		CallableStatement cs = conn.prepareCall(sql1);
		//第1个参数的值设定为2
		cs.setInt(1, 2);
		//声明 out参数的数据类型
		//SQL用Types.VARCHAR、Types.INTEGER、Types.DOUBLE
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
		//第二种表达方式，但是需要导包import oracle.jdbc.OracleTypes;
		cs.registerOutParameter(3, OracleTypes.NUMBER);
		//CallableStatement执行
		cs.execute();
		//取结果
		System.out.println(cs.getString(2));
		System.out.println(cs.getInt(3)); //getDouble()也可以的
		
		/**
		 * 存储过程无返回值
		 */
		String sql2 = "call TEST_UPDATE()";
		cs = conn.prepareCall(sql2);
		cs.execute();
		/**
		 * 存储过程返回结果集，利用游标取数据
		 */
		String sql3 = "call TEST_SELECT2(?)";
		cs = conn.prepareCall(sql3);
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(1);
		System.out.println("NAME"+"\t"+"SALARY");
		while(rs.next()) {
			System.out.println(rs.getString(3)+"\t"+rs.getString(5));
		}
		/**
		 * 存储过程in out参数
		 */
		String sql5 = "call TEST_EXCHANGE(?,?)";
		cs = conn.prepareCall(sql5);
		cs.setInt(1, 100);
		cs.setInt(2, 200);
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);
		cs.execute();
		System.out.println("数字1是："+cs.getInt(1)+"，数字2是："+cs.getInt(2));
		/**
		 * 存储过程case
		 */
		String sql6 = "call TEST_SELECT3(?,?)";
		cs = conn.prepareCall(sql6);
		cs.setInt(1, 2);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
		cs.execute();
		System.out.println(cs.getString(2));
		
		rs.close();
		cs.close();
		conn.close();
	}
}