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

建表sqlCREATE 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=王八