第七章 事务、过程、触发器

1、事务

    （transaction）事务就是将一系列操作作为一个不可分割的整体，要么一起执行失败，要么一起执行成功 。   

    事务是一个不可分割的工作逻辑单元。

    所有数据库中的增删改实现都是通过事务来实现的。

事务具备四个属性ACID：

    原子性：（Atomicity）事务是一个完整的操作。事务的各步操作时不可分的（原子性）；要么都执行，要么都不执行。

    一致性：（Consistency）当事务完成时，数据必须处于抑制状态。

    隔离性：（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

    永久性：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。

管理事务的语句

    commit                 提交事务   

    savepoint              记录回滚点

    rollback                回滚事务

commit提交

    为了保证JAVA程序可以正确访问到数据库的数据内容，必须手动提交：commit。

    insert，update、delete语句都需要通过事务来提交至物理文件，默认情况下所有持久化操作都是在缓存中对数据进行访问。

    在DBMS关闭时数据库将自动对缓存中的数据进行commit提交。（比如在plsqldev这个dbms工具执行插入数据的操作，在物理文件的底层是看不到数据的，需要commit后才可以看到数据，关闭这个dbms工具在开启也可以看到）

    select优先到数据库的缓存中查询数据，如果缓存中不存在数据则至数据文件中继续查询。

    

    savepoint 和 rollback

例子：

   
   
declare
v_money float;
begin
--记录事务的回滚点（还原点）
--savepoint p1;
     update bank set balance=balance+1200where cardId=1002;
--一个事务可以定义多个回滚点
--savepoint p2;
     update bank set balance=balance-1200where cardId=1001;
--查询转账后的数据是否合法
select balance into v_money from bank where cardId=1001;
if v_money<0then
--非法数据进行回滚
        rollback to p1;
else
--提交事务
        commit;
endif;
end;

       每个过程也是一个事务，当过程内发生约束错误不会改变数据，因为会自动回滚到begin

2、游标

        映射在结果集中一行数据上位置的实体，通过游标，就能访问结果集中的每一行数据，当游标放置到某一行上即可对该行进行相应的操作。

   

    游标的分类：隐式游标 和显式游标

    游标具有的属性：

                    %found

                    %notfound

                    %rowcount            语句执行影响的行数

                    %isopen

创建游标

            declare    cursor    游标名    is    查询语句

使用游标的步骤

        1、打开游标

                open    游标名；

        2、获取游标中下一行数据

                fetch    游标名    into    变量名；

             一般获取游标的数值，通过死循环来获取。

   
   
loop
   fetch  游标名into变量名;
exitwhen游标名%notfound;//当找不到游标值就退出循环
--执行的一系列操作
end loop;

                    如果不使用循环获取游标读取的数据，就只能使用一行数据。

           3、关闭游标

                close    游标名；

例子：

        游标读取结果集的数据时单列的，必须要使用变量来接收。

                               fetch c_emp into v_emp

       游标读取结果集的数据是多列的，可以使用属性行变量，也可以使用复合属性来接收。

        

  
  
--创建游标
declare
cursor c_emp isselect*from emp;
--记录游标读取的行
v_emp emp%rowtype;
begin
--开启游标
     open c_emp;
--控制游标读取结果集中的下一行数据
/*fetch c_emp into v_emp;
     dbms_output.put_line(v_emp.first_name||v_emp.last_name||'  '||v_emp.salary);
     fetch c_emp into v_emp;
     dbms_output.put_line(v_emp.first_name||v_emp.last_name||'  '||v_emp.salary);*/
--游标需要循环反复读取，直到读完结果集
     loop
         fetch c_emp into v_emp;
exitwhen c_emp%notfound;
         dbms_output.put_line(v_emp.first_name||v_emp.last_name||'  '||v_emp.salary);
end loop;
--关闭游标
     close c_emp;
end;

   
   
--将部门的所有员工的薪水增加100
declare
cursor c_empId isselect employee_id from emp;
v_empId int;
begin
     open c_empId;
     loop
--读取一行数据
         fetch c_empId into v_empId;
