java调用Oracle存储过程

本文为学习笔记

 

Test.java:

 

package bing.oracleprocedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import oracle.jdbc.OracleTypes;

/**
 * 练习存储过程的调用
 * @author bing
 * @version 2011-07-09
 */
public class Test {
	public static void test(){
		System.out.println("=====Test.test=====");
		Connection conn = new Conn().getConnection(); // 获得数据连接对象
		CallableStatement cstmt = null;
		ResultSet rs = null;
		try{
			/*
			 -- 创建练习用的表t_test
			create table t_test(
			  t_id number(4) not null,
			  t_name varchar2(20),
			  t_msg varchar2(100)
			);
			alter table t_test add constraint pk_t_test primary key(t_id);
			-- 编写练习用的过程up_insert_test 
			-- 插入数据到表t_test
			create or replace procedure up_insert_test(v_id in number, v_name in varchar2, v_msg in varchar2)
			is
			begin
			  insert into t_test(t_id,t_name,t_msg) values(v_id,v_name,v_msg);
			end up_insert_test;
			/
			 */
			// 过程调用，无返回值
			cstmt = conn.prepareCall("call up_insert_test(?,?,?)");
			cstmt.setInt(1, 1);
			cstmt.setString(2, "bing");
			cstmt.setString(3, "super man");
			cstmt.execute();
			cstmt.setInt(1, 2);
			cstmt.setString(2, "admin");
			cstmt.setString(3, "a worker");
			cstmt.execute();
			cstmt.setInt(1, 3);
			cstmt.setString(2, "user");
			cstmt.setString(3, "a user");
			cstmt.execute();
			System.out.println("插入成功");
			
			/*
		 	-- 编写练习用的存储过程up_select_test_1 
			-- 输入id，输出name，查询t_test中是单条记录
			create or replace procedure up_select_test_1
			(v_id in number, v_name out varchar2)
			is
			begin
			  select t_name into v_name from t_test where t_id = v_id;
			end up_select_test_1;
			/
			 */
			// 过程调用，返回单条记录
			// oracle过程没有返回值，所有返回值都是通过out参数来替代的
			cstmt = conn.prepareCall("call up_select_test_1(?,?)");
			cstmt.setInt(1, 1);
			cstmt.registerOutParameter(2, Types.VARCHAR);// 注册out参数，注意序号对应过程的参数序号
			cstmt.execute();
			String name = cstmt.getString(2);// 获取out参数，注意序号对应过程的参数序号
			System.out.println("查询成功");
			System.out.println("name = " + name);
			
			/*
			-- 编写包upk_select_test,为存储过程up_select_test_2准备 --
			create or replace package upk_select_test
			as type uc_test is ref cursor;
			end upk_select_test;
			/
			-- 编写存储过程up_select_test_2 
			-- 查询表t_test中的所有记录
			create or replace procedure up_select_test_2
			(uc_result out upk_select_test.uc_test)
			is
			begin
			  open uc_result for select * from t_test;
			end up_select_test_2;
			/
			 */
			// 过程调用，返回多条记录
			// 集合不能用一般的参数，必须要用pagkage，从上面的注释可以看到游标作为out参数，过程返回的是一个游标
			cstmt = conn.prepareCall("call up_select_test_2(?)");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.execute();
			rs = (ResultSet) cstmt.getObject(1);
			System.out.println("查询成功");
			while(rs.next()){
				System.out.println("id = " + rs.getString(1) 
						+ "  name = " + rs.getString(2) 
						+ "  msg = " + rs.getString(3));
			}
			
			rs.close();
			cstmt.close();
			conn.close();
		}catch(Exception e){
			System.out.println("=====Test.test=====\n操作失败");
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		new Test().test();
		/* 控制台输出：
		   =====Test.test=====
			插入成功
			查询成功
			name = bing
			查询成功
			id = 3  name = user  msg = a user
			id = 1  name = bing  msg = super man
			id = 2  name = admin  msg = a worker
		 */
	}
}

 

Conn.java:

package bing.oracleprocedure;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * 数据库连接类
 * @author bing
 * @version 2011-07-09
 *
 */
public class Conn {
	private static String DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:oracledbtest";
	private static String USER = "bing";
	private static String PASSWORD = "bing";
	private Connection conn = null;
	
	/**
	 * 获得连接对象
	 * @return 连接对象
	 */
	public Connection getConnection(){
		try{
			if(conn==null||conn.isClosed()){
					Class.forName(DRIVER);
					conn = DriverManager.getConnection(URL, USER, PASSWORD);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}
}