明月看潮生

明月看潮生

关注
发信
关注(28)粉丝(399)

青少年编程与数学 02-002 Sql Server 数据库应用 12课题、存储过程编写

课题摘要:

本课题讲解了SQL Server中存储过程的编写和操作。存储过程是数据库中预编译的SQL语句集合，能提高性能，确保数据安全性，并通过参数化提高灵活性。它们可以用于数据检索、修改和定义等任务，并可通过EXEC语句调用。存储过程的创建使用CREATE PROCEDURE，修改使用ALTER PROCEDURE，删除使用DROP PROCEDURE。SQL Server还提供了多种内置存储过程，如sp_helpdb和sp_configure，用于执行各种数据库管理任务。通过一个示例，展示了存储过程在订单处理中的应用，包括插入订单、更新状态和计算总价。

一、存储过程

在SQL Server中，存储过程（Stored Procedure）是一种在数据库中预编译的SQL语句集合，它被保存在数据库中，可以通过一个调用语句来执行。存储过程可以接收输入参数、返回输出参数，以及返回单个结果集或多个结果集。

存储过程的主要特点包括：

  1. 封装性：存储过程将SQL逻辑封装在数据库内部，使得应用程序代码更加简洁，减少了重复的数据库操作代码。

  2. 性能优化：由于存储过程在创建时会进行编译和优化，因此在执行时通常比单独的SQL语句执行得更快。

  3. 重用性：存储过程可以被不同的应用程序和用户重复调用，提高了代码的重用性。

  4. 安全性：通过限制直接对数据库表的访问，存储过程可以作为访问控制的一种手段，只允许执行存储过程中定义的操作。

  5. 事务管理：存储过程可以包含事务控制语句，如BEGIN TRANSACTION、COMMIT和ROLLBACK，以确保数据的完整性。

  6. 参数化：存储过程可以接受参数，使得调用更加灵活，可以根据需要传递不同的值来执行不同的操作。

  7. 返回结果：存储过程可以返回一个或多个结果集，也可以返回值，如通过RETURN语句。

创建存储过程的基本语法如下：

CREATE PROCEDURE procedure_name
    [@parameter1 datatype [= default_value]], [@parameter2 datatype [= default_value]]
AS
BEGIN
    -- SQL statements
END

例如，创建一个简单的存储过程来查询员工信息：

CREATE PROCEDURE GetEmployeeInfo
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END

然后可以通过以下方式调用这个存储过程：

EXEC GetEmployeeInfo @EmployeeID = 123;

这将返回员工ID为123的员工信息。

二、分类

在SQL Server中，存储过程可以根据不同的标准进行分类。以下是几种常见的分类方式：

  1. 按功能分类：

    • 数据检索存储过程：用于查询数据库并返回结果集，通常不修改数据。
    • 数据修改存储过程：用于插入、更新或删除数据库中的数据。
    • 数据定义存储过程：用于创建、修改或删除数据库对象，如表、视图、索引等。

  2. 按参数分类：

    • 无参数存储过程：不接受任何输入参数。
    • 有参数存储过程：接受一个或多个输入参数，可以是输入参数、输出参数或两者都有。

  3. 按返回结果分类：

    • 返回单个结果集的存储过程：执行后返回一个结果集。
    • 返回多个结果集的存储过程：可以返回多个结果集，通常通过在存储过程内部使用SELECT语句实现。

  4. 按事务处理分类：

    • 非事务存储过程：不涉及事务控制，或者事务控制完全由调用者管理。
    • 事务存储过程：在存储过程内部管理事务，使用BEGIN TRANSACTION、COMMIT和ROLLBACK等语句。

  5. 按复杂度分类：

    • 简单存储过程：只包含一些基本的SQL语句，执行简单的数据库操作。
    • 复杂存储过程：包含复杂的逻辑，可能使用循环、条件语句、临时表、游标等高级SQL特性。

  6. 按调用方式分类：

    • 本地存储过程：只能在创建它们的数据库服务器上调用。
    • 远程存储过程：可以在远程服务器上调用，需要适当的网络配置和权限设置。

  7. 按执行环境分类：

    • 用户定义存储过程：由数据库用户创建和使用的存储过程。
    • 系统存储过程：由SQL Server提供，用于管理数据库系统的功能和操作。

  8. 按是否可重入分类：

    • 可重入存储过程：可以被其他存储过程调用，且不依赖于外部变量的状态。
    • 不可重入存储过程：在执行期间可能会被其他存储过程调用，可能会依赖于外部变量的状态。

