达梦数据库常用对象

视图（VIEW）

定义

视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，它简化了用户数据模型，提供了逻辑数据独立性，实现了数据共享和数据的安全保密。

从系统实现的角度讲，视图是从一个或几个基表（或视图）导出的表，但它是一个虚表（不占用物理存储空间），即数据字典中只存放视图的定义（由视图名和查询语句组成），而不存放对应的数据，这些数据仍存放在原来的基表中。

视图简单来说就是把SQL查询语句封装为一个视图对象。

特性总结

无物理数据，占用空间极小；
数据实时刷新，始终查询最新基表数据；
可屏蔽敏感字段、简化多表联查；

动态性能视图

动态性能视图(以V或GV开头的视图）是数据库运行时生成的虚拟表，用于实时监控数据库实例的状态、性能和资源使用情况等。数据存储在内存中，由数据库实例动态更新。

可通过查询V$dynamic_tables获取所有动态性能视图信息

select * from v$dynamic_tables;

创建视图

说明：

1.需要create view 权限，且对sql查询语句中的表有select权限。
2.视图列名可省略，但不可同名。
3.FORCE表示强制创建，即在没有基表的时候，创建视图不报错，但此时视图无效。
4.对于可更新视图使用with check option, 表明向该视图INSERT或UPDATE数据时，新数据必须满足指定条件。

创建简单视图

-- 单表视图
CREATE or REPLACE VIEW v_cust_simple AS 
SELECT cust_id, cust_name, gender, age, create_time FROM customer;

SELECT * FROM v_cust_simple;

-- 其他用户DM2查询视图，必须赋予查询该视图的权限，否则及时用户可以查询基表，也不能查询到虚表视图信息。
SELECT * FROM DM1.customer;
SELECT * FROM DM1.v_cust_simple;

-- 可赋予视图查询权限
GRANT SELECT ON DM1.V_CUST_SIMPLE to dm2;

创建复杂视图，多表连接

CREATE OR REPLACE VIEW v_order_detail AS
SELECT
    o.order_id,
    c.cust_id,
    c.cust_name,
    c.phone,
    g.goods_id,
    g.goods_name,
    i.item_no,
    i.buy_num,
    i.item_price,
    o.total_amt,
    o.order_time,
    o.remark,
    CASE o.order_status
        WHEN 0 THEN '待支付'
        WHEN 1 THEN '已支付'
        WHEN 2 THEN '已发货'
        WHEN 3 THEN '已完成'
        WHEN 9 THEN '已取消'
    END AS order_status_text
FROM order_info o
INNER JOIN customer c ON o.cust_id = c.cust_id
INNER JOIN order_item i ON o.order_id = i.order_id
INNER JOIN goods g ON i.goods_id = g.goods_id;

-- 查询视图，直接使用，和查表一样
SELECT * FROM v_order_detail;

视图有效性

视图定义依赖于其基表或视图，如果基表被删除，或表结构、表名、权限等发生改变，可能导致视图失效
通过以下语句查看视图是否有效
SELECT SF_VIEW_EXPIRED('DM1','V_ORDER_DETAIL'); --返回0或1，0有效，1无效 

SELECT T.NAME,T.ID,T."TYPE$",T."SUBTYPE$",T.CRTDATE,T.VALID
FROM SYSOBJECTS T
WHERE T."SUBTYPE$"='VIEW';

视图更新

1.如果视图建在单个基表或单个可更新视图上，则该视图为可更新视图(对视图更新会影响到基表数据)；
2.如果视图定义中的查询包含DISTINCT、表达式、集合运算符、集函数、GROUPBY子句、多表连接等，则视图
一般不允许更新；
3.在不允许更新视图之上建立的视图也不允许更新。

查看视图是否可更新，read_only为N,表示可更新
select view_name, text, read_only from user_views;

更新视图，基表数据也被修改了
UPDATE v_cust_simple SET CUST_NAME='wangwang' WHERE CUST_ID=1;
SELECT * from v_cust_simple;
SELECT * from customer;

视图删除

--语法
DROP VIEW [IF EXISTS] [<模式名>.]<视图名> [RESTRICT | CASCADE];

DROP view DM1.V_CUST_SIMPLE;

说明：
1.删除视图必须是视图的拥有者或者有DBA权限；
2.视图删除后，引用了该视图的对象会变为无效状态；
3.视图删除后，用户在其上的权限也均自动取消（重新创建视图，需要重新赋予权限）。

视图字典

查看视图依赖，在视图所属用户会话下
SELECT * from USER_DEPENDENCIES

-- 查看用户拥有视图
SELECT * from user_views;

物化视图

定义

物化视图是从一个或几个基表导出的表，同视图相比，它存储了导出表的真实数据。本质其实就是预先计算存储查询结果，将查询结果保存到物理存储上。

当基表中的数据发生变化时，物化视图所存储的数据将变得陈旧，用户可以通过手动刷新或自动刷新来对数据进行同步。

特性	普通视图	物化视图
数据存储	仅保存查询逻辑语句（虚拟表），不存储数据	存储查询结果的物理数据（实体表）
查询性能	每次查询动态计算，可能较慢	直接读取预计算结果，速度极快
刷新机制	实时（查询时自动生成结果）	手动或自动定期刷新
数据实时性	实时	可基于事务刷新（实时），或手动、定时刷新（非实时，有延迟）
适用场景	简单查询、逻辑封装	复杂聚合、高频查询优化、历史数据快照

刷新选项

当基表中的数据发生变化时，物化视图所存储的数据将变得陈旧，此时可通过手动刷新或自动刷新来对数据进行同步。
物化视图的刷新选项包含：

刷新模式：增量刷新(FAST）、完全刷新(COMPLETE）、FORCE（默认）
FAST：根据表中数据更改记录增量刷新（需要先创建物化视图日志），可指定根据主键或rowid进行刷新。
complete: 删除表中所有记录，根据物化视图定义，重新生成。
FORCE：优先fast,否则complete。


刷新时机：什么时候刷新？定时刷、手工刷、自动刷、从不刷新。
on commit: 类似于实时刷新，基表commit时，自动刷新。
start with…………next:定时刷新，start with指定首次刷新时间，next指定自动刷新时间间隔
on demand：手工刷新，refresh命令刷新（默认）

创建物化视图

创建物化视图时，会产生以下字典对象：物化视图、物化视图表，或者时间触发器（根据刷新选项）。

-- 需要赋予创建物化视图权限
-- 创建默认的物化视图
CREATE  MATERIALIZED VIEW Dv_cust_simple AS 
SELECT cust_id, cust_name, gender, age, create_time FROM customer;

-- 指定刷新模式，时机，间隔一天刷新一次（该语句会自动创建一个时间触发器）
CREATE  MATERIALIZED VIEW Dv_cust_simple 
refresh with rowid start with sysdate next sysdate+1
AS 
SELECT cust_id, cust_name, gender, age, create_time FROM customer;

查询触发器，name后缀是物化视图id
SELECT * from sysobjects t where t.name like '%TRG%';

使用预建表创建物化分区

使用预建表创建物化视图的核心优势在于对物化视图底层物理存储结构获得精细控制权，尤其是实现分区、定制索引和选择存储格式；显著提升大型物化视图的刷新效率（尤其是增量刷新）和用户的查询性能。

使用预建表创建物化视图适用于：
数据量巨大的物化视图。
源表更新频繁，需要高效增量刷新的场景。
对物化视图的查询性能有极高要求的场景。
需要利用特定物理存储优化特性（分区、高级索引、列存等）的场景。
有严格存储、权限或生命周期管理需求的场景。

CREATE TABLE mtab_user_stat (
    cust_name    VARCHAR(50),
    order_cnt    BIGINT,    -- 用户订单数量
    total_money  DECIMAL(14,2)  -- 用户消费总金额
) TABLESPACE MAIN;

CREATE MATERIALIZED VIEW mv_user_stat
FOR mtab_user_stat ON PREBUILT TABLE
REFRESH ON DEMAND
AS
SELECT
    c.cust_name,
    COUNT(o.order_id) AS order_cnt,
    SUM(o.total_amt) AS total_money
FROM customer c
LEFT JOIN order_info o ON c.cust_id = o.cust_id
GROUP BY c.cust_name;

使用预建表创建物化视图后刷新
refresh materialized view mv_user_stat  complete;
SELECT * from mv_user_stat;

物化视图是否陈旧

SELECT owner,mview_name,REFRESH_METHOD,STALENESS from user_mviews;
查看STALENESS列，此时数据是新的

修改基表数据并提交，再次查询，数据已陈旧
UPDATE DM1.customer set cust_name='lili' where cust_id=2;
COMMIT;

此时需要刷新视图
refresh  MATERIALIZED VIEW  Dv_cust_simple;

删除

-- 语法
DROP MATERIALIZED VIEW [IF EXISTS] [<模式名>.]<物化视图名>;

DROP MATERIALIZED VIEW mv_user_stat;

序列（SEQUENCE）

序列（sequence）是 DM 数据库中的数据库实体之一。通过使用序列，多个用户可以产生和使用一组不重复的有序整数值。比如可以用序列来自动地生成主关键字值。序列通过提供唯一数值的顺序表来简化程序设计工作。

特性总结

序列是全局对象，可多张表，跨会话共用；
支持循环、缓存、最大最小值设置；
适合跨表唯一编号、订单号、业务流水号；
相比自增列更加灵活可控。

创建序列

需要有创建序列的权限

INCREMENT BY:步长
START WITH：起始
maxvalue、minvalue:最大最小值
cycle:达到最大后回到minvalue循环
cache: 预缓存 20 个序列值到内存
-- 创建序列
CREATE SEQUENCE seq_test INCREMENT BY 5 START WITH 1 maxvalue 30 MINVALUE 1 CYCLE CACHE 3;

--使用序列，序列1为起始，每次增加5
SELECT seq_test.currval; --获取当前值（首次使用，需要先获取nextval）
SELECT seq_test.nextval; -- 获取下一序列值

使用序列

表主键自增

CREATE SEQUENCE dm1.seq_test1 INCREMENT BY 1 START WITH 1 MINVALUE 1 ;

CREATE TABLE emp (
empid    NUMBER DEFAULT dm1.seq_test1.NEXTVAL PRIMARY KEY,   --主键使用序列自增
empname  VARCHAR2(100) NOT NULL,
email    VARCHAR2(100) UNIQUE,
hire_date DATE DEFAULT SYSDATE
);

-- 3、插入，不给empid字段，自动取序列值填充
INSERT INTO emp(empname, email) VALUES('李敏','limin@dameng.com');
COMMIT;

-- 查询验证
SELECT * FROM emp WHERE empname = '李敏';

序列字典

-当前用户下的序列信息
SELECT * FROM USER_SEQUENCES;
-用户能查询的的序列
SELECT * FROM ALL_SEQUENCES;
--数据库中所有序列
SELECT*FROMDBA_SEQUENCES;

同义词（SYNONYM）

定义

同义词是数据库对象的别名，可以为表、视图、序列、存储过程等创建简称，可简单理解为一个快捷方式。

同义词作用：用于简化数据库对象的访问路径，提高数据库的安全性和灵活性。

同义词分类：

说明	私有同义词	公有同义词
创建语句	`CREATE SYNONYM`	`CREATE PUBLIC SYNONYM`
创建权限	需要 `CREATE SYNONYM` 权限	需要 `CREATE PUBLIC SYNONYM` 权限
命名	不能与现有模式下对象名（如表、视图）重名	不能和已有公有同义词重名
访问方式	模式名。同义词名	同义词名
访问权限	拥有者可以访问，其他用户访问需赋予权限	无须额外权限，任何用户都可以访问
依赖关系	删除原对象后，同义词仍存在但访问会报错	删除原对象后，同义词仍存在但访问会报错

创建同义词

CREATE [OR REPLACE] [PUBLIC] SYNONYM [IF NOT EXISTS] [<模式名>.]<同义词名> 
FOR [<模式名>.]<对象名>;

1. 命名规则
   - 公有同义词、私有同义词二者可以重名。
   - 私有同义词不能和本模式下已有的表 / 视图等目标对象重名。
2. 对象支持范围：FOR后面的对象，可以是本地对象，也可以是 DBLINK 远程数据库对象。
权限要求
   - 创建私有同义词：需要 CREATE SYNONYM权限。
   - 创建公有同义词：需要更高权限 `CREATE PUBLIC SYNONYM`（一般 DBA 才具备）。
3.模式名约束
   公有同义词：创建不能指定模式名，直接写同义词名字。
   私有同义词：别的用户访问私有同义词，必须带上模式名.同义词名。

创建私有同义词
CREATE OR REPLACE SYNONYM dm1.co FOR customer;
select * from co;

创建公共同义词
DBA用户创建
CREATE PUBLIC SYNONYM gd FOR DM1.GOODS;
DM2用户可不指定模式名，直接访问同义词

删除同义词

  -- 删除模式 A 下的同义词 S1。
  DROP SYNONYM A.S1;
  
  --  删除公共同义词 S2
  DROP PUBLIC SYNONYM S2;

同义词字典

 user_synonyms(用户拥有的同义词)、 all_synonyms（用户可访问的同义词）

显示了同义词所属，以及指向对象
SELECT * from user_synonyms;

数据库链接（DBLINK）

定义

外部链接对象（LINK）是 DM 中的一种特殊的数据库实体对象，它记录了远程数据库的连接和路径信息，用于建立与远程数据的联系。

通过多台数据库主库间的相互通讯，用户可以透明地操作远程数据库的数据，使应用程序看起来只有一个大型数据库。用户远程数据库中的数据请求，都被自动转换为网络请求，并在相应结点上实现相应的操作。用户可以建立一个数据库链接，以说明一个对象在远程数据库中的访问路径。这个链接可以是公用的（数据库中所有用户使用），也可以是私有的（只能被某个用户使用）。

DBLINK使用场景

DBLINK是轻量级跨库解决方案，通过合理使用DBLINK，可以实现高效的跨数据库协作，适用实时场景，但需权
衡性能、安全性和维护成本。DBLINK使用场景如下：
1.实时数据同步：从远程数据库直接拉取最新数据到本地报表系统。
2.跨库联合查询：如合并本地数据库和远程数据库的数据。
3.备份与恢复：将数据通过DBLINK传输到备份数据库。

DBLINK分类

普通DBLINK：只有创建语句中标识的用户才可以访问远程数据库
公用DBLINK：本地数据的所有用户都可以访问远程数据库

创建使用DBLINK

语法格式

CREATE [OR REPLACE] [PUBLIC] LINK [IF NOT EXISTS] &lt;外部链接名> CONNECT ['&lt;连接库类型>'] WITH &lt;登录名> IDENTIFIED BY &lt;登录口令> USING '&lt;外部链接串>' [&lt;OPTION子句>];

参数说明：
-- PUBLIC 此链接对象是否能够被创建者之外的用户引用；
-- &lt; 外部链接名 > 数据库链接的名称；
-- &lt; 连接库类型 > 目前只支持 DPI、ODBC、GATEWAY、DAMENG 或 ORACLE，默认为 DPI。DPI 和 DAMENG 用于连接达梦数据库；ODBC 可以连接所有支持 ODBC 协议的数据库；ORACLE 用于连接 ORACLE 数据库；GATEWAY 可以使用达梦的 ODBC 驱动，需配置扩展选项 GWDSN，指定远程 GATEWAY 的 DSN，通过 GATEWAY 中转，连接到远程数据库；
-- &lt; 登录名 > 登录用户名称；
-- &lt; 登录口令 > 登录用户口令；
-- &lt;DPI 外部链接串 > 通过 DPI 接口访问远程达梦数据库，不需要进行额外配置；

通过外部链接，可以对远程服务器的对象进行查询或进行增删改操作，可以调用远程的过程或函数。

使用外部链接进行查询或增删改的语法格式与普通格式基本一致，唯一的区别在于指定外部链接对象时需要使用如下格式

&lt;对象名> @ &lt;外部链接名>

-- 创建私有DBLINK，仅当前会话用户可使用
CREATE  LINK dmlink CONNECT 'DPI' WITH SYSDBA IDENTIFIED BY DMdata456 USING '192.168.43.3:5234';

-- 使用DBLINK查询远程数据库
SELECT * from DM1.GOODS@dmlink

-- 创建公共DBLINK，所有会话用户可使用
CREATE public LINK dmPlink CONNECT 'DPI' WITH SYSDBA IDENTIFIED BY DMdata456 USING '192.168.43.3:5234';

删除外部链接

-- 语法
  DROP [PUBLIC] LINK [IF EXISTS] [&lt;模式名>.]&lt;外部链接名>;
  
--删除外部连接LINK1
  DROP LINK LINK1;

DBLINK字典

SELECT * from DBA_DB_LINKS;  --查看所有DBLINK信息
SELECT * FROM SYS.V$DBLINK; --查询DBLINK连接信息