存储过程基本了解

文章目录

• 介绍
• 存储过程示例
• • 1. 目的
  • 2. 输入参数
  • 3. 输出参数
  • 4. 执行逻辑
  • 5. 返回值
  • 6. 示例用法
  • 7. 注意事项
  • 存储过程的关键字有哪些
  • ���单实操

    介绍

    存储过程是一组预编译的SQL语句，以及流程控制语句，封装在数据库服务器中并可以被重复调用。它们可以接收参数、执行逻辑和返回结果。存储过程通常用于实现复杂的业务逻辑和数据操作，提供了以下几个主要优势：

    1. 代码重用和模块化： 存储过程可以将复杂的业务逻辑封装成一个可重复调用的单元。这样可以提高代码的重用性和维护性，避免了重复编写相同的代码逻辑。
    2. 性能优化： 存储过程在数据库服务器上进行预编译，并可被缓存，从而提供更高的性能。通过减少网络开销和减少解析时间，存储过程可以显著提升查询和事务处理的性能。
    3. 安全性和权限控制： 存储过程可以通过授权机制实现对数据库对象的访问控制。数据库管理员可以授予或撤销用户对存储过程的执行权限，从而保护敏感数据和确保数据的安全性。
    4. 减少数据传输量： 存储过程可以在数据库服务器上执行大量的数据处理和计算，只将结果返回给客户端，减少了数据传输的量，提高了网络传输效率。
    5. 事务处理和数据一致性： 存储过程可以包含多个SQL语句，并可以在一个事务中执行。这样可以确保逻辑上相关的操作要么全部成功要么全部失败，从而保持数据的一致性。
    6. 简化客户端代码： 存储过程可以将复杂的业务逻辑移至数据库服务器端，减少了客户端的代码量，使客户端更加简洁和易于维护。

    存储过程示例

    1. 目的

    获取特定客户的信息，包括客户姓名、电话号码和地址。

    2. 输入参数

    • @CustomerID (INT)：客户ID，用于指定要查询的客户。

      3. 输出参数

      • @CustomerName (VARCHAR)：客户姓名。
      • @PhoneNumber (VARCHAR)：客户电话号码。
      • @Address (VARCHAR)：客户地址。

        4. 执行逻辑

        CREATE PROCEDURE GetCustomerInfo    @CustomerID INTASBEGIN    SELECT         CustomerName,         PhoneNumber,         Address    FROM Customers    WHERE CustomerID = @CustomerID;END;

        5. 返回值

        无

        6. 示例用法

        EXEC GetCustomerInfo @CustomerID = 123;

        7. 注意事项

        • 调用该存储过程时，需传入有效的客户ID。
        • 请注意存储过程中涉及到的表和字段名称，确保与实际数据库结构匹配。

          下面是一个示例的复杂存储过程代码，该存储过程用于计算订单总金额并更新订单状态：

          CREATE PROCEDURE CalculateOrderTotalAndSetStatus    @OrderID INTASBEGIN    DECLARE @TotalAmount DECIMAL(10, 2);    DECLARE @ItemCount INT;    DECLARE @OrderStatus VARCHAR(20);    -- 计算订单总金额    SELECT @TotalAmount = SUM(UnitPrice * Quantity)    FROM OrderDetails    WHERE OrderID = @OrderID;    -- 获取订单中商品数量    SELECT @ItemCount = COUNT(*)    FROM OrderDetails    WHERE OrderID = @OrderID;    -- 根据订单总金额和商品数量设置订单状态    IF @TotalAmount > 1000        SET @OrderStatus = 'High Value';    ELSE        SET @OrderStatus = 'Normal';    -- 更新订单信息表中的订单状态和总金额    UPDATE Orders    SET TotalAmount = @TotalAmount,        ItemCount = @ItemCount,        Status = @OrderStatus    WHERE OrderID = @OrderID;    PRINT 'Order total amount calculated and status updated successfully.';END;

          在上面的示例中，存储过程CalculateOrderTotalAndSetStatus接收一个订单ID作为输入参数，然后执行以下操作：

          1. 计算订单的总金额；
          2. 获取订单中商品的数量；
          3. 根据总金额设置订单状态为“High Value”或“Normal”；
          4. 更新订单信息表中的总金额、商品数量和状态。

          存储过程的关键字有哪些

          1. CREATE PROCEDURE：用于创建存储过程。
          2. ALTER PROCEDURE：用于修改现有存储过程的定义。
          3. DROP PROCEDURE：用于删除存储过程。
          4. EXEC 或 EXECUTE：用于执行存储过程。
          5. WITH ENCRYPTION：用于加密存储过程的源代码，以保护存储过程的逻辑。
          6. WITH RECOMPILE：用于指示数据库引擎在每次执行存储过程时重新编译存储过程。
          7. AS：用于指定存储过程的主体部分。
          8. BEGIN 和 END：用于定义存储过程的代码块。
          9. DECLARE：用于声明变量或游标。
          10. SET：用于给变量赋值。
          11. SELECT：用于从表中检索数据。
          12. UPDATE、INSERT、DELETE：用于更新、插入和删除数据。
          13. IF、ELSEIF、ELSE：用于条件控制。
          14. WHILE、BEGIN…END WHILE：用于循环控制。
          15. RETURN：用于从存储过程中返回值。
          16. OUTPUT：用于输出参数。
          17. INOUT：用于输入输出参数。

          简单实操

          在存储过程中可以包含多个select语句，显示姓名中含有”张“字职工信息及其所在的仓库信息，

          create procedure pro_sql5asbegin   select * from 职工 where 姓名 like '%张%'   select * from 仓库 where 仓库号 in(select 仓库号 from 职工 where 姓名 like '%张%')endgoexecute pro_sql5

          带有输入参数的存储过程 找出三个数字中的最大数：

          create proc proc_sql6@num1 int,@num2 int,@num3 intasbegin   declare @max int   if @num1>@num2      set @max = @num1   else set @max = @num2   if @num3 > @max      set @max = @num3   print '3个数中最大的数字是:' + cast(@max as varchar(20))end

          求阶乘之和 如6! + 5! + 4! + 3! + 2! + 1

          execute proc_sql7 6  

          带有输入参数的数据查询功能的存储过程

          create proc proc_sql8  @mingz int,  @maxgz intasbegin   select * from 职工 where 工资>@mingz and 工资