SQL开发与常用对象应用实测

一、实验范围

项目	内容
数据库	本机DM8实例DMDISDEMO
客户端	DISQL、DM管理工具
模式实验	W6_SALES、W6_HR
对象实验	CODEX_TASK_LOG、CODEX_SEQ_BATCH、CODEX_TASK_AUDIT、CODEX_V_ORDER_SUMMARY等
验证重点	对象状态、数据结果、跨模式权限、脚本执行

常用对象可以分成五层：

层次	对象	主要作用
存储	表空间、表、分区表、临时表	保存和组织数据
命名与安全	用户、模式、角色、权限	确定对象归属和访问边界
访问	索引、视图、物化视图	改善访问路径或提供查询接口
编号与逻辑	序列、自增列、触发器、过程、函数、包	生成编号并封装数据库端逻辑
连接与别名	同义词、外部链接	简化名称或访问远端对象

这张表主要是帮我把对象放回各自的位置。刚开始学的时候，很容易把用户和模式、序列和自增列、视图和物化视图混在一起。实际用过一遍后会发现，它们解决的根本不是同一类问题。先搞清楚对象负责什么，再去记语法，会轻松很多。

二、模式与权限实验

模式是对象的命名空间，用户是登录和权限主体。DM会为用户建立同名默认模式，但两者不是同一个概念。

实验中创建W6_SALES和W6_HR两个模式，并在其中创建同名CONFIG表：

CREATE SCHEMA W6_SALES AUTHORIZATION SYSDBA
/
CREATE SCHEMA W6_HR AUTHORIZATION SYSDBA
/

两张CONFIG表结构相同，分别写入SALES和HR。切换当前模式后，未限定名称指向不同对象：

SET SCHEMA W6_SALES;
SELECT * FROM CONFIG;

SET SCHEMA W6_HR;
SELECT * FROM CONFIG;

实验结果为：

当前模式	SELECT * FROM CONFIG结果
W6_SALES	SALES
W6_HR	HR

使用W6_SALES.CONFIG完全限定名时，不受当前模式影响。

随后创建W6_REPORT用户，只授予销售模式查询权限：

GRANT SELECT ON W6_SALES.CONFIG TO W6_REPORT;

该用户查询W6_SALES.CONFIG成功，查询W6_HR.CONFIG返回-5504权限错误。这一组结果很直观：

模式决定对象定位
GRANT决定能否访问
SET SCHEMA不会自动获得权限

三、表、数据类型与约束

建表时最容易出现的做法是“先全部用VARCHAR，后面再说”。短期看确实省事，但金额排序、日期计算、索引使用和数据校验都会变麻烦。订单场景里，我把金额定义成DECIMAL，时间使用TIMESTAMP，状态值用CHECK限制范围。

CREATE TABLE SALES_ORDER(
  ORDER_ID BIGINT PRIMARY KEY,
  ORDER_NO VARCHAR(32) NOT NULL,
  CUSTOMER_ID BIGINT NOT NULL,
  ORDER_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  STATUS VARCHAR(12) DEFAULT 'NEW' NOT NULL,
  TOTAL_AMOUNT DECIMAL(14,2) DEFAULT 0 NOT NULL,
  CONSTRAINT UQ_ORDER_NO UNIQUE(ORDER_NO),
  CONSTRAINT CK_ORDER_STATUS
    CHECK(STATUS IN('NEW','PAID','FINISHED','CANCELLED'))
);

这张表同时使用主键、业务唯一键、非空、默认值和检查约束。主键负责标识一行，ORDER_NO负责保证订单号不重复。两者经常会被当成一回事，实际含义并不相同。

3.1 常用数据类型怎么选

数据内容	这次采用的思路
主键、批次号	根据增长规模选择INT或BIGINT
金额	使用DECIMAL明确精度和小数位
日期时间	使用DATE或TIMESTAMP，方便范围查询和计算
状态码	使用短字符，并配合CHECK约束
名称、备注	使用VARCHAR并给出实际长度
长文本、附件	分别评估CLOB和BLOB

浮点类型更适合允许误差的测量或统计数据，不太适合精确到分的金额。字符列也不是越长越省事，全部写成VARCHAR(4000)会让模型失去约束意义，还可能给行存储和临时结果带来额外负担。

3.2 不同表类型的认识

普通B树表是联机业务最常见的选择。除此之外，我还学习了堆表、HUGE表、分区表和全局临时表。

表类型	比较典型的使用场景
普通表	订单、客户、配置等日常业务表
堆表	批量导入暂存、短期交换数据
HUGE表	大规模历史明细和分析读取
分区表	按月、按地区管理的大表
全局临时表	报表中间结果、批处理临时数据

