Oracle 视图、序列、索引、游标、触发器、事务

超、凢脫俗 2022-04-25 08:32 310阅读 0赞

**目录**

视图（view ）创建、删除、查询

序列（sequence）创建、修改、删除、查询

索引（index ）创建、删除、查询

游标(cursor ) fetch into

引用游标读取存储过程返回的结果集

触发器（trigger创建、删除、查询）

序列+触发器模拟主键自增

数据库事务、提交、回滚

--------------------

[~~~ 准备员工表与部门表测试数据][Link 1]

# 视图（view ）创建、删除、查询 #

1、视图是查询结果的一个封装，视图中的所有数据都来自它查询的表，视图本身不存储任何数据。

2、视图能过封装复杂的查询结果，**创建视图**：create \[or replace\] view 视图名 as 查询语句 \[with read only\];

<table> 
 <tbody> 
  <tr> 
   <td>or replace：表示如果视图已经存在，则替换<br> with read only：表示视图是否只读，默认视图是可以修改的，如添加/更新/删除数据。</td> 
  </tr> 
 </tbody> 
</table>

select * from emp;--查询员工表
    create or replace view emp_view as select empno,ename,job from emp;--创建一个视图，此时它是可以修改的数据的
    create or replace view emp_view_2 as select empno,ename,job from emp with read only;--创建一个视图，视图只读，无法修改数据
    --查询视图，增删改查视图与表是一样的，视图就相当于封装后的表。此时只能查询指定的3列，起到了对 emp 表的封装作用
    select * from emp_view;
    select * from emp_view_2;
    --因为视图是不存储数据的，这里对视图的修改就相当于对 emp 的修改。实际应用中应该避免视图来修改数据，让它尽量只读
    update emp_view set ename='liSi' where empno = 7499;--可以修改数据
    update emp_view_2 set ename='liSi2' where empno = 7499;--无法再修改数据，因为 emp_view2 只读,此时会报错。

![20190530185444710.gif][]

--查询每个部门最高薪水的员工姓名，薪水，以及他所属的部门名称。
    select e1.ename, e1.sal,d1.dname from emp e1,(select deptno,max(sal) as maxsal from emp group by deptno) t1,dept d1 
           where e1.deptno = t1.deptno and e1.sal = t1.maxsal and e1.deptno = d1.deptno;
    --对于复杂的查询语句，封装成视图后，查询会看起来清爽很多
    create or replace view emp_dept_v1 as
    select e1.ename, e1.sal,d1.dname from emp e1,(select deptno,max(sal) as maxsal from emp group by deptno) t1,dept d1 
           where e1.deptno = t1.deptno and e1.sal = t1.maxsal and e1.deptno = d1.deptno
    with read only;
    select * from emp_dept_v1;--同样是检索各部门薪水最高的员工，通过视图转一层

3、**删除视图**：drop view 视图名，如：drop view emp\_view\_2;--删除视图 emp\_view\_2

4、**查询视图**：

<table> 
 <tbody> 
  <tr> 
   <td>select * from user_views; --查询当前用户下创建好所有视图<br> select * from all_views; --查询所有用户下创建好所有视图</td> 
  </tr> 
 </tbody> 
</table>

5、[表与视图设置注释][Link 2]

6、完整视图举例：[人员信息同步视图.sql][.sql]

# 序列（sequence）创建、修改、删除、查询 #

1、序列是一种数据库对象，用来自动生成一组唯一的序号。序列是一种共享式的对象，多个用户可以共同使用序列中的序号。一般将序列应用于表的主键列，确保主键不会重复。

2、Sequence 创建语法：create sequence sequence\_name \[increment by num\] \[start with num\] \[maxvalue num|nomaxvalue\] \[minvalue num|nominvalue\] \[cycle|nocycle\] \[cache num|nocache\]

