Oracle创建用户、创建表、分页、存储过程

--创建用户
create user OracleTest identified by password
--给用户授权
grant connect,resource to OracleTest
conn OracleTest/password;
-- 创建新表 DEPARTMENT。
-- DEPARTMENT : Department 的表
-- D_ID : D_ID 标识 Department
-- D_NAME : D_Name 属于 Department
create table DEPARTMENT (
D_ID VARCHAR2(10) not null,
D_NAME VARCHAR2(10) not null, constraint DEPARTMENT_PK primary key (D_ID) );

-- 创建新表 REGISTER。
-- REGISTER : Register 的表
-- R_ID : R_ID 标识 Register
-- C_ID : C_ID 属于 Register
-- S_ID : S_ID 属于 Register
-- T_ID : T_ID 属于 Register
-- R_DATE : R_Date 属于 Register
-- R_SCORE : R_Score 属于 Register
create table REGISTER (
R_ID VARCHAR2(10) not null,
C_ID VARCHAR2(10) not null,
S_ID VARCHAR2(10) not null,
T_ID VARCHAR2(10) not null,
R_DATE DATE not null,
R_SCORE NUMBER(38,0) null, constraint REGISTER_PK primary key (R_ID) );

-- 创建新表 COURSE。
-- COURSE : Course 的表
-- C_ID : C_ID 标识 Course
-- C_NAME : C_Name 属于 Course
-- C_COMMENT : C_Comment 属于 Course
create table COURSE (
C_ID VARCHAR2(10) not null,
C_NAME VARCHAR2(10) not null,
C_COMMENT VARCHAR2(50) null, constraint COURSE_PK primary key (C_ID) );

-- 创建新表 STUDENT。
-- STUDENT : Student 的表
-- S_ID : S_ID 标识 Student
-- S_NAME : S_Name 属于 Student
-- S_MAIL : S_Mail 属于 Student
create table STUDENT (
S_ID VARCHAR2(10) not null,
S_NAME VARCHAR2(10) not null,
S_MAIL VARCHAR2(50) null, constraint STUDENT_PK primary key (S_ID) );

-- 创建新表 TEACHER。
-- TEACHER : Teacher 的表
-- T_ID : T_ID 标识 Teacher
-- T_NAME : T_Name 属于 Teacher
-- D_ID : D_ID 属于 Teacher
-- T_MAIL : T_Mail 属于 Teacher
create table TEACHER (
T_ID VARCHAR2(10) not null,
T_NAME VARCHAR2(10) not null,
D_ID VARCHAR2(10) not null,
T_MAIL VARCHAR2(50) null, constraint TEACHER_PK primary key (T_ID) );