这些分类并不是互斥的，一个存储过程可能同时属于多个分类。例如，一个存储过程可以既是数据修改存储过程，又是有参数存储过程，还是事务存储过程。

三、创建

在SQL Server中创建存储过程，你可以使用CREATE PROCEDURE语句。以下是创建存储过程的基本步骤和示例：

基本语法

CREATE PROCEDURE procedure_name
    [@parameter1 datatype [= default_value] [OUTPUT | INPUTOUTPUT]],
    [@parameter2 datatype [= default_value] [OUTPUT | INPUTOUTPUT]],
    ...
AS
BEGIN
    -- SQL statements
    -- You can include any number of SQL statements
END

参数说明

  • procedure_name：你想要创建的存储过程的名称。
  • @parameter1, @parameter2, …：存储过程的参数，使用@前缀。参数可以是输入参数（INPUT）、输出参数（OUTPUT）或两者兼有（INPUTOUTPUT）。
  • datatype：参数的数据类型。
  • default_value：参数的默认值，如果未提供值，则使用此默认值。
  • OUTPUT：指定参数为输出参数，存储过程可以修改其值，并在存储过程执行完毕后返回给调用者。
  • INPUTOUTPUT：指定参数既可以作为输入也可以作为输出。

示例

1. 创建一个无参数的存储过程
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) FROM Employees;
END
2. 创建一个有输入参数的存储过程
CREATE PROCEDURE GetEmployeeByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END
3. 创建一个有输入输出参数的存储过程
CREATE PROCEDURE AddNewEmployee
    @EmployeeName NVARCHAR(100),
    @EmployeeID INT OUTPUT
AS
BEGIN
    INSERT INTO Employees (EmployeeName) VALUES (@EmployeeName);
    SET @EmployeeID = SCOPE_IDENTITY(); -- 获取新插入员工的ID
END

在这个例子中，@EmployeeID是一个输出参数，存储过程在插入新员工记录后，将新生成的员工ID赋值给这个参数。

创建存储过程时，你可以在其中使用各种SQL语句，包括数据查询、数据修改、事务控制等，以实现复杂的数据库操作。

四、调用

在SQL Server中，调用（或执行）存储过程非常简单。你可以使用EXEC或EXECUTE关键字来执行存储过程。以下是调用存储过程的基本语法和示例。

基本语法

EXEC [procedure_name [@parameter1 = value1 [, @parameter2 = value2] ...]];

或者

EXECUTE [procedure_name [@parameter1 = value1 [, @parameter2 = value2] ...]];

示例

假设你已经创建了以下存储过程：

  1. 无参数的存储过程：
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) FROM Employees;
END

调用这个存储过程：

EXEC GetEmployeeCount;

或者

EXECUTE GetEmployeeCount;
  1. 有输入参数的存储过程：
CREATE PROCEDURE GetEmployeeByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END

调用这个存储过程，并传递一个部门ID：

EXEC GetEmployeeByDepartment @DepartmentID = 10;

或者

EXECUTE GetEmployeeByDepartment @DepartmentID = 10;
  1. 有输入输出参数的存储过程：
CREATE PROCEDURE AddNewEmployee
    @EmployeeName NVARCHAR(100),
    @EmployeeID INT OUTPUT
AS
BEGIN
    INSERT INTO Employees (EmployeeName) VALUES (@EmployeeName);
    SET @EmployeeID = SCOPE_IDENTITY(); -- 获取新插入员工的ID
END

调用这个存储过程，并获取新插入员工的ID：

DECLARE @NewEmployeeID INT;
EXEC AddNewEmployee @EmployeeName = N'John Doe', @EmployeeID = @NewEmployeeID OUTPUT;
SELECT @NewEmployeeID;

在这个例子中，@NewEmployeeID是一个输出参数，存储过程执行完毕后，你可以使用SELECT @NewEmployeeID;来查看新插入员工的ID。

注意事项

  • 如果存储过程有返回值（使用RETURN语句），你可以通过SELECT语句来获取这个返回值：
