当前位置:   article > 正文

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

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

or replace:表示如果视图已经存在,则替换
with read only:表示视图只读。视图默认可以修改目标表的数据,如添加/更新/删除数据。

with check option:表示只允许修改视图能看到的数据,如下所示,此时无法通过 emp10 视图新增 10号部门以外的数据,也无法修改部门号。

create or replace view emp10 as SELECT T.* FROM emp t where deptno =10 with check option;

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

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

3、删除视图:drop view 视图名,如:drop view emp_view_2;--删除视图 emp_view_2

4、查询视图

select * from user_views; --查询当前用户下创建好所有视图
select * from all_views; --查询所有用户下创建好所有视图

-- 查询某个表被当前用户下的哪些视图引用
select * from user_dependencies where REFERENCED_NAME='表名' and TYPE='VIEW';

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]

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

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

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

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

1. 不能修改序列的初始值。
2. 最小值不能大于当前值。
3. 最大值不能小于当前值。
alter sequence seq1 cache 10;

修改序列

drop sequence seq2;使用 "drop sequence序列名" 命令可以删除一个序列对象,如删除序列 seq2
select * from user_sequences;--查询当前用户下创建的所有序列
select * from all_sequences t where t.SEQUENCE_OWNER = 'SYS';--查询所有用户创建的所以序列,SEQUENCE_OWNER 字段是用户名称(必须大写)

索引(index )创建、删除、查询

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

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

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

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

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

1. unique:指定索引列上的值必须是唯一的,称为唯一索引,否则就是普通索引。
2. index_name :指定的索引名。
3. tabl_name :指定要为哪个表创建索引。
4. column_name :指定要对哪个列创建索引。(可以对多列创建索引,称为组合索引 )

select * from user_indexes; -- 查询当前登陆用户下的所有索引

distinct_keys:比较一下唯一键的数量和表中的行数,就可以判断索引的选择性。选择性越高,索引返回的数据就越少。
clustering_factor: 该列反映了数据相对于已建索引的列是否显得有序。
如果它的值接近于索引中的树叶块(leaf的数目),表中的数据就越有序,如果它的值接近于表中的行数,则表中的数据就不是很有序。

select * from all_indexes; -- 查询所有用户下的所有索引

SELECT T.* FROM dba_indexes t;-- 查询所有用户下的所有索引

-- 查找表中的所有索引列(包括索引名,类型,构成列)
select t.*, i.index_type
  from user_ind_columns t, user_indexes i
 where t.index_name = i.index_name
   and t.table_name = i.table_name
   and t.table_name = 'BAS_PERSON_INFO';
