本文章介绍

针对软件学院期末oracle plsql考试整理内容，作者做班教课近两年来的一些精华内容，既然现在已经毕业了，把以前做成书本的内容拿出来分享给大家，本文章可以作为初识plsql使用想要学习plsql也不妨看看，本文章课程内容均是作者个人观点意见看法，希望大家喜欢，支持的可以打个赏哈哈。

---

在进入Oracle的学习，首先我们需要知道几项内容

• 参考类型
  参考类型分为两种，%TYPE和%ROWTYPE。
  这两种参考类型的好处是：不必了解数据库中列的个数和数据类型，定义的变量或游标会参考已经存在的表中的某一列或多列属性的数据类型，如果表结构改变了，PL/SQL程序可以不变，减少程序的维护工作。
• %TYPE
  即一个变量的类型用另一个已经定义的变量的类型定义，或用某一个表的某一列的类型定义。

v_a1  NUMBER;
v_a2  v_a1%TYPE;        --v_a2参照自v_a1变量的类型    
v_sal  emp.sal%TYPE;    --v_salary参照自**emp员工表中sal列的类型。

• %ROWTYPE
  即一个变量的类型参考基表视图中记录的类型或游标的结构类型。%ROWTYPE前面是表名或游标名。
  v_grade job_grades%ROWTYPE;

好了，我们已经踏上PLSQL的大船，然我们来与之乘风破浪吧

Oracle异常习题

习题1 编写带有异常处理的PL/SQL程序：从键盘上输入课程名称，查询选修该课程的学生人数
（1）如果该课程不存在，触发系统异常，输出：“没开设该课程”。
（2）若人数少于20人,则该课程是不允许开设的,此时触发一个异常,输出提示：“选修人数太少,无法开课”。
（3）若人数超过100人,选修人数超过了最大的选修人数上限,也是不允许的,也触发一个异常,则输出提示：“需要增加授课教师”。
（4）否则输出选课人数。
我们先贴出代码

declare
  v_cname course.cname%type:=&p1;
  n number;
  e1 exception;
  e2 exception;
begin
  select count(*) into n from course,sc where sc.cno=course.cno and cname=v_cname;
  if n<20 then
     raise e1;
  elsif n>100 then
     raise e2;
  else
    dbms_output.put_line('选课人数'||n);
  end if;
exception
  when no_data_found then
    dbms_output.put_line('没开设该课程');
  when e1 then
    dbms_output.put_line('选修人数太少,无法开课');
  when e2 then
    dbms_output.put_line('需要增加授课教师');
end;

哈哈 代码很多，其实很简单，让我来给你做做分析

declare

用于声明变量，我们这里声明了v_cname这么一个变量它的参数类型和course表中的cname列属性一样，这里使用的是参考类型。注意！我们在后面加上了一句:=&p1，意思代表我们需要在控制台上输入一个值，根据题意我们应该输入一个课程名字。然后我们声明了一个n是number（数字）类型，其次我们自定义了两个异常。

begin end表示PLSQL的程序主体，由begin开始到end结束

好，这样我们进入了主程序
第一句

select count(*) into n from course,sc where sc.cno=course.cno and cname=v_cname;

这里根据题意，我们查询出我们在控制台输入的课程名，那个课程所选修的学生数量，但是course表中只有课程相关的信息，所有选课的信息是存储在sc表中的，但是又出现问题了sc表里没有课程名字呀，情急之下，我们需要连接两张表来查询出来选了我们输入哪个课程名字的课程有多少人。
这里我们使用sc.cno=course.cno and cname=v_cname这么一句来连接两张表，然后我们使用select count(*)来查询出来我们这里一共有多少条数据就是有多少名学生选了这么课。
但是我们需要取这个值来判断，是少于20人呀，还是多余100人，我们这个select他不会取存储我们刚刚查询出来的值，但是我们也没有必要去做三次查询来完成这道题目所以我们这里写出了select count(*) into n把我们刚刚所查询出来的值存储到n当中，哈哈，这样我们在往后的操作中就可以通过n的值来判断我们这里有多少人了！是不是很简单呀

引发起异常 raise e1

if n<20 then 
    raise e1; 
elsif n>100 then 
    raise e2; 