--判断是否需要退出
exitwhen c_empId%notfound;
--每读到一个人的编号就进行一次加薪
         update emp set salary=salary+100where emp.employee_id=v_empId;
end loop;
     close c_empId;
end;

        游标对应的结果集可以使用子查询实现。游标对应的select中可以使用变量,但这变量的创建必须在游标的创建前。

   
   
--将sales部门的所有员工的薪水增加100
--游标对应的结果集可以使用子查询实现
--游戏对应的select中可以使用变量
declare
v_depId int;
--cursor c_empId isselect emp.employee_id from emp where department_id=(select department_id from dep where dep.department_name='Sales');
cursor c_empId isselect emp.employee_id from emp where department_id=v_depId;
v_empId int;
begin
--获取Sales的部门编号
select department_id into v_depId from dep where dep.department_name='Sales';
     open c_empId;
     loop
--读取一行数据
         fetch c_empId into v_empId;
--判断是否需要退出
exitwhen c_empId%notfound;
--每读到一个人的编号就进行一次加薪
--update emp set salary=salary+100where emp.employee_id=v_empId;
         dbms_output.put_line(v_empId);
end loop;
     close c_empId;
end;

            可以定义复合类型接受游标获取的数据

   
   
--打印显示Sales部门的员工的姓名，薪水，入职时间
declare
cursor c_emp isselect first_name||last_name,salary,hire_date from emp where department_id=(select department_id from dep where dep.department_name='Sales');
--定义复合类型接收游标获取的每一行数据
type empInfo is record
(
 empName varchar2(50),
 salary float,
 hireDate emp.hire_date%type
);
--复合类型的变量
v_empRow empInfo;
begin
     open c_emp;
     loop
         fetch c_emp into v_empRow;
exitwhen c_emp%notfound;
         dbms_output.put_line(v_empRow.empName||'  '||v_empRow.salary||'  '||v_empRow.hireDate);
end loop;
     close c_emp;
end;

           游标可以嵌套使用

   
   
--打印显示在Seattle工作的所有员工的姓名、薪水，所在部门的名称，岗位名称
declare
v_locationId int;
v_depId int;
--查询Seattle对应部门的游标
cursor c_dep isselect department_id from dep where location_id=v_locationId;
--查询部门对应员工的游标
cursor c_emp isselect 	first_name||last_name,salary,department_id,job_id from emp 		where department_id=v_depId;
type empInfo is record
(
 empName varchar2(50),
 salary float,
 depId int,
 jobId job.job_id%type
);
--自定义类型的变量
v_emp empInfo;
v_depName dep.department_name%type;
v_jobName job.job_title%type;
begin
--查询对应的地区编号
select location_id into v_locationId from locations where locations.city='Seattle';
--开启游标
     open c_dep;
     loop
--读取每一条部门编号
         fetch c_dep into v_depId;
exitwhen c_dep%notfound;
         open c_emp;
         loop
--读取每个部门对应的员工信息
              fetch c_emp into v_emp;
exitwhen c_emp%notfound;
--根据部门编号查询部门名称
select dep.department_name into v_depName from dep where dep.department_id=v_emp.depId;
--根据岗位编号查询岗位名称
select job.job_title into v_jobName from job where job.job_id=v_emp.jobId;
              dbms_output.put_line(v_emp.empName||'  '||v_emp.salary||'  '||v_depName||'  '||v_jobName);
end loop;
         close c_emp;
end loop;
     close c_dep;
end;

3、存储过程

        存储过程类似Java中的方法，可以反复使用，用来执行一些列应用复杂的应用规则。

       

       创建存储过程

  
  
create  or  replace procedure 过程名 (参数名 in out 类型)
as
begin
--PL/SQL语句
end;

   
   
--创建存储过程
create or replace procedure myPro
as
begin
     dbms_output.put_line('我的第一个存储过程');
end;

       存储过程和方法一样可以传入参数，并且可以返回值。

           过程的参数类别

                in           表示输入参数

                out         表示输出参数

                in  out     表示该参数既可以是输入参数，也可以是输出参数

    注意点：使用out输出参数时调用参数前必须声明接受的参数。

        

    执行存储过程

        1) 在过程块外执行存储过程，需要用 call 过程名();

   
   
