第3章 高级查询与数据库编程 ​

WITH RANKED AS (
  SELECT O.ID, O.USER_ID, O.AMOUNT, O.PAY_TIME,
         ROW_NUMBER() OVER (PARTITION BY O.USER_ID ORDER BY O.PAY_TIME DESC) RN
    FROM T_ORDER O
)
SELECT R.*, U.USERNAME
  FROM RANKED R
  LEFT JOIN T_USER U ON U.ID = R.USER_ID
 WHERE R.RN <= 3
 ORDER BY R.USER_ID, R.RN;

CREATE OR REPLACE VIEW V_USER_STAT AS
SELECT U.ID USER_ID, U.USERNAME,
       COUNT(O.ID) ORDER_CNT, NVL(SUM(O.AMOUNT), 0) TOTAL_AMT
  FROM T_USER U LEFT JOIN T_ORDER O ON O.USER_ID = U.ID
 GROUP BY U.ID, U.USERNAME;

SELECT * FROM V_USER_STAT WHERE TOTAL_AMT > 1000 ORDER BY TOTAL_AMT DESC LIMIT 20;

-- 普通/唯一/组合索引：语法一致
CREATE INDEX IDX_ORDER_USER_TIME ON T_ORDER (USER_ID, PAY_TIME);
CREATE UNIQUE INDEX UK_ORDER_NO ON T_ORDER (ORDER_NO);

-- 函数索引：MySQL 8.0.13+ 才支持，达梦一直有
CREATE INDEX IDX_USER_UPPER ON T_USER (UPPER(USERNAME));

-- 位图索引：MySQL 没有的概念，低基数列（性别、状态）分析型查询利器
CREATE BITMAP INDEX BI_USER_STATUS ON T_USER (STATUS);

-- 全文索引
CREATE FULLTEXT INDEX FT_DOC_CONTENT ON T_DOC (CONTENT);

CREATE SEQUENCE SEQ_ORDER NOCYCLE CACHE 100 INCREMENT BY 1;

-- 场景1：SQL 里直接用（达梦允许 NEXTVAL 出现在 INSERT/SELECT 值位置）
INSERT INTO T_ORDER (ID, ORDER_NO) VALUES (SEQ_ORDER.NEXTVAL, 'A001');

-- 场景2：先取号再业务使用（如先生成订单号入库、再发消息）
SELECT SEQ_ORDER.NEXTVAL FROM DUAL;     -- Java 侧拿到 long 后再 INSERT 指定 ID

-- 场景3：IDENTITY 列手动补数后，重置自增起点
SELECT MAX(ID) FROM T_USER;              -- 假设 1000
ALTER TABLE T_USER MODIFY ID RESTART WITH 1001;   -- 防主键冲突，MySQL 的 ALTER ... AUTO_INCREMENT=1001 对应物

-- MySQL 风格存储过程（回忆用）
DELIMITER //
CREATE PROCEDURE p_stat(IN p_id BIGINT, OUT p_amt DECIMAL(18,2))
BEGIN
  SELECT SUM(amount) INTO p_amt FROM t_order WHERE user_id = p_id;
END //

-- 达梦写法：CREATE PROCEDURE ... AS/BEGIN/END（PL/SQL 味）
CREATE OR REPLACE PROCEDURE P_STAT (
  P_ID   IN  BIGINT,
  P_AMT  OUT DECIMAL(18,2)
)
AS
BEGIN
  SELECT NVL(SUM(AMOUNT), 0) INTO P_AMT FROM T_ORDER WHERE USER_ID = P_ID;
END;

-- 达梦函数：必须有 RETURN，且函数体里不能做 DML 提交
CREATE OR REPLACE FUNCTION F_USER_AMT (P_ID IN BIGINT)
RETURN DECIMAL(18,2)
AS
  V_AMT DECIMAL(18,2);
BEGIN
  SELECT NVL(SUM(AMOUNT), 0) INTO V_AMT FROM T_ORDER WHERE USER_ID = P_ID;
  RETURN V_AMT;