<table> 
 <tbody> 
  <tr> 
   <td>① start with num：设置序列开始的第一个整数。默认值为 1 。<br> ② increment by&nbsp; num：设置每次增长的数。正数表示升序，负数表示降序。默认值为 1 。<br> ③ maxvalue num | nomaxvalue ：设置最大值 num，nomaxvalue 关键字表示默认值，升序是 10的27次方 ，降序是 1 。<br> ⑤ minvalue&nbsp; num|&nbsp;nominvalue ：设置最小值 num，nominvalue 关键字表示默认值，升序是 1 ，降序是 10的26 次方。<br> &nbsp;4）cycle | nocycle：关键字表示序列达到最大值或者最小值，是否重新开始。如 cylce 表示升序达到最大值后从最小值重新开始，降序序列达到最小值后从最大值重新开始。nocycle 表示不重新开始，序列升序达到极限值后就报错。<span style="color:#f33b45;">默认 nocycle</span><br> 5）cache num：使用 cache 选项时，该序列会根据序列规则 预生成一组序列号 。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列 号的效率。<span style="color:#f33b45;"> oracle 默认会生产 20 个序列号 。</span><br> 6）nocache ：不预先在内存中生成序列号。</td> 
  </tr> 
 </tbody> 
</table>

3、序列创建之后，可以通过序列对象的 currval 和 nextval 两个“伪列” 分别访问该序列的当前值和下一个值。currval 必须在 nextval 调用之后才能使用。

--创建一个从 1 开始，默认最大值，每次增长 1 的序列，要求 NOCYCLE ，缓存中有 30 个预先分配好的序列号。
    create sequence seq1 minvalue 1 start with 1 nomaxvalue increment by 1 nocycle cache 30;
    select seq1.nextval from dual;--获取序列下一个值
    select seq1.currval from dual;--获取序列当前值，必须在使用 nextval 后有了值以后才能获取当前值
    
    create sequence seq3 ;--实际中这种使用最多，其余全部使用默认值
    --为 student 插入数据，主键从 seq3 序列中获取
    insert into student values(seq3 .nextval,'李四','男',22,23,sysdate,'雨花区人民中路28号',1002);

![20190530210036473.gif][]

5、使用 alter sequence 可以修改序列，修改只影响新产生的值，不影响已产生的值，在修改序列时有如下限制：

<table> 
 <tbody> 
  <tr> 
   <td>1. 不能修改序列的初始值。<br> 2. 最小值不能大于当前值。<br> 3. 最大值不能小于当前值。</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>alter sequence seq1 cache 10;</td> 
   <td> <p>修改序列</p> </td> 
  </tr> 
  <tr> 
   <td>drop sequence seq2;</td> 
   <td>使用<span style="color:#f33b45;"> "drop sequence序列名" 命令可以删除一个序列对象</span>，如删除序列 seq2</td> 
  </tr> 
  <tr> 
   <td>select * from user_sequences;</td> 
   <td>--查询当前用户下创建的所有序列</td> 
  </tr> 
  <tr> 
   <td>select * from all_sequences t where t.SEQUENCE_OWNER = 'SYS';</td> 
   <td>--查询所有用户创建的所以序列，SEQUENCE_OWNER 字段是用户名称(必须大写)</td> 
  </tr> 
 </tbody> 
</table>

# 索引（index ）创建、删除、查询 #

1、索引相当于一本书的目录，能提高检索的速度，如果某一列需要经常作为查询条件，则有必要为其创建索引，能显著提供效率。

2、创建索引语法：create \[unique\] index index\_name on table\_name(column\_name\[,column\_name…\])

<table> 
 <tbody> 
  <tr> 
   <td>1.<span style="color:#f33b45;"> unique：指定索引列上的值必须是唯一的，称为唯一索引，否则就是普通索引。</span><br> 2. index_name ：指定的索引名。<br> 3. tabl_name ：指定要为哪个表创建索引。<br> 4. column_name ：指定要对哪个列创建索引。（可以对多列创建索引，称为组合索引 ）</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>select * from user_indexes; -- 查询当前登陆用户下的所有索引<br> select * from all_indexes; -- 查询所有用户下的所有索引</td> 
  </tr> 
  <tr> 
   <td>drop index UK_ID_CART_ZZRY_XZ; --删除索引 UK_ID_CART_ZZRY_XZ</td> 
  </tr> 
 </tbody> 
