oracle_code

/* oracle期末设计 */


--一、定义表


--1、学生表
create table students(
student_id number(6)  constraint student_pk primary key,
monitor_id number(6),
name varchar2(10) not null,
sex varchar2(6) constraint sex_chk check(sex in('男','女')),
dob date,
specialty varchar2(10)
);


--2、系部表
create table departments(
department_id number(3)  constraint department_pk primary key,
department_name varchar2(8) not null,
address varchar2(40)
);


--3、教师表
create table teachers(
teacher_id number(5)  constraint teacher_pk primary key,
name varchar2(8) not null,
title varchar2(6),
hire_date date default sysdate,
bonus number(7,2),
wage number(7,2),
department_id number(3)   
constraint teachers_fk_departments references departments(department_id)
);


--4、课程表
create table courses(
course_id number(5)  constraint course_pk primary key,
course_name varchar2(30) not null,
credit_hour number(2)
);




--5、学生成绩表
create table students_grade(
student_id number(5)  
constraint students_grade_fk_students references students(student_id),
course_id number(5)   
constraint students_grade_fk_courses references courses(course_id),
score number(4,1)
);




--二、向各表中插入数据
insert into students values(200801,null,'王浩','女','20-12月-1989','音乐系');
insert into students values(200802,200801,'张品','男','05-7月-1990','机械系');
insert into students values(200803,200801,'刘明','男','15-8月-1988','计算机');
insert into students values(200804,200801,'王力','女','01-4月-1988','数学');
insert into students values(200805,200801,'李坚强','男','23-4月-1987','英语系');




insert into departments values(101,'数学',1号教学楼)
insert into departments values(102,'美术',2号教学楼)
insert into departments values(103,'音乐',3号教学楼)
insert into departments values(104,'英语系',4号教学楼)
insert into departments values(105,'计算机',5号教学楼)




insert into teachers
  values(10210,'杨文化', '教授', '03-10月-1989',1000,3100, 102); 
insert into teachers
  values(10206,'崔天', '助教', '05-9月-2000',500,1900, 102); 
insert into teachers
  values(10209,'孙晴碧','讲师', '11-5月-1998',600,2500, 102); 
insert into teachers
  values(10207,'张珂', '讲师', '16-8月-1997',700,2700, 102); 
insert into teachers 
values(10101,'李丽','副教授','06-7月-1994',500,2000,104)
insert into teachers 
values(10102,'刘明','教授','17-9月-1995',600,2500,101)




INSERT INTO courses VALUES(10102,'C++语言程序设计',3);
INSERT INTO courses VALUES(10202,'模拟电子技术',2);
INSERT INTO courses VALUES(10302,'理论力学',3);
insert into courses values(10303,'机械制图',3)
insert into courses values(10304,'音乐基础',2)




insert into students_grade values(200801,10102,89)
insert into students_grade values(200801,10303,70)
insert into students_grade values(200802,10102,85)
insert into students_grade values(200803,10304,75)
insert into students_grade values(200804,10202,90)




--三、查询数据
--简单查询
select * from students;
select * from departments;
select * from teachers;
select * from courses;
select * from students_grade;
select name as "姓名",dob as "生日" from students:
select name as "姓名"||'生日是:'||dob as "学生生日" from students;
select name as "姓名",bonus+wage as "月总收入" from teachers;
select distinct specialty from students;


--条件查询
select * from students where specialty='美术';
select name,title,bonus from teachers where bonus in (500,600);
select * from students where name like '刘%';
select name,hire_date,title from teachers where title is null;


--复合条件查询
select * from teachers order by wage asc;
select department_id from teachers group by department_id;
select department_id,max(wage),min(wage) 
from teachers group by department_id;
select student_id, name, sex, specialty
  from students WHERE specialty = '计算机' OR specialty = '自动化';
select name, hire_date, title, bonus, wage FROM teachers
  where not title = '副教授' 
        AND hire_date < '1-1月-2002' AND wage < 2000;




--分组查询
select avg(wage) from teachers;
seleect department_id from teachers group by department_id;
select department_id,max(wage) from teachers group by department_id;
select department_id, avg(wage) from teachers
  group by department_id having avg(wage) > 3000;




--连接查询
select teacher_id,name,department_name from teachers,departments 
where teachers.department_id=departments.department_id;