--在过程块外执行存储过程
call myPro();

        2) 在过程块内调用存储过程，直接用 过程名();

   
   
--在过程内调用存储过程
begin
    myPro();
end;

带有输入参数的存储过程

           参数书写：参数名  in  out   数据类型            （in  out必须放在参数名和类型的中间）

           输入参数默认 in

           参数的类型注意 字符类型不需要定义长度（如 直接写 varchar2）

   
   
--编写存储过程用于新增用户信息
--参数默认为in输入参数
create or replace procedure insertUser(v_userId int,v_uname in varchar2,v_birthday in date)
as
begin
     insert into users(userId,userName,birthday) values(userId,uname,birthday);
end;
call insertUser(1028,'jerry',to_date('19901010','yyyymmdd'));

   
   
--编写存储过程根据用户编号查询姓名和性别
create or replace procedure findUserById(v_userId int,v_userName out varchar2,v_sex out varchar2)
as
begin
select userName,sex into v_userName,v_sex from users where userId=v_userId;
end;

带有输出参数的存储过程必须要先创建变量用来保存输出参数

   
   
--带有输出参数的存储过程必须要先创建变量用来保存输出参数
declare
v_name varchar2(20);
v_sex users.sex%type;
begin
     findUserById(1024,v_name,v_sex);
     dbms_output.put_line(v_name||' '||v_sex);
end;

        带有in out类型参数的存储过程

   
   
--带有inout类型参数的存储过程
create or replace procedure testPro(a varchar2,b out varchar2,c inout varchar2)
as
begin
     dbms_output.put_line(a||','||b||','||c);
     c:='d';
end;
declare
b varchar2(20):='b';
c varchar2(20):='c';
begin
     testPro('a',b,c);
     dbms_output.put_line(c);
end;

打印输出结果： d

       注意点：如果需要声明变量，可以在as    begin 间声明变量，不需要declare。

       可读性案例：

   
   
--创建存储过程根据指定员工的编号将该员工所在部门中比其收入高的员工收入增加200，如果该编号的员工的薪资是低于该部门的平均薪资
--则加薪至平均薪资，并返回该部门的平均薪资
create or replace procedure findDepSalaryById(v_empId int,v_avg outfloat)
as
--员工的薪资
v_salary float;
--部门编号
v_depId int;
--游标收入指定高的结果集
cursor c_emp isselect emp.employee_id from emp where emp.department_id=v_depId and salary>v_salary;
--每个员工的编号
v_id int;
--加工资的人数
v_count int:=0;
begin
--查询员工的薪资和部门编号
select salary,emp.department_id into v_salary,v_depId from emp where emp.employee_id=v_empId;
--查询员工对应的平均薪资
select avg(salary)into v_avg from emp where emp.department_id=v_depId;
--开启游标
     open c_emp;
--读取游标的每一行数据
     loop
         fetch c_emp into v_id;
exitwhen c_emp%notfound;
--增加员工的薪资200
         update emp set salary=salary+200where emp.employee_id=v_id;
         v_count:=v_count+1;
end loop;
     dbms_output.put_line('加薪人数是：'||v_count);
--判断员工自身薪资是否低于平均薪资
if v_salary<v_avg then
        update emp set salary=v_avg where emp.employee_id=v_empId;
endif;
     close c_emp;
end;
declare
v_avg float;
begin
     findDepSalaryById(198,v_avg);
     dbms_output.put_line(v_avg);
end;

4、函数（函数这部分老师只是粗略的讲了下，未来的工作中基本不会用到）

       函数结构和过程非常相似。

        函数和过程的最大区别：

                   过程只能单独调用或者在语句块中使用

                   函数只能在DQL和DML语句中调用。

        函数可以传入外部的参数，且必须要有返回值。

       创建函数

   
   
create  or  replace function函数名(参数)
return数据类型
as
begin
--PL/SQL语句
return数据;
end;

例子

   
   
--创建自定义函数
create or replace function showDepName(v_depId int)
return varchar2
as
v_depName dep.department_name%type;
begin
select dep.department_name into v_depName from dep where dep.department_id=v_depId;
return v_depName;
end;

       函数的调用

   
   
