--创建数据库
use master
go
if exists(select * from sysdatabases where name = 'KnowledgeDataBase')
drop database KnowledgeDataBase
go
create database KnowledgeDataBase
go
use KnowledgeDataBase
go
--创建表
--用户表
if exists(select * from sysobjects where name = 'UserInfo')
drop table UserInfo
go
create table UserInfo
(
UserName varchar(50) not null primary key, --用户名称
[PassWord] varchar(50) check(len([PassWord])>3), --用户密码,长度大于3
Email varchar(50) not null, --电子邮箱
Telepone varchar(12), --电话,可以为空
LoginTimes int not null default(0), --登录次数
ArticIDs varchar(1000) --可以查看的文章编号,不包含发表的文章,题号之间以逗号分隔,例如“1,5,8”
)
go
select * from UserInfo
insert into UserInfo values ('yukai','123456','56565','1258','1236','1');
insert into UserInfo values ('keller','123456','56565','1258','1236','1');
-- 使用 存储过程
use KnowledgeDataBase
go
if exists(select * from sysobjects where name = 'sp_zs')
drop procedure sp_zs
go
create procedure sp_zs
@pwd int
as
select * from UserInfo where [PassWord] = @pwd
go
--执行存储过程
exec sp_zs @pwd='123456'
在类中写如下关键代码调用此存储过程:
public static void main(String[] args) {
Connection con = null;
CallableStatement cs = null;
try {
con = getConnection();
String sql = "{call proc_user_inout(?)}";
cs = con.prepareCall(sql);
cs.registerOutParameter(1, Types.VARCHAR);
cs.setInt(1, 2);
cs.execute();
int number = cs.getInt(1);
System.out.println(number);
} catch (Exception e) {
e.printStackTrace();
}
}
以上两种调用均可得到 userid >2 的记录数。