else   
    dbms_output.put_line('选课人数'||n); 
end if;

这里我们根据题意，当我们人数小于20人时候，我们触发一个自定义异常raise e1当人数大于100时候我们也触发一个异常raise e2 人数大于20小于100时候我们输出这里有多少人到控制台dbms_output.put_line('选课人数'||n);
好了，这里出现了一个问题，我们怎么没有去写没有人的时候的异常呢？
这个问题不用我们去想解决方法，Oracle给了我们一个解决的方法，让我们往下继续看

异常的定义

exception 
    when no_data_found then 
            dbms_output.put_line('没开设该课程'); 
    when e1 then 
            dbms_output.put_line('选修人数太少,无法开课'); 
    when e2 then 
            dbms_output.put_line('需要增加授课教师');

看看第一个异常是什么？
no_data_found 顾名思义，意思就是呢，当我们这里查询后没有任何值，我们的Oracle程序会自动抛出这么一个异常，就如我们第一个问所示，然后我们输出“没开设该课程”。
其次的两个自定义异常
e1 e2
我们使用
when e1 then和when e2 then来抓取从主代码块中抛出的异常，然后分别输出
这样我们的异常PLSQL程序就结束啦，看是不是很简单呀

Oracle游标习题

游标的作用是什么呢？我们之前说到的select into只能抓取一个数据来进行判断，但是我们要使用select into来抓去多个数据的时候Oracle会无法处理，会抛出Too Many Rows这样的一个错误，所以当我们要使用多条数据进行判断的时候，我们务必要使用游标来进行操作了，游标是映射在结果集中一行数据上的位置实体，有了游标，我们就可以使用游标来访问结果集中的任意一行数据，提取当前行的数据后，即可对该行数据进行操作。嘿嘿，听起来蛮复杂的，其实很简单来，跟着我往下看！
习题2 查询EMP表中某一部门员工的姓名、工作以及工资，并输出。
若员工工资<1800元，则将其工资调整为1800元；若员工工资>5000元，则将其工资调整为5000元。（注：无参数游标。其中部门号从键盘输入）

declare
   v_deptno emp.deptno%type:=&p1;
   cursor c1 is select ename,job,sal from emp where deptno=v_deptno for update;
begin
  for a in c1 loop  
    dbms_output.put_line(a.ename||','||a.job||','||a.sal);
    if a.sal<1800 then
       update emp set sal=1800 where current of c1;
    end if;
    if a.sal>5000 then
       update emp set sal=5000 where current of c1;
    end if;
  end loop;
end;

declare和上面一样，但是我们多加了一种东西，那就是游标
cursor c1 is select ename,job,sal from emp where deptno=v_deptno for update;
我们定义了游标名为c1的游标，用于(is这个关键字的意思) 查询出emp表中当deptno等于我们输入的值的那条数据中ename, job ,sal三个列的值，后面跟的for update代表的意思是我们的游标是用于更新数据使用。

游标使用循环来操作

for a in c1 loop 
  ...
end loop

使用这么一段for循环来循环操作游标当中的每一行的值，c1是我们的游标，a相当于循环变量，每一次循环取出每一行的值放入a中。
然后我们根据题意，当员工工资小于1800元时候将员工工资提高到1800元

if a.sal<1800 then
       update emp set sal=1800 where current of c1;
end if;

我们使用for循环中定义的a来取值，使用a.sal来获取当前员工的工资，然后根据if判断当员工工资小于1800的时候，我们进行update操作。
这里注意了，当我们使用游标去更新一个值的时候，我们需要在update操作的最后面加上where current of 当前的游标这样PLSQL才会区分我们是在游标里进行的更新操作
当员工工资大于5000时候操作同理

这样，我们的游标大题就完成了，是不是很简单呀！我们来看一下下一道游标习题

习题3 查询EMP表中某一部门员工的姓名、工作以及工资，并输出。
若员工工资<1800元，则将其工资调整为1800元；若员工工资>5000元，则将其工资调整为5000元。（注：带参数游标。参数为‘10’）

declare
   cursor c1(v_deptno emp.deptno%type) is select ename,job,sal from emp where deptno=v_deptno for update;