</table>

3、Oracle 数据库会为 表的主键 和 包含唯一约束的列自动创建索引。

--准备表
    create table person(
       pid number(32) primary key,
       pname varchar2(16) not null,
       paddress varchar2(16) not null
    );
    
    --使用 PLSql 语法插入 500万条数据。plsql 是 Oracle 对原生sql的封装，是 oralce 自己独有的
    declare 
    begin
      for i in 1..5000000 loop
        insert into person values(i,'姓名'||i,'地址'||i);
        end loop;
        commit;
    end;
    
    --在没有使用索引的情况下，查询 panme='姓名4000000' 的用户 。耗时：3-5 秒
    select * from person where pname = '姓名4000000';
    --实际中建表后就应该设置索引，这里已经有500万条数据后再创建索引花了32秒
    create index index_pname on person(pname);--为 pname 列创建索引
    --为 pname 列创建索引后再次查询，耗时：0.030       ,可见有没有索引完全是云泥之别
    select * from person where pname = '姓名4000000';
    
    --在没有复合索引的情况查询 panme='姓名4000000' 且 paddress='地址4000000' 的用户，耗时：0.032
    select * from person where pname = '姓名4000000' and paddress = '地址4000000';
    --为 panme 、paddress 创建复合索引后再次查询。耗时：0.25        与没建复合索引区别不是很明显
    create index index_pname_paddress on person(pname,paddress);--花了 46秒
    select * from person where pname = '姓名4000000' and paddress = '地址3500000';
    
    --经实测发现，pname 建了复合索引之后，select * from person where pname = '姓名4000000'; 速度更慢了

Pl/SQL 工具中可以选中查询语句然后按 F5 进行查看优化目标

4、索引的原理底层使用的是平衡二叉树。数据库中索引（Index ）的概念与目录的概念非常类似。如果某列出现在查询的条件中  
而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。 创建索引就是对某些特定列中的数据排序，生成独立的索引表。 在某列上创建索引后，如果该列出现在查询条件中，Oracle 会自动的引用该索引，先从索引表中 查询出符合条件记录的 rowid，由于 rowid 是记录的物理地址，因此可以根据 rowid 快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

5、如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该 列创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的 10%时，索引就非常有用。

6、索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

# 游标(cursor ) 与 fetch into #

1、游标：用于操作查询的结果集，类似 JDBC 的 ResultSet。

[2、select . into . 查询并赋值数据][2_select . into .]只能对单个结果进行处理，如果 select 查询的结果是多条，则可以借助游标进行遍历。

**普通游标语法**：cursor 游标名\[(参数名 参数类型)\] is 查询结果集

游标开发步骤：

<table> 
 <tbody> 
  <tr> 
   <td>1）声明游标&nbsp;&nbsp;&nbsp; —— <span style="color:#f33b45;">cursor </span>游标名(参数) is 查询结果集<br> 2）打开游标&nbsp;&nbsp;&nbsp; —— <span style="color:#f33b45;">open </span>游标名<br> 3）从游标中读取数据&nbsp;&nbsp;&nbsp; —— <span style="color:#f33b45;">fetch </span>游标名 <span style="color:#f33b45;">into </span>变量<br> &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;游标名%<span style="color:#f33b45;">isopen </span>：判断游标是否打开<br> &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;游标名%<span style="color:#f33b45;">rowcount </span>：当前为止 fetch 得到的数据行总条数<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 游标名%<span style="color:#f33b45;">found </span>：表示找到数据<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 游标名%<span style="color:#f33b45;">notfound </span>：表示没有找到数据<br> 4）关闭游标&nbsp;&nbsp;&nbsp; —— <span style="color:#f33b45;">close </span>游标名</td> 
  </tr> 
 </tbody> 
</table>

**普通游标示例1：**最基本的用法

