.Net程序员学用Oracle系列(8)：触发器、任务、序列、连接

2022-03-07 12:24:17

本文大纲

1、触发器
- 1.1、创建触发器
- 1.2、禁用触发器 & 启用触发器 & 删除触发器
2、任务
- 2.1、DBMS_JOB 包介绍
3、序列
- 3.1、创建序列
- 3.2、使用序列 & 删除序列
4、连接
- 4.1、创建 dblink
- 4.2、使用 dblink & 删除 dblink
5、总结

触发器

创建触发器

CREATE OR REPLACE TRIGGER trg_sync_staff90
-- 功能说明
AFTER INSERT OR UPDATE OR DELETE ON t_staff
FOR EACH ROW
DECLARE
  -- 定义变量
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF INSERTING THEN
    -- 定义DML语句
  ELSIF UPDATING THEN
    -- 定义DML语句
  ELSIF DELETING THEN
    -- 定义DML语句
  END IF;
END;

禁用触发器 & 启用触发器 & 删除触发器

ALTER TABLE t_staff DISABLE ALL TRIGGERS; -- 禁用 t_staff 表上所有的触发器
ALTER TABLE t_staff ENABLE ALL TRIGGERS;  -- 启用 t_staff 表上所有的触发器
ALTER TRIGGER trg_sync_staff90 DISABLE;   -- 禁用触发器 trg_sync_staff90
ALTER TRIGGER trg_sync_staff90 ENABLE;    -- 启用触发器 trg_sync_staff90
DROP TRIGGER trg_sync_staff90;            -- 删除触发器 trg_sync_staff90

任务

DBMS_JOB 包介绍

创建任务的方法有很多，在实际使用过程中，我发现用 DBMS_JOB.SUBMIT 包来创建是比较方便的，语法是 DBMS_JOB.SUBMIT(jobno, what, next_date, interval)，示例：

DECLARE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(jobno, 'sp_sync_staff90;', SYSDATE, 'TRUNC(SYSDATE) + 1 + 2/24');
  COMMIT;
END;

估计有人会感到疑惑，为什么创建任务的语句中 job 参数不给具体值，下面就来简要介绍下 DBMS_JOB.SUBMIT 各个参数及方法的用途和使用说明，如下：

job：任务编号，系统会自动分配。我尝试过给一个自己喜欢的数字，但系统不认，还是自动分配了。
what：任务要执行的操作（如调用过程等），可通过 DBMS_JOB.WHAT(jobno,what) 修改。
next_date：任务的下一次执行时间，可通过 DBMS_JOB.NEXT_DATE(jobno,next_date) 修改。
interval：任务执行时间间隔，可通过 DBMS_JOB.INTERVAL(jobno,interval) 修改。
启动 job：DBMS_JOB.RUN(jobno)。
禁用 job：DBMS_JOB.BROKEN(jobno,broken,next_date)，broken 参数需要提供布尔值，next_date 参数有默认值(SYSDATE)，不需要给值，broken 为 true 表示禁用任务，为 false 表示启用任务，如 DBMS_JOB.BROKEN(135,TRUE) 表示禁用 jobno 为 135 的任务。
删除 job：DBMS_JOB.REMOVE(jobno)。

以上关于 DBMS_JOB 的这些 PLSQL 都不能像普通 SQL 语句一样直接运行，需要写在 BEGIN 和 END 中间，示例：

BEGIN
  DBMS_JOB.BROKEN(135,TRUE);
  COMMIT;
END;

如果想看一下整个数据库目前的任务情况，可以通过 user_jobs 视图来查询，示例：

SELECT job jobno,what,to_char(next_date, 'yyyy-mm-dd hh24:mi:ss') next_date,interval FROM user_jobs;

下面来简要说明一下 user_jobs 视图各主要字段的含义，如下：

