为所有工资表添加员工确认字段_安全版.sql
4.84 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
-- ============================================
-- 为所有工资表添加员工确认字段（安全版）
-- 功能：支持员工确认工资条，确认后工资数据不可修改
-- 创建时间：2025年
-- 说明：此版本会先检查字段是否存在，避免重复添加
-- ============================================
-- 说明：lq_employee_salary_statistics 表在创建时已包含这些字段，无需添加
-- 使用存储过程安全添加字段（如果字段不存在则添加）
DELIMITER $$
DROP PROCEDURE IF EXISTS AddEmployeeConfirmFields$$
CREATE PROCEDURE AddEmployeeConfirmFields()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tableName VARCHAR(100);
DECLARE tableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN (
'lq_salary_statistics',
'lq_tech_teacher_salary_statistics',
'lq_assistant_salary_statistics',
'lq_store_manager_salary_statistics',
'lq_director_salary_statistics',
'lq_major_project_teacher_salary_statistics',
'lq_major_project_director_salary_statistics',
'lq_tech_general_manager_salary_statistics',
'lq_business_unit_manager_salary_statistics'
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN tableCursor;
read_loop: LOOP
FETCH tableCursor INTO tableName;
IF done THEN
LEAVE read_loop;
END IF;
-- 检查并添加 F_EmployeeConfirmStatus
SET @sql = CONCAT('SELECT COUNT(*) INTO @colCount FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = ''', tableName, '''
AND COLUMN_NAME = ''F_EmployeeConfirmStatus''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @colCount = 0 THEN
SET @sql = CONCAT('ALTER TABLE ', tableName, '
ADD COLUMN F_EmployeeConfirmStatus INT NOT NULL DEFAULT 0 COMMENT ''员工确认状态（0=未确认，1=已确认）''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT('已为表 ', tableName, ' 添加字段 F_EmployeeConfirmStatus') AS result;
END IF;
-- 检查并添加 F_EmployeeConfirmTime
SET @sql = CONCAT('SELECT COUNT(*) INTO @colCount FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = ''', tableName, '''
AND COLUMN_NAME = ''F_EmployeeConfirmTime''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @colCount = 0 THEN
SET @sql = CONCAT('ALTER TABLE ', tableName, '
ADD COLUMN F_EmployeeConfirmTime DATETIME NULL COMMENT ''员工确认时间''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT('已为表 ', tableName, ' 添加字段 F_EmployeeConfirmTime') AS result;
END IF;
-- 检查并添加 F_EmployeeConfirmRemark
SET @sql = CONCAT('SELECT COUNT(*) INTO @colCount FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = ''', tableName, '''
AND COLUMN_NAME = ''F_EmployeeConfirmRemark''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @colCount = 0 THEN
SET @sql = CONCAT('ALTER TABLE ', tableName, '
ADD COLUMN F_EmployeeConfirmRemark VARCHAR(500) NULL COMMENT ''员工确认备注''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT('已为表 ', tableName, ' 添加字段 F_EmployeeConfirmRemark') AS result;
END IF;
END LOOP;
CLOSE tableCursor;
END$$
DELIMITER ;
-- 执行存储过程
CALL AddEmployeeConfirmFields();
-- 删除存储过程
DROP PROCEDURE IF EXISTS AddEmployeeConfirmFields;
-- ============================================
-- 验证字段是否添加成功
-- ============================================
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN (
'lq_salary_statistics',
'lq_tech_teacher_salary_statistics',
'lq_assistant_salary_statistics',
'lq_store_manager_salary_statistics',
'lq_director_salary_statistics',
'lq_major_project_teacher_salary_statistics',
'lq_major_project_director_salary_statistics',
'lq_tech_general_manager_salary_statistics',
'lq_business_unit_manager_salary_statistics',
'lq_employee_salary_statistics'
)
AND COLUMN_NAME LIKE '%Confirm%'
ORDER BY TABLE_NAME, ORDINAL_POSITION;