Oracle学习笔记之有返回列表的存储过程
Oracle学习笔记之有返回列表的存储过程
建表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=王八
评论暂时关闭