job：NUMBER 类型，任务的唯一编号。
what：VARCHAR2(4000)，任务操作内容。
next_date：DATE 类型，下一次执行任务的时间。
interval：VARCHAR2(200)，任务执行时间间隔。
log_user：提交任务的用户。
priv_user：赋予任务权限的用户。
schema_user：对任务作语法分析的用户模式。
last_date：最后一次成功运行任务的时间。
last_sec：如 hh24:mm:ss 格式的 last_date 日期的小时，分钟和秒。
this_date：正在运行任务的开始时间，如果没有运行任务则为 null。
this_sec：如 hh24:mm:ss 格式的 this_date 日期的小时，分钟和秒。

interval 参数设置案例：

每分钟（的0秒）执行一次：interval => 'TRUNC(SYSDATE,''mi'') + 1/(24*60)'
每小时（的0分0秒）执行一次：interval => 'TRUNC(SYSDATE,''hh24'') + 1/24'
每隔 7 天执行一次：interval => 'TRUNC(SYSDATE) + 7 + 1/24'
每天凌晨两点执行一次：interval => 'TRUNC(SYSDATE) + 1 + 2/24'
每周一凌晨 1 点执行一次：interval => 'TRUNC(NEXT_DAY(SYSDATE,''monday'')) + 1/24'
每月 5 号凌晨 1 点执行一次：interval => 'TRUNC(LAST_DAY(SYSDATE)) + 5 + 1/24'
每季度第一天凌晨 1 点执行一次：interval => 'TRUNC(ADD_MONTHS(SYSDATE,3),''Q'') + 1/24'
每年 1 月 1 号凌晨 1 点执行一次：interval => 'ADD_MONTHS(TRUNC(SYSDATE,''yyyy''),12) + 1/24'

序列

我接触的两个基于 Oracle 开发的项目都从不用序列，以至于我都没有使用序列的实战经验。写这个章节前我特意查了下序列的定义，我觉得我们可以把序列当成是数字工厂，因为它唯一的功能就是生产等间隔的数值。

用过 Oracle 的人应该都知道，Oracle 没有提供类似于 SQL Server 或 MySQL 中自动增长列的功能，如果我们出于对性能或空间等其它因素的考虑，需要使用自动增长列，则可以通过序列来实现类似功能。

创建序列

创建序列的标准语法如下：

CREATE SEQUENCE seq_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n/NOMAXVALUE}] [{MINVALUE n/NOMINVALUE}] [{CYCLE/NOCYCLE}] [{CACHE n/NOCACHE}];

语法选项说明：

INCREMENT BY n：n 表示序列中连续两个值之间的间隔，也称作步长。如果 n 是正数则表示递增，如果 n 是负数则表示递减，默认是 1。
START WITH n：n 表示序列的起始值，即序列的第一个值，默认是 1，递增时 n 是 minValue，递减时 n 是 maxValue。
MAXVALUE n：n 表示序列的最大值，也可以选择 NOMAXVALUE，即不设置最大值，默认是 999999999999999999999999999。
MINVALUE n：n表示序列的最小值，也可以选择 NOMINVALUE，即不设置最小值，默认是 1。
CYCLE 和 NOCYCLE 分别表示当序列的值达到极限值后循环取值和不循环取值。
CACHE n：n 定义存放序列的内存块的大小，默认为 20（个数字）。NOCACHE 表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

使用序列 & 删除序列

使用序列
- seq_name.CURRVAL：返回序列的当前值。
- seq_name.NEXTVAL：返回序列的下一个值。
以下情况之一不能使用序列：
- 1、在 DELETE、SELECT、UPDATE 的子查询中。
- 2、在视图或物化事物的查询中。
- 3、SELECT 查询中使用了 DISTINCT 操作符。
- 4、SELECT 查询中有 GROUP BY 或 ORDER BY。

如果想查询一下数据中到底有那些序列，语法如下：

SELECT * FROM USER_SEQUENCES;
SELECT * FROM ALL_SEQUENCES;
SELECT * FROM DBA_SEQUENCES;

删除序列，示例：

DROP SEQUENCE seq_name;

连接

Oracle 中有个叫做 Database link 的东东，翻译成中文应该是数据库连接，为了称呼方便，下文统一称之为 dblink。在我跟公司一个技术专家对话时，对方提到“数据库连接”，于是我上网查了下：dblink 是定义一个数据库到另一个数据库的路径的对象，dblink 允许你查询远程表及执行远程程序。

