oracle学习笔记1

zxingchao2005

浏览: 80382 次
性别:
来自: 北京

最近访客更多访客>>

linchers

xuewei97

xinger666

kanpiaoxue

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (83)

社区版块

存档分类

--练习1
CREATE TABLE student(
student_id NUMBER PRIMARY KEY,
student_name vARCHAR2(30) NOT NULL)

CREATE TABLE score(
score_id NUMBER PRIMARY KEY,
student_id NUMBER,
course_id NUMBER,
score NUMBER)

CREATE TABLE course(
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(30))

insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (1, '张三');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (2, '李四');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (3, '王五');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (4, '马六');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (5, '孙七');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (6, '王八');

insert into COURSE (COURSE_ID, COURSE_NAME)
values (1, '语文');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (2, '数学');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (3, '英语');

insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (1, 1, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (2, 1, 2, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (3, 1, 3, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (4, 2, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (5, 2, 2, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (6, 2, 3, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (7, 3, 1, 96);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (8, 3, 2, 95);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (9, 3, 3, 94);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (10, 4, 1, 93);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (11, 4, 2, 92);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (12, 4, 3, 91);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (13, 5, 1, 90);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (14, 5, 2, 89);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (15, 5, 3, 88);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (16, 6, 1, 87);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (17, 6, 2, 86);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (18, 6, 3, 85);

--(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名：
--根据不同的排名方式有三种不同的sql写法：
--1.1成绩相同的人排名相同，且排名是连续的。

select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.drank < 6;

--1.2成绩相同的人排名相同，且排名不是连续的
select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.ranking < 6;

--1.2成绩相同的人根据学号排序，排名是连续的。
select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.rn < 6;

--(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名：
select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.rn = 3;
--(3)请查询出至少有三科成绩不低于90分的学生姓名和成绩
SELECT t.student_name, s.score
FROM student t,score s
WHERE
t.student_id=s.student_id
and
t.student_name IN (SELECT student_name
FROM score
WHERE score >= 90
GROUP BY t.student_id
HAVING(COUNT(*) >= 3))

--练习2
--oracle数据库分页
select student_id,student_name
from(select student_id,student_name,rownum r from (select * from student) where rownum<=5)
where r>=1;
--练习3
--oracle序列
CREATE SEQUENCE stu_id increment by 1 -- 每次递增1
start with 20 -- 从1开始
nomaxvalue -- 没有最大值
minvalue 1 -- 最小值=1
NOCYCLE -- 不循环

alter sequence stu_id increment by 2;--修改序列步长为2
insert into student values(stu_id.nextval,'张兴超');
select stu_id.currval,stu_id.nextval from dual--查询序列的当前值和下一个值
select * from student
--练习4
--通过rowid 删除指定字段的重复记录，重复的留一条
delete from student a
where rowid>(select min(rowid) from student b where a.student_name=b.student_name)

--练习5
--随机取几条记录
select * from(
select * from student
order by dbms_random.random
)where rownum<3;

--练习6
--游标使用
create table ren_student(
sid number(3) ,
sname varchar(10)
);
declare v_id ren_student.sid%TYPE :=2;
v_student ren_student%rowtype;
cursor cur_stu is
select * from ren_student
where sid=v_id;
begin
open cur_stu;
fetch cur_stu into v_student;
DBMS_OUTPUT.PUT_LINE(v_student.sname);
close cur_stu;
END;
--游标循环的使用
declare v_id ren_student.sid%TYPE :=2;
v_student ren_student%rowtype;
cursor cur_stu is
select * from ren_student
where sid=v_id;
begin
open cur_stu;
fetch cur_stu into v_student;
while cur_stu%found loop
DBMS_OUTPUT.PUT_LINE(v_student.sname);
fetch cur_stu into v_student;
end loop;
close cur_stu;
END;
--游标for循环的使用
declare v_id ren_student.sid%TYPE :=2;
v_student ren_student%rowtype;
cursor cur_stu is
select * from ren_student
where sid=v_id;
begin
for v_emp in cur_stu loop
DBMS_OUTPUT.PUT_LINE(v_emp.sname);
end loop;
END;
--练习7
--异常
declare
v_stu ren_student%rowtype;
begin
select * into v_stu
from ren_student
where sid=45;
DBMS_OUTPUT.PUT_LINE(v_stu.sname);
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('v_stu:no data found');
end;
--自己定义异常
declare
e_myexception exception;
v_stu ren_student%rowtype;
begin
select * into v_stu
from ren_student
where sid=5;
DBMS_OUTPUT.PUT_LINE(v_stu.sname);
if v_stu.sid=5 then
raise e_myexception;
end if;
exception
when e_myexception then
DBMS_OUTPUT.PUT_LINE('123');
end;
--练习8存储过程
create or replace procedure ren_pro(p_id ren_student.sid%TYPE)as
v_emp ren_student%rowtype;
begin
select * into v_emp
from ren_student
where sid=p_id;
DBMS_OUTPUT.PUT_LINE(v_emp.sname);
end;
begin
ren_pro(5);
end;@ren
create or replace procedure ren_pro(
p_id [in] ren_student.sid%TYPE,
p_name out ren_student.sname%type
)as
v_emp ren_student%rowtype;
begin
select * into v_emp
from ren_student
where sid=p_id;
p_name:=v_emp.sname;
DBMS_OUTPUT.PUT_LINE(v_emp.sname);
end;
declare
v_name ren_student.sname%type;
begin
ren_pro(5,v_name);
DBMS_OUTPUT.PUT_LINE('diao_yong:'||v_name);
end;

--练习7函数
create or replace function ren_fun(
p_id ren_student.sid%TYPE)
return ren_student.sname%type
as
v_emp ren_student%rowtype;
begin
select * into v_emp
from ren_student
where sid=p_id;
return v_emp.sname;
end;
declare
v_name ren_student.sname%type;
begin
v_name:=ren_pro(5);
DBMS_OUTPUT.PUT_LINE('diao_yong:'||v_name);
end;

--练习8 包
create or replace package ren_pack as
v_emp ren_student%rowtype;
procedure addEmp(p_emp ren_student%rowtype);
end ren_pack;
create or replace package body ren_pack as
procedure addEmp(p_emp ren_student%rowtype)as
begin
insert into ren_student
values(p_emp.sid,p_emp.sname);
commit;
end;
end ren_pack;
declare
v_stu ren_student%ROWTYPE;
begin
v_stu.sid:=101;
v_stu.sname:='101ren';
ren_pack.addEmp(v_stu);
end;

--练习9触发器
--触发器
create or replace trigger ren_tr
after insert or update or delete on ren_student
declare
v_cnt number;
begin
select count(*) into v_cnt
from ren_student;
DBMS_OUTPUT.PUT_LINE('v_cnt:'||v_cnt);
end ren_tr;
--不能删root 用户触发器中:old :new表的使用
create or replace trigger ren_del
before delete on ren_student
for each row
begin
if:old.sid=1 then
raise_application_error(-20100,'root');//必须大于20000
end if;
end ren_del;
--记录对表的操作
create table renlog(
username varchar2(20),
sysd date,
opr varchar2(20)
);
create or replace trigger ren_dml
after insert or update or delete on ren_student
begin
if inserting then
insert into renlog values(user,sysdate,'insert');
elsif updating then
insert into renlog values(user,sysdate,'update');
elsif deteting then
insert into renlog values(user,sysdate,'detete');
else
null;
end if;
end ren_dml;

分享到：