此随笔是写给被这些概念困扰的人，你只需复制代码到“查询分析器”逐个调试即可，
同时别忘了在表里添加几条测试用的数据哦。
create table S
(
S# char(8),
SNAME varchar(20) not null,
AGE tinyint,
SEX bit default 1,
DNAME char(10),
primary key(S#),
check(SEX =0 or SEX = 1)
)
create table C
(
C# char(8),
CNAME varchar(8) not null,
CREDIT tinyint,
PC# char(8),
primary key(C#),
foreign key(PC#) references C(C#)
)
create table SC
(
S# char(8),
C# char(8),
GRADE tinyint,
primary key (S#,C#),
foreign key(S#) references S(S#) on delete cascade,
foreign key(C#) references C(C#) on delete cascade,
check((GRADE is null) or (GRADE between 0 and 100))
)
游标
declare @s_id varchar(10),@s_name varchar(20),@message varchar(80),@c_name varchar(20),@sc_grade smallint
print '------------计算机系学生选课情况报表-----------------------'
declare student_cursor cursor for
SELECT S#,SName
FROM S
WHERE DNAME = 'cs'
ORDER BY S#
open student_cursor
fetch next from student_cursor
into @s_id,@s_name
while @@FETCH_STATUS = 0
begin
print' '
select @message = @s_name + '学生选修课程:'
print @message
--获取当前学生所选修的课程及其成绩
declare sc_cursor cursor for
select CNAME,GRADE
from C,SC
WHERE C.C# = SC.C# AND SC.S# = @s_id
open sc_cursor
fetch next from sc_cursor
into @c_name,@sc_grade
if @@FETCH_STATUS <> 0
print' 该学生没有选课'
while @@FETCH_STATUS = 0
begin
select @message = ' ' + @c_name + ' ' + cast(@sc_grade as varchar)
print @message
fetch next from sc_cursor
into @c_name,@sc_grade
end
close sc_cursor
deallocate sc_cursor
--获取下一位学生
fetch next from student_cursor
into @s_id,@s_name
end
close student_cursor
deallocate student_cursor
go
储存过程
create proc s_c_varg
@var_grade smallint
as
select SNAME,CNAME,GRADE
from S,C,SC
WHERE S.S# = SC.S#
AND C.C# = SC.C#
AND grade > @var_grade
用户定义函数
create function AverageGrade(@dname varchar(20))
returns smallint
begin
return
(
select avg(GRADE)
from S,SC
where S.S# = SC.S#
AND DNAME = @dname
)
end
go
select S.S#,SNAME
from S,SC
WHERE S.S# = SC.S#
AND DNAME = 'cs'
AND GRADE >dbo.AverageGRADE('cs')
GO
触发器
--做一个触发器,当有人试图删除S表中元组时,下面的触发器将向客户端显示一条消息
create trigger reminder
on S
after delete
as
print '你刚刚删除了一个元组'
go
delete from S where AGE>100