begin
  for a in c1('10') loop
    dbms_output.put_line(a.ename||','||a.job||','||a.sal);
    if a.sal<1800 then
       update emp set sal=1800 where current of c1;
    end if;
    if a.sal>5000 then
       update emp set sal=5000 where current of c1;
    end if;
  end loop;
end;

咳咳，你们发现这两个代码有什么不同嘛？啊，哈哈，原来这个代码是带参数的游标，上面那一个是没有参数的游标
这里的参数的意思指的我们去查找，指定部分（参数）的员工的工资情况，并根据参数值来修改员工工资

带参游标的定义

cursor c1(v_deptno emp.deptno%type) is select ename,job,sal from emp where deptno=v_deptno for update;

c1传入参数v_deptno是emp表中deptno类型，作用是查询表emp中当deptno为传入参数的数据中ename，job，sal列的值
剩下唯一的不同点就在于，调用游标时候不一样啦，这里我们调用该游标是使用的是c1('10')，哈哈这就是带参数游标了！

Oracle包和存储子程序

包、函数、子程序的内容比较容易，相比于游标的理解程度要低了不少，所以我们这里把包和存储子程序三者放到一起来叙说。

函数function

和数学上的函数意思一致，又传入值，函数会根据我的传入值，作出相对应的返回值，这样的程序体就算是函数了
PLSQL里的函数
function f1(v_cname course.cname%type) return number
传入值v_name是course表中的cname属性的值，返回值是number类型

子程序procedure

和函数基本一致，但是子程序没有返回值，只需要处理我传入的值就可以了
procedure p1(v_sno student.sno%type,v_credit out number)
这里传入值是v_sno是student表中的sno类型，注意！这里定义了一个number类型的out参数v_credit，简单的来说我们的自程序没有返回值，但是我们可以通过这样的一个out参数来获取自程序中的处理参数（严格讲，貌似没有什么用，程序逻辑处理上如果需要返回值的时候为什么不用function，却要用procedure out来装一下？哈哈哈）

包

程序包是将一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素组织在一起，成为一个完整的单元，编译后存储在数据库的数据字典中，作为一种全局结构，供应用程序调用。
哈哈，这么说起来有点玄学了
其实吧，我们之前写的游标写的函数，都是单独存在的，假若我们想完成一组程序操作，里面需要游标，需要函数，需要子程序，需要多段代码才能实现我们的程序，这时候我们聚需要使用包来包装上我们所有的代码了！

包的定义，创建

• 包的创建
  包的创建分为两个步骤：
  包说明（PACKAGE）的创建
  包主体（PACKAGE BODY）的创建

create or replace package p1
create or replace package body p1

• 包的定义

 CREATE [OR REPLACE] PACKAGE 包名
    {IS | AS}
          公共变量的定义
　　    公共类型的定义
          公共出错处理的定义
          公共游标的定义 
          函数说明
          过程说明
    END[包名]；

顾名思义 让我们来看一道代码，就可以看清楚都是些什么啦！

包、函数、子程序联系题

习题4 建立一个包，包中包含一个存储函数：根据课程名查询该课程不及格人数；一个存储过程：根据学号查询该学生总学分数。并测试这个包的功能。(1.函数需要定义变量类型 查询和返回值类型过程只有一个查询2.两个都需要declare定义变量类型)

create or replace package p1//定义包，包说明的创建
is
    function f1(v_cname course.cname%type) return number ;
    //定义了一个函数
    procedure p1(v_sno student.sno%type,v_credit out number);
    //定义了一个自程序
end;
//包说明结束
create or replace package body p1 //定义包的主体
is
function f1(v_cname course.cname%type) return number
//定义函数
   is
      //函数的declare声明
      v_count number;
    begin
      //函数的主题内容开始
        select count(*) into v_count from sc,course where sc.cno=course.cno and cname=v_cname;
        return v_count;
    end;

  procedure p1(v_sno student.sno%type,v_credit out number)
//定义子程序
  is
    begin
        select sum(credit) into v_grade from sc,course where sc.cno=course.cno and sno=v_sno;
    end;
 end;

包的调用

当我们创建万一个包之后，当然我们是想使用他的，哈哈哈，这里我们给出包的调用方法

declare
    v_count number;
    v_credit number;
