MYSQL基础上机练习题（八）用户自定义存储过程和触发器的创建、修改、删除和执行

实验目标：

1．通过实验掌握存储过程的概念、功能。

2．掌握用户自定义存储过程的创建、修改、删除和执行。

3．掌握触发器的创建、删除、修改及其使用方法。

4．掌握触发器的功能。

一、利用shiyan14.sql完成下列内容。

shiyan14.sql脚本：

/****** Object:  Table [dbo].[c]    Script Date: 2017/5/16 10:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[c](
[cno] [char](2) NOT NULL,
[cn] [char](10) NOT NULL,
[ct] [tinyint] NULL,
PRIMARY KEY CLUSTERED 
(
[cno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[s]    Script Date: 2017/5/16 10:51:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[s](
[sno] [char](2) NOT NULL,
[sn] [char](8) NOT NULL,
[sex] [char](2) NULL,
[age] [tinyint] NULL,
[dept] [char](10) NULL,
PRIMARY KEY CLUSTERED 
(
[sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[sc]    Script Date: 2017/5/16 10:51:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sc](
[sno] [char](2) NOT NULL,
[cno] [char](2) NOT NULL,
[score] [tinyint] NULL,
PRIMARY KEY CLUSTERED 
(
[sno] ASC,
[cno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


GO
SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[t]    Script Date: 2017/5/16 10:51:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t](
[tno] [char](2) NOT NULL,
[tn] [char](8) NOT NULL,
[sex] [char](2) NULL,
[age] [tinyint] NULL,
[prof] [char](10) NULL,
[sal] [smallint] NULL,
[comm] [smallint] NULL,
[dept] [char](10) NULL,
PRIMARY KEY CLUSTERED 
(
[tno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tc]    Script Date: 2017/5/16 10:51:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tc](
[tno] [char](2) NOT NULL,
[cno] [char](2) NOT NULL,
[evaluation] [char](20) NULL,
PRIMARY KEY CLUSTERED 
(
[tno] ASC,
[cno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c1', N'程序设计  ', 60)
INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c2', N'微机原理  ', 80)
INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c3', N'数字逻辑  ', 60)
INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c4', N'数据结构  ', 80)
INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c5', N'数据库    ', 60)
INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c6', N'编译原理  ', 60)
INSERT [dbo].[c] ([cno], [cn], [ct]) VALUES (N'c7', N'操作系统  ', 60)
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's0', N'王青山  ', N'男', 19, N'计算机    ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's2', N'钱尔    ', N'男', 18, N'信息      ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's3', N'张晓明  ', N'男', 18, N'信息      ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's4', N'李思    ', N'男', 21, N'自动化    ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's5', N'周武    ', N'男', 19, N'计算机    ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's6', N'吴丽    ', N'女', 20, N'自动化    ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's7', N'范思萌  ', N'女', 18, N'信息      ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's8', N'孙珊    ', N'女', 20, N'信息      ')
INSERT [dbo].[s] ([sno], [sn], [sex], [age], [dept]) VALUES (N's9', N'张海涛  ', N'男', 19, N'信息      ')
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's0', N'c1', 90)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's0', N'c2', 85)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's2', N'c1', NULL)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's2', N'c5', 67)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's2', N'c6', 80)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's3', N'c1', 75)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's3', N'c2', 70)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's3', N'c4', 85)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's4', N'c1', 93)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's4', N'c2', 85)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's4', N'c3', 83)
INSERT [dbo].[sc] ([sno], [cno], [score]) VALUES (N's5', N'c2', 89)
INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't1', N'李力    ', N'男', 47, N'教授      ', 1800, 3000, N'计算机    ')
INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't2', N'王平    ', N'女', 28, N'讲师      ', 850, 1200, N'信息      ')
INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't3', N'刘伟    ', N'男', 30, N'讲师      ', 900, 1200, N'计算机    ')
INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't4', N'张雪    ', N'女', 51, N'教授      ', 1900, 3000, N'自动化    ')
INSERT [dbo].[t] ([tno], [tn], [sex], [age], [prof], [sal], [comm], [dept]) VALUES (N't5', N'张兰    ', N'女', 39, N'副教授    ', 1300, 2000, N'信息      ')
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't1', N'c1', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't1', N'c4', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't2', N'c5', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't3', N'c1', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't3', N'c5', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't4', N'c2', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't4', N'c3', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't5', N'c5', NULL)
INSERT [dbo].[tc] ([tno], [cno], [evaluation]) VALUES (N't5', N'c7', NULL)

ALTER TABLE [dbo].[sc]  WITH CHECK ADD FOREIGN KEY([cno])
REFERENCES [dbo].[c] ([cno])
GO
ALTER TABLE [dbo].[sc]  WITH CHECK ADD FOREIGN KEY([sno])
REFERENCES [dbo].[s] ([sno])
GO

ALTER TABLE [dbo].[tc]  WITH CHECK ADD FOREIGN KEY([tno])
REFERENCES [dbo].[t] ([tno])
GO
ALTER TABLE [dbo].[tc]  WITH CHECK ADD FOREIGN KEY([cno])
REFERENCES [dbo].[c] ([cno])
GO

1．创建并使用存储过程，注意创建存储过程前一定要先用IF EXISTS判断一下是否已创建同名的存储过程，若有先删除在创建。

①　创建一存储过程利用学生姓名查询该生选修的课程名、成绩、以及任课教师姓名（涉及的表可能有s、sc、t、c、tc）,并调用存储过程查询王青山的选修的课程名、成绩以及任课教师姓名，结果如图所示。

 

②　创建一存储过程利用系名查询某系的学生的最大年龄和最小年龄。调用存储过程查询信息系的学生最大年龄和最小年龄。（涉及的表可能有s）

 

③　创建一存储过程利用学生姓名和课程名检索该生该课程的成绩，涉及的表可能有s,sc,c。调用该存储过程查询王青山程序设计的成绩，结果如下图所示。

 

创建存储过程：

if exists(select name from sysobjects where name='pro_qscore' and type='p')

drop proc pro_qscore

go

CREATE proc [dbo].[pro_qscore]

   @sname_in char(10),

   @cname_in char(10),

   @grade_out tinyint output

as

   select @grade_out=score

   from s,sc,c

   where [email protected]_in

   and  s.sno=sc.sno

   and sc.cno=c.cno

   and [email protected]_in

Go

调用存储过程：

declare @sname_in char(10),@cname_in char(10),@grade_out tinyint

set @sname_in='王青山'

set @cname_in='程序设计'

exec pro_qscore @sname_in,@cname_in,@grade_out output

select @sname_in 姓名,@cname_in 课程,@grade_out 成绩 

④　查询某系的教师人数、平均年龄和学生人数。

 

创建存储过程：

if exists(select name from sysobjects where name='pro_dept_count' and type='p')

drop proc pro_dept_count

go

create proc pro_dept_count

   @dept_in char(10)

as

   select a.dept 系别,平均年龄,学生人数

   from (select dept,count(tno) as 教师人数,avg(age) as 平均年龄 from t group by dept) a,(select dept,count(sno) 学生人数 from s group by dept) b

   where a.dept=b.dept and [email protected]_in

go

调用存储过程：

declare @dept_in char(10)

set @dept_in='计算机'

exec pro_dept_count @dept_in

⑤　利用教师姓名和课程名检索该教师该课的课程名、课时数、选课人数、平均成绩、最高成绩、并查询教师“张雪”的“微机原理”课程的情况记录及教师“张朋”的“数据库”课程，结果如图所示。

 

 

创建存储过程：

if exists(select name from sysobjects where name='pro_tname_cname' and type='p')

drop proc pro_tname_cname

go

create proc pro_dept_count

   @tname_in char(10),

   @cname_in char(10),

   @student_sum int output,

   @grade_avg int output,

   @grade_max int output

as

   select @student_sum=count(sno),@grade_avg=avg(Score),@grade_max=max(score)

   from t,c,sc,tc

   where sc.cno=c.cno and c.cno=tc.cno

   and tc.tno=t.tno and [email protected]_in

   and [email protected]_in

   group by tn,cn

Go

调用存储过程：

declare @tname_in char(10), @cname_in char(10),

   @student_sum int,@grade_avg int,@grade_max int

set @tname_in='张雪'

set @cname_in='微机原理'

exec pro_dept_count @tname_in,@cname_in,@student_sum output,@grade_avg output,@grade_max output

if (@student_sum is null)

print'没有学生选微机原理这门课程，故总课时数，选课人数，平均成绩等统统为0'

else

select @tname_in 教师姓名,@cname_in 学生姓名,@student_sum 选课总人数,@grade_avg 平均成绩,@grade_max 总成绩

 

2．创建并使用触发器，注意创建触发器前一定要先用IF EXISTS判断一下是否已创建同名的触发器，若有先删除在创建。

①　为表sc创建一触发器：当插入或修改一个记录时，确保此记录的成绩在0~100分之间。（思路：新的数据行就会被插入inserted表，通过检查该表判断插入的行是否在0~100之间，如果在正常插入，否则不做插入，回滚至插入的状态。）

l 插入数据行('s5','c1','101')，是否弹出如图所示的错误。请问该行数据是否插入到sc表中

 

l 将s5的c1成绩更新至101,能否更新成功？

 

if exists(select name from sysobjects where name='score_sc_tri' and type='tr')

drop trigger score_sc_tri

go

create trigger score_sc_tri

on sc for insert,update

as

  declare @score_in tinyint

  select @score_in=score from inserted

  if @score_in<0 or @score_in>100

  begin

     print '成绩超出0-100！请重新输入.'

 rollback transaction

  end

  go

insert into sc values ('s5','c1','101')　/*触发器**，插入失败*/

Update sc set score=101 where sno='s5' and cno='c1'

/*触发器**，更新失败*/

②　为数据库表T创建一触发器：当职称从“讲师”晋升为“副教授”时，津贴自动增加500元，从“副教授”晋升为“教授”时，岗位津贴自动增加900元。

更新前：

 

更新后：

 

 

if exists (select name from sysobjects where name='ut' and type='tr')

drop trigger ut

go

create trigger ut

on t for update

as

declare @prof_old char(10)

declare @prof_new char(10)

declare @tno char(2)

select @prof_old=prof from deleted

select @prof_new=prof from inserted

select @tno=tno from deleted

if @prof_old='讲师' and @prof_new='副教授'

begin

   update t set comm=comm+500 where [email protected]

end

if @prof_old='副教授' and @prof_new='教授'

begin

   update t set comm=comm+900 where [email protected]

end

go

update t set prof='副教授' where tn='刘伟'