END;

SELECT F_USER_AMT(1) FROM DUAL;    -- 函数可直接进 SELECT

-- disql 里用匿名块调用（出参在块内接收）：
DECLARE
  V_AMT DECIMAL(18,2);
BEGIN
  P_STAT(P_ID => 1, P_AMT => V_AMT);          -- 达梦支持 => 关联实参（Oracle 味）
  DBMS_OUTPUT.PUT_LINE('总金额=' || V_AMT);   -- 需先执行 SET SERVEROUTPUT ON;
END;

-- 看所有存储程序源码（对照 MySQL: SHOW CREATE PROCEDURE）
SELECT * FROM ALL_SOURCE WHERE NAME = 'P_STAT' ORDER BY LINE;

CREATE OR REPLACE PACKAGE PKG_ORDER AS
  PROCEDURE CREATE_ORDER (P_USER_ID IN BIGINT, P_AMOUNT IN DECIMAL(18,2), P_ID OUT BIGINT);
  FUNCTION  USER_TOTAL  (P_USER_ID IN BIGINT) RETURN DECIMAL(18,2);
END PKG_ORDER;
/
CREATE OR REPLACE PACKAGE BODY PKG_ORDER AS

  FUNCTION USER_TOTAL (P_USER_ID IN BIGINT) RETURN DECIMAL(18,2) AS
    V_AMT DECIMAL(18,2);
  BEGIN
    SELECT NVL(SUM(AMOUNT), 0) INTO V_AMT FROM T_ORDER WHERE USER_ID = P_USER_ID;
    RETURN V_AMT;
  END;

  PROCEDURE CREATE_ORDER (P_USER_ID IN BIGINT, P_AMOUNT IN DECIMAL(18,2), P_ID OUT BIGINT) AS
  BEGIN
    INSERT INTO T_ORDER (ID, USER_ID, AMOUNT, PAY_TIME)
      VALUES (SEQ_ORDER.NEXTVAL, P_USER_ID, P_AMOUNT, SYSDATE)
      RETURNING ID INTO P_ID;
  END;

END PKG_ORDER;
/

-- 调用带包名前缀（入参出参在匿名块里接收）
DECLARE
  V_ID BIGINT;
BEGIN
  PKG_ORDER.CREATE_ORDER(1, 99.00, V_ID);
  DBMS_OUTPUT.PUT_LINE('新订单ID=' || V_ID);
END;
/
SELECT PKG_ORDER.USER_TOTAL(1) FROM DUAL;    -- 函数可直接进 SELECT

CREATE OR REPLACE TRIGGER TRG_USER_STATUS
BEFORE UPDATE OF STATUS ON T_USER
FOR EACH ROW
BEGIN
  IF :OLD.STATUS = 0 AND :NEW.STATUS = 1 THEN
    :NEW.CREATE_TIME := SYSDATE;      -- 行级触发器里用 :NEW/:OLD 读写新旧值
  END IF;
END;

CREATE TABLE T_3X_ITEM (
  USER_ID BIGINT, GOODS VARCHAR(50), AMOUNT DECIMAL(10,2));
INSERT INTO T_3X_ITEM VALUES (1, 'Java实战',   99.00);
INSERT INTO T_3X_ITEM VALUES (1, 'Java实战',   99.00);   -- 同一商品买两次，合计 198
INSERT INTO T_3X_ITEM VALUES (1, 'MySQL入门',  59.00);
INSERT INTO T_3X_ITEM VALUES (1, '达梦精解',   79.00);
INSERT INTO T_3X_ITEM VALUES (2, '算法导论',  120.00);
INSERT INTO T_3X_ITEM VALUES (2, '算法导论',   10.00);   -- 合计 130
INSERT INTO T_3X_ITEM VALUES (2, 'Redis设计',  45.00);
INSERT INTO T_3X_ITEM VALUES (2, 'Kafka实战',  88.00);
COMMIT;

