以对表test进行增,删,改,查进行说明:
1.新建表test
create table TEST
(
TID NUMBER not null,
TNAME VARCHAR2(32),
TCODE VARCHAR2(32),
CREATEDATE DATE
)
alter table TEST
add constraint PK_TEST_ID primary key (TID)
2.数据库中存储过程的脚本:
create or replace package test_package is
procedure test_add(pid number,pcode varchar2,pname varchar2,pcreatedate date);
procedure test_update(pid number,pname varchar2);
procedure getNameAndCodeById(pid number,pcode out varchar2,pname out varchar2);
function getNameById(pid number) return varchar2;
end test_package;
create or replace package body test_package is
/*
向表中增加一条数据
*/
procedure test_add(pid number,pcode varchar2,pname varchar2,pcreatedate date)is
begin
insert into test values(pid,pcode,pname,pcreatedate);
commit;
end;
/*
将表中tid为pid的数据的tname更新为pname
*/
procedure test_update(pid number,pname varchar2)is
begin
update test set tname=pname where tid=pid;
commit;
end;
/*
根据ID查找对应的code和name
*/
procedure getNameAndCodeById(pid number,pcode out varchar2,pname out varchar2)is
begin
select t.tcode,t.tname into pcode,pname from test t where t.tid=pid;
end;
/*
根据ID查找对应的name
*/
function getNameById(pid number) return varchar2 is
returnValue varchar2(100) :='';
begin
select t.tname into returnValue from test t where t.tid=pid;
return returnValue;
end;
end test_package;
3.在java程序中调用存储过程
A.通过存储过程向表中添加一条数据
public void add() throws Exception
{
Connection con=new Condb().getConnection();
String sql="{call test_package.test_add(?,?,?,?)}";
// sql也可以写为 "begin test_package.test_add(?,?,?,?);end;"
CallableStatement stmt=con.prepareCall(sql);
stmt.setInt(1, 1);
stmt.setString(2, "test");
stmt.setString(3, "test");
//注意日期类型的处理
stmt.setDate(4, new Date(new java.util.Date().getTime()));
stmt.execute();
stmt.close();
con.close();
}
B.通过给定的tid更新这条数据的tname字段
public void update() throws Exception
{
Connection con=new Condb().getConnection();
String sql="{call test_package.test_update(?,?)}";
CallableStatement stmt=con.prepareCall(sql);
stmt.setInt(1, 1);
stmt.setString(2, "update");
stmt.execute();
stmt.close();
con.close();
}
C.调用存储过程根据给定的tid返回tcode和tname
public void getNameAndCodeById() throws Exception
{
String tcode="";
String tname="";
Connection con=new Condb().getConnection();
String sql="{call test_package.getNameAndCodeById(?,?,?)}";
CallableStatement stmt=con.prepareCall(sql);
stmt.setInt(1, 1);
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.execute();
tname=stmt.getString(2);
tcode=stmt.getString(3);
System.out.println("tname="+tname+",tcode="+tcode);
stmt.close();
con.close();
}
D.通过调用函数根据给定的tid返回tname
public void getNameById() throws Exception
{
String tname="";
Connection con=new Condb().getConnection();
String sql="select test_package.getNameById(?) from dual";
CallableStatement stmt=con.prepareCall(sql);
stmt.setInt(1, 1);
ResultSet rs=stmt.executeQuery();
while(rs.next())
{
tname=rs.getString(1);
}
System.out.println("name="+tname);
stmt.close();
con.close();
}