--三、增、删、改
insert into Students (student_id,monitor_id,name,dob,sex,specialty)
  VALUES(200807,10101,'王力铭', '20-12月-1990', '男','音乐');


update Students set dob='16-6月-1989'
  WHERE student_id = 200803;


DELETE FROM Students WHERE specialty = '计算机';




--四、匿名块
查询某性别的学生信息
declare 
v_students students%rowtype;
v_sex students.sex%type;
cursor c_students is
select * from students where sex=v_sex;
begin
v_sex:='&sex';
if not c_students%isopen then open c_students;
end if;
dbms_output.put_line('学号'||' '||'姓名'||' '||'性别');
loop
fetch c_students into v_students;
if c_students%notfound then
dbms_output.put_line('aaa');
end if;
exit;
end loop;
close c_students;
end;
/


--五、异常处理：
declare 
v_sname varchar2(10);
begin 
select name into v_sname from students where student_id=04;
dbms_output.put_line('姓名:'||v_sname);
exception 
when no_data_found then
dbms_output.put_line('该学生不存在!');
end;
/




--六、权限控制
授予用户llw create session, create user, create table等权限
connect system/administrator
grant create session, create user, create table to llw;




--七、游标
定义游标，使用游标输出某系学生的姓名
set serveroutput on
declare 
v_specialty students.specialty%type;
v_sname students.name%type;
cursor c_students
is
select name from students where specialty=v_specialty;
begin
v_specialty:='&specialty';
open c_students
dbms_output.put_line('学生姓名')；
loop
fetch c_students into v_sname;
exit when c_students%notfound;
dbms_output.put_line(v_sname);
end loop;
close  c_students;
end;
/


--八、过程
输入教师的ID号，根据教师的职称，修改其工资。
create or replace procedure cha(v_id Teachers.teacher_id%TYPE) as
   v_title Teachers.title%TYPE;
begin
  v_id := &teacher_id; 
  select title INTO v_title
    FROM Teachers WHERE teacher_id = v_id; 
  if v_title = '教授' THEN
     UPDATE Teachers
       SET wage = 1.1*wage WHERE teacher_id=v_id;
  elseif v_title = '高工' OR v_title= '副教授' THEN
     UPDATE Teachers
       SET wage = 1.05*wage WHERE teacher_id = v_id;
  else
     UPDATE Teachers
       SET wage = wage+100 WHERE teacher_id = v_id;
  end if;
end cha;
/
--调用过程(更改教师号为'10210'的工资)
set serveroutput on
exec cha(10210);




--九、函数
--查询某教师的工资与奖金之和。即薪金总和。
create or replace function totals(v_id in teachers.teacher_id%type)
return number
as
v_wage teachers.wage%type;
v_bonus teachers.bonus%type;
v_total teachers.wage%type;
v_max_wage teachers.wage%type;
v_dep_id teachers.department_id%type;
begin
select wage,bonus,department_id into v_wage,v_bonus,v_dep_id
from teachers where teacher_id=v_id;
select max(wage) into v_max_wage
from teachers where department_id=v_dep_id;
v_total:=v_wage+v_bonus;
return v_total;
exception
when no_data_found then
 dbms_output.put_line('该老师不存在');
end totals;
/
--调用函数(查询教师号为'10210'的薪金总和)
set serveroutput on
  begin
    dbms_output.put_line('该教师月总收入为：'||total(10210));
  end;
  /


--十、包
--查询某一特定学生某以特定课程的成绩，以及其他课程的成绩
create or replace package query_grade is 
procedure stu_grade(v_id number,c_id number);
end query_grade;
/


create or replace package body query_grade is 
procedure stu_grade(v_id number,c_id number)
as
v_score students_grade.score%type;
v_score1 students_grade.score%type;s
cursor p is
select score from students_grade where student_id=v_id and course_id!=c_id;
begin
select score into v_score from students_grade where student_id=v_id and course_id=c_id;
dbms_output.put_line('该学生的成绩是：'||v_score);
dbms_output.put_line('该学生的其他课程成绩是：');
open p;
loop
 fetch p into v_score1;
 exit when p%notfound;
dbms_output.put_line(v_score1);
end loop;
close p;
exception
when no_data_found then
dbms_output.put_line('不存在这样的学生！');
end stu_grade;
end query_grade;
/