java直接操作oracle存储过程.docx
- 文档编号:8253831
- 上传时间:2023-01-30
- 格式:DOCX
- 页数:8
- 大小:16.54KB
java直接操作oracle存储过程.docx
《java直接操作oracle存储过程.docx》由会员分享,可在线阅读,更多相关《java直接操作oracle存储过程.docx(8页珍藏版)》请在冰豆网上搜索。
java直接操作oracle存储过程
java直接操作oracle存储过程---增删改查
sql方面的***********************************************
createtableaa
(
a_idnumber(10)primarykey,
a_namevarchar2(50),
a_sexvarchar2(4),
a_datedate,
a_moneynumber(8,2)
)
--查看下是否成功
select*fromaa;
--创建触发器
createsequenceaa_id_seqincrementby1startwith1;
--插入一条数据测试一下
insertintoaa(a_id,a_name,a_sex,a_date,a_money)values(1,'陈飞龙','男',sysdate,55.55);--成功
insertintoaa(a_name,a_sex,a_date,a_money)values('陈飞龙','男',sysdate,55.55);--不成功,有序列但是没自增
--创建触发器
createorreplacetriggeraa_id_tigger
beforeinsertonaaforeachrow
begin
if:
new.a_idisnull
then
selectaa_id_seq.nextvalinto:
new.a_idfromdual;
endif;
end;
/
--插入一数据进行测试
deletefromaa;
insertintoaa(a_name,a_sex,a_date,a_money)values('陈小龙','男',sysdate,66.55);--成功
--插入信息的存储过程
createorreplaceprocedureaa_insert_pro
(
b_nameinaa.a_name%type,
b_sexinaa.a_sex%type,
b_moneyinaa.a_money%type
)
as
begin
insertintoaa(a_name,a_sex,a_date,a_money)values(b_name,b_sex,sysdate,b_money);
endaa_insert_pro;
/
--删除的存储过程
createorreplaceprocedureaa_delete_pro
(
b_idinnumber
)
as
begin
deletefromaawherea_id=b_id;
endaa_delete_pro;
/
--修改表aa的存储过程
createorreplaceprocedureaa_update_pro
(
b_idinnumber,
b_nameinvarchar2,
b_sexinaa.a_sex%type,
b_moneyinaa.a_money%type
)
as
begin
updateaaseta_name=b_name,a_sex=b_sex,a_date=sysdate,a_money=b_moneywherea_id=b_id;
endaa_update_pro;
/
--查询一条数据的某些个数据字段
createorreplaceprocedureaa_seleceById_pro
(
b_idinnumber,
b_nameoutvarchar2,
b_sexoutvarchar2,
b_dateoutdate,
b_moneyoutnumber
)
as
begin
selecta_name,a_sex,a_date,a_moneyintob_name,b_sex,b_date,b_moneyfromaawherea_id=b_id;
endaa_seleceById_pro;
/
-------------------------------------------------------------------------------
--查询多个数据,步骤1,2
--步骤1:
创建程序包
createorreplacepackageaa_packageas
typeaa_allisrefcursor;
endaa_package;
/
--步骤2:
利用程序包创建多查询
createorreplaceprocedureaa_selectMore_pro
(
b_alloutaa_package.aa_all
)
as
begin
openb_allforselect*fromaa;
endaa_selectMore_pro;
/
select*fromaawherea_id=2;
java类文件方面*****************************************************************************************
类DB,连接数据库:
packagecom.db;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
publicclassDB
{
privatestaticConnectioncon=null;
privatestaticResultSetrs=null;
privatestaticPreparedStatementpstm=null;
publicstaticConnectiongetCon()
{
Stringdriver="oracle.jdbc.driver.OracleDriver";
Stringurl="jdbc:
oracle:
thin:
@localhost:
1521:
orcl";
Stringuid="scott";
Stringpid="tiger";
try
{
Class.forName(driver);
con=DriverManager.getConnection(url,uid,pid);
}catch(Exceptione)
{
e.printStackTrace();
con=null;
}
System.out.println("打开");
returncon;
}
publicstaticvoidcloseCon()
{
try
{
if(rs!
=null)
{
rs=null;
}
if(con!
=null)
{
con=null;
}
if(pstm!
=null)
{
pstm=null;
}
}catch(Exceptione)
{
e.printStackTrace();
}finally
{
rs=null;
pstm=null;
con=null;
System.out.println("关闭");
}
}
/**
*@paramargs
*/
publicstaticvoidmain(String[]args)
{
DBdb=newDB();
System.out.println(DB.getCon());
DB.closeCon();
}
}
类TESTAA,操作存储过程
packagecom.chen;
importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.Types;
importjava.util.Date;
importcom.db.DB;
/**调用存储过程操作数据库*/
publicclassTestAA
{
privateConnectioncon=null;
ResultSetrs=null;
CallableStatementcstm=null;
/**增加数据*/
publicvoidinsertAA(Stringname,Stringsex,doublemoney)
{
try
{
Stringsql="callscott.aa_insert_pro(?
?
?
)";
if(con==null)con=DB.getCon();
cstm=con.prepareCall(sql);
cstm.setString(1,name);
cstm.setString(2,sex);
cstm.setDouble(3,money);
booleanflag=cstm.execute();
System.out.println("写入完成:
"+flag);
}catch(Exceptione)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**删除数据*/
publicvoiddeleteAA(intid)
{
try
{
Stringsql="callaa_delete_pro(?
)";
if(con==null)con=DB.getCon();
cstm=con.prepareCall(sql);
cstm.setInt(1,id);
booleanflag=cstm.execute();
System.out.println("删除结果:
"+flag);
}catch(Exceptione)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**修改数据*/
publicvoidupdateAA(intid,Stringname,Stringsex,doublemoney)
{
try
{
Stringsql="callaa_update_pro(?
?
?
?
)";
if(con==null)con=DB.getCon();
cstm=con.prepareCall(sql);
cstm.setInt(1,id);
cstm.setString(2,name);
cstm.setString(3,sex);
cstm.setDouble(4,money);
intk=cstm.executeUpdate();
booleanflag;
if(k>0)
{
flag=true;
}else
{flag=false;}
System.out.println("修改结果为:
"+flag);
}catch(Exceptione)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**查某记录某几个字段*/
publicvoidselectSomeById(intid)
{
try
{
Stringsql="callaa_seleceById_pro(?
?
?
?
?
)";
if(con==null)con=DB.getCon();
cstm=con.prepareCall(sql);
cstm.setInt(1,id);
cstm.registerOutParameter(2,Types.VARCHAR);
cstm.registerOutParameter(3,Types.VARCHAR);
cstm.registerOutParameter(4,Types.DATE);
cstm.registerOutParameter(5,Types.INTEGER);
cstm.execute();
Stringname=cstm.getString
(2);
Stringsex=cstm.getString(3);
Datedate=cstm.getDate(4);
intmoney=cstm.getInt(5);
System.out.println("name="+name+"\tsex="+sex+"\tdate="+date+"\tmoney="+money);
}catch(Exceptione)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**查询全部数据*/
publicvoidselectAllAA()
{
try
{
Stringsql="callaa_selectMore_pro(?
)";
if(con==null)con=DB.getCon();
cstm=con.prepareCall(sql);
cstm.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
// rs=cstm.executeQuery();
cstm.execute();
rs=(ResultSet)cstm.getObject
(1);
System.out.println("查询结果:
");
// System.out.println(rs);
while(rs.next())
{
// System.out.println("a_id="+rs.getInt
(1)+"\ta_name="+rs.getString
(2)+"\ta_sex="+rs.getString(3)+"\ta_date="+rs.getDate(4)+"\ta_money"+rs.getDouble(5));
System.out.println("-*****************************************************************--------------***********");
System.out.println("a_id="+rs.getInt("a_id")+"\ta_name="+rs.getString("a_name")+"\ta_sex="+rs.getString("a_sex")+"\ta_date="+rs.getDate("a_date")+"\ta_money"+rs.getDouble("a_money"));
}
}catch(Exceptione)
{
e.printStackTrace();
}
finally
{
DB.closeCon();
}
}
/**
*@paramargs
*/
publicstaticvoidmain(String[]args)
{
TestAAta=newTestAA();
// ta.insertAA("朱老三","男",99.63);
// ta.insertAA("朱传文","男",869.63);
// ta.insertAA("王小丫","女",299.63);
// ta.insertAA("王大拿","男",199.63);
// ta.deleteAA(9);
ta.updateAA(5,"覃媚媚","女",594.21);//--还存在问题
ta.selectSomeById(5);
// ta.selectAllAA();
}
}
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- java 直接 操作 oracle 存储 过程