SQL Server 数据库操作实战：存储过程、触发器与安全配置

本文将详细介绍如何使用 SQL Server 创建存储过程、触发器并进行数据库安全配置。案例展示了根据姓名查询个人信息、根据性别查询个人数量、添加个人信息等操作，并通过代码示例说明如何调用存储过程。此外，还讲解了如何创建触发器以实现自动添加、更新和删除数据，以及如何创建用户和角色并授予权限，确保数据库安全。

数据准备

首先，我们准备一些数据，用于演示存储过程、触发器和安全配置的操作。

数据表：

CREATE TABLE dbo.person (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    birthdate DATE NOT NULL,
    id_number VARCHAR(18) NOT NULL
);

CREATE TABLE dbo.contact (
    id INT PRIMARY KEY,
    phone VARCHAR(20),
    email VARCHAR(50)
);

数据插入：

INSERT INTO dbo.person (id, name, gender, birthdate, id_number) VALUES
(1003, '本紫', '男', '1990-01-01', '410211199901011234'),
(1004, '李兰', '女', '1987-12-12', '320602198712121234'),
(1005, '易祺瑞', '男', '1985-11-11', '440308198511111234'),
(1006, '周笑临', '男', '1978-12-09', '530112197812091234'),
(1007, '余囡', '女', '1966-03-30', '620402196603301234');

8. 创建与应用存储过程

存储过程1：根据姓名查询个人信息

**功能描述：**根据输入的姓名查询个人信息，返回该人的姓名、性别、出生日期、身份证号码。

代码：

CREATE PROCEDURE dbo.get_person_info_by_name
    @name VARCHAR(50)
AS
BEGIN
    SELECT name, gender, birthdate, id_number
    FROM dbo.person
    WHERE name = @name
END

调用示例：

String sql = '{CALL dbo.get_person_info_by_name(?)}';
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setString(1, '易祺瑞');
    try (ResultSet rs = cs.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString('name') + ', ' + rs.getString('gender') + ', ' + rs.getDate('birthdate') + ', ' + rs.getString('id_number'));
        }
    }
}

调用结果：

[截图展示调用结果]

存储过程2：根据性别查询个人数量

**功能描述：**根据输入的性别查询个人数量，返回该性别的个人数量。

代码：

CREATE PROCEDURE dbo.get_person_count_by_gender
    @gender VARCHAR(10)
AS
BEGIN
    SELECT COUNT(*) AS count
    FROM dbo.person
    WHERE gender = @gender
END

调用示例：

String sql = '{CALL dbo.get_person_count_by_gender(?)}';
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setString(1, '男');
    try (ResultSet rs = cs.executeQuery()) {
        while (rs.next()) {
            System.out.println('男性数量：' + rs.getInt('count'));
        }
    }
}

调用结果：

[截图展示调用结果]

存储过程3：添加个人信息

**功能描述：**向个人信息表中添加一条记录，包括姓名、性别、出生日期、身份证号码。

代码：

CREATE PROCEDURE dbo.add_person_info
    @name VARCHAR(50),
    @gender VARCHAR(10),
    @birthdate DATE,
    @id_number VARCHAR(18)
AS
BEGIN
    INSERT INTO dbo.person (name, gender, birthdate, id_number)
    VALUES (@name, @gender, @birthdate, @id_number)
END

调用示例：

String sql = '{CALL dbo.add_person_info(?,?,?,?)}';
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setString(1, '张三');
    cs.setString(2, '男');
    cs.setDate(3, Date.valueOf('1990-01-01'));
    cs.setString(4, '110101199001010001');
    cs.executeUpdate();
}

调用结果：

[截图展示调用结果]

9. 创建与应用触发器

触发器1：在个人信息表中添加记录时，自动向联系方式表中添加一条记录，包括电话和邮箱。

**功能描述：**在个人信息表中添加记录时，自动向联系方式表中添加一条记录，包括电话和邮箱。

代码：

CREATE TRIGGER dbo.add_contact_info
ON dbo.person
FOR INSERT
AS
BEGIN
    INSERT INTO dbo.contact (id, phone, email)
    SELECT id, '', ''
    FROM inserted
END

测试结果：

[截图展示测试结果]

触发器2：在个人信息表中更新记录时，自动更新联系方式表中的电话和邮箱。

**功能描述：**在个人信息表中更新记录时，自动更新联系方式表中的电话和邮箱。

代码：

CREATE TRIGGER dbo.update_contact_info
ON dbo.person
FOR UPDATE
AS
BEGIN
    UPDATE dbo.contact
    SET phone = i.phone, email = i.email
    FROM dbo.contact c
    JOIN inserted i ON c.id = i.id
END

测试结果：

[截图展示测试结果]

触发器3：在个人信息表中删除记录时，自动删除联系方式表中的对应记录。

**功能描述：**在个人信息表中删除记录时，自动删除联系方式表中的对应记录。

代码：

CREATE TRIGGER dbo.delete_contact_info
ON dbo.person
FOR DELETE
AS
BEGIN
    DELETE FROM dbo.contact
    WHERE id IN (SELECT id FROM deleted)
END

测试结果：

[截图展示测试结果]

10. 数据库安全

（1）创建 SQL Server 身份验证的登录名“SQLUser01”，密码为“abc@123”，默认数据库为你自己创建的这个数据库；

步骤：

打开 SQL Server Management Studio (SSMS)。
在“对象资源管理器”中右键单击“安全性”->“登录名”。
选择“新建登录名”。
在“登录名 - 新建”窗口中，输入登录名“SQLUser01”和密码“abc@123”。
在“默认数据库”下拉列表中选择你创建的数据库。
点击“确定”按钮。

（2）在本数据库中创建 1 个 SQL Server 身份验证的数据库用户账户“db_user01”，并将其映射到登录名“SQLUser01”；

步骤：

在“对象资源管理器”中右键单击你的数据库，选择“新建”->“用户”。
在“用户 - 新建”窗口中，输入用户名“db_user01”。
选择“从服务器登录名映射”。
在“映射到的登录名”下拉列表中选择“SQLUser01”。
点击“确定”按钮。

（3）创建自定义角色“role01”，并且将数据库用户“db_user01”添加为该自定义角色的成员，然后为该角色授予相应的权限；

代码：

CREATE ROLE role01;
ALTER ROLE role01 ADD MEMBER db_user01;

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.person TO role01;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.contact TO role01;

（4）从角色“role01”收回部分权限内容：

代码：

REVOKE DELETE ON dbo.person FROM role01;
REVOKE DELETE ON dbo.contact FROM role01;

总结

本文介绍了在 SQL Server 中创建存储过程、触发器和进行安全配置的步骤和方法，并通过代码示例和截图展示了操作过程。通过学习本文，您可以更好地理解 SQL Server 数据库操作的原理和实践，并在实际项目中应用这些技术，提高数据库开发效率和安全性。