目录

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

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

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

游标(cursor ) fetch into

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

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

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

数据库事务、提交、回滚

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

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

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

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

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 数据库会为 表的主键 和 包含唯一约束的列自动创建索引。

--准备表
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 . 查询并赋值数据只能对单个结果进行处理，如果 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;

游标嵌套遍历示例：

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

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;

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

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

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

2、触发器类型：

3、触发器创建语法：

触发器示例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：不满足要求时，主动抛出异常，让脚本无法再继续执行。

触发器示例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/基础库触发器汇总.sql

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

--模拟 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;

数据库事务、提交、回滚

1、事务特性：

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

3、事物常见操作有：

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