【数据库】应用编程

• 基本输入输出
  print ‘hello world’
  select ‘hello world’ as 示例

• 变量定义 declare @变量名 类型
  赋值 set 变量=表达式 or select 变量=表达式

Declare @Stnum int, @StAvgAge int;
set @Stnum = (select count(*) from Student);
set @StAvgAge = (select avg(Sage) from Student);
print @Stnum; print @StAvgAge;
-- or
Declare @Stnum int, @StAvgAge int;
select @Stnum = count(*),@StAvgAge = avg(Sage) from Student;
print @Stnum; print @StAvgAge;

• 流程控制

1. 顺序执行
2. 分支选择 if else \ switch case
3. 循环 while() \for()
4. 语句开始begin 结束end

例子： 分支

Declare @i int;
set @i = 10;
if(@i >= 5) 
begin
	print '很大'
	print 'too big'
end
else
	print '很小'

select *,
	case
		when Grade >= 90 then '优秀'
		when Grade < 90 and Grade >= 80 then '良好'
	end as 成绩描述
from SC

select *,
	case(Sdept)
		when 'CS' then '计算机'
		when 'IS' then '信息'
		when 'MA' then '数学'
	end as 系中文名
from Student

例子：循环

Declare @i int;
set @i = 10;
while(@i >= 0) begin
	print @i;
	set @i = @i - 1;
end

• 自定义函数

create function 函数名(形参) returns as begin
函数体
end

create procedure 过程名(型参) as begin
过程体
end

create function AddTest(@i int, @j int) returns int as begin
	declare @result int;
	set @result = @i + @j;
	return @result;
end

-- 调用系统函数
print substring('abc', 1, 1)

-- 调用自定义函数 dbo
print dbo.AddTest(2,5)

-- 调用过程
execute 过程名

• 函数返回系选课人数

create function DeptSnum(@sdept) returns int as begin
	declare @num int;
	set @num = (select count(*) from Student where Sdept = @sdept and Sno in (select Sno from SC));
	return @num;
end

print dbo.DeptSnum('IS')
-- or
select *, dbo.DeptSnum(Sdept) as '选课人数' from Student 

• 创建一个函数，输入学生的学号返回学生称谓(先生或女士)

create function getStSno(@Sno varchar(10)) returns varchar(10) as begin
	declare @Sname varchar(10), @result varchar(10),@Ssex varchar(2);
	select @Sname=Sname, @Ssex=Ssex from Student where Sno=@Sno;
	if(@Ssex='女') 
		set @result = SUBSTRING(@Sname, 1, 1)+'女士'
	else
		set @result = SUBSTRING(@Sname, 1, 1)+'先生'
	return @result;
end

print dbo.getStSno('95001')
select *, dbo.getStSno(Sno) as 称谓 from Student

• 创建一个存储过程 查询学生称谓和年龄

create procedure QuerrySt as begin
	select dbo.getStSno(Sno),Sage from Student
end

execute QuerrySt

• 创建一个存储过程 查询特定系的学生称谓和年龄

create procedure QuerrySt(@Sdept varchar(10)) as begin
	select dbo.getStSno(Sno),Sage from Student where Sdept = @Sdept
end

execute QuerrySt 'IS'

• 创建每个系成绩前20% 学生姓名和年龄 按以下格式排列
  系 学生信息
  CS 学生姓名性别

create function GetStInfoBySdept(@Sdept varchar(10)) returns varchar(max) as begin
	declare @result varchar(max),@Sname varchar(10),@Ssex varchar(10), @Sage int;
	@result = '';
	declare StCursor Cursor for select Sname, Ssex, Sage from Student where Sdept = @Sdept;
	open StCursor;
	while(@@Fetch_Status=0) begin
		 Fetch Next from StCursor into @Sname, @Ssex, @Sage;
		 if(@@Fetch_Status=0) begin
		 set @result = @result +  @Sname+',' + @Ssex + ',' + ltrim(str(@Sage)) + ';';
	end; close StCursor; deallocate StCursor;
	return @result; ❌ -- 若没有游标 只能取一条记录

游标 Cursor
定义 Declare 游标名 Cursor for select…
打开 open 游标名 Fetch Next from 游标 Into 变量组
循环是否达到游标尾部 while @@Fetch_Status 0-未到达尾部 -1到达游标尾部
关闭 释放 close 游标名 deallocate 游标名

• 建立一个存储过程显示学生的学号、姓名、性别、系、必修课选课情况、系内名次，系内名次在本系范围按照平均分降序排列。
  查询实例：95001，李勇，男，CS，选课情况（数据库：90；数学：80），第2名

Student(Sno，Sname，Ssex，Sage，Sdept，Leader)
Course(Cno，Cname，Ccredit，Cpno)
SC(Sno，Cno，Grade)

select Sno, Sname, Ssex, Sdept, dbo.GetCInfoBySno(Sno) as 选课情况, '第' + ltrim(dbo.GetSRankBySno(Sno)) + '名' as 名次 from Student

create function GetCInfoBySno(@Sno varchar(10)) returns varchar(500) as begin
	declare @result varchar(500), @Cname varchar(20), @Grade int; 
	@result = '选课情况(';
	declare StCCursor Cursor for select Cname, Grade from SC join Course on SC.Cno = Course.Cno where Sno = @Sno and Ccredit>=4
	open StCCursor;
	while(@@Fetch_Status=0) begin
		 Fetch Next from StCCursor into @Cname, @Grade;
		 if(@@Fetch_Status=0) 
		 set @result = @result +  @Cname + ':' + ltrim(str(@Grade)) + ';';
	end; close StCCursor; deallocate StCCursor;
	set @result = @result + ')';
	return @result; ❌ -- 若没有游标 只能取一条记录
end


create function GetSRankBySno(@QSno varchar(10)) returns int as begin
	Declare @Rank int, @curRank int, @Sno varchar(10), @Grade int, @preGrade int;
	set @Rank = 0;
	set @preGrade = 0;
	Declare RankCursor Cursor for
	select Sno, (select avg(Grade) from 
	SC where Sno=Student.Sno) as Grade from Student where 	Sdept=(select Sdept from Student where Sno=@QSno) 
	order by (select avg(Grade) from SC where Sno=Student.Sno) 	desc
	open RankCursor;
	Fetch Next from RankCursor into @Sno, @Grade;
	while(@@Fetch_Status=0) begin
		if(@Grade != @preGrade) begin --并列
			set @Rank = @Rank + 1;
			set @preGrade = @Grade;
		end
		if(@Sno = @QSno)
			set @curRank = @Rank;
		Fetch Next from RankCursor into @Sno, @Grade;
	end;close RankCursor;deallocate RankCursor;
	return @curRank
end