问题:按年龄查询超过某年龄并且担任教练的员工信息(教练年龄调用GetAge函数实现)
相关表:Employees
GetAge函数:
CREATE FUNCTION GetAge(@no char(10))
RETURNS int
BEGIN
DECLARE @age int,@birth datetime
SET @birth = (SELECT birthDate FROM Employees WHERE empNo=@no)
SET @age = (YEAR(GETDATE())-YEAR(@birth))
RETURN @age
END
存储过程proEmployee:
CREATE PROCEDURE proEmployee(@age int)
AS
SET NOCOUNT ON
CREATE TABLE #temp (//临时表
empNo char(10),
empName varchar(20),
empType char(20),
title char(20))
DECLARE emp CURSOR FOR //创建游标
SELECT empNo,empName,empType,title FROM Employees
DECLARE @no char(10),@name varchar(20),@type char(20),@titl char(20)
OPEN emp
FETCH emp INTO @no,@name,@type,@titl //将游标里的元组付给变量@
WHILE(@@FETCH_STATUS=0)//从当前游标移到游标集的下一条记录
BEGIN
IF (SELECT dbo.GetAge (@no)) > @age
BEGIN
INSERT INTO #temp//将改元组插入到临时表
VALUES(@no,@name,@type,@titl)
END
FETCH emp INTO @no,@name,@type,@titl//获取下一个游标
END
CLOSE emp//关闭游标!!!
DEALLOCATE emp
SELECT * FROM #temp//显示临时表全部内容
- 存储过程 SET NOCOUNT ON:当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。当 SET NOCOUNT 为 OFF 时,返回计数。如果不加这句话,在Java调用时会出现空指针异常。
- 必须用临时表,如果用select逐条输出会每条带一个表头
Java调用:
//链接数据库
final String DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
final String DATABASE_URL = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=DirvingSchool";
final String DATABASE_USRE = "sa";
final String DATABASE_PASSWORD = "12345";
try {
Class.forName(DRIVER_CLASS);
Connection connection=DriverManager.getConnection(DATABASE_URL,DATABASE_USRE,DATABASE_PASSWORD);
//调用存储过程
CallableStatement cs7=connection.prepareCall("{call proEmployee(?)}");
cs7.setInt(1,50);
cs7.execute();//返回多个ResultSet对象
ResultSet rs4=cs7.getResultSet();//输出第一个
while(rs4.next()){
String id=rs4.getString(1);
String name=rs4.getString(2);
String type=rs4.getString(3);
String title=rs4.getString(4);
System.out.println(id+","+name+","+type+","+title);
}
while(cs7.getMoreResults()) {//后面的
ResultSet rs5=cs7.getResultSet();
while(rs5.next()){
String id=rs5.getString(1);
String name=rs5.getString(2);
String type=rs5.getString(3);
String title=rs5.getString(4);
System.out.println(id+","+name+","+type+","+title);
}
}
break;
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}