drop index UK_ID_CART_ZZRY_XZ; --删除索引 UK_ID_CART_ZZRY_XZ

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

  1. --准备表
  2. create table person(
  3. pid number(32) primary key,
  4. pname varchar2(16) not null,
  5. paddress varchar2(16) not null
  6. );
  7. --使用 PLSql 语法插入 500万条数据。plsql 是 Oracle 对原生sql的封装,是 oralce 自己独有的
  8. declare
  9. begin
  10. for i in 1..5000000 loop
  11. insert into person values(i,'姓名'||i,'地址'||i);
  12. end loop;
  13. commit;
  14. end;
  15. --在没有使用索引的情况下,查询 panme='姓名4000000' 的用户 。耗时:3-5
  16. select * from person where pname = '姓名4000000';
  17. --实际中建表后就应该设置索引,这里已经有500万条数据后再创建索引花了32
  18. create index index_pname on person(pname);--为 pname 列创建索引
  19. --为 pname 列创建索引后再次查询,耗时:0.030 ,可见有没有索引完全是云泥之别
  20. select * from person where pname = '姓名4000000';
  21. --在没有复合索引的情况查询 panme='姓名4000000' 且 paddress='地址4000000' 的用户,耗时:0.032
  22. select * from person where pname = '姓名4000000' and paddress = '地址4000000';
  23. --为 panme 、paddress 创建复合索引后再次查询。耗时:0.25 与没建复合索引区别不是很明显
  24. create index index_pname_paddress on person(pname,paddress);--花了 46
  25. select * from person where pname = '姓名4000000' and paddress = '地址3500000';
  26. --经实测发现,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)数据只有一条。此时上面的常规用法已经难以满足要求,因为此时需要在未删除数据上建立唯一约束,已删除数据不约束。

  1. -- decode(表达式,值1,结果1,[值2,结果2...][,否则]:select 中实现 if else 功能。
  2. -- 注意结果的类型必须相同,值的类型必须相同。
  3. -- 人员信息表添加唯一索引
  4. -- 同一区划(mof_div_code)、单位(agency_code)、年度(fiscal_year)、版本(version)、人员类型(ui_code)下有效身份证(iden_no)只允许有一条
  5. -- per_id 主键
  6. -- is_deleted 是否删除,1是2否,必录项.
  7. -- is_deleted=2时,约束有效数据只允许有一条;否则 is_deleted=1 时 per_id 本身是主键,即已删除数据不约束。
  8. CREATE UNIQUE INDEX uni_indx_bas_person_info ON bas_person_info
  9. (mof_div_code, agency_code, fiscal_year, version, ui_code, iden_no, DECODE(TO_CHAR(is_deleted), '2', '2', per_id));
  1. -- biz_key 允许为null,所以需要加 nvl 处理,当它为null时,使用主键.
  2. 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 函数类似的功能。

  1. --准备表
  2. create table DEPT(
  3. DEPTNO NUMBER(2) not null constraint DEPT_PK primary key,
  4. DNAME VARCHAR2(14),
  5. LOC VARCHAR2(13),
  6. DELETED NUMBER(1) not null check ( DELETED in (1, 2) )
  7. );
  8. --需求:
  9. --1、每个地区的部门名称(dname)必须唯一,即有效数据(deleted=2)只能有1条;
  10. --2、其中波士顿(loc=BOSTON)地区允许名称(dname)随意重复;
  11. /**
  12. * 创建函数
  13. * 参数 v_dept_no:部门主键ID,表中有非空约束,主键永不重复;
  14. * 参数 v_dept_loc:位置,可为空;
  15. * 参数 v_deleted:是否删除(1是2否),表中有非空约束;
  16. */
  17. CREATE OR REPLACE FUNCTION GET_DEPT_UNI01(v_dept_no in Dept.DEPTNO%type,
  18. v_dept_loc in Dept.LOC%type,
  19. v_deleted in Dept.DELETED%type)
  20. --必须添加关键字'deterministic-不可逆转的',否则创建基于函数的索引时会报错:ORA-30553: The function is not deterministic
  21. --如果返回的是随机值或者序列,删除(delete)操作会报错未找到索引键:ORA-08102: index key not found
  22. return varchar2 deterministic is
  23. begin
  24. if v_dept_loc is not null and upper(trim(v_dept_loc)) = 'BOSTON' then
  25. --是波士顿地区时,允许随意重复;主键永不重复,所以返回主键即可;
  26. --防止类型不一致,统一转成字符串再返回.
  27. return to_char(v_dept_no);
  28. else
  29. if v_deleted = 1 then
  30. --如果是删除数据,则同样允许有多条,因为同一个部门可以反复新增删除;
  31. return to_char(v_dept_no);
  32. else
  33. --有效数据只允许有1条,所以原样返回2即可。
  34. return to_char(v_deleted);
  35. end if;
  36. end if;
  37. end;
  38. /
  39. --创建基于函数的索引
  40. CREATE UNIQUE INDEX uni_dept1 ON dept (DNAME, GET_DEPT_UNI01(DEPTNO, LOC, DELETED));
  41. --等价于 decode
  42. 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)声明游标    —— cursor 游标名(参数) is 查询结果集
2)打开游标    —— open 游标名
3)从游标中读取数据    —— fetch 游标名 into 变量
        游标名%isopen :判断游标是否打开
        游标名%rowcount :当前为止 fetch 得到的数据行总条数
        游标名%found :表示找到数据
        游标名%notfound :表示没有找到数据
4)关闭游标    —— close 游标名

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

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

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

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

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

1)声明游标:游标名 sys_refcursor
2)打开游标:open 游标名 for 查询结果集
3)从游标中读取数据    —— fetch 游标名 into 变量
        游标名%isopen :判断游标是否打开
        游标名%rowcount :当前为止 fetch 得到的数据行总条数
        游标名%found :表示找到数据
        游标名%notfound :表示没有找到数据
4)关闭游标    —— close 游标名

 引用游标示例1:

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

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

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

多重循环 

 游标嵌套遍历示例:

declare
 cursor dept_rows is select * from dept; -- 定义游标
 cursor emp_rows is select * from emp; -- 定义游标
begin
 for t1 in dept_rows loop  --遍历游标
   dbms_output.put_line('单位:deptno=' ||t1.DEPTNO||',dname='||t1.DNAME||',loc='||t1.LOC);
   for t in emp_rows loop  --遍历游标
     dbms_output.put_line('    员工:empno=' ||t.EMPNO||',ename='||t.ENAME||',hiredate='||t.HIREDATE||',job='||t.JOB||',sal='||t.SAL);
   end loop;
 end loop;  