--输出/打印所有员工的姓名与薪水。
    --declare - begin - end 为 PLSQL 固定结构。declare 中定义变量，begin 中执行业务/逻辑操作，end 表示结束
    declare
    cursor vrows is select * from emp;--1、声明游标
    vrow emp%rowtype;--2、声明变量 vrow，变量类型为 emp 中的一行
    begin
      open vrows;  --3、打开游标
      loop--4、使用 loop 循环遍历游标
        fetch vrows into vrow;--5、遍历游标 v rows 的每一行结果给(into)遍历 vrow
        exit when vrows%notfound;--6、当游标 vrows 没有再读取到值时，则退出循环
        --dbms_output.put_line()：plsql 的输出语句
        dbms_output.put_line('姓名：'||vrow.ename || ' 工资：'|| vrow.sal);
      end loop;
      close vrows; -- 结束时必须关闭游标
    end;

**普通游标示例2：**定义带参数的游标

--输出/打印指定部门(deptno)下员工的姓名与薪水
    declare 
    --1、声明带参数的游标，参数名为 dno，参数类型为 number
    cursor vrows(dno number) is select * from emp where deptno = dno;
    vrow emp%rowtype;--2、定义变量 vrow，为 emp 行类型
    begin
         open vrows(10);--3、打开游标。查询 10 号部门
         loop --4、遍历游标，读取数据进行处理
         fetch vrows into vrow;
         if  vrows%notfound then  --5、当游标中没有再读取到数据时，退出循环
           exit;
         end if;
         dbms_output.put_line('姓名：'|| vrow.ename || ' 薪水：'|| vrow.sal);
         end loop;
         close vrows;     --6、必须关闭游标
    end;--结尾必须要有分号

**系统引用游标**：与上面的普通游标写法上就是第一、二步稍有不同，使用步骤：

<table> 
 <tbody> 
  <tr> 
   <td>1）声明游标：游标名 <span style="color:#f33b45;">sys_refcursor</span><br> 2）打开游标：<span style="color:#f33b45;">open </span>游标名 <span style="color:#f33b45;">for </span>查询结果集<br> 3）从游标中读取数据&nbsp;&nbsp;&nbsp; —— <span style="color:#f33b45;">fetch </span>游标名 <span style="color:#f33b45;">into </span>变量<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 游标名%<span style="color:#f33b45;">isopen </span>：判断游标是否打开<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 游标名%<span style="color:#f33b45;">rowcount </span>：当前为止 fetch 得到的数据行总条数<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 游标名%<span style="color:#f33b45;">found </span>：表示找到数据<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 游标名%<span style="color:#f33b45;">notfound </span>：表示没有找到数据<br> 4）关闭游标&nbsp;&nbsp;&nbsp; —— <span style="color:#f33b45;">close </span>游标名</td> 
  </tr> 
 </tbody> 
</table>

**引用游标示例1：**

--仍然输出/打印所有员工的姓名与薪水
    declare 
    vrows sys_refcursor;--1、声明系统引用游标
    vrow emp%rowtype;--2、声明接收变量
    begin
    open vrows for select * from emp;--3、打开游标
    loop--4、遍历游标
      fetch vrows into vrow;
      exit when vrows%notfound; -- 5、游标读取不到值时，退出循环
      dbms_output.put_line('姓名：'|| vrow.ename || ' 薪水：'|| vrow.sal);
    end loop;
    close vrows;-- 6、必须关闭游标
    end;

**for 循环遍历游标(推荐方式)：**for 方式遍历游标时，不需要额外声明每次遍历接收的变量，也不再需要手动开/关游标，for 循环会自动进行处理([PL/sql 流程控制][PL_sql])。

--给所有员工加薪，总裁加 1000，经理加600，其它人加 400
    declare
    cursor vrows is select * from emp;--1、声明游标（使用普通游标）
    begin
      for vrow in vrows loop-- 2、for 循环遍历游标，无需手动开关游标
      -- 3、根据不同的职位(job)加薪，使用 PLSQL 的 if else 判断语法
      if vrow.job = 'PRESIDENT' then
        update emp set sal = sal + 1000 where empno = vrow.empno;
      elsif vrow.job = 'MANAGER' then
        update emp set sal = sal + 600 where empno = vrow.empno;
      else
        update emp set sal = sal + 400 where empno = vrow.empno;
      end if;
      end loop; 
      commit;--4、提交事务
    end;

