oracle学习笔记1

--练习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;