例如订单历史可以按日期范围分区：

CREATE TABLE ORDER_HIS(
  ORDER_ID BIGINT,
  ORDER_DATE DATE,
  TOTAL_AMOUNT DECIMAL(14,2)
)
PARTITION BY RANGE(ORDER_DATE)(
  PARTITION P202607 VALUES LESS THAN(DATE '2026-08-01'),
  PARTITION P202608 VALUES LESS THAN(DATE '2026-09-01'),
  PARTITION PMAX VALUES LESS THAN(MAXVALUE)
);

分区不只是为了查询快一点。按月装载、归档和清理数据时，它也能把处理范围缩小。前提是SQL条件能对应到分区键，不然还是可能访问很多分区。

3.3 DML和事务

INSERT、UPDATE、DELETE本身不难，真正容易出问题的是条件范围和提交时机。我现在更习惯先用同样的WHERE查询，再执行更新：

SELECT ORDER_ID, STATUS
FROM SALES_ORDER
WHERE ORDER_ID=1001;

UPDATE SALES_ORDER
SET STATUS='PAID'
WHERE ORDER_ID=1001
  AND STATUS='NEW';

COMMIT;

把原状态写进条件，可以减少重复更新。批量修改时还要留意日志量、锁和执行时间，不能因为一条SQL语法没问题，就直接在大表上跑。

四、自增列与序列实验

4.1 自增列

本机创建CODEX_TASK_LOG，主键使用IDENTITY(1,1)，状态和时间使用默认值：