![20190601142730399.gif][]

** 游标嵌套遍历示例：**

<table> 
 <tbody> 
  <tr> 
   <td> <p>declare<br> &nbsp;cursor dept_rows is select * from dept; -- 定义游标<br> &nbsp;cursor emp_rows is select * from emp; -- 定义游标<br> begin<br> &nbsp;for t1 in dept_rows loop&nbsp; --遍历游标<br> &nbsp;&nbsp; dbms_output.put_line('单位：deptno=' ||t1.DEPTNO||'，dname='||t1.DNAME||'，loc='||t1.LOC);<br> &nbsp;&nbsp; for t in emp_rows loop&nbsp; --遍历游标<br> &nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line('&nbsp;&nbsp;&nbsp; 员工：empno=' ||t.EMPNO||'，ename='||t.ENAME||'，hiredate='||t.HIREDATE||'，job='||t.JOB||'，sal='||t.SAL);<br> &nbsp;&nbsp; end loop;<br> &nbsp;end loop; &nbsp;<br> end;</p> </td> 
  </tr> 
 </tbody> 
</table>

# 引用游标读取存储过程返回的结果集 #

1、存储过程如果返回的只是单个结果，如返回整数 3、字符串 ok 等，则使用普通的变量作为输出参数即可接收。当存储过程返回的结果有多条时，比如分页查询，区间查询，in 查询等等，此时可以借助引用游标来进行取值(普通游标不行)。

--创建存储过程，用于分页查询
    --传入参数：pageNo 查询的页码，pageSize 每页的条数；输出参数：vrows 使用一个引用游标用于接收多条结果集。普通游标无法做到，只能使用引用游标
    create or replace procedure pro_query_emp_limit(pageNo in number,pageSize in number,vrows out sys_refcursor) is
    begin
      --存储过程中只进行打开游标，将 select 查询出的所有数据放置到 vrows 游标中，让调用着进行获取
    open vrows for select t.empno,t.ename,t.job,t.mgr,t.hiredate,t.sal,t.comm,t.deptno from (select rownum r,t1.* from emp t1) t 
         where t.r between ((pageNo-1) * pageSize+1) and pageNo * pageSize;
    end;
    
    --使用引用游标读取上面的存储过程返回的值
    declare
         vrows sys_refcursor ;--声明引用游标
         vrow emp%rowtype; --定义变量接收遍历到的每一行数据
    begin
         pro_query_emp_limit(4,3,vrows);--调用存储过程
         loop 
           fetch vrows into vrow; -- fetch into 获取游标的值
           exit when vrows%notfound; -- 如果没有获取到值，则退出循环
           dbms_output.put_line('姓名：'|| vrow.ename || ' 薪水：'|| vrow.sal);
         end loop;
    end;

![20200328192319683.gif][]

在 JDBC 代码中调用存储过程时也是如此。

# 触发器（trigger创建、删除、查询） #

1、触发器是一个与表关联的、存储的 PL/SQL 程序，当用户执行了 insert、update、delete 操作之前/后，Oracle 自动地执行触发器中定义的语句序列。

2、触发器类型：

<table> 
 <tbody> 
  <tr> 
   <td style="width:101px;">表级触发器</td> 
   <td>针对整个表的 <span style="color:#f33b45;">insert、update、delete&nbsp; </span>等操作进行触发，无法获取具体行的数据。</td> 
  </tr> 
  <tr> 
   <td style="width:101px;"><span style="color:#f33b45;">行级触发器</span></td> 
   <td>针对表中的具体行进行触发，<span style="color:#f33b45;">可以使用 :old 和 :new 伪记录变量</span>获取数据前后的值，如 insert 操作可以使用 :new 获取插入的新值，delete 操作可以使用 :old 获取被删除的旧值，update 操作可以使用 :new 获取修改后的值，也可以使用 :old 获取修改前的值。</td> 
  </tr> 
 </tbody> 