select first_name,showDepName(emp.department_id)from emp

5、返回游标的存储过程

    了解一个系统游标     sys_refcursor

        1）创建存储过程的参数里带有声明的系统游标    

        2）在存储过程里开启这个游标

        3）在过程里返回这个游标

--自己感觉 返回游标的存储过程就像java中的一个方法返回一个集合，然后在另一个方法中获取集合的值

例子：

   
   
--返回游标的存储过程
--根据部门编号返回该部门的所有员工姓名
create or replace procedure findEmpByDepId(v_depId int,c_emp out sys_refcursor)
as
begin
--开启系统游标
     open c_emp forselect first_name||last_name from emp where emp.department_id=v_depId;
end;
--调用返回游标的存储过程
declare
c_emp sys_refcursor;
v_empName varchar2(20);
begin
     findEmpByDepId(50,c_emp);
--系统游标获取时不需要打开
     loop
       fetch c_emp into v_empName;
exitwhen c_emp%notfound;
       dbms_output.put_line(v_empName);
end loop;
     close c_emp;
end;

作业题目：使用存储过程查询指定版块的所有主贴标题，返回游标

   
   
--使用存储过程查询指定版块中的所有主贴标题,返回游标
create or replace procedure getTopicById(v_id int,c_tid out sys_refcursor)
as
begin
     open c_tid forselect tid from bbstopic where tsid = v_id;
end;


declare 
c_tid sys_refcursor;
v_topic bbstopic.ttopic%type;
v_id int;
begin
     getTopicById(1,c_tid);
     loop 
          fetch c_tid into v_id;
exitwhen c_tid%notfound;
select ttopic into v_topic from bbstopic where tid = v_id;
          dbms_output.put_line(v_id||' '||v_topic);
end loop;
     close c_tid;
end;

6、触发器

        触发器是一种特殊的存储过程，在插入，修改或者删除表中的数据时将会被出发执行，触发器在事件发生时是自动执行的，并且不能输入参数。

        触发器的作用

            1）安全性    限制用户的一些特殊操作

            2）审计       将用户执行的操作放到一个表中，跟踪用户的操作

            3）实现非标准的更为复杂的业务规则

        触发器的种类

               1）DML触发器            进行DML语句操作时出发     （主要学习的触发器） 

                2）替换触发器            只能在视图上出发

                3）事件触发器            DDL和数据库事件出发

           DML触发器的创建    (又被称为行级触发器)

  
  
create  or  replace  trigger 触发器名称
触发时间触发类型  on  数据表for  each row
begin
--PL/SQL语句
end;

           删除触发器  

   
   
drop  trigger  触发器名称;

            触发时间   before （操作执行之前）  after（操作执行之后）

            触发类型   insert    update    delete

伪记录  

        ：new  新纪录  

        ：old    原记录

            对于update  :new 表示更新后的数据行    ：old表示更新前的数据行

            对于 insert   ：new表示新增的数据行        没有：old

            对于 delete   ：old表示被删除的数据行      没有：new

     伪记录就相当于一个%rowtype行变量，可以使用 :new.列名获取该列值

  

    触发器内部可以对其他表进行DML操作，但不能对当前表执行DML操作。（想想会不会进入死循环）

例子：

insert操作

   
   
create or replace trigger users_before_insert
before insert on users for each row
begin
--使用伪记录获取数据
--:new表示新增的数据行
     dbms_output.put_line(:new.userId||'  '||:new.userName);
--insert触发器无法访问old伪记录
     dbms_output.put_line(:old.userName);
     dbms_output.put_line('在users表新增数据时触发了');
end;

delete操作

   
   
create or replace trigger users_before_delete
before delete on users for each row
begin
--:old表示被删除的数据行
--delete触发器无法访问new伪记录
     dbms_output.put_line(:old.userName);
     dbms_output.put_line('执行了删除');
end;

update 操作

需要

--自治式事务处理

DECLARE

PRAGMA  AUTONOMOUS_TRANSACTION;

   
   
