--创建学生表create table XS_543 ( XH char(6) not null , XM varchar2(20) not null, ZYM varchar2(10), XB char(4) default '男', CSSJ date, ZXF number(2), BZ varchar2(100), constraint pk_xh primary key(xh));
--创建课程表
create table KC_543( KCH char(3) not null , KCM varchar2(20) not null , KKXQ number(1) default 1 , XS number(2), XF number(1), constraint pk_kch primary key (kch), constraint ck_kkxq check(KKXQ between 1 and 8));
--创建成绩表
create table XS_KC_543( XH char(6) not null , KCH char(3) not null , CJ number(3), constraint pk_xh_kch primary key (XH,KCH), constraint fk_xh foreign key (xh) references xs_543(xh), constraint fk_kch foreign key (kch) references kc_543(kch));
--插入数据
insert into XS_543 values('061101','王林', '计算机','男',to_date('1986-2-01','yyyy-mm-dd'),50,null);insert into XS_543 values('061102','程明', '计算机','男',to_date('1987-2-10','yyyy-mm-dd'),50,null);insert into XS_543 values('061103','王燕', '计算机','女',to_date('1985-1-06','yyyy-mm-dd'),50,null);insert into XS_543 values('061104','韦10严平','计算机','男',to_date('1986-8-26','yyyy-mm-dd'),50,null);insert into XS_543 values('061106','李方方','计算机','男',to_date('1986-11-20','yyyy-mm-dd'),50,null);insert into XS_543 values('061107','李明', '计算机','男',to_date('1986-5-01','yyyy-mm-dd'),54,'提前修完数据结构');insert into XS_543 values('061108','林一帆','计算机','男',to_date('1985-8-05','yyyy-mm-dd'),50,'已提前修完一门课');insert into XS_543 values('061109','张强民','计算机','男',to_date('1984-8-11','yyyy-mm-dd'),50,null);insert into XS_543 values('061110','张蔚', '计算机','女',to_date('1987-7-22','yyyy-mm-dd'),50,'三好学生');insert into XS_543 values('061111','赵琳', '计算机','女',to_date('1986-3-18','yyyy-mm-dd'),50,null);insert into XS_543 values('061113','严红', '计算机','女',to_date('1985-8-11','yyyy-mm-dd'),48,'一门课不及格');insert into XS_543 values('061201','王敏', '通信工程', '男',to_date('1984-6-10','yyyy-mm-dd'),42,null);insert into XS_543 values('061202','王林', '通信工程', '男',to_date('1985-1-29','yyyy-mm-dd'),42,null);insert into XS_543 values('061203','王玉民','通信工程','男',to_date('1986-3-26','yyyy-mm-dd'),42,null);insert into XS_543 values('061204','马琳琳','通信工程','女',to_date('1984-2-10','yyyy-mm-dd'),42,null);insert into XS_543 values('061206','李计', '通信工程','女',to_date('1985-9-20','yyyy-mm-dd'),42,null);insert into XS_543 values('061210','李红庆','通信工程','女',to_date('1985-5-10','yyyy-mm-dd'),44,'提前修完一门课');insert into XS_543 values('061216','孙祥欣', '通信工程','女',to_date('1984-3-09','yyyy-mm-dd'),42,null);insert into XS_543 values('061218','孙研', '通信工程','男',to_date('1986-10-9','yyyy-mm-dd'),42,null);insert into KC_543 values('101','计算机基础',1,80,5);insert into KC_543 values('102','程序设计语言',2,68,4);insert into KC_543 values('206','离散数学',2,68,4);insert into KC_543 values('208','数据结构',5,68,4);insert into KC_543 values('209','操作系统',6,68,4);insert into KC_543 values('210','计算机原理',7,85,5);insert into KC_543 values('212','数据库原理',7,68,4);insert into KC_543 values('301','计算机网络',7,51,3);insert into KC_543 values('302','软件工程',7,51,3);insert into XS_KC_543 values('061101','101',80);insert into XS_KC_543 values('061101','102',78);insert into XS_KC_543 values('061101','206',76);insert into XS_KC_543 values('061103','101',62);insert into XS_KC_543 values('061103','206',81);insert into XS_KC_543 values('061103','102',70);insert into XS_KC_543 values('061104','101',90);insert into XS_KC_543 values('061104','102',84);insert into XS_KC_543 values('061104','206',65);insert into XS_KC_543 values('061102','102',78);insert into XS_KC_543 values('061102','206',78);insert into XS_KC_543 values('061107','101',78);insert into XS_KC_543 values('061107','102',80);insert into XS_KC_543 values('061107','206',68);insert into XS_KC_543 values('061108','101',85);insert into XS_KC_543 values('061108','102',64);insert into XS_KC_543 values('061108','206',87);insert into XS_KC_543 values('061109','101',66);insert into XS_KC_543 values('061109','102',83);insert into XS_KC_543 values('061109','206',70);insert into XS_KC_543 values('061111','206',76);insert into XS_KC_543 values('061113','101',63);insert into XS_KC_543 values('061113','102',79);insert into XS_KC_543 values('061113','206',60);insert into XS_KC_543 values('061201','101',80);insert into XS_KC_543 values('061202','101',65);insert into XS_KC_543 values('061203','101',87);insert into XS_KC_543 values('061204','101',91);insert into XS_KC_543 values('061210','101',76);commit;
declare v_xh xs_kc_543.xh%type; v_kch xs_kc_543.kch%type; v_cj xs_kc_543.cj%type; rank char(2);begin v_xh:=&v_xh; v_kch:=&v_kch; select cj into v_cj from xs_kc_543 where xh=v_xh and kch=v_kch; case v_cj/10
--上次这个地方出错了
when 9 then rank:='a'; when 8 then rank:='b'; when 7 then rank:='c'; when 6 then rank:='d'; when 5 then rank:='e'; end case;dbms_output.put_line(rank);end;
转载于:https://www.cnblogs.com/hgc-bky/p/5588998.html
相关资源：JAVA上百实例源码以及开源项目