</table>

3、触发器创建语法：

<table> 
 <tbody> 
  <tr> 
   <td> <p>create [or replace] trigger 触发器名 before/after insert/update/delete [of 列名] on 表名<br> [for each row [when(条件)]]<br> declare<br> ...<br> begin<br> &nbsp;&nbsp;&nbsp; PLSQL块<br> end;</p> <p><span style="color:#f33b45;">--before：表示在 insert/update/delete 操作之前触发<br> --after：表示在 insert/update/delete 操作之后触发<br> --for each row：表示行级触发器，否则就是表级触发器</span></p> <p><span style="color:#f33b45;">-- 想要在插入或者更新前触发，则是：before insert or update</span><span style="color:#fe2c24;"> [of 列名] on 表名</span></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td style="width:469px;"><span style="color:#f33b45;">drop trigger 触发器名</span></td> 
   <td style="width:432px;"><span style="color:#f33b45;">删除触发器</span></td> 
  </tr> 
  <tr> 
   <td style="width:469px;">alter&nbsp; trigger&nbsp; <span style="color:#f33b45;">触发器名 </span>enable | disable;</td> 
   <td style="width:432px;"> <p><span style="color:#f33b45;">禁用与启用触发器</span>，如：alter trigger trig_dept_del_backup disable;</p> </td> 
  </tr> 
  <tr> 
   <td style="width:469px;">alter table <span style="color:#f33b45;">表名&nbsp; </span>disable | enable all&nbsp; triggers;</td> 
   <td style="width:432px;">禁用与启用指定表上的所有触发器，不会影响其它表</td> 
  </tr> 
  <tr> 
   <td style="width:469px;">select&nbsp; *&nbsp; from&nbsp; all_triggers;</td> 
   <td style="width:432px;"> <p>查询所有用户创建好的所有触发器</p> </td> 
  </tr> 
  <tr> 
   <td style="width:469px;">select&nbsp; *&nbsp; from&nbsp; user_triggers;</td> 
   <td style="width:432px;">查看当前用户下的所有触发器</td> 
  </tr> 
 </tbody> 
</table>

** 触发器示例1：**删除部门(dept )时，使用行级触发器级联删除关联的员工数据。

--删除部门 dept 时，使用行级触发器级联删除关联的员工的数据。
    --因为需要获取删除的部门id，所以需要使用行级触发器，因为需要在删除部门前先删除员工，所以使用 before
    create or replace trigger trig_dept_del_emp
    before delete on dept --在 dept 执行 delete 操作前触发
    for each row --使用行级触发器才能获取到操作行的值
    declare
    dept_val dept.deptno%type; -- 接收被删除单位 id
    begin
        -- 使用 select into 获取被触发行的旧值(:old获取)，然后赋给变量 dept_val
        select :old.deptno into dept_val from dual;
        delete from emp t where t.deptno = dept_val;
    end;

![20200318201318678.gif][]

** 触发器示例2：**删除部门数据时，使用行级触发器将被删除的数据自动备份到备份表中

--为 dept 部门表创建备份表
    create table dept_backup as select * from dept where 1=2;
    
    --创建触发器：删除部门 dept 数据时，将被删除的数据备份到 dept_backup 表中
    create or replace trigger trig_dept_del_backup
    before delete on dept -- 1、在 dept 执行 delete 操作前触发
    for each row -- 2、使用行级触发器才能获取到操作行的值
    declare
    dept_row dept%rowtype; -- 3、定义参数，类型为 dept 行类型，接收行参数
    
    begin
        -- 4、使用 select into 获取被触发行的旧值(:old获取)，然后赋给变量 dept_row
        select :old.deptno,:old.dname,:old.loc into dept_row.deptno,dept_row.dname,dept_row.loc from dual;
        -- 5、完备份表插入数据，值从 dept_row 行变量中获取.
        insert into dept_backup values(dept_row.deptno,dept_row.dname,dept_row.loc);
    end;