create or replace trigger users_before_update
before update on users for each row
begin
--update触发器可以同时访问:new和:old伪记录
--:new表示修改后的数据行
--:old表示修改前的数据行
     dbms_output.put_line(:new.userName);
     dbms_output.put_line(:old.userName);
     dbms_output.put_line('执行了修改');
end;

       体现触发器作用二（跟踪用户操作）的案例

   
   
--编写触发器将用户修改数据的情况备份在user_log表中
create or replace trigger users_log
after update on users for each row
begin
--触发器内部可以对其他数据表执行DML操作
--触发器不能对当前表执行DML操作
     insert into user_log values(logId.Nextval,'update',sysdate);
end;

在触发器中自定义异常

        declare   

        异常名  exception;

         抛出异常（不同java的抛出异常）

        raise  异常名;

        处理异常（所有的异常处理都是在过程的末端执行，否则会报错）

        exception when 异常名 then

                raise_application_error(参数一,参数二);

            --第一个参数表示错误的编号，所有自定义的编号都从-20001开始取，只能比-20000小

            --第二个参数表示错误提示信息

   
   
--异常处理
--所有异常的处理应在过程的末端执行
begin
     insert into users values(1025,'tom',sysdate,null,null,20,null);
--处理系统异常
     exception when others then
           dbms_output.put_line('数据新增出现了异常');
end;

       异常的处理在Oracle只是提示错误信息，但是在java中可以获取这些错误信息，进而进行相应的操作。

   
   
--新增用户数据时，用户的年龄不能超过用户表中所有用户的平均年龄
create or replace trigger users_before_insert
before insert on users for each row
declare
v_avg int;
--自定义异常
myError exception;
begin
--查询平均年龄
select round(avg(age))into v_avg from users;
     dbms_output.put_line('平均年龄是'||v_avg);
--判断新增的年龄是否超过平均年龄
if:new.age>v_avg then
        dbms_output.put_line('新增的年龄大于平均年龄');
--可以改变伪记录的数据
--:new.age:=v_avg;
--抛出异常
raise myError;
endif;
--处理异常
     exception when myError then
--第一个参数是错误编号，所有自定义异常编号从-20000开始
--第二个参数是错误的信息内容
         raise_application_error(-20001,'age must less than avg age!');
end;

   
   
--编写触发器，要求当用户修改emp表的数据时，salary的值必须在其岗位的薪资范围内，同时用户的薪水不超过该部门经理的薪水
create or replace trigger emp_update
before update on emp for each row
declare
PRAGMA  AUTONOMOUS_TRANSACTION;
v_jobid job.job_id%type;
v_min float;
v_max float;
--部门经理的薪水
v_salary float;
--自定义异常
myError exception;
begin
--查询修改数据行对应的岗位编号
select job_id into v_jobid from emp where emp.employee_id=:new.employee_id;
--根据岗位编号查询薪资范围
select min_salary,max_salary into v_min,v_max from job where job_id=v_jobid;
--查询部门经理的薪水
select salary into v_salary from emp where emp.employee_id=(select emp.manager_id from emp where emp.employee_id=:new.employee_id);
--判断修改后的薪资是否合法
if:new.salary<v_min or:new.salary>v_max or:new.salary>v_salary then
raise myError;
endif;
--处理异常
     exception when myError then
        raise_application_error(-20001,'薪水范围错误！');
end;
update emp set salary=5600where emp.employee_id=198;

        

使用触发器实现成绩表的数据添加不能新增学生中学号以外的非法学号

实现思路：  用：new获取添加的学号判断在学生表是否存在

   
   
--使用触发器实现成绩表的数据添加不能新增学生中学号以外的非法学号
create or replace trigger score_insert
before insert on score for each row
--创建游标获取所有学号
declare
cursor c_stuid isselect stuId from student;
v_stuId int;
--标识新增的学号是否存在
v_hasId boolean:=false;
--异常
myError exception;
begin
--判断要新增的成绩信息中的学号是否出现在学生的学号范围内
     open c_stuid;
     loop
         fetch c_stuid into v_stuid;
exitwhen c_stuid%notfound;
if v_stuid=:new.stuid then
            v_hasId:=true;