begin
      v_count:=p1.f1('java');
      dbms_output.put_line('该课程不及格人数：'||v_count);
      p1.p1('05880101',v_credit);
      dbms_output.put_line('总学分'||v_credit);
end;

v_count:=p1.f1('java');调用p1中的f1函数，传入值是java，返回值使用v_count来获取
p1.p1('05880101',v_credit);这里执行的p1包中的p1子程序，传入值是05880101，传出参数是v_credit

好了，到这里我们完成了包，函数，子程序的说明，哈哈，是不是还是那么简单，接下来我们来完成最后一章的内容

Oracle触发器

触发器内容和简单，和我们之前所有的东西都不一样，触发器是单独拿出来写的，到这同学们该想触发器是什么东西呢？？
Oracle里的触发器，是用于当我们执行增删改操作的时候，我们想跟随着这些增删改操作附加上另外的一些东西，比如当我们删除一行数据的时候我想打印出来我删除了什么数据，再比如我们更新一行数据的时候，我想打印出来我们在更新前的数据是多少，更新后的数据是多少？
哈哈，怎么办呢？这里只有触发器能帮助我们来完成这些操作啦！

习题5 创建一个行级UPDATE触发器，当更新某个学生年龄之后，输出此学生修改前与改后的年龄。

create or replace trigger tg1 after update of age on student for each row
begin
   dbms_output.put_line('修改前'||:old.age); 
   dbms_output.put_line('修改后'||:new.age); 
end;

我们来解释下，触发器都是怎么构成的
create or replace trigger tg1 after update of age on student for each row
创建触发器tg1，它作用于在更新表student中age列后执行触发器操作内容
重点：这里我们重点画到了 “更新” 和 “后”
触发器的操作只有2种分别为先和后，再加上增删改操作，我们可以组成更新前，更新后，删除前，删除后，增加前，增加后。（'before update after update before delete after delete before insert after insert'）
for each row是我们触发器必须要写的内容，意思是我们去触发在表上的每一行
这里还有一处重点

old.age
new.age

当我们更新后，oracle会自动为我们存储更新前的值和更新后的值，当我们要去使用这两个值的时候，我们需要分别写上old和new

习题5创建一个触发器，当向sc表插入数据时，如果课程名是english，则引发一个错误，中断数据插入，并显示“该课程已经考试结束，不能添加成绩”；并往sc表中插入一条English的成绩测试它。

create or replace trigger tg2 before insert on sc for each row
declare
   v_cno sc.cno%type;
begin
    select cno into v_cno from course where cname='english';
  if :new.cno=v_cno then
     raise_application_error(-21000,'该课程已经考试结束，不能添加成绩');
  end if;
end;

好啦到这里我们的Oracle PLSQL课程就结束了，明天会更新一些习题代码。

先贴出一些习题内容

-----异常
1．编写带有异常处理的PL/SQL程序：针对EMP表，输出某位雇员的姓名和工资（员工编号从键盘随机输入）。
   （1）如果雇员不存在，触发系统异常，输出：“查无此人”。
   （2）如果雇员存在，但工资小于800元，触发自定义异常，输出：“工资太低，需要涨工资”。
   （3）如果雇员存在，且工资>=800元，输出该雇员的姓名和工资。

2．编写带有异常处理的PL/SQL程序：针对SC表，从键盘上随机输入某个学生的学号，查询该学生的不及格课程数。
   （1）当不及格课程数>3时，则触发异常；当此异常发生时，输出：“留级”。
   （2）当不及格课程数为2或3门时，则触发异常；当此异常发生时，输出：“跟班试读”。
   （3）其余情况，正常输出不及格课程数即可。

3．编写带有异常处理的PL/SQL程序：输出选修课程号为“c3”的学生人数。
   （1）若人数少于15人,则该课程是不允许开设的,此时触发一个异常,输出提示：“选修人数太少,无法开课”。
   （2）若人数超过80人,选修人数超过了最大的选修人数上限,也是不允许的,也触发一个异常,则输出提示：“选修人数过多,需要增加授课教师”。

4．编写带有异常处理的PL/SQL程序：查询学生表中当前学生总人数，并根据总人数进行判定。
   （1）如果人数>100，则触发异常e_big。异常处理时输出：“学生过多，请缩减招生”。
   （2）如果人数<10，则触发异常e_small。异常处理时输出：“学生过少，请扩大招生”。
   （3）否则人数在10至100间，无须触发异常。直接输出当前人数即可。

