oracle学习笔记2之有返回列表的存储过程

建表sql

CREATE TABLE student( 
student_id NUMBER PRIMARY KEY, 
student_name vARCHAR2(30) NOT NULL) 

insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (1, '张三');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (2, '李四');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (3, '王五');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (4, '马六');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (5, '孙七');  
insert into STUDENT (STUDENT_ID, STUDENT_NAME)  
values (6, '王八');

创建包

create or replace package testpackage as 
type Test_CURSOR is ref cursor;
end testpackage;

创建存储过程
create or replace procedure testc(p_cursor out testpackage.Test_CURSOR)
is
begin 
open p_cursor for select * from student;
end  testc;

java调用

package com.anxin.util;

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

public class DBAccess {
	public DBAccess() {
	}

	public static Connection getConnection(String type) {
		Connection conn = null;
		String url = "";
		String user = "";
		String password = "";
		String driverName = "";
		if (type.equals("mysql")) {
			url = "jdbc:mysql://localhost:3306/file?characterEncoding=utf8";
			user = "root";
			password = "123";
			driverName = "com.mysql.jdbc.Driver";
		} else if (type.equals("oracle")) {
			url = "jdbc:oracle:thin:@localhost:1521:anxin";
			user = "system";
			password = "123456";
			driverName = "oracle.jdbc.driver.OracleDriver";
		}
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驱动程序未找!");
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库连接出错啦!");
			e.printStackTrace();
		}
		System.out.println("Connection连接成功!");
		return conn;

	}

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		Connection connection = getConnection("oracle");
		connection.setAutoCommit(false);
		CallableStatement cal=null;
//		cal=connection.prepareCall("call insertStu(?,?,?)");
//		cal.setInt(1, 34);
//		cal.setString(2, "李蓓");
//		cal.registerOutParameter(3,Types.INTEGER);
//		cal.execute();
//		System.out.println(cal.getInt(3));
		
		cal=connection.prepareCall("call testc(?)");
		cal.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
		cal.execute();
		ResultSet rs = (ResultSet)cal.getObject(1);
		while(rs.next())
	      {
	          System.out.println("<tr><td>" + rs.getInt(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
	      }
	}

}

输出结果:

学生ID=1,学生姓名Name=张三
学生ID=2,学生姓名Name=李四
学生ID=3,学生姓名Name=王五
学生ID=4,学生姓名Name=马六
学生ID=5,学生姓名Name=孙七
学生ID=6,学生姓名Name=王八