数据库小实验

来源：互联网发布：东莞知路电子有限公司编辑：程序博客网时间：2024/05/16 10:14

问题：

假设要开发一个学生成绩管理系统，实现学生信息课程信息和成绩信息的管理，请结合你了解的成绩管理实际情况，设计四个表：学生、课程、成绩、管理员（具体字段请根据自己的分析来建立），并输入几条测试数据(有一条以自己姓名拼音做为用户名，学号做为密码的记录)，表的创建，及表数据插入生成一个脚本（可以使用plsql developer工具的导出功能）。然后在此基础上完成如下工作：

1、编写一个存储过程实现用户登陆功能（登陆时候要判断用户名是否存在，不存在返回-2；判断密码是否正确，如果密码不正确返回-1；如果用户名密码都正确，根据不同的身份返回对应的值，登陆的身份是学生则返回0、教师返回1、管理员返回2）。

2、根据成绩表数据，分别编写过程和函数，实现根据每个学生选修每个课程的成绩，输出该成绩对应的等级，每次输入一个学号和课程号，如果是90~100分，返回“优”；如果是80~90分，返回“良”；如果是70～80分，返回“中”，如果是60～70，返回“及格”，如果是60以下返回“不及格”。并编写调用代码。

3、编写存储过程，每次输入一个学号，计算该学生的所有课程的平均分，如果是85~100分，返回“优”；如果是75~84分，返回“良”；如果是65~74分，返回“中”；如果是0~64分，返回“差”。

4、编写触发器实现学生表和成绩表、课程表和成绩表之间的级联更新功能。

答案：

prompt create user fantiantian
create user fantiantian identified by admin;
grant connect to fantiantian;
grant create table to fantiantian;
grant unlimited tablespace to fantiantian;
prompt connect fantiantian user

conn fantiantian/admin
prompt PL/SQL Developer import file
prompt Created on 2008年3月7日 by Administrator
set feedback off
set define off

prompt Creating STUDENT...
create table STUDENT
(
SNO NUMBER(10),
SNAME NVARCHAR2(8) not null,
SSEX NVARCHAR2(2),
SAGE NUMBER(10),
SDEPT NVARCHAR2(20)
)
;

prompt Creating COURSE...
create table COURSE
(
CNO NUMBER(10),
CNAME NVARCHAR2(20),
CPNO NUMBER(10),
CREDIT NUMBER(10)

)
;

prompt Creating SC...
create table SC
(
SNO NUMBER(10),
CNO NUMBER(10),
GRADE NUMBER(10)
)
;

prompt Creating USERS...
create table USERS
(
USERNAME NVARCHAR2(20)，
USERPASSWORD NUMBER(10),
USERLEVEL NVARCHAR2(20)
)
;

prompt Loading STUDENT...
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95001, '张力', 'cs', '男', 18);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95002, '李丽', 'is', '女', 19);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95003, '赵海', 'ma', '男', 20);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95004, '张那', 'cs', '女', 17);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95005, '刘晨', 'is', '男', 18);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95006, '刘丹', 'ma', '女', 17);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95007, '刘立', 'cs', '男', 21);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95008, '王江', 'cs', '男', 19);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95009, '高晓', 'is', '男', 20);
insert into STUDENT (SNO, SNAME, SDEPT, SSEX, SAGE)
values (95010, '张丽', 'cs', '女', 19);
commit;
prompt 10 records loaded

prompt Loading COURSE...
insert into COURSE (CNO, CPNO, CREDIT, CNAME)
values (1, 2, 2, '数据库');
insert into COURSE (CNO, CPNO, CREDIT, CNAME)
values (2, 3, 3, '数据结构');
insert into COURSE (CNO, CPNO, CREDIT, CNAME)
values (3, null, 2, '程序设计');
insert into COURSE (CNO, CPNO, CREDIT, CNAME)
values (4, 5, 1, '微机原理');
insert into COURSE (CNO, CPNO, CREDIT, CNAME)
values (5, 3, 1, '汇编语言');
insert into COURSE (CNO, CPNO, CREDIT, CNAME)
values (6, null, 3, '专业英语');
commit;
prompt 6 records loaded

prompt Loading SC...
insert into SC (SNO, CNO, GRADE)
values (95001, 1, 100);
insert into SC (SNO, CNO, GRADE)
values (95002, 2, 98);
insert into SC (SNO, CNO, GRADE)
values (95003, 1, 99);
insert into SC (SNO, CNO, GRADE)
values (95004, 1, 90);
insert into SC (SNO, CNO, GRADE)
values (95001, 3, 89);
insert into SC (SNO, CNO, GRADE)
values (95001, 4, 90);
insert into SC (SNO, CNO, GRADE)
values (95002, 3, null);
insert into SC (SNO, CNO, GRADE)
values (95003, 3, 98);
insert into SC (SNO, CNO, GRADE)
values (95005, 2, 97);
insert into SC (SNO, CNO, GRADE)
values (95006, 3, null);
insert into SC (SNO, CNO, GRADE)
values (95008, 1, null);
insert into SC (SNO, CNO, GRADE)
values (95009, 1, 89);
insert into SC (SNO, CNO, GRADE)
values (95010, 3, 100);
insert into SC (SNO, CNO, GRADE)
values (95001, 6, 100);
commit;
prompt 14 records loaded