5．编写带有异常处理的PL/SQL程序：修改员工表EMP中某位雇员的工资（从键盘输入员工号），如果工资小于500元，涨50%；
工资在500－1500元，涨30%；在1500－3000元，涨10%；超过3000元，则触发一个异常，输出“工资太高，不需涨薪”。


-----游标
1．用游标的FOR循环实现从EMP表中取出某一部门的员工姓名和工资，并输出。（注：无参数游标。其中部门号从键盘输入）

2．随机输入某一系别名称，通过游标实现取出某一系别选修“maths”课程的学生姓名和成绩，并输出。（注：无参数游标。其中系别名称从键盘输入）

3．从学生表中取出年龄在18至20的学生姓名和性别，并将其输出。（注：无参数游标）

4．查询EMP表中某一部门员工的姓名、工作以及工资，并输出。若员工工资<1800元，则将其工资调整为1800元；
若员工工资>5000元，则将其工资调整为5000元。（注：无参数游标。其中部门号从键盘输入）

5．用带参数的游标实现，从EMP表中查询部门号为20的雇员姓名和薪水，并输出。（20为实参）

6．用带参数的游标实现，从STUDENT表中查询“计算机系”学生的姓名和年龄，并输出。（注：“计算机系”为实参）

7．用带参数的游标，实现输出“财务部”员工的编号和姓名。（注：“财务部”为实参）

-----包，存储函数，存储过程
1．创建并调用包，完成下列功能：
（1）创建一个包，包名为emp_package。其中包括一个存储过程，根据部门号返回该部门的经理；还包括一个存储函数，根据部门号返回该部门包含员工的人数。
（2）在PL/SQL块中调用此包中的过程，实现输出部门号为“10”的部门经理的姓名。
（3）在PL/SQL块中调用此包中的函数，实现输出部门号为“10”的员工人数。

2．在上一题中，调用包中的存储过程和存储函数都需要一个输入参数为部门号：
（1）试重新编写包emp_package，用一个公有变量表示部门号，这样存储过程、存储函数和包外部的程序都可以使用此变量。
（2）并使用PL/SQL程序调用包中的存储过程和存储函数，输出某一部门的经理姓名和该部门的员工人数，部门号由键盘输入。

3．创建并调用包，完成下列功能：
（1）定义一个程序包，该程序包包括一个函数和一个过程。函数以部门号为参数返回该部门的平均工资；
过程以部门号为参数，输出该部门中工资低于平均工资的员工的员工号、员工名。
（2）在PL/SQL块中调用包中的过程和函数，测试并输出“20”号部门平均工资和工资低于平均工资的员工信息。

4．创建并调用包，完成下列功能：
（1）创建一个程序包，该程序包中重载两个过程，分别以部门号和部门名称为参数，查询相应部门的员工的员工号和员工姓名。
（2）在PL/SQL块中调用包中的过程，输出编号为“10”部门的员工信息，以及名称为“销售部”部门的员工信息。

-----触发器
1．为student表创建一个语句级触发器。当执行插入操作时，输出插入后的学生的姓名；
当执行更新年龄操作时，输出更新前和更新后的年龄；当执行删除操作时，输出删除的学生的系别。

2．创建一个行级UPDATE触发器，当更新学生表中某个学生的系别名称时，激发触发器，输出该学生的学号以及修改前的系别名称与修改后的系别名称。

3．创建一个行级DELETE触发器，当删除学生表student中某个学生信息时，激发触发器，同时删除sc表中该学生所有的选课信息。

4．为学生表student创建一个触发器，当执行更新学生年龄操作时，统计更新后所有学生的平均年龄并输出。

5．创建一个行级UPDATE触发器，当更新学生表student中的学号时，激发触发器，自动修改选课信息表SC中学生的学号。

6．为emp表创建一个触发器，当执行插入操作后，激发触发器，统计插入操作后的员工总人数和插入操作后的员工平均工资，并输出。

7．编写一个触发器，当修改emp表中的工资时，保证“10”号部门不超过50000元，其他部门不超过70000元。