規範化 SQL DDL/DML 腳本模板：加欄、建表、存儲過程、游標、循環最佳實踐

設計原則

• 冪等性：腳本可重複執行不報錯（IF NOT EXISTS、DROP IF EXISTS）。
• 顯式事務：DDL 隱式提交，DML 批量操作建議顯式 BEGIN/COMMIT。
• 命名規範：表 snake_case、列 snake_case、索引 idx_表_列、外鍵 fk_表_引用表、存儲過程 sp_動詞_名詞。
• 版本管理：每腳本含版本號、作者、日期、變更說明；配合 Flyway/Liquibase 執行。

1. 安全加欄（冪等）

-- MySQL 8.0+
ALTER TABLE query_data_source
ADD COLUMN IF NOT EXISTS is_cross_table TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否跨表查詢';

-- 兼容 5.7：需用 INFORMATION_SCHEMA 判斷
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'query_data_source'
      AND COLUMN_NAME = 'is_cross_table'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE query_data_source ADD COLUMN is_cross_table TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否跨表查詢''',
    'SELECT ''Column already exists'' AS msg'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

2. 存儲過程標準模板（CRUD）

DROP PROCEDURE IF EXISTS sp_plsystem_insert;
DELIMITER //
CREATE PROCEDURE sp_plsystem_insert(
    IN p_id SMALLINT,
    IN p_name VARCHAR(20),
    IN p_flag CHAR(2)
)
BEGIN
    INSERT INTO pl_system (pl_system_id, pl_system_name, pl_system_flag)
    VALUES (p_id, p_name, p_flag);
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS sp_plsystem_update;
DELIMITER //
CREATE PROCEDURE sp_plsystem_update(
    IN p_id SMALLINT,
    IN p_name VARCHAR(20),
    IN p_flag CHAR(2)
)
BEGIN
    UPDATE pl_system
    SET pl_system_name = p_name,
        pl_system_flag = p_flag
    WHERE pl_system_id = p_id;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS sp_plsystem_delete;
DELIMITER //
CREATE PROCEDURE sp_plsystem_delete(IN p_id SMALLINT)
BEGIN
    DELETE FROM pl_system WHERE pl_system_id = p_id;
END//
DELIMITER ;

3. 建表冪等腳本

DROP TABLE IF EXISTS pl_system;
CREATE TABLE pl_system (
    pl_system_id   TINYINT UNSIGNED NOT NULL COMMENT '系統ID',
    pl_system_name VARCHAR(20)      NULL COMMENT '系統名稱',
    pl_system_flag CHAR(2)          NULL COMMENT '系統標誌',
    PRIMARY KEY (pl_system_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系統基礎信息表';

4. 游標循環處理（批量業務邏輯）

DROP PROCEDURE IF EXISTS sp_batch_assign_dept;
DELIMITER //
CREATE PROCEDURE sp_batch_assign_dept()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_user_id VARCHAR(8);
    DECLARE cur CURSOR FOR
        SELECT pl_user_id FROM pl_user WHERE p_cancel_judge = 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_user_id;
        IF done THEN LEAVE read_loop; END IF;

        INSERT INTO pl_user_actor_dept (dept_id, pl_actor_system_id, pl_user_id)
        SELECT department_id, pl_system_id, pl_user_id
        FROM ms_user_right
        WHERE ms_user_right.pl_user_id = v_user_id
          AND department_id NOT IN (
              SELECT department_id FROM pl_department
              WHERE d_parent = (SELECT department_id FROM pl_user WHERE pl_user_id = v_user_id)
                 OR department_id = (SELECT department_id FROM pl_user WHERE pl_user_id = v_user_id)
          );
    END LOOP;
    CLOSE cur;
END//
DELIMITER ;

5. WHILE 循環批量造數

DROP PROCEDURE IF EXISTS sp_insert_students;
DELIMITER //
CREATE PROCEDURE sp_insert_students(IN p_count INT)
BEGIN
    DECLARE v_i INT DEFAULT 0;
    WHILE v_i < p_count DO
        INSERT INTO student (name, gender, score, address)
        VALUES (CONCAT('STU_', v_i), 'M', 100, 'FUJIAN');
        SET v_i = v_i + 1;
    END WHILE;
END//
DELIMITER ;

-- 調用
CALL sp_insert_students(200000);

執行與驗證清單

1. 開發庫執行腳本，無報錯。
2. SHOW CREATE TABLE pl_system; 確認結構。
3. CALL sp_plsystem_insert(1, 'Test', 'OK'); SELECT * FROM pl_system; 驗證 CRUD。
4. CALL sp_batch_assign_dept(); 檢查目標表增量。
5. 納入 CI/CD：Flyway migrate 自動執行版本化腳本。