dblink 有两种类型的，分别是公用的和私有的。一个公用数据库链接对于数据库中的所有用户都是可用的，而一个私有链接仅对创建它的用户可用。由一个用户给另外一个用户授权私有数据库链接是不可能的，一个数据库链接要么是公用的，要么是私有的。在任何分布式环境里，dblink 都是必要的。另外要注意的是 dblink 是单向的连接。

创建 dblink

创建 dblink 之前先得确定三件事，第一本地数据库和远程数据库之间的网络是可以正常连接的，第二创建 dblink 的账号必须得有 CREATE DATABASE LINK 或 CREATE PUBLIC DATABASE LINK 的权限，第三用来登录到远程数据库的帐号必须得有 CREATE SESSION 权限。

实践告诉我，创建 dblink 的正确语法如下：

CREATE [PUBLIC] DATABASE LINK dblink_name CONNECT TO user_name IDENTIFIED BY user_pwd USING 'connect_string';

其中 connect_string 有两种写法，示例：

-- 第一种写法
CREATE PUBLIC DATABASE LINK dblink168
CONNECT TO office
IDENTIFIED BY 123456
USING '192.168.1.168:1521/orcl';
-- 第二种写法
CREATE PUBLIC DATABASE LINK dblink168
CONNECT TO office
IDENTIFIED BY 123456
USING '(DESCRIPTION = (
        ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.168)(PORT = 1521))
    )(
        CONNECT_DATA = (SERVICE_NAME = orcl)
    )
)';

网上很多文章中给出的创建语法里都不包含指定密码这一项，还说如果不指定，则使用当前的用户名和口令登录到远程数据库，我反复实验了好多次，都是直接报语法错误。不过我倒是发现了一个特点，指定密码创建成功之后，再去查看 dblink 定义的 SQL，会发现密码指令项不见了。我猜应该是 Oracle 做了特殊的加密处理，所以如果你要创建 dblink，不必担心显示指定密码后别人会看到，造成安全泄漏。

使用 dblink & 删除 dblink

使用 dblink

我觉得 dblink 真正的魅力之一便是使用方便，无论增删改查那种语句，只需要在表名后面跟上 @dblink_name 就能操作远程数据库了。如要查询 168 上用户表中女员工的数量，示例：

SELECT COUNT(1) FROM t_staff@dblink168 t WHERE t.gender=0;

测试中我也发现一个小问题，假如我要查询 168 上的服务器时间，按理说写法应该如下：

SELECT SYSDATE FROM DUAL@dblink168; -- 结果显示出来的时间仍是本地数据库的服务器时间

如果为了命名更加统一，或者不想让对方知道 dblink 的名字，也可以通过视图或同义词包装一下，示例：

CREATE VIEW v_name AS SELECT * FROM table_name@dblink_name;
CREATE SYNONYM table_name FOR table_name@dblink_name;

跟 dblink 有关的几个视图，介绍如下：

SELECT * FROM dba_db_links; -- 查询当前数据库实例中所有 dblink
SELECT * FROM v$dblink;     -- 查询当前数据库示例中正在打开状态的 dblink
SELECT * FROM user_sys_privs WHERE PRIVILEGE LIKE '%LINK%'; -- 查询跟 dblink 有关的系统权限

删除 dblink

对于非 PUBLIC 类型的 dblink，只有 owner 自己才能删除，非 PUBLIC 类型的 dblink 没有这个要求。删除 dblink 的示例：

DROP [PUBLIC] DATABASE LINK dblink168;

总结

本文主要介绍了 Oracle 中相对不那么常用的一些对象的 SQL 操作，但在实际做项目过程中，有时候它们又非常实用，所以有必要总结一下，以免用到的时候不记得语法，还得去查资料。

《.Net程序员学用Oracle系列：导航目录》

本文声明：如果您认为这篇文章还可以或对您有帮助，请点击文章末尾的“推荐”按钮。欢迎转载、演绎或用于商业目的，但必须保留本文的署名韩宗泽，并且要在明显位置给出原文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

码农公寓

触发器