Ms sql创建存储过程
create proc insert_dept(
@name_1 char(4)
)
as
insert into dept(name)
values(@name_1)
Go
存储过程的调用
exec insert_dept '马上'
JDBC调用无参数的存储过程
Connection con = null;
try {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取连接
con = DriverManager
.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
"wepull", "wepull");
//设置要执行的SQL语句，如果是执行存储过程，那么执行存储过程的格式按括号内所示
CallableStatement cst = con.prepareCall("{call insert_user(?,?) }");
//设置两个问号的值
cst.setString(1, "zhangxuan");
cst.setString(2, "123");
//执行SQL语句
cst.execute();
con.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != con) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
有返回值的存储过程来接收
create proc dept_count(
@name_1 int output
)
as
select @name_1=count(*) from dept
Go
Connection con = null;
try {
// 加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 获取连接
con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
"wepull", "wepull");
// 设置要执行的SQL语句，如果是执行存储过程，那么执行存储过程的格式按括号内所示
CallableStatement cst = con
.prepareCall("{call check_user(?,?,?) }");
// 设置存储过程中两个输入参数的值
cst.setString(1, "zhangxuan");
cst.setString(2, "123");
// 设置存储过程中输出参数的值
cst.registerOutParameter(3, Types.INTEGER);
// 执行SQL语句
cst.execute();
// 执行完存储过程后，取得存储过程中输出参数的值
int testPrint = cst.getInt(3);
System.out.println(testPrint);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != con) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
