表的建立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;
}
}