最近写代码,老师叫我们练习下在java代码中调用存储过程来实现对数据库的操作。学习了几天,现在把学到的知识整理下。
1.如果不要求返回结果集,就直接定义存储过程,然后在程序中调用就可以了,这个很简单,这里就不说了。
2.如果要求返回结果集,那么就要就定包,再在包体中定义存储过程,最后是在程序中调用包中的存储过程,其步骤如下:
(1)创建包,这里我创建了一个名为OeCom的包,它包括addComType,deleteComType,modifyComType,getTotalRows,queryComTypeList(它要返回一个结果集(数据库中主要是返回一个游标),等会要注意它在程序中的调用方式)四个存储过程,其代码如下:
create or replace package OeCom is
type outList is ref cursor;
PROCEDURE addComType(comTypeId in varchar2 ,comTypeName in varchar2,state out varchar2);
PROCEDURE deleteComType(comTypeId in varchar2 ,delRow out integer);
PROCEDURE modifyComType(comTypeId in varchar2 ,comTypeName in varchar2,modifyTypeId in varchar2,modifyRow out integer);
PROCEDURE getTotalRows(comTypeId in varchar2,comTypeName in varchar2,totalRows out integer);
PROCEDURE queryComTypeList(comTypeId in varchar2,comTypeName in varchar2,startRow in Integer,pageSize in Integer,comTypeList out outList);
end OeCom;
(2)实现包体,也就是实现里面的存储过程,其代码如下:
CREATE OR REPLACE package body OeCom
is
--"添加"过程
PROCEDURE addComType(comTypeId in varchar2 ,comTypeName in varchar2,state out varchar2)
IS
BEGIN
INSERT INTO OA_COMMODITY_TYPE_T(COM_TYPE_ID,COM_TYPE_NAME) VALUES(comTypeId,comTypeName);
if sql%rowcount>0 then
state:='true';
else
state:='false';
end if;
end;
--"删除"过程
PROCEDURE deleteComType(comTypeId in varchar2 ,delRow out integer)
IS
BEGIN
delete from OA_COMMODITY_TYPE_T where COM_TYPE_ID=comTypeId;
delRow:=sql%rowcount;
end;
--"修改"过程
PROCEDURE modifyComType(comTypeId in varchar2 ,comTypeName in varchar2,modifyTypeId in varchar2,modifyRow out integer)
IS
BEGIN
update OA_COMMODITY_TYPE_T set COM_TYPE_ID=comTypeId,COM_TYPE_NAME=comTypeName where COM_TYPE_ID=modifyTypeId;
modifyRow:=sql%rowcount;
end;
--获得总记录数
PROCEDURE getTotalRows(comTypeId in varchar2,comTypeName in varchar2,totalRows out integer)
is
qsql varchar2(500);
begin
qsql:='select count(*) from OA_COMMODITY_TYPE_T where 1=1';
if comTypeId is not null then
qsql:=qsql||' and com_type_id='||chr(39)||comTypeId||chr(39);
end if;
if comTypeName is not null then
qsql:=qsql||' and com_type_name='||chr(39)||comTypeName||chr(39);
end if;
execute immediate qsql into totalRows;
end;
--获得list
PROCEDURE queryComTypeList(comTypeId in varchar2,comTypeName in varchar2,startRow in Integer,pageSize in Integer,comTypeList out outList)
is
endRow integer;
qsql varchar2(500);
begin
endRow:=startRow+pageSize;
qsql:='select * from (select com_type_id,com_type_name,rownum rn from OA_COMMODITY_TYPE_T where 1=1 ';
if comTypeId is not null then
qsql:=qsql||' and com_type_id='||chr(39)||comTypeId||chr(39);
end if;
if comTypeName is not null then
qsql:=qsql||' and com_type_name='||chr(39)||comTypeName||chr(39);
end if;
qsql:=qsql||' and rownum<='||endRow;
qsql:=qsql||' order by com_type_id desc) where rn >'||startRow;
open comTypeList for qsql;
end;
end OeCom;
(3)完成上面两步后,你可以测试下,代码如下:
--测试
declare
qsql varchar2(1000);
csql varchar2(1000);
type outList is ref cursor;
list outlist;
delRow integer;
modifyRow integer;
totalRows integer;
begin
oecom.getTotalRows('','',totalRows);
OeCom.queryComTypeList('','',0,10,list);
oecom.modifyComType('1','3','1',modifyRow);
oecom.deleteComType('1',delRow);
dbms_output.put_line('totalRows:'||totalRows);
dbms_output.put_line('modifyRow:'||modifyRow);
dbms_output.put_line('delRow:'||delRow);
end;
(4)如果测试成功,那么我们再来看下在程序里面的调用方式:
①没有返回结果集的普通调用(这里用jdbc或连接池都行):
public boolean deleteComType(String com_type_id) throws SQLException
{
CallableStatement proc = conn
.prepareCall("{ call oecom.deleteComType(?,?)}");
proc.setString(1, com_type_id.trim());
proc.registerOutParameter(2, Types.INTEGER);
proc.execute();
int delRow = proc.getInt(2);
if (proc != null)//如果proc不为空,就关闭
{
proc.close();
}
if (delRow > 0)//如果更新记录条数大于0,说明更新成功
return true;
else
return false;
}
②有返回结果集的:
public List<CommodityType> queryComTypePages(CommodityType commodityType,
int pageSize, int startRow) throws SQLException
{
String comTypeId = commodityType.getComTypeId().trim();
String comTypeName = commodityType.getComTypeName().trim();
CallableStatement proc = conn
.prepareCall("{ call OeCom.queryComTypeList(?,?,?,?,?) }");
proc.setString(1, comTypeId);
proc.setString(2, comTypeName);
proc.setInt(3, startRow);
proc.setInt(4, pageSize);
proc.registerOutParameter(5, OracleTypes.CURSOR);
proc.execute();
ResultSet rs = (ResultSet) proc.getObject(5);
List<CommodityType> list = new ArrayList<CommodityType>();
while (rs.next())
{
CommodityType commoditytypetab = new CommodityType();
commoditytypetab.setComTypeId(rs.getString("com_type_id"));
commoditytypetab.setComTypeName(rs.getString("com_type_name"));
list.add(commoditytypetab);
}
if (rs != null)
{
rs.close();
}
if (proc != null)
{
proc.close();
}
return list;
}
(5)OK,大致就是这样了!