![20200827191316777.gif][]

注意事项：上面的触发器中第5步不能使用此种方式：insert into dept\_backup select \* from dept t where t.deptno = :old.deptno;

否则触发器被触发时会报错如下，即无法在触发器中操作被触发的表中的数据，即使是查询也不行。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70][]

**触发器示例3：**不满足要求时，主动抛出异常，让脚本无法再继续执行。

<table> 
 <tbody> 
  <tr> 
   <td> <p>--创建触发器，工作日（星期六、星期天）不能删除 emp 表中的员工数据<br> create or replace trigger trig_delete_emp_check<br> before delete on emp -- 1、在 demp 执行 delete 操作前触发<br> for each row -- 2、使用行级触发器<br> declare<br> begin<br> &nbsp; dbms_output.put_line('今天是：'|| to_char(sysdate,'day'));<br> &nbsp; if to_char(sysdate,'day') in ('星期六','星期日') then<br> &nbsp;&nbsp;&nbsp;&nbsp; --3、<span style="color:#f33b45;">抛出异常，比如 java 程序调用时抛出此异常，则 java 程序中就能看到此异常信息.</span><br> &nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#f33b45;">raise_application_error</span>(-20001,'禁止在非工作时间删除员工数据');<br> &nbsp; end if;<br> end;</p> </td> 
  </tr> 
 </tbody> 
</table>

![20200904144222997.gif][]

**触发器示例4：**当往 ele\_agency 表新增或者更新数据时，如果 level\_num 字段值为 null，则使用 level\_no 字段的值赋给 level\_num。

-- 创建触发器，当往 ele_agency 表新增数据时
    -- 如果 level_num 字段的值为 null，则使用 level_no 字段的值赋给 level_num
    -- 如果 isdept 字段的值为 null ，则默认设置为 1
    create or replace trigger trig_ele_agency_before_insert
      before insert or update on ele_agency
      for each row
    declare
    begin
      if :new.level_num is null then
    	dbms_output.put_line('ele_agency 插入新数据，其中 level_num 为 null，将 level_no 的值【' || :new.level_no || '】赋给 level_num');
    	:new.level_num := :new.level_no;
      end if;
      if :new.isdept is null then
    	dbms_output.put_line('ele_agency 插入新数据，其中 isdept 为 null，强制将其值设置为 1！');
    	:new.isdept := 1;
      end if;
    end;

