{aa25aa} 本文大纲
- 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.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 各个参数及方法的用途和使用说明,如下:
以上关于
的这些 PLSQL 都不能像普通 SQL 语句一样直接运行,需要写在 BEGIN 和 END 中间,示例:
- DBMS_JOB
- 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
:NUMBER 类型,任务的唯一编号。
- job
:VARCHAR2(4000),任务操作内容。
- what
:DATE 类型,下一次执行任务的时间。
- next_date
:VARCHAR2(200),任务执行时间间隔。
- interval
:提交任务的用户。
- log_user
:赋予任务权限的用户。
- priv_user
:对任务作语法分析的用户模式。
- schema_user
:最后一次成功运行任务的时间。
- last_date
:如
- last_sec
格式的
- hh24:mm:ss
日期的小时,分钟和秒。
- last_date
:正在运行任务的开始时间,如果没有运行任务则为 null。
- this_date
:如
- this_sec
格式的
- hh24:mm:ss
日期的小时,分钟和秒。
- this_date
interval 参数设置案例:
我接触的两个基于 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
- }];
语法选项说明:
如果想查询一下数据中到底有那些序列,语法如下:
- 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 的账号必须得有 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_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 有关的系统权限
对于非 PUBLIC 类型的 dblink,只有 owner 自己才能删除,非 PUBLIC 类型的 dblink 没有这个要求。删除 dblink 的示例:
- DROP [PUBLIC] DATABASE LINK dblink168;
本文主要介绍了 Oracle 中相对不那么常用的一些对象的 SQL 操作,但在实际做项目过程中,有时候它们又非常实用,所以有必要总结一下,以免用到的时候不记得语法,还得去查资料。
来源: