练习题

触发器练习题

image-20220827065009335

1、写一个存储过程，添加一条学生信息，只允许有一个参数，需要判断添加的班级否存在，不存在抛出异常。

1
create or

2、写一存储过程，分配宿舍，传入一个学生ID和床位编号，返回“学生名入住宿舍名”，并变更宿舍信息（包括床位的状态等）

1

3、写一个存储过程，根据学生编号将一个学生的所有信息从数据库中删除，学生信息包含班级，成绩，宿舍等信息

1

4、有一个学生班级记录表，字段如下：

image-20220827065154171

当前学生报到或者转变班级时，向clazz_history表中添加一条记录(触发器实现) 

1

当我们往empa（emp表的复制表）表中插入数据时，不能插入入职时间大于sysdate的时间

1

编写一个触发器实现如下功能：
对修改emp表职工薪金的操作进行合法性检查：
a) 修改后的薪金要大于修改前的薪金
b) 工资增量不能超过原工资的10%
c) 目前没有所属部门的职工不能涨工资
d)违反以上限制抛出相应异常

1

例：创建一个empa表的日志表empa_rizhi，创建一个触发器，当empa表中的数据被删除时，将这条数据保存到empa_rizhi中

1

例：创建一个（级联删除功能）触发器，当我们直接删除dept表中数据时，不会告诉我们违反外键约束，并且能删除成功

1

stu表（学生表）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create table stu(
   sno number(11) primary key, --学号
   sname varchar2(30), --姓名
   sage number(3), --年龄
   ssex varchar2(3) --性别
);
insert into stu 
select 1,'张三',18,'男' from dual
union all
select 2,'李四',28,'男' from dual
union all
select 3,'王五',19,'男' from dual
union all
select 4,'赵六',18,'女' from dual;
teacher表(老师表)
create table teachert(
   tno number(11) primary key, --老师编号
   tname varchar2(30) --老师名称
);
insert into teachert
select 1,'张小平' from dual
union all
select 2,'李明' from dual
union all
select 3,'smith' from dual
union all
select 4,'王小乐' from dual
union all
select 5,'孙小东' from dual
union all
select 6,'张豪' from dual;

corse表（课程表）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table coursed(
   cno number(11) primary key, --课程号
   cname varchar2(40),   --课程名称
   tno number(11) --老师编号
);
insert into coursed
select 1,'语文',1 from dual
union all
select 2,'英语',3 from dual
union all
select 3,'oracle',2 from dual
union all
select 4,'python',5 from dual
union all
select 5,'etl',6 from dual
union all
select 6,'数学',4 from dual;

sc表(选课表)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table scd(
   sno number(11), --学号
   cno number(11), --课程号
   score number(3)  --成绩
);
insert into scd 
select 1,2,70 from dual
union all 
select 1,3,80 from dual
union all
select 1,5,90 from dual
union all
select 2,1,76 from dual
union all
select 2,2,88 from dual
union all
select 2,6,30 from dual
union all
select 3,1,70 from dual
union all
select 3,2,65 from dual
union all
select 3,6,75 from dual
union all
select 3,4,79 from dual
union all
select 4,2,77 from dual;

sc_number表（选课统计表）

1
2
3
4
5
6
7
8
9
10
11
create table sc_number(
   sno number(10),  --学号
   nu number(3),   --选课数
   score number(4,1)  --平均成绩
);
drop table scd
select * from stu;
select * from scd;
select * from coursed;
select * from teachert;
select * from sc_number;

1.向scd表添加数据时，修改sc_number表中的选课数，如果sc_number中没有则新添加一条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create or replace trigger tri_0722_o before insert on scd for each row
declare
a number;
b number;
c number;
begin 
  select count(1) into b from scd where sno=:new.sno;  --查询是原本有多少选课数
    if b=0 then 
    insert into sc_number values(:new.sno,1,:new.score);   --在scd表中新的一条记录
    else 
    select (sum(nvl(score,0))+:new.score)/(b+1) into c from scd where sno=:new.sno;
    select count(1) into a from sc_number where sno=:new.sno;
    if a=0 then 
    insert into sc_number values(:new.sno,b+1,c);      --在scd表中存在其他课程记录
    else
    update sc_number set nu=nu+1,score=c where sno=:new.sno;
    end if;  
  end if;
end;

select * from scd;
insert into scd values(30,2,100);

2.当删除scd表时，修改sc_number表中的选课数

3.当修改sc表数据时，如果修改了学号sno，修改sc_number中相应的选课数，否则打印’学生的选课信息已更改’

4.不能删除成绩是70分以上的学生的选课信息

5.向stu表中添加数据时，年龄不能超过30岁

6.删除学生信息时，同时删除sc表和sc_number表中的数据

7.修改emp表中的工资时，只能增加不能减少

8.当前删除emp表中的数据时，将数据备份到emp_log表中
emp_log建表语句：

9.在每天的早上9点之前和6点之后，不允许对emp表中的数据进行添加,修改,删除

10.当emp表中的数据变更时，统计各部门的人数，和员工的平均工资，总工资，
并将统计结果保存到统计表emp_cal表中

​

11.创建一个包，包中包含以下功能
1.函数，传入一个员工编号，返回员工的信息
2.过程，根据员工编号，修改员工的工资 （两个参数，一个员工编号，一个员工工资）
3.函数，根据部门编号，返回该部门的人数
4.过程，根据部门编号，输出该部门下所有员工的详细信息 (一个部门编号是传入参数，输出参数，集合类型，需要在包声名一个类型)

​
​
​