Oracle PL/SQL学习

第一次尝试： 在云端使用centOS来安装oracle 12c

尝试结果：因为最近服务器在作为快应用服务器，同时服务器还在运行mysql数据库，所以没有更多的资源让我使用使用oracle数据库。

随后决定使用docker来安装oracle数据库。在centOS中安装docker的步骤不再详述，重点是在安装来oracle之后如何使用, 网上有很多这块的教程，就不详细说了。这个教程就很不错：Oracle安装

​ 但是很遗憾的是，我跟着教程做了之后，在本地使用DataGrip连接数据库后，执行一条SQL指令的时间长的让我害怕。于是决定还在在本地MacOS环境下使用docker来运行oracle11g。

第二次尝试： 在本地使用docker来安装oracle 11g

​ 在MacOS下安装docker的过程如下：Docker安装教程（MacOS下）

​ 随后使用下述代码完成oracle数据库的运行。

docker pull alexeiled/docker-oracle-xe-11g #拉取oracle数据库
docker run -h "oracle" --name "oracle" -d -p 49160:22 -p 49161:1521 -p 49162:8080 alexeiled/docker-oracle-xe-11g #使用这条命令后，将本机的49160端映射到docker的22端口，49161端口映射到1521端口（oracle数据库进程）
docker ps #获取container ID
docker exec -it [container ID] /bin/bash #进入特定容器
root@oracle:/# sqlplus system/oracle #进入oracle数据库

​ 在这之前，我们都是可以使用(账号：system 密码：manager)来登陆数据库的。但是这样不安全，于是我决定在数据库中创建新的用户。
执行以下代码：

create user [用户名] identified by [密码]; 	--创建用户
grant connect, resource, dba to [用户名];	 --赋予权限

随后就可以使用DataGrip来登陆啦，用户名要换成自己的哦
随手记录真好，前面看的好多东西都忘了😂，害。

安装数据库之后，就要完成这次的数据库作业啦，就是PL/SQL作业，这个只能现学现卖啦😂

哇，这个课讲的真的不错PL/SQL学习

实验代码如下，题目和验证都写上了：

create table Student (
    Sno int primary key ,
    Sname varchar(8),
    Ssex  varchar(8),
    Sclass int,
    Sage  int,
    Sdept varchar(8)
);

create table teacher (
    Tno int primary key ,
    Tname varchar(8),
    Tsex  varchar(8)
);

create table Course (
    Cno int primary key ,
    Cname varchar(8),
    Cpno int,
    Ccredit int,
    Tno int references Teacher(Tno)
);

create table relation (
    Sno int references Student(Sno),
    Cno int references Course(Cno),
    Grade int
);
select * from Student;
select * from teacher;
select * from relation;
select * from Course;
insert into STUDENT values (1, 'stu_why', 'M', '1802', 18, 'Com');
insert into Course values (1, 'DateBase', 1, 3, 1);
insert into teacher (Tno, Tname, Tsex) values (1, 'Tea_Liu', 'M');
insert into relation(Sno, Cno, Grade) values (1, 1, 101);

-- 1：在选课表中定义一个更新和插入的触发器，保证插入的成绩在0-100之内
create or replace trigger tri_updateGrade
before insert or update on relation
for each row
declare
begin
    if :new.Grade > 100 or :new.Grade < 0 then
        raise_application_error('-20001', '插入的成绩需要在0-100之内');
    end if;
end;

insert into relation (Sno, Cno, Grade) values (1, 1, 95);
update relation set Grade = 101 where Sno = 1 and Cno = 1;

-- 2:在选课表relation表中定义一个保证课程号在课程表的课程范围内的插入触发器
create or replace trigger tri_grantCnoInRange
before insert on relation
for each row
declare
    cursor findCno is select Course.Cno from Course where Course.Cno = :new.Cno;
    container Course.Cno%type;
begin
    open findCno;
    fetch findCno into container;
    if findCno%notfound then
        raise_application_error('-20002', '课程号需要在课程表的课程范围内');
    end if;
    close findCno;
end;

delete from relation where Cno = 1 and Sno = 1;
select * from relation;
insert into relation(Sno, Cno, Grade) VALUES (1, 1, 95);
insert into relation(Sno, Cno, Grade) VALUES (1, 2, 95);


--3:在选课表relation表上定义一个限制学生选课门数不能超5门的触发器
create or replace trigger tri_CourseMax
before insert on relation
for each row
declare
    num int;