DECLARE @ReturnValue INT;
EXEC @ReturnValue = YourStoredProcedure;
SELECT @ReturnValue;

  • 如果存储过程没有参数，你仍然需要使用EXEC或EXECUTE关键字，即使后面没有跟任何参数。

  • 确保在调用存储过程时，传递的参数类型和顺序与存储过程中定义的参数相匹配。

通过这些基本的调用方法，你可以灵活地执行存储过程，实现各种数据库操作。

五、修改

在SQL Server中，如果你需要修改已经存在的存储过程，你可以使用ALTER PROCEDURE语句。这个语句允许你更改存储过程的参数、逻辑或返回类型等。下面是如何修改存储过程的基本步骤和示例。

基本语法

ALTER PROCEDURE procedure_name
    [@parameter1 datatype [= default_value] [READONLY]],
    [@parameter2 datatype [= default_value] [READONLY]],
    ...
AS
BEGIN
    -- SQL statements
    -- You can include any number of SQL statements
END

示例

假设你有一个存储过程GetEmployeeByDepartment，它接受一个部门ID作为参数，并返回该部门的所有员工信息。现在，你想要修改这个存储过程，使其能够接受两个参数：部门ID和员工状态（如在职或离职），并根据这两个参数过滤结果。

原始存储过程
CREATE PROCEDURE GetEmployeeByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END
修改后的存储过程

使用ALTER PROCEDURE来修改这个存储过程：

ALTER PROCEDURE GetEmployeeByDepartment
    @DepartmentID INT,
    @EmployeeStatus NVARCHAR(50) = 'Active' -- 添加一个默认参数
AS
BEGIN
    SELECT * FROM Employees 
    WHERE DepartmentID = @DepartmentID 
    AND EmployeeStatus = @EmployeeStatus;
END

在这个修改中，我们添加了一个新的参数@EmployeeStatus，并在查询中使用了这个参数来过滤结果。同时，我们为这个新参数设置了一个默认值'Active'，这意味着如果调用存储过程时没有指定@EmployeeStatus的值，它将默认为'Active'。

注意事项

  • 参数顺序：在ALTER PROCEDURE语句中，参数的顺序很重要。如果原始存储过程已经包含参数，新添加的参数应该放在原始参数之后。
  • 参数默认值：你可以为参数设置默认值，这样在调用存储过程时，如果未提供该参数的值，将使用默认值。
  • 修改逻辑：在ALTER PROCEDURE中，你可以修改存储过程的逻辑，包括添加、删除或修改SQL语句。
  • 权限问题：确保你有足够的权限来修改存储过程。通常，你需要有对该数据库的ALTER权限。

使用ALTER PROCEDURE可以灵活地调整存储过程以适应不断变化的需求，而无需删除并重新创建存储过程。这样可以减少对数据库的干扰，并保持数据库的稳定性。

六、删除

在SQL Server中，如果你需要删除一个不再需要的存储过程，你可以使用DROP PROCEDURE语句。这个语句会从数据库中完全移除指定的存储过程。

基本语法

DROP PROCEDURE [schema_name.]procedure_name;
  • schema_name：存储过程所属的模式（schema）的名称。如果未指定，默认为dbo。
  • procedure_name：要删除的存储过程的名称。

示例

假设你有一个名为GetEmployeeByDepartment的存储过程，现在你想要删除它。

DROP PROCEDURE GetEmployeeByDepartment;

如果你的存储过程属于非dbo模式，你需要指定模式名称：

DROP PROCEDURE mySchema.GetEmployeeByDepartment;

注意事项

  • 权限：确保你有足够的权限来删除存储过程。通常，你需要有对该数据库的DROP权限。
  • 依赖性：在删除存储过程之前，确保没有其他数据库对象（如视图、触发器或其他存储过程）依赖于它。否则，删除存储过程可能会破坏这些对象的功能。
  • 备份：在删除存储过程之前，考虑是否需要备份存储过程的代码，特别是如果你不确定未来是否还需要它。
  • 测试：在生产环境中删除存储过程之前，建议在测试环境中进行测试，以确保删除操作不会对数据库的其他部分产生不良影响。

使用DROP PROCEDURE语句可以有效地管理数据库中的存储过程，保持数据库的整洁和高效。

