文章说明:
本文章主要介绍实现了达梦数据的触发器、物化视图、DBLINK、函数、存储过程等相关内容,跟随文章学习需要下载达梦数据库并安装示例库,更多关于分区表等内容可以移至官网学习。
一、触发器
--创建测试数据
create table test1(id int);
insert into test1 values(1);
select * from test1;
--表级触发器:创建模板
CREATE OR REPLACE TRIGGER TRG_NAME [WITH ENCRYPTION]
BEFORE|AFTER|INSTEAD OF
INSERT OR DELETE OR UPDATE [OF 列名]
ON TABLE_NAME
FOR EACH ROW [WHEN 条件] --行级触发器中,此子句一定不能省略
BEGIN
PRINT 'INSERT OR DELETE OR UPDATE OPERATION ON TABLE_NAME';--要执行的SQL。
END;
/*
[WITH ENCRYPTION]:对数据进行加密,加密以后,无法看到触发器里的 sql 语句内容。
UPDATE [OF 列名]:如果指定了 UPDATE 命令,还可以进一步指定当表中的哪个列受到 UPDATE 命令的影响时激发该触发器。
FOR EACH ROW [WHEN 条件] :对于元组级触发器,可以用一个 WHEN 子句来限制针对当前记录是否执行该触发器。
WHEN 子句包含一条布尔表达式,当它的值为 TRUE 时,执行触发器;否则,跳过该触发器。
*/
--创建语句级触发器
CREATE OR REPLACE TRIGGER TEST1_TRI
AFTER UPDATE ON TEST1
FOR EACH STATEMENT --语句级触发器:对每个触发命令都执行一次
BEGIN
PRINT 'TEST1表被更新';
END;
SELECT * FROM TEST1;
UPDATE TEST1 SET ID=2 WHERE ID=1;
--创建行级触发器:对触发命令所影响的每一条记录都激发一次
INSERT INTO TEST1 VALUES(3); --插入测试数据
INSERT INTO TEST1 VALUES(4);
INSERT INTO TEST1 VALUES(5);
INSERT INTO TEST1 VALUES(6);
INSERT INTO TEST1 VALUES(7);
SELECT * FROM TEST1;
CREATE OR REPLACE TRIGGER TEST1_TRIROW
AFTER DELETE ON TEST1
FOR EACH ROW
BEGIN
PRINT '执行TEST1表数据删除1次';
END;
DELETE FROM TEST1 WHERE ID<5;
--时间触发器:时间触发器属于一种特殊的事件触发器,可以定义一些有规律性执行的、定点执行的任务。
--eg:创建时间触发器,在屏幕上每隔一分钟输出一行HELLO WORLD
CREATE OR REPLACE TRIGGER timer1
AFTER TIMER on DATABASE
for each 1 day for each 1 minute
BEGIN
PRINT 'HELLO WORLD';
END;
ALTER TRIGGER timer1 DISABLE; --关闭不删除触发器
ALTER TRIGGER timer1 ENABLE; --打开触发器
SELECT * FROM DBA_TRIGGERS; --查看当前数据库的全部触发器
SELECT * FROM ALL_TRIGGERS; --查看当前用户有权限访问的触发器
SELECT * FROM USER_TRIGGERS; --查看当前用户所拥有的触发器
DROP TRIGGER timer1; --删除触发器
/*
注意
1.<触发器名> 是触发器的名称,它不能与模式内的其他模式级对象同名。
2.可以使用 OR REPLACE 选项来替换一个触发器,但是要注意被替换的触发器的触发表不能改变。如果要在同一模式内不同的表上重新创建一个同名的触发器,则必须先删除该触发器,然后再创建。
3.INSTEAD OF 触发器仅允许建立在视图上,并且只支持行级触发。
4.表级触发器不支持跨模式,即 <触发器名> 必须和 <触发表名>、<触发视图名> 的模式名一致。
5.水平分区子表、HUGE 表不支持表级触发器。
6.同类触发器的激发顺序没有明确的定义。如果顺序非常重要的话,应该把所有的操作组合在一个触发器中。
7.在 DM 的数据守护环境下,备库上定义的触发器是不会被触发的。
*/
二、闪回查询
/*
闪回查询只支持普通表(包括加密表与压缩表)、水平分区表和堆表,不支持临时表、列存储表、外部表与视图;
闪回查询中 trxid 的值,一般需要由闪回版本查询的伪列来确定。实际使用中多采用指定时刻的方式。
*/
--查询闪回功能状态,数据库默认关闭(0-关闭,1-打开)
select name,value,sys_value,file_value
from v$parameter
where name LIKE '%FLASHBACK%';
--开启闪回功能方法一:参数名必须加单引号
alter system set 'enable_flashback'=1 both;
select name,value,sys_value,file_value --再次查询
from v$parameter
where name LIKE '%FLASHBACK%';
--开启闪回功能方法二
SP_SET_PARA_VALUE(1,'ENABLE+FLASHBACK',1); --第一个参数为1(内存和dm.ini参数都修改,不需重启服务器,为2则只修改dm.ini中的值,需要重启服务器)
SELECT SF_GET_PARA_VALUE(1,'ENABLE_FLASHBACK')in_file, --获取参数
SF_GET_PARA_VALUE(2,'ENABLE_FLASHBACK')in_mem;
alter system set 'undo_retention'=1200 both; --回滚记录的保留时间设置为1200s
select name,type,value,sys_value,file_value --查询修改后的值
from v$parameter
where name='UNDO_RETENTION';
--按时间查询历史记录,该闪回查询在disql工具中进行(使用 dmhr 模式下的 city 表)
--查询 city_id = 'CD' 时 city 表中的信息
SELECT *
FROM dmhr.city
WHERE city_id = 'CD';
--更新 city_id = 'CD' 时 REGION_ID 的值,更新时间为 '2020-11-03 09:56:06'
UPDATE dmhr.city
SET region_id = 10
WHERE city_id = 'CD';
--再次查询 city_id = 'CD' 时 city 表中的信息,此时 REGION_ID 的值为更新后的值
SELECT *
FROM dmhr.city
WHERE city_id = 'CD';
--闪回查询 update 操作前的 city 表中的信息,结果显示的是更新前 'REGION_ID' 的值
SELECT *
FROM dmhr.city
WHEN TIMESTAMP '2020-11-03 09:56:06'
WHERE city_id='CD';
--(2)按事务 id 号查询历史记录
SELECT versions_endtrxid, *
FROM dmhr.job VERSIONS BETWEEN TIMESTAMP '2020-11-03 10:30:00' AND SYSDATE
WHERE JOB_ID = 22;
UPDATE dmhr.job SET MIN_SALARY = 3500 WHERE JOB_ID = 22;COMMIT; --调整文员最低工资为3500并提交
UPDATE dmhr.job SET MIN_SALARY = 1000 WHERE JOB_ID = 22;COMMIT; --调整文员最低工资为1000并提交
SELECT versions_endtrxid, *
FROM dmhr.job VERSIONS BETWEEN TIMESTAMP '2020-11-03 10:30:00' AND SYSDATE
WHERE JOB_ID = 22;
SELECT * FROM dmhr.job WHEN TRXID 24717 WHERE JOB_ID=22; --指定事务ID为原始数据的事务ID24717,即工资MIN_SALARY为2500时
--闪回事务查询提供系统视图 V$FLASHBACK_TRX_INFO,查看在事务级对数据库所做的更改。根据该视图信息,可以确定如何还原指定事务或指定时间段内的修改
SELECT commit_trxid, operation,table_name,undo_sql FROM V$FLASHBACK_TRX_INFO;
三、物化视图
--参数
--数据填充时机
BUILD IMMEDIATE:立即填充
BUILD DEFERRED:延迟填充且第一次需要全量填充
--刷新模式
FAST:根据相关表上的数据更改记录进行增量刷新。普通 DML 操作生成的记录存在于物化视图日志中。使用 FAST 刷新之前,必须先建好物化视图日志。
COMPLETE:通过执行物化视图的定义脚本进行完全刷新。
FORCE:默认选项。当快速刷新可用时采用快速刷新,否则采用完全刷新。
--刷新时机
ON COMMIT:相关表上有数据提交时进行刷新。刷新由异步线程执行,数据同步可能存在延迟。
START WITH… NEXT:START WITH 指首次刷新的时间,省略 START WITH,首次刷新时间为当前时间。
NEXT 指定刷新的时间间隔,省略 NEXT,则物化视图只刷新一次。
(ps:START WITH 或者 NEXT 均不指定,不会**自动执行**物化视图刷新。)
ON DEMAND:用户可通过 REFRESH 语法手动刷新,不能和 START WITH 一起使用。
NEVER REFRESH:物化视图从不刷新。
--刷新依据
WITH PRIMARY KEY 基于主键
/*只能基于单表
必须含有 PRIMARY KEY 约束,选择列必须直接含有所有的 PRIMARY,KEY(UPPER(col_name) 的形式不可接受)
不能含有对象类型
WITH ROWID 基于 rowid
只能基于单表
不能含有对象类型
若同时使用 WITH ROWID 及快速刷新,则必须将 ROWID 提取出来,和其他列名一起,以别名的形式显示
*/
--物化视图的分类
SIMPLE:无 GROUP BY、无聚集函数、无连接操作
AGGREGATE:仅包含 GROUP BY 和聚集函数
JOIN:仅包含多表连接
Sub-Query:仅包含子查询
COMPLEX:除上述四种外的物化视图类型
SELECT * FROM SYS.USER_MVIEWS; --查询物化视图的分类
--(1)创建基于主键的物化视图:必须有主键
CREATE MATERIALIZED VIEW m_employees REFRESH WITH PRIMARY KEY AS
SELECT * FROM dmhr.EMPLOYEE;
--查询创建的物化视图
SELECT * FROM user_mviews t WHERE mview_name='M_EMPLOYEES'; --必须将创建的物化视图在系统内部重命名为M_EMPLOYEES,不然查询不到
SELECT segment_name,bytes,blocks,extents FROM dba_segments WHERE segment_name='MTAB$_M_EMPLOYEES'; --dba_segments中,可以查询到物化视图占用内存
--(2)创建基于rowid的物化视图:若表上无主键,则可以使用rowid方式创建物化视图
CREATE MATERIALIZED VIEW mv_employees_rowid refresh with rowid AS
SELECT * FROM DMHR.EMPLOYEE;
SELECT * FROM user_mviews t WHERE mview_name='MV_EMPLOYEES_ROWID'; --查询物化视图
SELECT * FROM mv_employees_rowid; --查询物化视图的数据
--物化视图的日志
(1):创建物化视图的日志:一张表上只能创建一个物化视图日志,基于主键或者ROWID
--eg:创建基于主键的物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee with primary key;
--eg:创建基于ROWID的物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH ROWID;
(2):删除物化视图日志:
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
四、DBLINK
/*
数据库链接对象LINK是DM中的一种特殊的数据库实体对象,它记录了远程数据库的连接和路径信息,用于建立与远程数据的联系
*/
--1、DM->DM 同构数据库链接
/*
两台服务器,其中一个为目的主机 A,另一个为测试机 B;分别在这两台服务器上进入数据库安装目录下的库目录里修改 dm.ini 文件:MAL_INI=1,实例名 INSTANCE_NAME 要对应,且配置 dmmal.ini(如无此文件,新建此文件)如下所示:
Copy
[mal_inst1]
mal_inst_name = DMSERVER --A的实例名
mal_host = 127.0.0.1 --A的ip
mal_port = 5282
[mal_inst2]
mal_inst_name = DMSERVER2 --B的实例名
mal_host = 127.0.0.1 --B的ip
mal_port = 5283
*/
/*
在主机 A 上建表 test,如下所示:
*/
CREATE TABLE TEST(C1 INT,C2 VARCHAR(20));
--在 B 上建立到 A 的数据库链接 LINK01,使用链接进行插入、更新和删除操作。如下所示:
CREATE PUBLIC LINK LINK01 CONNECT WITH SYSDBA IDENTIFIED BY SYSDBA USING '127.0.0.1/5282';
INSERT INTO TEST@LINK01 VALUES(1,'A');
INSERT INTO TEST@LINK01 VALUES(2,'B');
UPDATE TEST@LINK01 SET C2='C' WHERE C1=1;
DELETE FROM TEST@LINK01 WHERE C1=2;
COMMIT;
--在 B 上查询 A 服务器上表 test 的数据。如下所示:
select * from TEST@LINK01;
DROP LINK LINK01; --删除数据库连接LINK01
--2、DM->ORACLE
CREATE LINK LINK1 CONNECT 'ORACLE' WITH USER01 IDENTIFIED BY USER01PASSWD USING '127.0.0.1/orcl';
/*
数据库连接目前只支持 DM、Oracle 或 ODBC。
DM-DM 的同构数据库链接不支持 MPP 环境,DM 与异构数据库的数据库链接支持 MPP 环境。
增删改不支持 INTO 语句。
不支持使用游标进行增删改操作。
不支持操作远程表的复合类型列。
DBLINK 理论上不支持 LOB 类型列的操作,但支持简单的增删改语句中使用常量来对 LOB 类型列进行操作。
*/
五、视图、同义词
--SET SCHEMA SALES; --进入SALES模式
--创建视图
CREATE VIEW vw_test1 AS select * from TEST1 ;
--对视图进行查询
SELECT * FROM vw_test1;
--创建同义词(同义词Synonym让用户能够为数据库的一个模式下的对象提供别名,可以替换模式下的表、视图、序列、函数、存储过程等对象)
--eg:在dmhr模式下的表T1创建同义词
SET SCHEMA DMHR; --进入DMHR模式
CREATE TABLE DMHR.T1( --创建测试表
ID INTEGER,
NAME VARCHAR(50),
PRIMARY KEY(ID)
);
INSERT INTO DMHR.T1(ID,NAME) VALUES(1,'wxs');
INSERT INTO DMHR.T1(ID,NAME) VALUES(2,'una');
CREATE SYNONYM DMHR.S2 FOR DMHR.T1; --为表T1创建同义词S2
SELECT * FROM DMHR.S2;
DROP SYNONYM DMHR.S2; --删除同义词S2
六、存储过程
--存储过程创建与使用:一组为了完成特定功能的SQL语句集,,一次编译后永久有效
--1、创建带参数存储过程:
CREATE TABLE test_tab( --创建测试表
id int primary key,
name varchar(30)
);
CREATE OR REPLACE PROCEDURE pro_test(i IN int) --创建存储过程pro_test
as j int;
BEGIN
FOR j in 1 ..i LOOP
INSERT INTO test_tab values(j,'pro_test'||j); --向表test_tab插入数据
END LOOP;
END;
SELECT * FROM test_tab; --调用触发器前,test_tab表里没有数据
pro_test(3); --调用存储过,向表中插入三条数据
SELECT * FROM test_tab; --对test_tab表进行查询
--2、创建不带参数存储过程:
CREATE OR REPLACE PROCEDURE pro_test2
as j int;
BEGIN
FOR j in 5 ..10 LOOP --循环,插入ID从5到10的数据
INSERT INTO test_tab values(j,'pro_test2'||j);
END LOOP;
END;
CALL pro_test2; --调用不带参数的存储过程pro_test2
SELECT * FROM test_tab; --对test_tab表进行查询
七、函数
--达梦数据库的函数可以在SELECT、INSERT、UPDATE、DELETE等语句中使用,接受0个或多个输入参数,并返回一个输出结果,支持自定义函数
--函数的使用
SELECT employee_name,LENGTH(employee_name) FROM "DMHR"."EMPLOYEE";
--自定义函数的创建
/*
1、输入部分:函数可以有输入参数,在调用函数时,必须给输入参数赋值
2、逻辑计算部分:逻辑计算部是由DMSQL块组成业务逻辑计算部分,这部分主要是通过输入参数、表数据、SQL计算函数等进行逻辑计算得到想要的结果
3、输出部分:通过逻辑计算部分,会得到一个函数的唯一返回指进行返回(函数必须要有返回值)
*/
/*--创建函数模板
create [or replace] function 函数名
([p1,p2...pn])
return datatype
is|as
--声明部分
begin
--程序块
end
语法解析:
function: 是创建函数的关键字。
p1,p2...pn: 是函数的入参,DM 数据库创建的函数也可以不需要入参。
return datatype: 是函数的返回值的类型。
通过 is 或者 as 承接着程序块。这部分是函数的计算内容。
*/
CREATE OR REPLACE FUNCTION GET_SEX(id_card IN VARCHAR(50)) --自建获取性别的函数
RETURN CHAR(2) --返回一个CHAR类型的值
AS
v_sex CHAR(2); --变量
BEGIN
IF to_number(substr(id_card,17,1))%2=1 THEN --计算
v_sex:= '男';
ELSE
v_sex:= '女';
END IF;
RETURN v_sex; --返回变量v_sex的值
END;
select identity_card ,GET_SEX(identity_card) from DMHR.EMPLOYEE; --通过自定义身份证计算获取性别
八、DM 数据库常用内置函数和说明数值函数
序号 | 函数名 | 功能简要说明 |
---|---|---|
01 | ABS(n) | 求数值 n 的绝对值 |
02 | ACOS(n) | 求数值 n 的反余弦值 |
03 | ASIN(n) | 求数值 n 的反正弦值 |
04 | ATAN(n) | 求数值 n 的反正切值 |
05 | ATAN2(n1,n2) | 求数值 n1/n2 的反正切值 |
06 | CEIL(n) | 求大于或等于数值 n 的最小整数 |
07 | CEILING(n) | 求大于或等于数值 n 的最小整数,等价于 CEIL(n) |
08 | COS(n) | 求数值 n 的余弦值 |
09 | COSH(n) | 求数值 n 的双曲余弦值 |
10 | COT(n) | 求数值 n 的余切值 |
11 | DEGREES(n) | 求弧度 n 对应的角度值 |
12 | EXP(n) | 求数值 n 的自然指数 |
13 | FLOOR(n) | 求小于或等于数值 n 的最大整数 |
14 | GREATEST(n {,n}) | 求一个或多个数中最大的一个 |
15 | GREAT (n1,n2) | 求 n1、n2 两个数中最大的一个 |
16 | LEAST(n {,n}) | 求一个或多个数中最小的一个 |
17 | LN(n) | 求数值 n 的自然对数 |
18 | LOG(n1[,n2]) | 求数值 n2 以 n1 为底数的对数 |
19 | LOG10(n) | 求数值 n 以 10 为底的对数 |
20 | MOD(m,n) | 求数值 m 被数值 n 除的余数 |
21 | PI() | 得到常数 π |
22 | POWER(n1,n2)/POWER2(n1,n2) | 求数值 n2 以 n1 为基数的指数 |
23 | RADIANS(n) | 求角度 n 对应的弧度值 |
24 | RAND([n]) | 求一个 0 到 1 之间的随机浮点数 |
25 | ROUND(n[,m]) | 求四舍五入值函数 |
26 | SIGN(n) | 判断数值的数学符号 |
27 | SIN(n) | 求数值 n 的正弦值 |
28 | SINH(n) | 求数值 n 的双曲正弦值 |
29 | SQRT(n) | 求数值 n 的平方根 |
30 | TAN(n) | 求数值 n 的正切值 |
31 | TANH(n) | 求数值 n 的双曲正切值 |
32 | TO_NUMBER (char [,fmt]) | 将 CHAR、VARCHAR、VARCHAR2 等类型的字符串转换为 DECIMAL 类型的数值 |
33 | TRUNC(n[,m]) | 截取数值函数 |
34 | TRUNCATE(n[,m]) | 截取数值函数,等价于 TRUNC(n[,m]) |
35 | TO_CHAR(n [, fmt [, 'nls' ] ]) | 将数值类型的数据转换为 VARCHAR 类型输出 |
36 | BITAND(n1, n2) | 求两个数值型数值按位进行 AND 运算的结果 |
37 | NANVL(n1, n2) | 有一个参数为空则返回空,否则返回 n1 的值 |
38 | REMAINDER(n1, n2) | 计算 n1 除 n2 的余数,余数取绝对值更小的那一个 |
39 | TO_BINARY_FLOAT(n) | 将 number、real 或 double 类型数值转换成 binary float 类型 |
40 | TO_BINARY_DOUBLE(n) | 将 number、real 或 float 类型数值转换成 binary double 类型 |
更多达梦函数请移步 达梦数据库官方地址: https://eco.dameng.com