prompt Loading USERS...
insert into users (USERNAME,USERPASSWORD,USERLEVEL)
values ( 'fantiantian', 1315925566,'student');
insert into users (USERNAME,USERPASSWORD,USERLEVEL)
values ( 'zhanghong','1315925567','teacher');
insert into users (USERNAME,USERPASSWORD,USERLEVEL)
values ( 'lihong','1315925568','admonistrator');
commit;
prompt 3 records loaded

SQL> start "C:\Documents and Settings\Administrator.CHINA-AEE07668F\桌面\1.sql"
create user fantiantian
create user fantiantian...

用户已创建。

授权成功。

connect fantiantian user
已连接。
PL/SQL Developer import file
Created on 2008年3月7日 by Administrator
Creating STUDENT...
Creating COURSE...
Creating SC...
Creating USERS...
Loading STUDENT...
10 records loaded
Loading COURSE...
6 records loaded
Loading SC...
14 records loaded
Loading USERS...
3 records loaded
SQL> select * from student
2 /

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95001 张力             cs                                       男
        19

     95002 李丽             is                                       女
        20

     95003 赵海             ma                                       男
        21

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95004 张那             cs                                       女
        18

     95005 刘晨             is                                       男
        19

     95006 刘丹             ma                                       女
        18

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95007 刘立             cs                                       男
        22

     95008 王江             cs                                       男
        20

     95009 高晓             is                                       男
        21

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95010 张丽             cs                                       女
        20

     95001 张力             cs                                       男
        19

     95002 李丽             is                                       女
        20

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95003 赵海             ma                                       男
        21

     95004 张那             cs                                       女
        18

     95005 刘晨             is                                       男
        19

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95006 刘丹             ma                                       女
        18

     95007 刘立             cs                                       男
        22

     95008 王江             cs                                       男
        20

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95009 高晓             is                                       男
        21

     95010 张丽             cs                                       女
        20

     95001 张力             cs                                       男
        18

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95002 李丽             is                                       女
        19

     95003 赵海             ma                                       男
        20

     95004 张那             cs                                       女
        17

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95005 刘晨             is                                       男
        18

     95006 刘丹             ma                                       女
        17

     95007 刘立             cs                                       男
        21

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95008 王江             cs                                       男
        19

     95009 高晓             is                                       男
        20

     95010 张丽             cs                                       女
        19

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95010 张丽             cs                                       女
        20

SQL> select * from users;

USERNAME                                 USERPASSWORD
---------------------------------------- ------------
USERLEVEL
----------------------------------------
fantiantian                                1315925566
student

zhanghong                                     1315925567
teacher

lihong                                     1315925568
admonistrator

1.

SQL> edit
已写入 file afiedt.buf

1 create or replace function gradelevel
2 (
3 v_sno in sc.sno%type,
4 v_cno in sc.cno%type
5 )
6 return sc.grade%type
7 is
8 v_gradelevel sc.grade%type;
9 begin
10 select grade into v_gradelevel from sc
11 where sno=v_sno and cno=v_cno;
12 return v_gradelevel;
13* end;
SQL> /
create or replace function gradelevel
*
第 1 行出现错误:
ORA-01031: 权限不足

SQL> show user
USER 为 "FANTIANTIAN"
SQL> conn system as sysdba
已连接。
SQL> edit
已写入 file afiedt.buf

1* grant create any table to FANTIANTIAN;
SQL> /
SQL> show user
USER 为 "SYS"
SQL> conn fantiantian/admin
已连接。
SQL> edit
已写入 file afiedt.buf

1* grant all privileges to fantiantian
SQL> /
SQL> conn fantiantian/admin
已连接。
SQL> edit
已写入 file afiedt.buf

1 create or replace procedure users_login
2 is
3     v_username users.username%type;
4     v_password users.userpassword%type;
5     v_userlevel users.userlevel%type;
6 begin
7     declare cursor mycursor is select * from users;
8 begin
9     if not mycursor%isopen then
10       open mycursor;
11     end if;
12     loop
13         fetch mycursor into v_username,v_password,v_userlevel;
14         exit when mycursor%notfound;
15         if v_username is null then
16           dbms_output.put_line('-2');
17         end if;
18         if v_userlevel = 'student' then
19           dbms_output.put_line('0') ;
20         end if;
21         if v_userlevel = 'teacher' THEN
22           dbms_output.put_line('1') ;
23         end if;
24         if v_userlevel = 'root' THEN
25           dbms_output.put_line('2') ;
26         end if;
27     end loop;
28     exception
29           when login_denied then
30             dbms_output.put_line('-1');
31         close mycursor;
32     end;
33* end users_login;
SQL> /
SQL> set serveroutput on
SQL> execute users_login
0
1
2

