结合案例

表的建立sql
create table mytest(name varchar2(50),passwrd varchar2(50),id number);

1 序列
  create sequence seq_id
  increment by 1
  start with 1
  nomaxvalue;

2 触发器的建立
  create or replace trigger tri_id   --添加用户时自动加入用户id
  before insert on mytest
  for each row
  declare new_key number;
  begin
  select seq_id.nextval into new_key from dual;
  :new.id:=new_key;
  end;

3 创建一个包，在该包中，我定义类型test_cursor,是游标
 create or replace package testpackage as
 type test_cursor is ref cursor;
 end testpackage;

4 创建过程
 //获取数据
 create or replace procedure get_data
 (sid in number,p_cursor out testpackage.test_cursor) is
 begin
 open p_cursor for select * from mytest where id=sid;
 end;
 
 //根据所输入的表名,查询显示所有数据
 create or replace procedure list_data
 (tableName in varchar2,p_cursor out testpackage.test_cursor) is
 v_sql varchar2(100);
 begin
   v_sql:='select * from ' || tableName;  
 open p_cursor for  v_sql;
 end;
 
 //分页显示数据
 create or replace procedure pagaInfo
 (tableName in varchar2,
 Pagesize in number,--一页显示记录数
 pageNow in number,
 myrows out number,--总记录数
 myPageCount out number,--总页数
 p_cursor out testpackage.test_cursor --返回的记录集
 ) is
 --定义部分
 --定义sql语句 字符串
 v_sql varchar2(1000);
 --定义两个整数
 v_begin number:=(pageNow-1)*Pagesize+1;
 v_end number:=pageNow*Pagesize;
 begin
 --执行部分
 v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName
 ||') t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
 open p_cursor for v_sql;
 --计算myrows和myPageCount
 --组织一个sql
 v_sql:='select count(*) from '||tableName;
 --执行sql,并把返回的值 ，赋给myrows;
 execute immediate v_sql into myrows;
 --计算myPageCount
 if mod(myrows,Pagesize)=0 then
 myPageCount:=myrows/Pagesize;
 else
 myPageCount:=myrows/Pagesize+1;
 end if;
 --关闭游标
 --close p_cursor;
 end;

package com;

/*
 * 
 * 
 */
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import org.eclipse.jdt.internal.compiler.ast.ThisReference;

public class testOracle {
	public static void main(String argsp[]) throws Exception{
		//insert_data("g", "g");
		//get_data(new Integer(2));
		//list_data("mytest");
		PageList_data("");
	}
	
	//get data
	public static void get_data(int number) throws Exception{
		Connection conn = getOracle();
		CallableStatement cs = conn.prepareCall("{call get_data(?,?)}");
		cs.setInt(1, number);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		
		ResultSet rs = (ResultSet)cs.getObject(2);
		while(rs.next()){
			System.out.println("id:"+rs.getString("id")+"||"+"name:"+rs.getString("name")+"|| passwrd:"+rs.getString("passwrd"));
			
		}
	}
	
	//list data
	public static void list_data(String dataString) throws Exception{
		Connection conn = getOracle();
		CallableStatement cs = conn.prepareCall("{call list_data(?,?)}");
		cs.setString(1, dataString);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet)cs.getObject(2);
		while(rs.next()){
			System.out.println("id:"+rs.getString("id")+"||"+"name:"+rs.getString("name")+"|| passwrd:"+rs.getString("passwrd"));

		}
	}
	
	//pageList data
	public static void PageList_data(String dataString) throws Exception{
		Connection conn = getOracle();
		CallableStatement cs = conn.prepareCall("call pagaInfo(?,?,?,?,?,?)");
		cs.setString(1, "emp");
		cs.setInt(2, 5);
		cs.setInt(3, 1);
		cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
		cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
		cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		int rowNum = cs.getInt(4);
		int pageCount = cs.getInt(5);
		ResultSet rs = (ResultSet)cs.getObject(6);
		System.out.println("rowNum"+rowNum);
		System.out.println("总页数："+pageCount);
		while (rs.next()) {
			System.out.println("编号："+rs.getInt(1)+"名字："+rs.getString(2));
		}
	}
	
	//insert data
    public static void insert_data(String name,String passwrd) throws Exception{
    	Connection conn = getOracle();
		CallableStatement cs = conn.prepareCall("call insert_mytest(?,?)");
		cs.setString(1, name);
		cs.setString(2, passwrd);
		cs.execute();
	}
	
	//连接oracle数据库
	public static Connection getOracle() throws Exception{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@goby-server:1521:orcl","scott","tiger");
		//Connection conn=null;
		System.out.println("oracle成功接上啦!");
		return conn;
	}
}