文档章节

Oracle---PLSQL案例

我是小个子啊
 我是小个子啊
发布于 2016/05/14 14:10
字数 2190
阅读 111
收藏 3

要求:

    用PLSQL语言编写一个程序。按系(系名)分段统计(成绩小于60分,60-85分, 85分以上)“大学物理”课程各分数段的学生人数,及各系学生的平均成绩。

提示:可建立一张新表,存放统计信息。

实现效果:

原始数据:

drop table sc;
drop table course;
drop table student;
drop table teacher;
drop table dep;

CREATE TABLE DEP
       (DNO NUMBER(2),
        DNAME VARCHAR2(30),
        DIRECTOR NUMBER(4),
        TEL   VARCHAR2(8));

CREATE TABLE TEACHER
       (TNO NUMBER(4),
        TNAME VARCHAR2(10),
        TITLE VARCHAR2(20),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        BONUS  NUMBER(7,2),
   	MGR NUMBER(4),             
        DEPTNO NUMBER(2));

CREATE TABLE student
       (sno NUMBER(6),
        sname VARCHAR2(8),
        sex VARCHAR2(2),
        birth   DATE,
        passwd  VARCHAR2(8),
        dno  NUMBER(2));
        
CREATE TABLE course
       (cno VARCHAR2(8),
        cname VARCHAR2(20),
        credit NUMBER(1),
        ctime  NUMBER(2),
        quota  NUMBER(3));
        
CREATE TABLE sc
       (sno NUMBER(6),
        cno  VARCHAR2(8),
        grade NUMBER(3));        
        
alter table dep add (constraint pk_deptno primary key(dno));
alter table dep add(constraint dno_number_check check(dno>=10 and dno<=50));
alter table dep modify(tel default 62795032);
alter table student add (constraint pk_sno primary key(sno));
alter table student add(constraint sex_check check(sex='男' or sex='女'));
alter table student modify(birth default sysdate);
alter table course add (constraint pk_cno primary key(cno));
alter table sc add (constraint pk_key primary key(cno,sno));
alter table teacher add (constraint pk_tno primary key(tno));
alter table sc add (FOREIGN KEY(cno) REFERENCES course(cno));
alter table sc add (FOREIGN KEY(sno) REFERENCES student(sno));
alter table student add (FOREIGN KEY(dno) REFERENCES dep(dno));
alter table teacher add (FOREIGN KEY(deptno) REFERENCES dep(dno));  

INSERT INTO DEP VALUES (10, '计算机系', 9469 , '62785234');
INSERT INTO DEP VALUES (20,'自动化系', 9581 , '62775234');
INSERT INTO DEP VALUES (30,'无线电系', 9791 , '62778932');
INSERT INTO DEP VALUES (40,'信息管理系', 9611, '62785520');
INSERT INTO DEP VALUES (50,'微纳电子系', 2031, '62797686');


INSERT INTO TEACHER VALUES(9468,'CHARLES','PROFESSOR','17-12月-2004',8000,1000,NULL,10);
INSERT INTO TEACHER VALUES(9469,'SMITH','PROFESSOR','17-12月-2004',5000,1000 ,9468,10);
INSERT INTO TEACHER VALUES(9470,'ALLEN','ASSOCIATE PROFESSOR', '20-2月-2003',4200,500,9469,10);
INSERT INTO TEACHER VALUES(9471,'WARD','LECTURER', '22-2月-2004',3000,300,9469,10);
INSERT INTO TEACHER VALUES(9581,'JONES','PROFESSOR ', '2-4月-2003',6500,1000,9468,20);
INSERT INTO TEACHER VALUES(9582,'MARTIN','ASSOCIATE PROFESSOR ','28-9月-2005',4000,800,9581,20);
INSERT INTO TEACHER VALUES(9583,'BLAKE','LECTURER ','1-5月-2006',3000,300,9581,20);
INSERT INTO TEACHER VALUES(9791,'CLARK','PROFESSO', '9-6月-2003',5500,NULL,9468,30);
INSERT INTO TEACHER VALUES(9792,'SCOTT','ASSOCIATE PROFESSOR ','09-12月-2004',4500,NULL,9791,30);
INSERT INTO TEACHER VALUES(9793,'BAGGY','LECTURER','17-11月-2004',3000,NULL,9791,30);
INSERT INTO TEACHER VALUES(9611,'TURNER','PROFESSOR ','8-9月-2005',6000,1000,9468,40);
INSERT INTO TEACHER VALUES(9612,'ADAMS','ASSOCIATE PROFESSO','12-1月-2004',4800,800,9611,40);
INSERT INTO TEACHER VALUES(9613,'JAMES','LECTURER','3-12月-2006',2800,200,9611,40);
INSERT INTO TEACHER VALUES(2031,'FORD','PROFESSOR','3-12月-2005',5500,NULL,9468,50);
INSERT INTO TEACHER VALUES(2032,'MILLER','ASSOCIATE PROFESSO','23-1月-2005',4300,NULL,2031,50);
INSERT INTO TEACHER VALUES(2033,'MIGEAL','LECTURER','23-1月-2006',2900,NULL,2031,50);
INSERT INTO TEACHER VALUES(2034,'PEGGY', 'LECTURER', '23-1月-2007',2500,NULL,2031,50);


