实验六 触发器与存储过程

1、SQL触发器：删除学生数据

题目

#创建触发器，删除学生数据时，将删除的数据备份到数据表student_bf，以备误删除时进行数据恢复。

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

代码

#创建触发器，删除学生数据时，将删除的数据备份到数据表student_bf，以备误删除时进行数据恢复。
delimiter $$ 
create trigger students
before delete on student
for each row begin
insert into student_bf 
select * 
from student
where sno=old.sno;
end $$

题解

这段 SQL 代码定义了一个名为 “students” 的触发器，它在从 “student” 表中删除一行之前被执行。触发器将为每个被删除的行执行一次。

触发器代码使用 “before delete” 事件，这意味着触发器将在将行从 “student” 表中删除提交到数据库之前执行。

“for each row” 子句指定触发器代码将为从 “student” 表中删除的每一行执行一次。

触发器代码以一个 “insert into” 语句开始，它将一行插入到 “student_bf” 表中。假设这个表已经存在于数据库中。

接下来的 “select” 语句选择了 “student” 表中所有列，其中 “sno” 列的值与 “old.sno” 对象的值匹配。“old” 关键字指的是从 “student” 表中被删除的行。

因此，触发器代码将正在从 “student” 表中删除的行的副本插入到 “student_bf” 表中。这允许数据库在需要时保留已删除行的记录以便以后恢复。

2、SQL触发器：创建成绩表插入触发器

题目

#创建触发器，插入学生成绩时，限制必须在0-100之间。如果不在0-100之间，设置为0.

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

代码

#创建触发器，插入学生成绩时，限制必须在0-100之间。如果不在0-100之间，设置为0.
delimiter $$
create trigger xiaohui
before insert on sc 
for each row 
begin
if new.grade<0 or new.grade>100
then set new.grade=0;
end if;
end $$

题解

这段 SQL 代码定义了一个名为 “xiaohui” 的触发器，它在向 “sc” 表中插入一行之前被执行。触发器将为每个插入操作执行一次。

触发器代码使用 “before insert” 事件，这意味着触发器将在将行插入到 “sc” 表之前执行。

“for each row” 子句指定触发器代码将为每个插入的行执行一次。

触发器代码包含了一个条件语句，它检查将要插入的新行中的 “grade” 列的值是否小于 0 或大于 100。如果是，则将 “grade” 列的值设置为 0。

这样做的目的是确保 “sc” 表中的 “grade” 列的值始终在 0 到 100 的范围内，以避免出现无效的成绩。

需要注意的是，“new” 是一个隐含的对象，它指向触发器所关联的操作（例如，INSERT 操作）中正在被插入的行。在触发器中，“new” 对象包含了正在被插入的行的所有列的值。可以通过在触发器中引用 “new” 对象的列名来访问这些值，例如 “new.column_name”。

此触发器的目的是在插入新行之前检查 “grade” 列的值，并在需要时将其设置为 0。

3、 SQL存储过程：查询订单

题目

#创建存储过程：total_order(IN orderid INT , OUT totals NUMERIC(9,2), OUT cid INT)

要求：根据输入的订单编号(order_id)，返回订单总金额(total_money),顾客编号(customer_id)。

订单表：orders如下：

代码

#创建存储过程：total_order(IN orderid INT , OUT totals NUMERIC(9,2), OUT cid INT)
create procedure total_order(
in orderid INT,out totals NUMERIC(9,2),out cid INT)
select total_money,customer_id into totals,cid
from orders
where orderid = order_id;

题解

这段 SQL 代码定义了一个名为 “total_order” 的存储过程，它有一个输入参数 “orderid” 和两个输出参数 “totals” 和 “cid”。

存储过程的目的是查询 “orders” 表中与输入参数 “orderid” 匹配的订单的总金额和客户 ID，并将这些值赋给输出参数 “totals” 和 “cid”。

在存储过程中，“in” 关键字指定了输入参数 “orderid” 的类型为整数。

“out” 关键字指定了输出参数 “totals” 和 “cid” 的类型分别为 NUMERIC(9,2) 和整数。

存储过程的主体部分是一个 “select” 语句，它从 “orders” 表中选取与输入参数 “orderid” 匹配的订单的总金额和客户 ID，并将这些值赋给输出参数 “totals” 和 “cid”。

需要注意的是，在存储过程中，“into” 关键字用于将查询结果赋给输出参数。

4、SQL存储过程：建立存储过程，查询课程的成绩表

题目

#建立存储过程show_grade：根据学号，课程名参数，返回学生的成绩。

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

代码

#建立存储过程show_grade：根据学号，课程名参数，返回学生的成绩。
create procedure show_grade(
in s CHAR(20),
in c VARCHAR (20),
out g SMALLINT
)
select grade into g
from sc,course
where sc.cno = course.cno
and cname = c and sno = s;

题解

这段 SQL 代码定义了一个名为 “show_grade” 的存储过程，它有两个输入参数 “s” 和 “c”，一个输出参数 “g”。

存储过程的目的是查询与输入参数 “s” 和 “c” 匹配的学生的成绩，并将查询结果赋给输出参数 “g”。

在存储过程中，“in” 关键字指定了输入参数 “s” 和 “c” 的类型分别为 CHAR(20) 和 VARCHAR(20)。

“out” 关键字指定了输出参数 “g” 的类型为 SMALLINT。

存储过程的主体部分是一个 “select” 语句，它从 “sc” 表和 “course” 表中选取与输入参数 “s” 和 “c” 匹配的记录的 “grade” 列的值，并将这个值赋给输出参数 “g”。

需要注意的是，在 “select” 语句中，“and” 连接了 “sc” 表和 “course” 表，并使用了 “where” 子句来限制结果的范围。“sc.cno = course.cno” 确保了只有在 “sc” 表和 “course” 表中的 “cno” 列匹配时，才会返回结果。“cname = c and sno = s” 确保了只有在输入参数 “s” 和 “c” 与 “sc” 表和 “course” 表中的记录匹配时，才会返回结果。

5、 SQL存储函数：查询学生的平均分

题目

#建立存储函数show_grade：根据课程号，返回该课程的平均成绩(保留两位小数)。

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

代码

delimiter $$
create function show_grade(ccno char(10))
returns decimal(10,2)              
begin
  return(select avg(grade) from sc
  where cno=ccno);
end $$

题解

这段 SQL 代码定义了一个名为 “show_grade” 的函数，它有一个输入参数 “ccno” 和一个返回值，返回值的类型为 DECIMAL(10,2)。

函数的目的是查询与输入参数 “ccno” 匹配的课程的平均成绩，并将结果作为函数的返回值。

在函数中，“in” 关键字指定了输入参数 “ccno” 的类型为 CHAR(10)。

“returns” 关键字指定了函数的返回值类型为 DECIMAL(10,2)。

函数的主体部分是一个 “select” 语句，它从 “sc” 表中选取与输入参数 “ccno” 匹配的记录的 “grade” 列的平均值，并将这个值作为函数的返回值。

需要注意的是，在 “select” 语句中，“where” 子句限制了结果的范围，只有在 “sc” 表中的 “cno” 列匹配输入参数 “ccno” 时，才会返回结果。