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

来源：互联网发布：淘宝服装模特编辑：程序博客网时间：2024/05/29 12:15

建表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)isbegin 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 blockSystem.out.println("驱动程序未找!");e.printStackTrace();}try {conn = DriverManager.getConnection(url, user, password);} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.println("数据库连接出错啦!");e.printStackTrace();}System.out.println("Connection连接成功!");return conn;}public static void main(String[] args) throws Exception {// TODO Auto-generated method stubConnection 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=王八