insert into student(birth,sno,sname,sex,passwd,dno) values('01-8月 -10',1,'John','男','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('02-8月 -10',2,'Jacob','男','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('03-8月 -10',3,'Michael','男','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('04-8月 -10',4,'Joshua','男','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('05-8月 -10',5,'Ethan','男','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('06-8月 -10',6,'Matthew','男','123456',20);
insert into student(birth,sno,sname,sex,passwd,dno) values('07-8月 -10',7,'Daniel','男','123456',20);
insert into student(birth,sno,sname,sex,passwd,dno) values('08-8月 -10',8,'Chris','男','123456',20);
insert into student(birth,sno,sname,sex,passwd,dno) values('09-8月 -10',9,'Andrew','男','123456',30);
insert into student(birth,sno,sname,sex,passwd,dno) values('10-8月 -10',10,'Anthony','男','123456',30);
insert into student(birth,sno,sname,sex,passwd,dno) values('11-8月 -10',11,'William','男','123456',30);
insert into student(birth,sno,sname,sex,passwd,dno) values('12-8月 -10',12,'Joseph','男','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('13-8月 -10',13,'Alex','男','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('14-8月 -10',14,'David','男','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('15-8月 -10',15,'Ryan','男','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('16-8月 -10',16,'Noah','男','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('17-8月 -10',17,'James','男','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('18-8月 -10',18,'Nicholas','男','123456',50);
insert into student(birth,sno,sname,sex,passwd,dno) values('19-8月 -10',19,'Tyler','男','123456',50);
insert into student(birth,sno,sname,sex,passwd,dno) values('20-8月 -10',20,'Logan','男','123456',50);
insert into student(birth,sno,sname,sex,passwd,dno) values('21-8月 -10',21,'Emily','女','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('22-8月 -10',22,'Emma','女','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('23-8月 -10',23,'Madis','女','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('24-8月 -10',24,'Isabe','女','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('25-8月 -10',25,'Ava','女','123456',10);
insert into student(birth,sno,sname,sex,passwd,dno) values('26-8月 -10',26,'Abigail','女','123456',20);
insert into student(birth,sno,sname,sex,passwd,dno) values('27-8月 -10',27,'Olivia','女','123456',20);
insert into student(birth,sno,sname,sex,passwd,dno) values('28-8月 -10',28,'Hannah','女','123456',20);
insert into student(birth,sno,sname,sex,passwd,dno) values('29-8月 -10',29,'Sophia','女','123456',30);
insert into student(birth,sno,sname,sex,passwd,dno) values('30-8月 -10',30,'Samant','女','123456',30);
insert into student(birth,sno,sname,sex,passwd,dno) values('31-8月 -10',31,'Elizab','女','123456',30);
insert into student(birth,sno,sname,sex,passwd,dno) values('01-7月 -10',32,'Ashley','女','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('02-7月 -10',33,'Mia','女','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('11-8月 -10',34,'Alexis','女','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('12-8月 -10',35,'Sarah','女','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('13-8月 -10',36,'Natalie','女','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('14-8月 -10',37,'Grace','女','123456',40);
insert into student(birth,sno,sname,sex,passwd,dno) values('15-8月 -10',38,'Chloe','女','123456',50);
insert into student(birth,sno,sname,sex,passwd,dno) values('16-8月 -10',39,'Alyssa','女','123456',50);
insert into student(birth,sno,sname,sex,passwd,dno) values('17-8月 -10',40,'Brianna','女','123456',50);         


insert into course values('c001','数据结构',3,10,100);
insert into course values('c002','Java语言',2,20,100);
insert into course values('c003','数字电路',3,30,100);
insert into course values('c004','模拟电路',3,40,100);
insert into course values('c005','信号与系统',4,50,100);
insert into course values('c006','C语言',3,60,100);
insert into course values('c007','高等数学',5,70,100);
insert into course values('c008','自动原理',1,80,100);
insert into course values('c009','数理方程',3,90,100);
insert into course values('c010','大学语文',2,61,100);
insert into course values('c011','机械制图',3,52,100);
insert into course values('c012','微机原理',3,43,100);
insert into course values('c013','通信基础',3,74,100);
insert into course values('c014','计算机原理',5,35,100);
insert into course values('c015','数据库',3,86,100);
insert into course values('c016','编译原理',2,97,100);
insert into course values('c017','大学物理',2,38,100);
insert into course values('c018','统计基础',4,50,100);
insert into course values('c019','线性代数',4,70,100);
insert into course values('c020','Linux基础',3,60,100);



insert into sc values(6,'c002',60);
insert into sc values(6,'c015',60);
insert into sc values(6,'c010',61);
insert into sc values(27,'c010',65);
insert into sc values(6,'c001',60);
insert into sc values(6,'c011',61);
insert into sc values(6,'c018',70);
insert into sc values(8,'c007',65);
insert into sc values(27,'c020',65);
insert into sc values(27,'c015',65);       
insert into sc values(26,'c015',55);   
insert into sc values(25,'c015',59);      
insert into sc values(1,'c017',65);
insert into sc values(2,'c017',66);
insert into sc values(3,'c017',67);
insert into sc values(4,'c017',68);
insert into sc values(5,'c017',69);
insert into sc values(6,'c017',70);
insert into sc values(7,'c017',71);
insert into sc values(8,'c017',72);
insert into sc values(9,'c017',73);
insert into sc values(10,'c017',74);
insert into sc values(11,'c017',75);
insert into sc values(12,'c017',76);
insert into sc values(13,'c017',77);
insert into sc values(14,'c017',78);
insert into sc values(15,'c017',79);
insert into sc values(16,'c017',80);
insert into sc values(17,'c017',81);
insert into sc values(18,'c017',82);
insert into sc values(19,'c017',83);
insert into sc values(20,'c017',84);
insert into sc values(21,'c017',85);
insert into sc values(22,'c017',86);
insert into sc values(23,'c017',87);
insert into sc values(24,'c017',88);
insert into sc values(25,'c017',89);
insert into sc values(26,'c017',90);
insert into sc values(27,'c017',89);
insert into sc values(28,'c017',88);
insert into sc values(29,'c017',87);
insert into sc values(30,'c017',86);
insert into sc values(31,'c017',85);
insert into sc values(32,'c017',84);
insert into sc values(33,'c017',83);
insert into sc values(34,'c017',82);
insert into sc values(35,'c017',81);
insert into sc values(36,'c017',80);
insert into sc values(37,'c017',79);
insert into sc values(38,'c017',78);
insert into sc values(39,'c017',77);
insert into sc values(40,'c017',76);
commit;

具体实现:

 --创建一张表,用来存放结果
create table gradelists
( dname varchar2(20),
  cname varchar2(20) default '大学物理',
  lt number,
  be number,
  gt number,
  avggrade number
);

set serveroutput on;
declare
  --存储所有的系名
  cursor deplist is select dname from dep;
  --存储某系的所有大学物理分数
  cursor gradelist(dtname dep.dname%type) is 
    select  sct.grade from 
    dep dt, student st, sc sct, course ct 
    where
    dt.dno=st.dno and st.sno=sct.sno and sct.cno=ct.cno and ct.cname='大学物理' and dt.dname=dtname; 
    dtname dep.dname%type;
    --lt用于存储小于60分的人数
    lt number :=0;
    -- be用于存储60-85分之间的人数
    be number :=0;
    -- gt用于存储85分以上的人数
    gt number :=0;
    avggrade number :=0;
    sumgrade number :=0;
    grade sc.grade%type;
begin
    --遍历院系名称
    open deplist;
    loop
      fetch deplist into dtname;
      exit when deplist%notfound;
          lt :=0;
          be :=0;
          gt :=0;
          sumgrade :=0;
          -- 遍历该院系的所有分数
          open gradelist(dtname);
          loop
          fetch gradelist into grade;
          exit when gradelist%notfound;
            if grade<60 then lt := lt + 1;
              elsif grade>=60 and grade<85 then be :=be + 1;
              else gt :=gt + 1;
            end if;
            sumgrade :=sumgrade+grade;
          end loop;
          close gradelist;
          insert into gradelists(dname,lt,be,gt,avggrade) values(dtname,lt,be,gt,sumgrade/(lt+be+gt));
    end loop;
    close deplist;
end;
/
select * from gradelists;


 

© 著作权归作者所有

我是小个子啊
粉丝 14
博文 32
码字总数 26893
作品 0
驻马店
私信 提问
国有大行、业内大咖悉数到场,这场DBA技术盛宴怎可不赴!

  【IT168 评论】9月2日,2017首届ORACLE・欢乐颂技术大会北京站盛大开启。本届大会由CESOUG(中国经验分享ORACLE用户组)、甲骨文(中国)软件系统有限公司、北京中亦安图科技股份有限公司联合...

it168网站
2017/09/05
0
0
PLSQL Developer连接Oracle数据库

一、环境 Oracle当前版本:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production Oracle安装路径:D:oraclexe PLSQL Developer版本:PLSQL Developer 11.0.3.1700,3......

最王座
2016/08/31
23
0
PL/SQL Developer(64bit)免安装客户端连接Oracle数据库

在工作中,连接oracle数据库的客户端工具很多,比如navicat、toad、Oracle SQL Developer、plsql developer等。这里介绍如何使用plsql developer免安装oracle客户端连接数据库。首先下载pls...

rm_rf_db
2017/12/17
0
0
PLSQL连接oracle11g数据库

1.安装oracle; 2.安装PLSQL(有绿色版); 3.下载instantclient112,解压 4.打开PLSQL,按取消,工具,首选项,将Oracle Home路径指定为Oracle Client目录(如图),OCI Libaray路径为Oracle ...

不最醉不龟归
2016/11/07
8
0
oracle10 ora-12154错误的解决办法

安装好oracle10客户端,配置好网络服务名称,用enterprsie manager console 连接服务器,一切正常,连接成功。 接着安装plsql developer,安装完成后,运行plsql, 输入用户名和密码,数据库名...

彭苏云
2014/06/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

如何使用 rsync 备份 Linux 系统的一些介绍

备份一直是 Linux 世界的热门话题。回到 2017,David Both 为 Opensource.com 的读者在使用 rsync 备份 Linux 系统方面提了一些建议,在这年的更早时候,他发起了一项问卷调查询问大家,在 ...

xiangyunyan
24分钟前
0
0
二进制位操作

单片机,或者一些模块的设置操作,都是由一个字节数据来完成,每位各有定义。就需进行位操作来组合需要的数字结果。 以JavaScript为例,编写位操作。 我们期望得到这样一个二进制数:0101101...

format
38分钟前
3
0
聊聊中国的通信行业:从“七国八制”到“中华”脊梁

本期文章和大家一起来聊一聊我曾经从事过的通信行业吧。最近各方面信息的泛滥,包括和华为的同学聊天,自己确实也感慨颇多。想想我自己本科主修通信工程,研究生再修信息与通信工程,从本科开...

CodeSheep
今天
7
0
MDK:ARM M451M:exceed the range of code meory, continue to erase or not?

问题: 代码空间超限 几天前就遇到:exceed the range of code meory, continue to erase or not? 如下所示: 解决过程 开始以为中MDK软件的128KB限制,如是就不能生成HEX文件,应该链接时有提...

SamXIAO
今天
1
1
OSChina 周六乱弹 —— 因违反《中华人民共和国治安管理处罚法》第四十四条之规定

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @xiaoshiyue :#今日歌曲推荐# 惊艳分享谷微的单曲《安守本份》(@网易云音乐) 《安守本份》- 谷微 手机党少年们想听歌,请使劲儿戳(这里) ...

小小编辑
今天
628
12

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部