end;

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

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

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

  1. --创建存储过程,用于分页查询
  2. --传入参数:pageNo 查询的页码,pageSize 每页的条数;输出参数:vrows 使用一个引用游标用于接收多条结果集。普通游标无法做到,只能使用引用游标
  3. create or replace procedure pro_query_emp_limit(pageNo in number,pageSize in number,vrows out sys_refcursor) is
  4. begin
  5. --存储过程中只进行打开游标,将 select 查询出的所有数据放置到 vrows 游标中,让调用着进行获取
  6. 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
  7. where t.r between ((pageNo-1) * pageSize+1) and pageNo * pageSize;
  8. end;
  9. --使用引用游标读取上面的存储过程返回的值
  10. declare
  11. vrows sys_refcursor ;--声明引用游标
  12. vrow emp%rowtype; --定义变量接收遍历到的每一行数据
  13. begin
  14. pro_query_emp_limit(4,3,vrows);--调用存储过程
  15. loop
  16. fetch vrows into vrow; -- fetch into 获取游标的值
  17. exit when vrows%notfound; -- 如果没有获取到值,则退出循环
  18. dbms_output.put_line('姓名:'|| vrow.ename || ' 薪水:'|| vrow.sal);
  19. end loop;
  20. close vrows;--关闭游标
  21. end;

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

触发器(trigger创建、删除、查询)

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

2、触发器类型:

语句级触发器在SQL(比如insert、update、delete等)语句操作之前或者之后触发一次,不管这条语句影响了多少行,无法获取具体行的数据。
行级触发器

针对SQL语句影响的每一行进行触发,可以使用 :old 和 :new 伪记录变量获取数据前后的值,

insert 操作(无论before还是after)可以用 :new 获取插入的新值,而 :old 获取的值恒为 null.

delete 操作(无论before还是after)可以用 :old 获取被删除的旧值,而 :new 获取的值恒为 null.

update 操作(无论before还是after)可以用 :new、:old 可以获取到修改前后的值。

3、触发器创建语法:

create [or replace] trigger 触发器名 before/after insert/update/delete [of 列名] on 表名
[for each row [when(条件)]]
declare
...
begin
    PLSQL块
end;

--before:表示在 insert/update/delete 操作之前触发
--after:表示在 insert/update/delete 操作之后触发

--[of 列名]:表示只对指定的列进行触发,默认是所有列。
--for each row:表示行级触发器,
否则就是语句级触发器。想要在插入或者更新前触发,则是:before insert or update [of 列名] on 表名

--[when (条件)]:行级触发器时,表示对指定条件的数据行进行触发。

drop trigger 触发器名

删除触发器

--注意:如果触发器名称含有特殊字符,则必须使用双引号包裹,否则报错

drop trigger "BIN$wsVXhP2Omf7gU90eEwpAUg==$0";

alter  trigger  触发器名 enable | disable;

禁用与启用触发器,如:alter trigger trig_dept_del_backup disable;

alter table 表名  disable | enable all  triggers;禁用与启用指定表上的所有触发器,不会影响其它表
select  *  from  all_triggers;

查询所有用户创建好的所有触发器

select  *  from  user_triggers;查看当前用户下的所有触发器
查询触发器(TRIGGER)、存储过程(PROCEDURE)、函数(FUNCTION),类型(Type) 等对象的源代码。
SELECT * FROM user_source T where t.NAME='xxx' order by t.LINE;

基础库触发器汇总.sql

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

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

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

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

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

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

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

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

触发器示例4:当往 ele_agency 表新增或者更新数据时,如果 level_num 字段值为 null,则使用 level_no 字段的值赋给 level_num。

  1. -- 创建触发器,当往 ele_agency 表新增数据时
  2. -- 如果 level_num 字段的值为 null,则使用 level_no 字段的值赋给 level_num
  3. -- 如果 isdept 字段的值为 null ,则默认设置为 1
  4. create or replace trigger trig_ele_agency_before_insert
  5. before insert or update on ele_agency
  6. for each row
  7. declare
  8. begin
  9. if :new.level_num is null then
  10. dbms_output.put_line('ele_agency 插入新数据,其中 level_num 为 null,将 level_no 的值【' || :new.level_no || '】赋给 level_num');
  11. :new.level_num := :new.level_no;
  12. end if;
  13. if :new.isdept is null then
  14. dbms_output.put_line('ele_agency 插入新数据,其中 isdept 为 null,强制将其值设置为 1!');
  15. :new.isdept := 1;
  16. end if;
  17. end;

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

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

基础库触发器汇总.sql

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

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

数据库事务、提交、回滚

1、事务特性:

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

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

3、事物常见操作有:

1)提交、回滚:commit、rollback
2)设置保存点:savepoint 保存点
3)回滚到保存点:rollback to 保存点

4、事务可以显式的提交,也可以隐式提交(即自动提交事务):

显式提交:commit
隐式提交:遇到 DDL(数据定义语言),如  create 、alter 、drop 命令等会自动提交事务;遇到 DCL (数据控制语言),如 grant (授权)命令、 revoke (撤销)命令等。以及关闭会话,断开连接时也会自动提交。

PL/Sql 执行 DML 语句

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/602028
推荐阅读
相关标签
  

闽ICP备14008679号