触发器示例：[https://gitee.com/wangmx1993/material/blob/master/sql/oracle/][https_gitee.com_wangmx1993_material_blob_master_sql_oracle][基础库触发器汇总.sql][https_gitee.com_wangmx1993_material_blob_master_sql_oracle]

# 序列+触发器模拟主键自增 #

--模拟 mysql 中主键 id 的自增属性 auto_increment
    --Oralce 中可以使用 序列 sequence 结合 触发器 trigger 达到同样的效果
    
    --先建一张表
    create table person2(
    pid number(32) primary key,
    pname varchar2(16)
    );
    
    create sequence person2_id_sequ;--创建一个序列。默认从1开始，每次递增1，没有最大值
    
    --创建触发器
    create or replace trigger trig_person2_add_pid
    before insert on person2 --在插入数据前触发，因为需要修改 pid 的 null值
    for each row  --行级触发器
    declare
    begin
        dbms_output.put_line('新增员工名称为：'||:new.pname);--打印语句，可以删除
        select person2_id_sequ.nextval into :new.pid from dual;--正式插入前修改新记录的  pid 字段值
    end;
    
    --插入用户
    insert into person2 values(null,'华安');--插入前触发器会自动通过序列修改 pid 的 null 值为具体的数字
    insert into person2 values(2,'华安');--此时自己设置 pid 也是无效的
    select * from person2;

![20190602132808293.gif][]

# 数据库事务、提交、回滚 #

1、事务特性：

<table> 
 <tbody> 
  <tr> 
   <td>1）原子性( Atomicity)：原子意为最小的粒子，或者说不能再分的事物。数据库事务的不可再分的原则即为原子性，要么全部成功，要么全部无效。<br> 2）一致性( Consistency)：指数据的规则在事务前/后应保持一致。<br> 3）隔离性( Isolation)：简单来说是某个事务的操作对其他事务不可见<br> 4）持久性( Durability)：当事务完成后，其影响应该保留下来,不能撤消。</td> 
  </tr> 
 </tbody> 
</table>

2、Oracle 中事物默认是开启的，即执行任意 dml(数据库操纵语句，如 Insert、update、delete等)，事物默认是打开的.

3、事物常见操作有：

<table> 
 <tbody> 
  <tr> 
   <td>1）提交、回滚：commit、rollback<br> 2）设置保存点：savepoint 保存点<br> 3）回滚到保存点：rollback to 保存点</td> 
  </tr> 
 </tbody> 
</table>

4、事务可以显式的提交，也可以隐式提交（即自动提交事务）：

<table> 
 <tbody> 
  <tr> 
   <td>显式提交：commit<br> 隐式提交：遇到 DDL（数据定义语言），如 &nbsp;create 、alter 、drop 命令等会自动提交事务；遇到 DCL （数据控制语言），如 grant （授权）命令、 revoke （撤销）命令等。以及关闭会话，断开连接时也会自动提交。</td> 
  </tr> 
 </tbody> 
</table>

![20200314182823592.gif][]

[Link 1]: https://wangmaoxiong.blog.csdn.net/article/details/90581383#%E5%87%86%E5%A4%87%E5%91%98%E5%B7%A5%E8%A1%A8%E4%B8%8E%E9%83%A8%E9%97%A8%E8%A1%A8%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE
[20190530185444710.gif]: /images/20220125/6318957724734881a3b620d251c2d2a3.png
[Link 2]: https://wangmaoxiong.blog.csdn.net/article/details/90690400#%E8%A1%A8%E4%B8%8E%E8%A7%86%E5%9B%BE%E8%AE%BE%E7%BD%AE%E6%B3%A8%E9%87%8A
[.sql]: https://gitee.com/wangmx1993/material/blob/master/sql/oracle/%E4%BA%BA%E5%91%98%E4%BF%A1%E6%81%AF%E5%90%8C%E6%AD%A5%E8%A7%86%E5%9B%BE.sql
[20190530210036473.gif]: /images/20220125/772b3745eeaa49aca7ebcc0955971e12.png
[2_select . into .]: https://wangmaoxiong.blog.csdn.net/article/details/102514681#PL%2Fsql%20%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE
[PL_sql]: https://wangmaoxiong.blog.csdn.net/article/details/102514681#PL%2Fsql%20%E5%9D%97%E4%B8%AD%E6%B5%81%E7%A8%8B%E6%8E%A7%E5%88%B6
[20190601142730399.gif]: /images/20220125/a8d2b7bd32e3446f820913dd971f54d5.png
[20200328192319683.gif]: /images/20220125/7d6826af5da64acebc841a91136f3a29.png
[20200318201318678.gif]: /images/20220125/ac375ecc33f2459e84bbd0f835dd9ece.png
[20200827191316777.gif]: /images/20220125/32c7dff89a214523a34fbcfc7d14a3f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70]: /images/20220125/0f496e71d0fe4af985c995698428a887.png
[20200904144222997.gif]: /images/20220125/9fba8f31705c4982b1953b4ba1c48491.png
[https_gitee.com_wangmx1993_material_blob_master_sql_oracle]: https://gitee.com/wangmx1993/material/blob/master/sql/oracle/%E5%9F%BA%E7%A1%80%E5%BA%93%E8%A7%A6%E5%8F%91%E5%99%A8%E6%B1%87%E6%80%BB.sql
[20190602132808293.gif]: /images/20220125/dfa633bc616f4090919525516a1b0523.png
[20200314182823592.gif]: /images/20220125/8256b43939a741ff910de48cf404295e.png