七、应用示例

我们通过一个具体的例子来展示如何编写和使用 SQL Server 中的存储过程。我们将创建一个存储过程，该存储过程用于处理订单，包括插入新的订单、更新订单状态以及计算订单的总价。

步骤 1: 创建必要的表结构

首先，我们需要创建一些基本的表结构。假设我们有一个数据库，其中包含以下表格：

  • Customers: 包含客户信息。
  • Orders: 包含订单信息。
  • OrderDetails: 包含订单详情（每个订单项）。
  • Products: 包含产品信息。
CREATE TABLE Customers (
    CustomerID int IDENTITY PRIMARY KEY,
    CustomerName nvarchar(100),
    ContactName nvarchar(50),
    Country nvarchar(50)
);

CREATE TABLE Orders (
    OrderID int IDENTITY PRIMARY KEY,
    CustomerID int,
    OrderDate datetime,
    Status nvarchar(20),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderDetails (
    OrderDetailID int IDENTITY PRIMARY KEY,
    OrderID int,
    ProductID int,
    Quantity int,
    UnitPrice money,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

CREATE TABLE Products (
    ProductID int IDENTITY PRIMARY KEY,
    ProductName nvarchar(100),
    UnitPrice money
);

步骤 2: 插入一些测试数据

接下来，我们需要向这些表中插入一些测试数据：

INSERT INTO Customers (CustomerName, ContactName, Country)
VALUES ('Company A', 'John Doe', 'USA'),
       ('Company B', 'Jane Smith', 'Canada');

INSERT INTO Orders (CustomerID, OrderDate, Status)
VALUES (1, '2024-01-15', 'New'),
       (2, '2024-01-16', 'Processing');

INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES (1, 1, 5, 100.00),
       (1, 2, 2, 50.00),
       (2, 3, 3, 150.00);

INSERT INTO Products (ProductName, UnitPrice)
VALUES ('Product X', 100.00),
       ('Product Y', 50.00),
       ('Product Z', 150.00);

步骤 3: 创建存储过程

现在，我们将创建一个存储过程来处理订单的不同方面。这个存储过程将包括以下功能：

  1. 插入新的订单。
  2. 更新订单状态。
  3. 计算订单总价。
CREATE PROCEDURE dbo.ProcessOrder
    @CustomerID int,
    @OrderDate datetime,
    @Status nvarchar(20),
    @ProductID int,
    @Quantity int,
    @UnitPrice money,
    @OrderID int = NULL OUTPUT
AS
BEGIN
    -- 插入新的订单
    IF @OrderID IS NULL
    BEGIN
        INSERT INTO Orders (CustomerID, OrderDate, Status)
        VALUES (@CustomerID, @OrderDate, @Status);
        
        -- 获取新插入的订单ID
        SET @OrderID = SCOPE_IDENTITY();
    END

    -- 插入订单详情
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
    VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice);

    -- 更新订单状态
    UPDATE Orders
    SET Status = @Status
    WHERE OrderID = @OrderID;

    -- 计算订单总价
    DECLARE @TotalAmount money;
    SELECT @TotalAmount = SUM(od.Quantity * od.UnitPrice)
    FROM OrderDetails od
    WHERE od.OrderID = @OrderID;

    -- 输出订单总价
    PRINT 'Total Amount for Order ' + CAST(@OrderID AS nvarchar) + ': ' + CAST(@TotalAmount AS nvarchar);
END;

步骤 4: 调用存储过程

最后，我们可以编写一个脚本来调用这个存储过程，并查看输出结果：

DECLARE @OrderID int;

-- 插入新订单并获取订单ID
EXEC dbo.ProcessOrder @CustomerID = 1, @OrderDate = '2024-01-17', @Status = 'New', @ProductID = 1, @Quantity = 3, @UnitPrice = 100.00, @OrderID = @OrderID OUTPUT;

-- 输出订单ID
PRINT 'New Order ID: ' + CAST(@OrderID AS nvarchar);

-- 更新订单状态
EXEC dbo.ProcessOrder @OrderID = @OrderID, @Status = 'Processing', @ProductID = 1, @Quantity = 3, @UnitPrice = 100.00;

-- 查看所有订单信息
SELECT o.OrderID, o.OrderDate, o.Status, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS TotalAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
GROUP BY o.OrderID, o.OrderDate, o.Status, c.CustomerName;

这个示例展示了如何使用存储过程来插入新订单、更新订单状态以及计算订单总价。存储过程可以有效地封装复杂的业务逻辑，并且可以在多个地方重用，提高代码的可维护性和可读性。

八、内置存储过程

SQL Server 提供了许多内置存储过程（Stored Procedures），这些存储过程可以用于执行常见的数据库管理任务，如备份、恢复、登录管理等。内置存储过程通常以 sp_ 开头，并且不需要用户显式地创建它们。以下是一些常见的内置存储过程及其用途：

数据库管理

  • sp_helpdb: 获取数据库的帮助信息。
  • sp_helpindex: 获取索引的帮助信息。
  • sp_renamedb: 重命名数据库。

对象管理

  • sp_helptext: 获取存储过程、函数或视图的文本。
  • sp_rename: 重命名数据库对象。
  • sp_tables: 列出数据库中的表。
  • sp_columns: 列出表中的列。

用户和权限管理

  • sp_grantdbaccess: 向用户授予数据库访问权限。
  • sp_grantlogin: 向用户授予登录权限。
  • sp_adduser: 添加数据库用户。
  • sp_addrole: 添加数据库角色。
  • sp_addrolemember: 将用户添加到角色中。
  • sp_denylogin: 拒绝用户的登录权限。

安全管理

  • sp_change_password: 更改用户的密码。
  • sp_dropuser: 删除数据库用户。
  • sp_droprolemember: 从角色中移除用户。

备份和恢复

  • sp_addlinkedserver: 添加链接服务器。
  • sp_dropserver: 删除链接服务器。
  • sp_attach_db: 附加数据库。
  • sp_detach_db: 分离数据库。
  • sp_renamedb: 重命名数据库。
  • sp_helpdevice: 获取物理设备的帮助信息。
  • sp_addumpdevice: 添加备份设备。
  • sp_dropdevice: 删除备份设备。
  • sp_restore: 恢复数据库。
  • sp_addmessage: 添加消息。
  • sp_dropmessage: 删除消息。

诊断和维护

  • sp_configure: 显示或更改配置选项。
  • sp_dbcmptlevel: 设置或显示兼容级别。
  • sp_dbmajorversion: 设置或显示主版本号。
  • sp_dbminorversion: 设置或显示次版本号。
  • sp_dbhelp: 获取数据库的帮助信息。
  • sp_dboption: 设置或显示数据库选项。
  • sp_dbsetting: 设置或显示数据库设置。
  • sp_dbstatus: 获取数据库的状态信息。
  • sp_dbusers: 获取数据库用户列表。
  • sp_dbtables: 获取数据库表列表。
  • sp_dbcolumns: 获取数据库表中的列信息。
  • sp_dbindexes: 获取数据库表中的索引信息。

其他

  • sp_help: 获取数据库对象的帮助信息。
  • sp_lock: 获取锁信息。
  • sp_msforeachtable: 对每个表执行命令。
  • sp_msforeachdb: 对每个数据库执行命令。
  • sp_MSforeachdb: 对每个数据库执行命令（注意大小写区别）。
  • sp_MSforeachfile: 对每个文件执行命令。
  • sp_MSforeachdir: 对每个目录执行命令。

复制相关

  • sp_addarticle: 添加复制文章。
  • sp_addsubscription: 添加订阅。
  • sp_addpushsubscription_agent: 添加推送订阅代理。
  • sp_addpullsubscription: 添加拉取订阅。
  • sp_addlogreader_agent: 添加日志阅读代理。
  • sp_addmerge_agent: 添加合并代理。
  • sp_addsnapshot_agent: 添加快照代理。
  • sp_add_distribution: 添加发布。
  • sp_add_publication: 添加出版物。
  • sp_add_job: 添加作业。

这些存储过程提供了丰富的工具来管理和维护 SQL Server 数据库。使用这些存储过程可以帮助开发者和 DBA 更高效地完成日常任务。当然，SQL Server 的内置存储过程远不止这些，详细的列表和使用方法可以在 Microsoft 的官方文档中找到。

10-29 23:36
Powered by LMLPHP ©2024 明月看潮生 0.001352