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

目录

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

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

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

唯一索引

基于函数的索引

游标(cursor ) 与 fetch into

多重循环 

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

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

序列+触发器实现主键自增

数据库事务、提交、回滚

PL/Sql 执行 DML 语句

---

~~~ 准备员工表与部门表测试数据

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

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

视图并不能提供性能，理论上直接查表性能更高。更不推荐通过视图修改数据。

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

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 只读,此时会报错。

--查询每个部门最高薪水的员工姓名，薪水，以及他所属的部门名称。
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、查询视图：

5、表与视图设置注释

6、完整视图举例：人员信息同步视图.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]

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);

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

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

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

2、创建索引语法：create [unique] index index_name on table_name(column_name[,column_name…])

3、索引一旦建立，Oracle 会对其进行自动维护，而且由 Oracle 决定何时使用索引，用户不用在查询语句中指定使用哪个索引。

4、索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度。在删除一个表时，所有基于该表的索引会自动被删除。

5、在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引。

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

唯一索引

1、唯一索引和唯一约束一样，约束的列可以为 null，比如 dept_name 列添加了唯一索引，但是多行数据上的 dept_name 为 null，此时是允许的，因为 null 和 null 是不相等的。

2、常规用法：即对某一列或者多列添加唯一索引，即整个表中不允许重复。

CREATE UNIQUE INDEX index_name ON table_name(column_name[,column_name…]);

3、高级用法：产品设计时，通常会设计逻辑删除，即 is_deleted 表示是否删除，1是2否，用于保留历史数据备查，或者将来恢复。比如一个人员表中，无论同一个人如何的反复添加与删除，在表里面必须保证有效的(is_deleted=2)数据只有一条。此时上面的常规用法已经难以满足要求，因为此时需要在未删除数据上建立唯一约束，已删除数据不约束。

-- decode(表达式,值1,结果1,[值2,结果2...][,否则]：select 中实现 if else 功能。
-- 注意结果的类型必须相同，值的类型必须相同。
-- 人员信息表添加唯一索引
-- 同一区划(mof_div_code)、单位(agency_code)、年度(fiscal_year)、版本(version)、人员类型(ui_code)下有效身份证(iden_no)只允许有一条
-- per_id 主键
-- is_deleted 是否删除,1是2否，必录项.
-- is_deleted=2时，约束有效数据只允许有一条；否则 is_deleted=1 时 per_id 本身是主键，即已删除数据不约束。
CREATE UNIQUE INDEX uni_indx_bas_person_info ON bas_person_info 
	(mof_div_code, agency_code, fiscal_year, version, ui_code, iden_no, DECODE(TO_CHAR(is_deleted), '2', '2', per_id));

-- biz_key 允许为null，所以需要加 nvl 处理，当它为null时，使用主键.
CREATE UNIQUE INDEX "支出标准业务唯一标识（BIZ_KEY）应该唯一标识一条记录。" ON BAS_EXP_CRI (mof_div_code, nvl(BIZ_KEY,exp_cri_id), DECODE(TO_CHAR(is_deleted), '2', '2', exp_cri_id));

 

基于函数的索引

1、上一节唯一索引中使用的 decode 是 Oracle 自带的函数，这种用法就是'基于函数的索引'，可以满足在复杂的业务场景中使用。除了使用 Oracle 自带的函数，也可以自己创建函数创建索引。

2、下面自定义含义来实现 decode 函数类似的功能。

--准备表
create table DEPT(
    DEPTNO  NUMBER(2) not null constraint DEPT_PK primary key,
    DNAME   VARCHAR2(14),
    LOC     VARCHAR2(13),
    DELETED NUMBER(1) not null check ( DELETED in (1, 2) )
);
--需求：
--1、每个地区的部门名称(dname)必须唯一，即有效数据(deleted=2)只能有1条；
--2、其中波士顿(loc=BOSTON)地区允许名称(dname)随意重复；
/**
 * 创建函数
 * 参数 v_dept_no：部门主键ID，表中有非空约束，主键永不重复；
 * 参数 v_dept_loc：位置，可为空；
 * 参数 v_deleted：是否删除(1是2否)，表中有非空约束；
 */
CREATE OR REPLACE FUNCTION GET_DEPT_UNI01(v_dept_no in Dept.DEPTNO%type,
                                          v_dept_loc in Dept.LOC%type,
                                          v_deleted in Dept.DELETED%type)
    --必须添加关键字'deterministic-不可逆转的'，否则创建基于函数的索引时会报错：ORA-30553: The function is not deterministic
    --如果返回的是随机值或者序列，删除(delete)操作会报错未找到索引键：ORA-08102: index key not found
    return varchar2 deterministic is
begin
    if v_dept_loc is not null and upper(trim(v_dept_loc)) = 'BOSTON' then
        --是波士顿地区时，允许随意重复；主键永不重复,所以返回主键即可；
        --防止类型不一致，统一转成字符串再返回.
        return to_char(v_dept_no);
    else
        if v_deleted = 1 then
            --如果是删除数据，则同样允许有多条，因为同一个部门可以反复新增删除；
            return to_char(v_dept_no);
        else
            --有效数据只允许有1条，所以原样返回2即可。
            return to_char(v_deleted);
        end if;
    end if;
 
end;
/
--创建基于函数的索引
CREATE UNIQUE INDEX uni_dept1 ON dept (DNAME, GET_DEPT_UNI01(DEPTNO, LOC, DELETED));
--等价于 decode
CREATE UNIQUE INDEX uni_dept2 ON dept (DNAME, decode(upper(trim(LOC)), 'BOSTON', DEPTNO, decode(DELETED, 1, DEPTNO, 2)));

游标(cursor ) 与 fetch into

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

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

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

游标开发步骤：

 普通游标示例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;--结尾必须要有分号

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

 引用游标示例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 流程控制)。

--给所有员工加薪，总裁加 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;
 

多重循环 

 游标嵌套遍历示例：

多重循环示例：https://blog.csdn.net/wangmx1993328/article/details/102514681#多重循环。 

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

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;
     close vrows;--关闭游标
end;

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

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

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

2、触发器类型：

3、触发器创建语法：

基础库触发器汇总.sql

 触发器示例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;

  触发器示例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;

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

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

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

--语句级触发器：
--a）工作日（星期六、星期天）不能操作 emp 表中的员工数据
--b）下班时间(9-18以外的时间)，不能操作 emp 表中的员工数据
create or replace trigger trig_delete_emp_check
-- 在 demp 执行 增删改 操作前触发
  before insert or update or delete on emp
declare
begin
  if to_char(sysdate, 'day') in ('星期六', '星期日') or
     to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
    --抛出异常，比如 java 程序调用时抛出此异常，则 java 程序中就能看到此异常信息.
    raise_application_error(-20001, '禁止在非工作时间操作员工数据');
  end if;
end;

触发器示例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;

触发器示例5：当往 dept 表中插入(insert)数据后(after)，同时将数据备份到 dept_backup 表中：

--为 dept 部门表创建备份表
create table dept_backup as select * from dept where 1=2;
 
-- 创建触发器：当往 dept 表中插入数据后，同时将数据备份到 dept_backup 表中
create or replace trigger trig_dept_add_log
after insert on dept
for each row
declare
begin
    insert into dept_backup(deptno,dname,loc) values(:new.deptno,:new.dname,:new.loc);
end;

基础库触发器汇总.sql

序列+触发器实现主键自增

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

数据库事务、提交、回滚

1、事务特性：

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

3、事物常见操作有：

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

PL/Sql 执行 DML 语句

https://wangmaoxiong.blog.csdn.net/article/details/102514681