写在前面
写着练手的，若有错，欢迎大家指正！
开始之前准备
- 数据库：oracle
- 工具：Oracle SQL Developer（cmd也可操作，大家随意）
1、设计目标
1. 用户注册：注册时检查名字是否重复
2. 限制注册时段
3. 用户登录：登录出错时，显示对应错误信息
4.用户数据库发生操作时，打印相应操作信息
5. 分页查询：分页查询学生信息
6. 为学生涨学分，及从最低学分调起每人涨5%,但学分总共不能超过350,请计算涨学分的人数和涨学分后的学分总额,并输出涨学分人数及学分总额
7. 按照学生入学年份分别统计学生人数
8. 学分数据确认(触发器)
注：5–8应在用户登陆后才可操作
2、关系表
用户表（usertable）
| 列名 | 类型 | 长度 | 是否为空 | 约束 |
|---|---|---|---|---|
| 用户名（uname） | VARCHAR2 | 20 | not null | primary key |
| 用户密码（upassword） | number | 8 | not null |
用户登录状态记录表
| 列名 | 类型 | 长度 | 是否为空 | 约束 |
|---|---|---|---|---|
| （登录时间）u_date | date | 默认 | primary key | |
| （状态值）uinfo | number | 2 | not null | |
| （状态）ustatus | VARCHAR2 | 20 |
学生表（students）
| 列名 | 类型 | 长度 | 是否为空 | 约束 |
|---|---|---|---|---|
| 学号(student_id) | number | 10 | not null | primary key |
| 学生姓名(student_name) | varchar2 | 8 | not null | |
| 性别(student_sex) | varchar2 | 2 | ||
| 入学年份(school_year) | date | 默认 | not null | |
| 年级(grade) | varchar2 | 8 | not null | |
| 课程号(course_no)：1001，1002，1003，1004 | number | 5 | not null | |
| 学分(credits) | number | 2 | not null |
成绩表(scores)
| 列名 | 类型 | 长度 | 是否为空 | 约束 |
|---|---|---|---|---|
| 成绩ID(cid) | number | 5 | Not null | primary key |
| 课程号(course_no):1001，1002，1003，1004 | number | 5 | Not null | |
| 学号(student_id) | number | 10 | Not null | |
| 课程名(course_name) | varchar2 | 10 | Not null | |
| 成绩(score) | number | 3 | Not null | |
| 任教老师(teacher) | varchar2 | 8 |
3、建表
1、用户表
/*用户表*/
create table usertable
(
uname VARCHAR2(20) not null primary key,
upassword number(8) not null,
);
2、用户登录状态记录表
/*用户登录状态记录*/
create table userinfo
(
u_date date primary key,
uinfo number(2) not null,
ustatus VARCHAR2(20)
);
3、学生表
注：注意时间格式
/*插入数据前应更改日期格式*/
select *from v$nls_parameters;
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
/*创建学生表*/
create table students
(
student_id number(10) not null primary key,
student_name varchar2(8) not null,
student_sex varchar2(2),
school_year date not null,
grade varchar2(8) not null,
course_no number(5) not null,
credits number(2) not null
);
/*插入数据*/
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10001,'Tom','男','2016-01-01','大一',1001，20);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10002,'Jack','男','2016-01-05','大一',1001，21);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10003,'rose','女','2016-02-01','大一',1003，28);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10004,'SMITH','男','2015-01-11','大二',1001，20);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10005,'ALLEN','男','2016-02-11','大一',1002，25);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10006,'JONES','男','2015-02-18','大二',1001，30);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10007,'MARTIN','女','2014-03-01','大三',1004，25);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10008,'MAR','女','2014-02-08','大三',1004，40);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10009,'ADMN','女','2014-02-09','大三',1003，40);
insert into students(student_id,student_name,student_sex,school_year,grade,course_no,credits) values (10010,'NIKE','男','2014-02-18','大三',1002，31);
commit;
4、成绩表
/*创建成绩表*/
create table scores
(
cid number(5) not null primary key,
course_no number(5) not null,
student_id number(10) not null,
course_name varchar2(10) not null,
score number(3) not null,
teacher varchar2(8)
)
/*插入数据*/
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(1,1001,10001,'操作系统',78,'李诚');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(2,1002,10001,'高等数学',80,'张旭');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(3,1003,10001,'数据电路',65,'王萍');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(4,1004,10001,'计算机导论',91,'刘冰');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(5,1004,10002,'计算机导论',88,'刘冰');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(6,1002,10002,'高等数学',91,'张旭');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(7,1003,10002,'数据电路',91,'王萍');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(8,1001,10003,'操作系统',78,'李诚');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(9,1002,10004,'高等数学',80,'张旭');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(10,1003,10006,'数据电路',65,'王萍');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(11,1004,10005,'计算机导论',91,'刘冰');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(12,1001,10006,'操作系统',87,'李诚');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(13,1002,10005,'高等数学',90,'张旭');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(14,1003,10004,'数据电路',67,'王萍');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(15,1004,10003,'计算机导论',54,'刘冰');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(16,1001,10007,'操作系统',60,'李诚');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(17,1002,10008,'高等数学',58,'张旭');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(18,1003,10009,'数据电路',77,'王萍');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(19,1004,10010,'计算机导论',82,'刘冰');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(20,1001,10010,'操作系统',60,'李诚');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(21,1002,10009,'高等数学',58,'张旭');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(22,1003,10008,'数据电路',77,'王萍');
insert into scores(cid,course_no,student_id,course_name,score,teacher) values(23,1004,10007,'计算机导论',82,'刘冰');
commit;
4、实现
1. 用户注册：注册时检查名字是否重复
set serveroutput on;
create or replace procedure registered(u_name in varchar2,pass in number)
as
username number;
userpass number;
begin
select count(*) into username from usertable where uname=u_name;
if username=1 then
dbms_output.put_line('用户名已存在，请重新输入');
else
insert into usertable(uname,upassword) values (u_name,pass);
commit;
dbms_output.put_line('注册成功');
end if;
end;
效果图：
2. 使用触发器，限制注册时段
/*限制注册时段*/
create or replace trigger banregistered
before insert
on usertable
begin
if to_char(sysdate,'day') in ('星期六','星期日') or to_number(to_char(sysdate,'hh24'))not between 12 and 20 then
raise_application_error(-20001,'禁止在非工作时间注册');
end if;
end banregistered;
效果图：
3.用户登录：登录出错时，显示对应错误信息
create or replace procedure Login(name in varchar2,pass in number)
as
username number;
userpass number;
begin
select count(*) into username from usertable where uname=name;
select count(*) into userpass from usertable where upassword=pass and uname=name;
if username=1 and userpass=1 then
insert into userinfo(u_date,uinfo,ustatus) values(sysdate,1,'已登录');
commit;
dbms_output.put_line('登录成功');
elsif username=1 and userpass=0 then
dbms_output.put_line('密码错误');
insert into userinfo(u_date,uinfo,ustatus) values(sysdate,0,'未登录');
commit;
else
dbms_output.put_line('用户名错误');
insert into userinfo(u_date,uinfo,ustatus) values(sysdate,0,'未登录');
commit;
end if;
end;
效果图：
4. 触发器：用户数据库发生操作时，打印相应操作信息
create or replace trigger trg_user
before insert or update or delete on usertable
declare
v_now varchar2(30);
begin
v_now:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
case
when INSERTING THEN
DBMS_OUTPUT.PUT_LINE(v_now||'对usertable表进行了insert操作');
when updating then
dbms_output.put_line(v_now||'对usertable表进行了update操作');
when deleting then
dbms_output.put_line(v_now||'对usertable表进行了delete操作');
end case;
end;
5.分页查询：分页查询学生信息
/*1、创建包规则，定义游标和分页过程*/
create or replace package sp_package is type tesc_cursor is ref cursor;
procedure feny(
spname in varchar2,--分页的表名
spsize in number,--每页显示的记录数
sppagenow in number,--当前页
spzongjls out number,--总记录数
spzongys out number,--总页数
spyoub out tesc_cursor --游标
);
end sp_package;
/*2、创建包体*/
create or replace package body sp_package is
procedure feny(
spname in varchar2,--分页的表名
spsize in number,--每页显示的记录数
sppagenow in number,--当前页
spzongjls out number,--总记录数
spzongys out number,--总页数
spyoub out tesc_cursor --游标
)is
/*定义部分:定义一个sql语句 字符串*/
v_sql varchar2(1000);
/*定义两个整数:分别用来计算总记录数和总页数*/
v_begin number:= (sppagenow-1)*spsize;
v_end number:= sppagenow*spsize;
begin
/*执行部分*/
v_sql:='select STUDENT_ID,STUDENT_NAME,GRADE,CREDITS
from (select tab.*,rownum rn
from (select * from '||spname ||') tab
where rownum <='||v_end||') temp
where temp.rn >'||v_begin;
/*打开游标*/
open spyoub for v_sql;
/*计算总记录数*/
v_sql:='select count(*) from '||spname;
/*执行sql语句并把返回的值赋给总记录数*/
execute immediate v_sql into spzongjls;
/*计算总页数*/
if mod(spzongjls,spsize ) = 0 then
spzongys :=spzongjls/spsize ;
else
spzongys :=spzongjls/spsize+1 ;
end if;
end;
end;
/*3、编写一个过程实现分页*/
/*
*tablename：表名
*pagesize：每页数据量
*sppagenow：指定页数
*/
create or replace procedure page (tablename varchar2,pagesize number,sppagenow number) is
ptotal number(5);--总记录数
pagetotal number(5);--总页数
spyoub sp_package.tesc_cursor; --游标
student_id STUDENTS.STUDENT_ID%type;
student_name STUDENTS.STUDENT_NAME%type;
student_grade STUDENTS.GRADE%type;
student_credits STUDENTS.CREDITS%type;
/*u_info用于登录验证*/
u_info number;
begin
/*登录验证*/
select uinfo into u_info from (select * from userinfo order by u_date desc) where Rownum=1;
/*验证失败则退出*/
if u_info=0 then return;
/*验证成功则继续操作*/
else
sp_package.feny(tablename,pagesize,sppagenow,ptotal,pagetotal,spyoub);
loop
fetch spyoub into student_id,student_name,student_grade,student_credits;
exit when spyoub%notfound;
dbms_output.put_line('学号：'||student_id||'***'||'姓名：'||student_name||'***'||'年级:'||student_grade||'***'||'学分：'||student_credits);
end loop;
dbms_output.put_line('总记录：'||ptotal);
dbms_output.put_line('总页数：'||pagetotal);
dbms_output.put_line('当前页数：'||sppagenow);
end if;
end;
/*调用分页过程*/
exec page('students',5,1);
效果图：
6. 为学生涨学分，及从最低学分调起每人涨5%,但学分总共不能超过350,请计算涨学分的人数和涨学分后的学分总额,并输出涨学分人数及学分总额
create or replace procedure add_credit
as
cursor cur is select student_id,credits from students order by credits;
pno students.student_id%type;
pcr students.credits%type;
/*涨学分的人数*/
countEmp number:=0;
/*涨后的学分总额*/
creditsTotal number;
/*u_info用于登录验证*/
u_info number;
begin
/*登录验证*/
select uinfo into u_info from (select * from userinfo order by u_date desc) where Rownum=1;
/*验证失败则退出*/
if u_info=0 then return;
/*验证成功则继续操作*/
else
/*得到学分总额的初始值*/
select sum(credits) into creditsTotal from students;
open cur;
loop
/*学分总额>350*/
exit when creditsTotal>350;
/*取一个学生涨学分*/
fetch cur into pno,pcr;
exit when cur%notfound;
/*涨学分*/
if creditsTotal+pcr*1.05<350 then
update students set credits=credits*1.05 where student_id=pno;
/*人数+1*/
countEmp:=countEmp+1;
/* 涨后的学分总额=涨前的学分总额+pcr*0.05 */
creditsTotal:=creditsTotal+pcr*0.05;
end if;
end loop;
close cur;
commit;
dbms_output.put_line('人数：'||countEmp);
dbms_output.put_line('涨后的学分总额：'||creditsTotal);
end if;
end;
/*测试*/
exec add_credit;
效果图：
7. 按照学生入学年份分别统计学生人数
create or replace procedure stdenttotal
as
cursor cemp is select to_char(school_year,'yyyy') from students;
schooldate varchar2(4);
count14 number := 0;
count15 number := 0;
count16 number := 0;
/*u_info用于登录验证*/
u_info number;
begin
/*登录验证*/
select uinfo into u_info from (select * from userinfo order by u_date desc) where Rownum=1;
/*验证失败则退出*/
if u_info=0 then return;
/*验证成功则继续操作*/
else
open cemp;
loop
fetch cemp into schooldate;
exit when cemp%notfound;
if schooldate='2014' then count14:=count14+1;
elsif schooldate='2015' then count15:=count15+1;
else count16:=count16+1;
end if;
end loop;
close cemp;
dbms_output.put('总人数：');
dbms_output.put_line(count14+count15+count16);
dbms_output.put('2014年入学人数：');
dbms_output.put_line(count14);
dbms_output.put('2015年入学人数：');
dbms_output.put_line(count15);
dbms_output.put('2016年入学人数：');
dbms_output.put_line(count16);
end if;
end;
/*测试*/
exec stdenttotal;
效果图：
8. 学分数据确认(触发器)
/*
*数据确认
*涨后的学分不能低于涨前的学分
*/
create or replace trigger checkcredits
before update
on students
for each row
begin
if :new.credits <:old.credits then
raise_application_error(-20002,'涨后的学分不能低于涨前的学分');
end if;
end checkcredits;
/*测试用例*/
UPDATE STUDENTS set CREDITS=21 where STUDENT_ID=10001;
效果图：