CREATE TABLE CODEX_TASK_LOG(
  LOG_ID BIGINT IDENTITY(1,1) PRIMARY KEY,
  TASK_NAME VARCHAR(100) NOT NULL,
  STATUS VARCHAR(20) DEFAULT 'NEW',
  CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

连续插入两行时不提供LOG_ID，结果自动生成1和2；第一行未提供状态，数据库补为NEW。

自增列适合单表代理主键。它简化了插入，但回滚、迁移和并发都可能造成编号空洞，因此不能直接承担“必须连续”的业务流水号。

4.2 序列

实验序列从1000开始、步长为1、缓存20：

CREATE SEQUENCE CODEX_SEQ_BATCH
START WITH 1000
INCREMENT BY 1
CACHE 20;

SELECT CODEX_SEQ_BATCH.NEXTVAL FROM DUAL;
SELECT CODEX_SEQ_BATCH.NEXTVAL FROM DUAL;
SELECT CODEX_SEQ_BATCH.CURRVAL FROM DUAL;

结果如下：

操作	返回值
第一次NEXTVAL	1000
第二次NEXTVAL	1001
随后CURRVAL	1001

NEXTVAL推进序列，CURRVAL读取当前会话最近取得的值。序列独立于表，可以被多个过程、触发器或批处理共享。

五、触发器状态审计实验

为了记录任务状态变化，创建AFTER UPDATE行级触发器，将:OLD和:NEW写入审计表：

CREATE OR REPLACE TRIGGER CODEX_TRG_TASK_STATUS
AFTER UPDATE OF STATUS ON CODEX_TASK_LOG
FOR EACH ROW
BEGIN
  INSERT INTO CODEX_TASK_AUDIT
         (TASK_ID, OLD_STATUS, NEW_STATUS, CHANGE_TIME)
  VALUES (:OLD.LOG_ID, :OLD.STATUS, :NEW.STATUS, CURRENT_TIMESTAMP);
END;
/

将任务状态由NEW更新为RUNNING后，主表状态发生变化，审计表自动生成一条记录：

字段	结果
OLD_STATUS	NEW
NEW_STATUS	RUNNING
CHANGE_TIME	自动记录

触发器没有替代原UPDATE，而是在同一个事务中附加审计逻辑。这个特点既方便，也容易把问题藏起来。我的理解是，触发器比较适合这种短小、固定的动作；如果里面塞进复杂循环或远程调用，后面排查会很痛苦。

六、视图与同义词实验

CODEX_V_ORDER_SUMMARY对订单按客户汇总，只暴露客户编号、订单数和总金额：

CREATE OR REPLACE VIEW CODEX_V_ORDER_SUMMARY AS
SELECT CUSTOMER_ID,
       COUNT(*) ORDER_COUNT,
       SUM(TOTAL_AMOUNT) TOTAL_AMOUNT
FROM CODEX_IDX_ORDER
GROUP BY CUSTOMER_ID;

查询结果中，两个测试客户的数据如下：

客户	订单数	总金额
1	5	5550
9527	5	4670

普通视图保存查询定义，基础表变化后会重新计算。对调用方来说，只要视图输出列保持稳定，就不用每次重复写GROUP BY，也看不到不需要暴露的备注等明细列。

随后为视图创建同义词：

CREATE SYNONYM CODEX_ORDER_SUMMARY
FOR CODEX_V_ORDER_SUMMARY;

SELECT * FROM CODEX_ORDER_SUMMARY
WHERE CUSTOMER_ID=9527;

查询仍返回5笔订单、总金额4670。结果说明同义词没有复制数据，只是改变对象名称入口；它也不会自动授予目标对象权限。

物化视图会保存查询结果，适合计算量大、查询频繁、又能接受一定延迟的报表。它换来的是读取速度，付出的是存储和刷新成本。普通视图更像实时查询接口，物化视图更像一份需要定期刷新的结果快照。

七、索引与访问路径

订单实验同时使用客户和时间条件，建立了(CUSTOMER_ID, ORDER_TIME)复合索引。索引列顺序应服务真实SQL，每增加一个索引也会增加空间和写入成本。完整数据在索引专题中记录。

八、过程、函数和包

表、视图和触发器解决的是数据结构与事件问题，过程和函数更适合封装一段能够重复调用的数据库逻辑。

函数通常返回一个值。例如根据订单明细计算总金额：

CREATE OR REPLACE FUNCTION FN_ORDER_AMOUNT(
  P_ORDER_ID BIGINT
) RETURN DECIMAL(14,2)
AS
  V_AMOUNT DECIMAL(14,2);
BEGIN
  SELECT NVL(SUM(QUANTITY*UNIT_PRICE),0)
    INTO V_AMOUNT
    FROM ORDER_ITEM
   WHERE ORDER_ID=P_ORDER_ID;

  RETURN V_AMOUNT;
END;
/

过程更适合完成一组操作，例如重新计算金额并回写订单：

CREATE OR REPLACE PROCEDURE SP_REFRESH_ORDER_AMOUNT(
  P_ORDER_ID IN BIGINT
)
AS
  V_AMOUNT DECIMAL(14,2);
BEGIN
  V_AMOUNT:=FN_ORDER_AMOUNT(P_ORDER_ID);

  UPDATE SALES_ORDER
     SET TOTAL_AMOUNT=V_AMOUNT
   WHERE ORDER_ID=P_ORDER_ID;
END;
/

CALL SP_REFRESH_ORDER_AMOUNT(1001);

如果同一模块有多组过程、函数和变量，还可以使用包统一组织。包头对外公布接口，包体保存实现。这样调用入口比较清楚，也方便把订单相关逻辑放在一起。

过程里是否提交事务，需要和调用方式统一。我的习惯是尽量让调用方控制提交，避免过程内部突然COMMIT，导致外层事务无法完整回滚。

九、外部链接和跨库访问

外部链接让本地SQL引用远端对象，适合少量跨库查询、迁移和数据核对：

CREATE LINK LINK_REMOTE
CONNECT 'REMOTE_USER' IDENTIFIED BY '******'
USING 'REMOTE_SERVICE';

SELECT ORDER_ID, STATUS
FROM REMOTE_ORDER@LINK_REMOTE;

它看上去像访问本地表，实际还依赖网络、远端账号、字符集和远端数据库状态。如果把高频业务查询建立在不稳定的远程链路上，问题会变得很难定位。所以我更倾向于把外部链接用在边界清楚、数据量可控的场景中。

十、DISQL脚本执行

DISQL既可以交互执行SQL，也可以运行部署脚本。普通SQL使用分号结束，触发器、过程、函数和匿名块还需要用单独一行/提交程序块。我在本机完成了以下验证：
直接连接并通过@执行外部脚本；
父脚本通过@@调用同目录子脚本；
使用&1、&2替换脚本参数；
使用/NOLOG启动后再CONNECT登录。

直接在交互界面输入@@disql_child.sql时，因为没有父脚本目录上下文而找不到文件；先用绝对路径执行父脚本后，父脚本内的@@disql_child.sql成功运行。脚本执行最容易忽略的是路径、结束符、参数、编码和事务边界。

十一、对象状态验收

对象创建完成后，通过USER_OBJECTS检查名称、类型和状态：

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME LIKE 'CODEX_%'
ORDER BY OBJECT_TYPE, OBJECT_NAME;

最终检查中，3张实验表、4个教学索引、约束、序列、触发器、视图和同义词均为VALID。