Oracle資料庫建表並用SQL編程分等級

標籤：

--建立學生表
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;

 

Oracle資料庫建表並用SQL編程分等級