--退出循环
exit;
endif;
end loop;
if v_hasId=falsethen
--抛出异常阻止数据的写入
raise myError;
endif;
     close c_stuid;
     exception when myError then
        raise_application_error(-20001,'违背了外键约束！');
end;
insert into score values(1000,1,9999);

创建一个自动插入序列的触发器

   
   
--自动插入序列
create or replace trigger auto_logId
before insert on user_log for each row
begin
--将序列的值写入新纪录
select logId.nextval into:new.logid from dual;
end;

触发器还可以级联操作（比如删除主贴级联删除回帖）

     

7、索引

           在数据库中，所有数据的存储也是按页进行存储的，通过索引能够快速定位到指定页中，再根据索引所指定的列进行快速查询。

            

         创建索引

      create index  索引名称  on 表名（列名） [tablespace 表空间名]

       索引是针对某个列创建的。

使用索引合

       1）数据量应至少达到10w行级别

       2）频繁搜索的数据列适合建立索引

       3）频繁进行排序的列适合建立索引

使用索引的注意点：

                    数据量过少的表格不适合建立

                     数据表的列被频繁修改不合适建立

 

    

8、视图   (主要用于查询)

      视图就是一张通过查询获取的虚拟表，视图中本身不存在数据，数据来自于视图对源表的映射作用，一张数据表可以根据不同的需要创建多个不同的视图。

      

        使用视图的优点：

                过滤表中的行数据

                组织未经授权的用户访问敏感数据

                降低数据操作的复杂度

                可以将多个表空间的数据抽象为一个逻辑数据

        创建视图

        create    view    视图名称    as    查询语句

   
   
--使用视图存储允许可见的数据
create or replace view cashView asselect cardid,userName from card;

         使用视图

         select  * from    视图名称

   
   
--使用视图查看数据
select*from cashview;

视图是对源表的映射，因此修改视图即修改源表。

           比如修改视图里的用户名即对源表的数据进行修改。

  
  
--视图是对源表数据的映射，因此修改视图即修改源表
update cashView set userName='tony'where cardId='100000001';

               如果视图存在多表数据，同一时间只能更新一张表。

               视图可以将表中的敏感数据进行屏蔽，同时也可以将多表的数据以更直观的方式将其作为整体呈现        

                  如下为：选择非敏感数据呈现的视图 

  
  
create or replace view cashView asselect recordId,card.cardId,userName,cash,case cashtype 
when1then'收入'
when0then'支出'endcase,cashtime from card inner join cashRecord on card.cardId=cashRecord.Cardid;

两张表银行卡号表，消费记录表

   
   
--银行卡号表
create table card
(
 cardid varchar2(20) primary key,
 userName varchar2(50)notnull,
 pwd varchar(6)notnull
);
create table cashRecord
(
 recordId int primary key,
 cardid int,
 cash float,
 cashType int,--0表示支出1表示收入
 cashTime date
);

这两张表以cardid关联，两张表的主键cardId、recordId

       键值保留表，表示保留主键的数据表，子表必须引用主表的主键或者是唯一键，如果修改的视图中的数据列是来自于A表，此时即表示为A表依赖于B表，B表中必须要有对应的主键或者是唯一键。

         视图

   
   
create or replace view cashView asselect recordId,card.cardId,userName,cash,case cashtype 
when1then'收入'
when0then'支出'endcase,cashtime from card inner join cashRecord on card.cardId=cashRecord.Cardid;

        对多表的视图进行修改

        以下就不能修改

        修改的是银行卡号表的数据，此时银行卡号表  依赖  消费记录表，但在消费记录表里，银行卡号并不是主键或唯一键，

   
   
update cashView set userName='tony'where cardId='100000001';

       例如A B两表各有字段ID列 如果视图已A.ID=B.ID进行表联接，当修改A表数据的时候则A于B关联，A表的数据应依赖于B表，因此此时的A表相当于子表，B表为主表，若B表不存在主键则无法修改数据；

对应的，如果修改B表数据的时候，B表依赖于A表，因此此时的B表相当于子表，A表为主表，因此要进行数据修改A表中必须存在主键