begin
    select count(*) into num from relation where Sno = :new.Sno ;
    if num >= 5 then
        raise_application_error('-20003', '学生选课门数不能超5门');
    end if;
end;

insert into teacher(tno, tname, tsex) VALUES (2, 'Tea_Hu', 'M');
insert into teacher(tno, tname, tsex) VALUES (3, 'TeaHuang', 'M');
insert into teacher(tno, tname, tsex) VALUES (4, 'TeaSheng', 'M');
insert into teacher(tno, tname, tsex) VALUES (5, 'Tea_Jin', 'M');
insert into teacher(tno, tname, tsex) VALUES (6, 'TeaZheng', 'F');
insert into Course(Cno, Cname, Cpno, Ccredit, Tno) VALUES (2, 'OS', 1, 3, 2);
insert into Course(Cno, Cname, Cpno, Ccredit, Tno) VALUES (3, 'Java', 1, 3, 3);
insert into Course(Cno, Cname, Cpno, Ccredit, Tno) VALUES (4, 'Com', 1, 4, 4);
insert into Course(Cno, Cname, Cpno, Ccredit, Tno) VALUES (5, 'Blue', 1, 2, 5);
insert into Course(Cno, Cname, Cpno, Ccredit, Tno) VALUES (7, 'His', 1, 3, 6);

insert into relation(Sno, Cno, Grade) VALUES (1, 2, 90);
insert into relation(Sno, Cno, Grade) VALUES (1, 3, 91);
insert into relation(Sno, Cno, Grade) VALUES (1, 4, 92);
insert into relation(Sno, Cno, Grade) VALUES (1, 5, 93);

insert into relation(Sno, Cno, Grade) VALUES (1, 6, 95);


--4.在课程表course表上定义一个限制删除有学生选课程的触发器.
create or replace trigger tri_checkDeleteCourse
before delete on Course
for each row
declare
    container relation%rowtype;
    cursor judge is select * from relation where Cno = :old.cno;
begin
    open judge;
    fetch judge into container;
    if judge%found then
        raise_application_error('-20004', '这门课已经有人选了，你不能删除这门课');
    end if;
end;

select * from relation;
delete from Course where Cno = 6;
delete from Course where Cno = 5;

--5：建立存储函数，它带有两个参数：输入你的姓名和你的班级号(演示的时候，一定要是你的学号与班级啊，
-- 不是你的不行)。该函数可以将指定的某班学生的成绩改为95。如果学生不在该班级，请定义例外处理。
-- P.S.老师，学号已经是唯一的了，这样班级号就没意义了，所以我换成了姓名
-- 同时，因为我的成绩定义为分数，我就换成了95
create or replace procedure changeGradeCNO
    (myName in STUDENT.Sname%type, class in STUDENT.Sclass%type, CnoID in relation.Cno%type)
is
    no_student exception;
    no_grade exception ;
    stuTemp student%rowtype;
    relTemp int := 0;
    cursor findStu is select * from Student where Student.Sname = myName and Sclass = class;
begin
     open findStu;
     fetch findStu into stuTemp;
     if findStu%notfound then
         raise no_student;
     else
         select count(*) into relTemp from relation where Cno = CnoID and Sno = stuTemp.Sno;
         if relTemp <= 0 then
             raise no_grade;
        else
            update relation set Grade = 96 where Sno = stuTemp.Sno and Cno = CnoID;
         end if;
         close findStu;
     end if;
exception
    when no_student then
        raise_application_error('-20005', '没有这个学生');
    when no_grade then
        raise_application_error('-20006', '这个学生没选这门课');
end;

declare
begin
    changeGradeCNO('stu_why', 1802, 6);
end;
select * from relation;


-- 6：给teacher表编写一个（before-delete)触发器,当要在teacher表中删除一个老师，将触发该触发器。
-- 在触发器中将判断老师是否已经授课？如果已经安排某位教师授课，它将激发一个例外，把无法删除的信息返回用户。
create or replace trigger tri_delTeacherCheck
before delete on teacher
for each row
declare
    temp Course%rowtype;
    cursor judge is select * from Course where Tno = :old.tno;
    cannot_delete exception ;
begin
    open judge;
    fetch judge into temp;
    if judge%found then
        raise cannot_delete;
    end if;
exception
    when cannot_delete then raise_application_error('-20007', '老师有课，不能删除');
end;

select * from teacher;
delete from teacher where Tno = 1;
delete from teacher where Tno = 7;