WITH S AS (                                   -- 第一层：用户×商品 消费合计
  SELECT USER_ID, GOODS, SUM(AMOUNT) TOTAL
    FROM T_3X_ITEM GROUP BY USER_ID, GOODS
), R AS (                                     -- 第二层：分区内排名
  SELECT S.*, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY TOTAL DESC) RN
    FROM S
)
SELECT USER_ID, GOODS, TOTAL, RN
  FROM R WHERE RN <= 3 ORDER BY USER_ID, RN;   -- CTE 语法与 MySQL 8 完全一致，直接平移

CREATE TABLE T_3X_DEPT (
  ID INT PRIMARY KEY, NAME VARCHAR(30), PARENT_ID INT);
INSERT INTO T_3X_DEPT VALUES (1, 'CEO',      NULL);   -- 根节点：父为空
INSERT INTO T_3X_DEPT VALUES (2, '技术总监', 1);
INSERT INTO T_3X_DEPT VALUES (3, '产品总监', 1);
INSERT INTO T_3X_DEPT VALUES (4, '研发经理', 2);
INSERT INTO T_3X_DEPT VALUES (5, '测试经理', 2);
INSERT INTO T_3X_DEPT VALUES (6, '工程师',   4);
COMMIT;

SELECT LPAD(' ', 2 * (LEVEL - 1)) || NAME || '(' || ID || ')' AS TREE, LEVEL
  FROM T_3X_DEPT
 START WITH PARENT_ID IS NULL          -- 根：MySQL 8 要靠递归 CTE 手写，达梦白送 CONNECT BY
CONNECT BY PRIOR ID = PARENT_ID
 ORDER SIBLINGS BY NAME;               -- 同层级按名字排，用 ORDER SIBLINGS 而非 ORDER BY（后者会拆断树形）

-- MySQL 原版
DELIMITER $$
CREATE PROCEDURE SP_SUM_ORDER(IN p_user_id BIGINT)
BEGIN
  DECLARE v_amt DECIMAL(10,2);
  DECLARE v_sum DECIMAL(10,2) DEFAULT 0;
  DECLARE v_done INT DEFAULT 0;
  DECLARE c1 CURSOR FOR SELECT AMOUNT FROM T_ORDER WHERE USER_ID = p_user_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
  OPEN c1;
  read_loop: LOOP
    FETCH c1 INTO v_amt;
    IF v_done = 1 THEN LEAVE read_loop; END IF;
    SET v_sum = v_sum + v_amt;
  END LOOP;
  CLOSE c1;
  UPDATE T_STAT SET TOTAL = v_sum WHERE USER_ID = p_user_id;
END$$
DELIMITER ;

CREATE TABLE T_3X_ORDER (ID BIGINT PRIMARY KEY, USER_ID BIGINT, AMOUNT DECIMAL(10,2));
CREATE TABLE T_3X_STAT  (USER_ID BIGINT PRIMARY KEY, TOTAL DECIMAL(10,2));
INSERT INTO T_3X_ORDER VALUES (1, 9, 100.00);
INSERT INTO T_3X_ORDER VALUES (2, 9, 50.50);
INSERT INTO T_3X_ORDER VALUES (3, 9, 49.50);      -- 合计 200.00
INSERT INTO T_3X_STAT  VALUES (9, 0);
COMMIT;

CREATE OR REPLACE PROCEDURE SP_SUM_ORDER (P_USER_ID IN BIGINT) AS
  V_AMT DECIMAL(10,2);
  V_SUM DECIMAL(10,2) := 0;          -- 【调整点②】默认值用 := ，不是 DEFAULT
  V_DONE INT := 0;
  CURSOR C1 IS SELECT AMOUNT FROM T_3X_ORDER WHERE USER_ID = P_USER_ID;   -- 【调整点③】游标在声明段集中声明