-- 在表 REGISTER 中添加外键约束。
alter table REGISTER
add constraint STUDENT_REGISTER_FK1 foreign key (S_ID)references STUDENT (S_ID);
alter table REGISTER
add constraint COURSE_REGISTER_FK1 foreign key (C_ID)references COURSE (C_ID);
alter table REGISTER
add constraint TEACHER_REGISTER_FK1 foreign key (T_ID)references TEACHER (T_ID);
-- 在表 TEACHER 中添加外键约束。
alter table TEACHER
add constraint DEPARTMENT_TEACHER_FK1 foreign key (D_ID)references DEPARTMENT (D_ID);
--创建序列和触发器
create sequence xulie
create sequence bumen
create sequence stu_xl
create sequence course_xl
create sequence register_xl
--创建触发器
--teacher
create or replace trigger teacher_id
before insert on teacher
for each row
declare
v_num number(3);
begin
select xulie.nextval into v_num from dual;
:new.T_ID:='HYT'||lpad(v_num,3,'0');
end;
-----------
--部门
create or replace trigger Department_id
before insert on Department
for each row
declare
v_num number(2);
begin
select bumen.nextval into v_num from dual;
:new.D_ID:='HYD'||lpad(v_num,2,'0');
end;
------
--学生
create or replace trigger stu_id
before insert on Student
for each row
declare
v_num number(3);
begin
select stu_xl.nextval into v_num from dual;
:new.S_ID:='HYS'||lpad(v_num,3,'0');
end;
--课程
create or replace trigger course_id
before insert on course
for each row
declare
v_num number(3);
begin
select course_xl.nextval into v_num from dual;
:new.C_ID:='HYC'||lpad(v_num,3,'0');
end;
--选课表
create or replace trigger register_id
before insert on register
for each row
declare
v_num number(4);
begin
select register_xl.nextval into v_num from dual;
:new.R_ID:='HYC'||lpad(v_num,4,'0');
end;
--添加测试数据
--部门表
insert into Department(D_name) values('教学一部')
insert into Department(D_name) values('教学二部')
--teacher表
insert into teacher(t_name,d_id,t_mail) values('张泽','HYD01','ZhangZe@hy.com')
insert into teacher(t_name,d_id,t_mail) values('刘跃','HYD01','YueLiu@hy.com')
insert into teacher(t_name,d_id,t_mail) values('张成','HYD01','ZhangCheng@hy.com')
insert into teacher(t_name,d_id,t_mail) values('李立','HYD02','')
insert into teacher(t_name,d_id,t_mail) values('蒋岩峰','HYD02','JiangYF@hy.com')
insert into teacher(t_name,d_id,t_mail) values('吴招炫','HYD02','WuZhxuan@hy.com')
--Student表
insert into student(s_name,s_mail) values('王一','Wyi@hy.com')
insert into student(s_name,s_mail) values('王二','Wer@hy.com')
insert into student(s_name,s_mail) values('王三','')
insert into student(s_name,s_mail) values('王四','Wsi@hy.com')
insert into student(s_name,s_mail) values('张一','Zhyi@hy.com')
insert into student(s_name,s_mail) values('张二','Zher@hy.com')
--couser表
insert into course(c_name,c_comment) values('C程序设计','C程序设计')
insert into course(c_name,c_comment) values('Java程序设计','')
insert into course(c_name,c_comment) values('MS SQL Server 2000','数据库技术')
insert into course(c_name,c_comment) values('HTML入门','网页设计技术')
insert into course(c_name,c_comment) values('.NET大型企业开发','.NET大型企业开发')
insert into course(c_name,c_comment) values('.LAMP','')
--选课表
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC001','HYS001','HYT001',to_date('2008-6-12','yyyy-mm-dd'),'76')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC001','HYS002','HYT001',to_date('2008-6-12','yyyy-mm-dd'),'87')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC001','HYS003','HYT001',to_date('2008-6-12','yyyy-mm-dd'),'90')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC001','HYS004','HYT001',to_date('2008-6-12','yyyy-mm-dd'),'69')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC006','HYS001','HYT003',to_date('2008-6-12','yyyy-mm-dd'),'80')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC006','HYS002','HYT003',to_date('2008-6-12','yyyy-mm-dd'),'84')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC006','HYS003','HYT003',to_date('2008-6-12','yyyy-mm-dd'),'60')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC006','HYS004','HYT003',to_date('2008-6-12','yyyy-mm-dd'),'55')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC003','HYS001','HYT004',to_date('2008-6-12','yyyy-mm-dd'),'')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC003','HYS002','HYT004',to_date('2008-7-22','yyyy-mm-dd'),'89')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC003','HYS003','HYT004',to_date('2008-7-22','yyyy-mm-dd'),'69')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC003','HYS004','HYT004',to_date('2008-7-22','yyyy-mm-dd'),'77')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC004','HYS001','HYT006',to_date('2008-7-22','yyyy-mm-dd'),'73')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC004','HYS002','HYT006',to_date('2008-7-22','yyyy-mm-dd'),'89')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC004','HYS003','HYT006',to_date('2008-7-22','yyyy-mm-dd'),'')
insert into register(C_ID,S_ID,T_ID,R_Date,R_score) values('HYC004','HYS004','HYT006',to_date('2008-7-22','yyyy-mm-dd'),'88')
--5.1 列出所有教师的姓名和部门名
select t.t_name,d.d_name from teacher t join department d on t.d_id=d.d_id
--5.2列出每个学生的姓名和平均成绩
select s.s_name,avg(r.r_score) from student s join register r on s.S_ID=r.S_ID group by s_name
--5.3 列出没有带课的老师的名字
select t_name from teacher where t_id not in (select t_id from register group by t_id)
--5.4 利用SQL查询语句显示学生的各科成绩）
select s.s_name 姓名,
sum(case c.c_name when 'C程序设计' then r.r_score end) C程序设计,
sum(case c.c_name when 'MS SQL Server 2000' then r.r_score end) SQL,
sum(case c.c_name when 'HTML入门' then r.r_score end) HTML,
sum(case c.c_name when 'LAMP' then r.r_score end) LAMP
from Student s join register r on s.S_ID=r.S_ID join course c on r.C_ID=c.C_ID
group by s_name
--5.5 列出教学成绩最好（平均成绩高）的老师的名字
select * from(select t.t_name,avg(r.r_score) av from register r join teacher t on r.t_id=t.t_id group by t.t_name order by av desc) where rownum=1
--5.6列出所有学生姓名，授课教师姓名，课程名称，选课时间，成绩，并包含没有参加选课的学生姓名。
select s.s_name,t.t_name,c.c_name,r.r_date,r.r_score from student s
left join register r on r.s_id= s.s_id
left join teacher t on t.t_id=r.t_id
left join course c on c.c_id=r.c_id
--6创建一个触发器实现级联更新，具体要求是当部门表（department）的部门被删除，则设置此部门教师（teacher）的部门编号为空
create or replace trigger del_dapartment
after delete
on department
for each row
begin
if deleting then
update teacher set d_id='' where d_id=:old.d_id;
end if;
end;
--7创建一存储过程实现对register表的数据进行分页。
create or replace procedure TestPages(
Curr_page int,
Page_Size int,
ResultSet out sys_refcursor)
is
v_start int;
v_end int;
v_count int;
v_pagecount int;
begin
select count(*) into v_count from register;
v_pagecount:=ceil(v_count/Page_Size);
if Curr_page>v_pagecount then
raise_application_error('-20001','你输入的页数太大');
else
v_start:=(Curr_page-1)*Page_Size+1;
v_end:=Curr_page*Page_Size;
if not ResultSet%isopen then open ResultSet for
select * from (select rownum id,register.* from register) t where t.id between v_start and v_end ;
end if;
end if;
end;
--8,1列出各门课程的课程名称和最高成绩。
select c.c_name,max(r.r_score) from register r right join course c on r.c_id=c.c_id group by c.c_name
--8.2列出所有的参加选课，但没有考试成绩的学生姓名
select s.s_name from register r join student s on r.s_id=s.s_id where r_score is null