创建存储过程的脚本
使用sqlserver 中的pubs 数据库中的 jobs表为例
create procedure showAll
as
select * from jobs
create procedure obtainJob_desc
@outputParam varchar() output
@id int
as
select @outputParam = job_desc from jobs where job_id = @id
create procedure obtainReturn
as
declare @ret int
select @ret = count(*) from jobs
return @ret
declare @ret int
exec @ret = obtainReturn
print @ret
用来获得连接的函数
public Connection getConnection(){
Connection con = null;
try {
ClassforName(commicrosoftjdbcsqlserverSQLServerDriver);
con = DriverManagergetConnection(jdbc:microsoft:sqlserver://localhost:;databasename=pubssa);
} catch (Exception e) {
eprintStackTrace();
}
return con ;
}
调用得到结果集的存储过程
public void getResultSet(){
//获得连接
Connection con = thisgetConnection();
try {
//showAll为存储过程名
javasqlCallableStatement cstm = conprepareCall({call showAll });
ResultSet rs = cstmexecuteQuery();
while(rsnext()){
//这里写逻辑代码
Systemoutprintln(rsgetString());
}
rsclose();
conclose();
} catch (SQLException e) {
// TODO Autogenerated catch block
eprintStackTrace();
}
}
调用带有 输入 输出 参数的存储过程
public void getOutParameter(int inParam){
String outParam;
Connection con = thisgetConnection();
try {
CallableStatement cstm = conprepareCall({call obtainJob_desc (??)});
cstmregisterOutParameter( TypesVARCHAR);
cstmsetInt( inParam);
cstmexecute();;
//得到输出参数
String outParma = cstmgetString();
Systemoutprintln(outParma);
cstmclose();
conclose();
} catch (SQLException e) {
// TODO Autogenerated catch block
eprintStackTrace();
}
}
调用带返回值的存储过程
public void getReturn(){
int ret;
Connection con = thisgetConnection();
try {
CallableStatement cstm = conprepareCall({?=call obtainReturn()});
cstmregisterOutParameter( TypesINTEGER);
cstmexecute();
//得到返回值
ret = cstmgetInt();
Systemoutprintln(ret);
cstmclose();
conclose();
} catch (SQLException e) {
// TODO Autogenerated catch block
eprintStackTrace();
}
}