BEGIN
  OPEN C1;
  LOOP                               -- 【调整点④】LOOP + EXIT WHEN，没有 LEAVE label
    FETCH C1 INTO V_AMT;
    EXIT WHEN C1%NOTFOUND;           -- 【调整点⑤】用 游标%NOTFOUND，不是 HANDLER 置位
    V_SUM := V_SUM + V_AMT;          -- 【调整点⑥】赋值直接 V_SUM := ...，没有 SET 语句
  END LOOP;
  CLOSE C1;
  UPDATE T_3X_STAT SET TOTAL = V_SUM WHERE USER_ID = P_USER_ID;
  COMMIT;                            -- 【调整点⑦】过程内提交需显式写（或交由调用方控制）
END;
/

CALL SP_SUM_ORDER(9);
SELECT * FROM T_3X_STAT WHERE USER_ID = 9;     -- TOTAL = 200.00（100+50.5+49.5 手算对账 ✓）

CREATE TABLE T_3X_USER (
  ID BIGINT PRIMARY KEY, NAME VARCHAR(50), STATUS SMALLINT DEFAULT 1);
CREATE SEQUENCE SEQ_3X_USER START WITH 101;

CREATE OR REPLACE PACKAGE PKG_USER AS
  PROCEDURE P_REGISTER (P_NAME IN VARCHAR, P_ID OUT BIGINT);        -- 注册
  PROCEDURE P_DISABLE  (P_ID IN BIGINT, P_ROWS OUT INT);            -- 禁用
  FUNCTION  F_STAT     (P_STATUS IN SMALLINT) RETURN INT;           -- 按状态人数
END PKG_USER;
/
CREATE OR REPLACE PACKAGE BODY PKG_USER AS

  PROCEDURE P_REGISTER (P_NAME IN VARCHAR, P_ID OUT BIGINT) AS
  BEGIN
    P_ID := SEQ_3X_USER.NEXTVAL;                    -- 白拿新号：序列在 PL/SQL 里直接赋值（3.5 知识点）
    INSERT INTO T_3X_USER (ID, NAME, STATUS) VALUES (P_ID, P_NAME, 1);
  END;

  PROCEDURE P_DISABLE (P_ID IN BIGINT, P_ROWS OUT INT) AS
  BEGIN
    UPDATE T_3X_USER SET STATUS = 0 WHERE ID = P_ID;
    P_ROWS := SQL%ROWCOUNT;                         -- 影响行数用 SQL%ROWCOUNT，对照 MySQL 的 ROW_COUNT()
  END;

  FUNCTION F_STAT (P_STATUS IN SMALLINT) RETURN INT AS
    V_CNT INT;
  BEGIN
    SELECT COUNT(*) INTO V_CNT FROM T_3X_USER WHERE STATUS = P_STATUS;
    RETURN V_CNT;
  END;

END PKG_USER;
/

SET SERVEROUTPUT ON;                 -- disql 里先开开关，否则 PUT_LINE 静默丢输出
DECLARE
  V_ID BIGINT;
  V_ROWS INT;
BEGIN
  PKG_USER.P_REGISTER('frank', V_ID);
  PKG_USER.P_REGISTER('grace', V_ID);          -- 第二次注册，拿 102
  DBMS_OUTPUT.PUT_LINE('最后注册ID=' || V_ID);   -- → 102（序列从 101 起，验证 NEXTVAL 递进）
  PKG_USER.P_DISABLE(101, V_ROWS);
  DBMS_OUTPUT.PUT_LINE('禁用影响行=' || V_ROWS); -- → 1
  DBMS_OUTPUT.PUT_LINE('启用人数=' || PKG_USER.F_STAT(1));   -- → 1（grace）
  DBMS_OUTPUT.PUT_LINE('禁用人数=' || PKG_USER.F_STAT(0));   -- → 1（frank）
  COMMIT;
END;
/

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
  FROM USER_TAB_COLUMNS
 WHERE COLUMN_NAME = 'AMOUNT'
 ORDER BY TABLE_NAME;