2.

SQL> edit
已写入 file afiedt.buf

1 create or replace function gradelevel
2 (
3 v_sno in sc.sno%type,
4 v_cno in sc.cno%type
5 )
6 return sc.grade%type
7 is
8 v_gradelevel sc.grade%type;
9 begin
10 select grade into v_gradelevel from sc
11 where sno=v_sno and cno=v_cno;
12 return v_gradelevel;
13* end;
SQL> /
SQL> insert into sc values(95100,1,67);
SQL> edit
已写入 file afiedt.buf

1   create or replace procedure pro_grade
2   is
3       v_gradelevel sc.grade%type;
4   begin
5       v_gradelevel:=gradelevel(95100,1);
6       if v_gradelevel <=100 and v_gradelevel >=90 then
7         dbms_output.put_line('优');
8       end if;
9       if v_gradelevel < 90 and v_gradelevel >= 80 then
10           dbms_output.put_line('良');
11       end if;
12       if v_gradelevel < 80 and v_gradelevel >= 70 then
13           dbms_output.put_line('中');
14       end if;
15       if v_gradelevel < 70 and v_gradelevel >= 60 then
16           dbms_output.put_line('及格');
17       end if;
18       if v_gradelevel < 60 then
19           dbms_output.put_line('不及格');
20       end if;
21* end pro_grade;
SQL> /
SQL> set serveroutput on
SQL> execute process_grade
及格
SQL> edit
已写入 file afiedt.buf

1 create or replace procedure process_grade
2 (
3     v_sno in sc.sno%type,
4     v_cno in sc.cno%type
5 )
6 as
7     v_grade sc.grade%type;
8     begin
9     select grade into v_grade from sc where sno = v_sno and cno = v_cno ;
10      if v_grade >= 90 and v_grade <= 100 then
11          dbms_output.put_line('优');
12      elsif v_grade >= 80 and v_grade <90 then
13          dbms_output.put_line('良');
14       elsif v_grade >= 70 and v_grade < 80 then
15          dbms_output.put_line('中');
16       elsif v_grade >= 60 and v_grade < 70 then
17          dbms_output.put_line('及格');
18       else
19          dbms_output.put_line('不及格');
20       end if;
21* end process_grade;
SQL> /
SQL> set serveroutput on
SQL> execute process_grade(95100,1);
及格
SQL> spool off

SQL> edit
已写入 file afiedt.buf

1 create or replace procedure avg_grade
2 (
3    v_sno in sc.sno%type
4 )
5 as
6    v_avg_grade number;
7 begin
8    select avg(grade) into v_avg_grade from sc where sno = v_sno ;
9    if v_avg_grade >= 85 and v_avg_grade <= 100 then
10 dbms_output.put_line('优');
11    elsif v_avg_grade >= 75 and v_avg_grade <=84 then
12 dbms_output.put_line('良');
13    elsif v_avg_grade >= 65 and v_avg_grade <= 74 then
14 dbms_output.put_line('中');
15    elsif v_avg_grade >= 0 and v_avg_grade <= 64 then
16 dbms_output.put_line('差');
17    end if;
18* end avg_grade;
SQL> /
SQL> set serveroutput on
SQL> execute avg_grade(95001);
优

create or replace trigger users_login
after update on sc
for each row
begin
if updating ('sno') then
update student set sno = :new.sno where sno = :old.sno;
end if;
if updating ('cno') then
update course set cno = :new.cno where cno = :old.cno;
end if;
end;

部分结果显示：

SQL>update sc set sno=sno+20 where sno='95008';
SQL> select * from sc;

       SNO        CNO      GRADE
---------- ---------- ----------
     95101          1        100
     95002          2         98
     95003          1         99
     95004          1         90
     95101          3         89
     95101          4         90
     95002          3
     95003          3         98
     95005          2         97
     95006          3
     95028          1
SQL> select * from student;

       SNO SNAME            SDEPT                                    SSEX
---------- ---------------- ---------------------------------------- ----
      SAGE
----------
     95007 刘立             cs                                       男
        22

     95028 王江             cs                                       男
        20

     95009 高晓             is                                       男
        21

SQL> update sc set cno=10 where cno=1;
SQL> select * from sc;

       SNO        CNO      GRADE
---------- ---------- ----------
     95101         10        100
     95002          2         98
     95003         10         99
     95004         10         90
     95101          3         89
     95101          4         90
     95002          3
     95003          3         98
     95005          2         97
     95006          3
     95028         10
SQL> select * from course;

       CNO       CPNO     CREDIT CNAME
---------- ---------- ---------- ----------------------------------------
        10          2          2 数据库
         2          3          3 数据结构
         3                     2 程序设计
         4          5          1 微机原理
         5          3          1 汇编语言
         6                     3 专业英语
        10          2          2 数据库
         2          3          3 数据结构
         3                     2 程序设计
         4          5          1 微机原理
         5          3          1 汇编语言

SQL> spool off

0 0